query performance question
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.
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