View Single Post
  #1 (permalink)  
Old February 7th, 2005, 11:29 AM
kBusby kBusby is offline
Registered User
Join Date: Jan 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
================================================== ===============