Help tuning an Oracle query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Help tuning an Oracle query

  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 01:57 PM.

  2. #2
    Nigel McFarlane is offline Former moderator, account closed
    Join Date
    Nov 2003
    Location
    I live on the 'net
    Posts
    229
    I don't have my Oracle books at this desk, but there are a number of things you can do.

    In the schema, the more hints you give to the server, the better the queries. Try checking for FOREIGN KEY clauses on your table definitions.

    I think the optimiser has detected that one table is 10 times bigger than the other and is using that table to drive the query. That's why you're getting an inner loop join rather than a sort merge join.

    The simplest way to speed the query is to put indexes on every column in the SELECT clause, in this case on two columns. That makes sense for big aggregate functions anyway.
    Nigel McFarlane, Author: "Firefox Hacks", "Rapid Application Development with Mozilla"

  3. #3
    Join Date
    Dec 2003
    Posts
    2,750
    Are you, or have you tried, running this as a stored procedure in an Oracle package?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

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