I do not do a lot of Oracle SQL, nor do I have any Oracle SQL analysis tools. I am asking if there are any obvious tweaks or changes to the query listed below to make Oracle handle it better. I dont know if there is a better way to specify joins and/or sub-queries that would help performance. The query is returning in about 3-4 minutes currently. I have included what I thought was other relevent information but let me know if more info would help.

Thanks for any suggestions.
KB.

Oracle 9i, I dont have the exact HW specs but this is a heafty production server.

The inner select is returning about 30 rows. The idp table has about 19 million rows total but only about 80,000 should match each of the V values from the inner select.
There is an index on the item column for idp and ki tables.
There is an index on the dg and V column for the idp table.
================================================== ===============
select /*+ RULE*/ distinct V,
     MAX( TO_CHAR(keyvaluedate, 'YYYYMM')) as INVDate
 from idp inner join ki on idp.item = ki.item
 where dg = 128 and V in
     (select distinct V from pp
       where dg = 128 and back = 1)
 group by V
 order by V, INVDate
================================================== ===============

just a bump - no ideas from the experts ??

Is there a reason that you are forcing the query to use the rule based optimizer rather than the cost based optimizer?

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

I am not sure. Adding the Rule hint seemed to speed it up by a minute or so and I had one dba that may prevent a table scan. But with out the ability to analyse the query plan, I am not sure what effect this has.

Just looking for suggestions on whether there is a more effective way to structure the query.