Help tuning an Oracle query
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:
FROM ratings r, unused_fill f
WHERE f.qh_key=r.qh_key AND
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)
......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.
Last edited by sb2c; 12-09-2003 at 01:57 PM.
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"
Are you, or have you tried, running this as a stored procedure in an Oracle package?
Microsoft MVP (Visual Basic)
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL