DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Help tuning an Oracle query

Threaded View

  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Help tuning an Oracle query

    Hello,
    I am somewhat new to Oracle, and could use some help tuning a query. I have a view (view_unused_fill) that joins two tables:

    SELECT ...
    FROM ratings r, unused_fill f
    WHERE f.qh_key=r.qh_key AND
    f.network=r.abbr_traffic AND
    r.rpt_type='dma'

    The tables have indexes on the columns used in the join.

    Next what I need to do is get a list of min and max dates visible in this view, grouped by the contract_no. Here is that query:

    select v.contract_no tvno, min(trunc(v.fill_dt,'dd')) mindate, max(trunc(v.fill_dt,'dd')) maxdate
    from view_unused_fill v
    group by v.contract_no
    order by v.contract_no

    The problem is that this query takes 2 minutes to run. It seems like it should be possible to make it run faster.

    Here is an Explain Plan of the query. I don't know what this means, but shouldn't it be possible to prevent the full table access of UNUSED_FILL by using an index? There is an index but the optimizer does not seem to want to use it.

    SELECT STATEMENT Optimizer=CHOOSE
    ..SORT (GROUP BY)
    ....NESTED LOOPS
    ......TABLE ACCESS (FULL) OF UNUSED_FILL
    ......TABLE ACCESS (BY INDEX ROWID) OF RATINGS
    ........INDEX (UNIQUE SCAN) OF RATINGS_QHK_ABBR_TRAFFIC (UNIQUE)


    The RATINGS table contains about 1.7 million records.
    The UNUSED_FILL table contains about 180 thousand.

    Any help is appreciated.

    Thanks,
    sb2c.
    Last edited by sb2c; 12-09-2003 at 02:57 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center