Thanks Iggy, Ric and Niall and others who replied. I got busy with other production emergency work, I am going to test it today soon. On Fri, Feb 8, 2013 at 6:32 AM, Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>wrote: > > Ram, > > Any update on tuning this SQL statement? > > -- Iggy > > > > In the query plan that was posted by Ram, 42 minutes are spent to > retrieve 133,000 rows from WFCTOTAL and then all but one are thrown away by > joining to PAYCODEIMMFLAT. > > > > Based on the rowsource statistics in the query plan that was posted, > here's what I think the query is trying to do: > > > > SELECT [fields] FROM TKCSOWNER.PAYCODE A2 -- a unique row is retrieved > > > > INNER JOIN TKCSOWNER.PAYCODE1MMFLAT A3 ON (A3.GRANDPAYCODEID = > A2.PAYCODEID) -- 1:1 FK join > > INNER JOIN TKCSOWNER.WFCTOTAL A7 ON (A7.PAYCODEID = A3.PAYCODEID) -- 1:N > FK join; most records are filtered out > > INNER JOIN TKCSOWNER.WTKEMPLOYEE A6 ON (A6.EMPLOYEEID = A7.EMPLOYEEID) > -- 1:1 FK join > > INNER JOIN TKCSOWNER.MYPAYPERIOD A5 ON (A5.PAYRULEID = A6.PAYRULEID ) -- > 1:1 FK join > > INNER JOIN TKCSOWNER.LABORACCT A4 ON (A4.LABORACCTID = A7.LABORACCTID) > -- 1:1 FK join > > INNER JOIN TKCSOWNER.PERSON A1 ON (A1.PERSONID = A6.PERSONID) -- 1:1 FK > join > > > > WHERE A2.TYPE ='P' AND A2.NAME ='STIIP-INDICATOR' -- a unique row is > retrieved > > AND A7.NOTPAIDSW = 0 AND A7.APPLYDTM >= A3.EFFECTIVEDTM AND A7.APPLYDTM > < A3.EXPIRATIONDTM -- very important filter > > AND A5.PPSTARTDATEDTM <= A7.APPLYDTM AND A5.PPENDDATEDTM >= A7.APPLYDTM > > > > Based on this analysis, the right join order is A2, A3, A7, A6, A5, A4, > and A1. > > > > The question is how to influence Oracle into choosing this plan. Ram > could use hint injection to inject a LEADING hint because this is a query > from a canned application called Kronos that he cannot modify. > > > > I have to question whether Oracle is getting the right information about > primary keys and foreign keys. For example: > > > > Is PAYCODEID the primary key of PAYCODE? > > Is PAYCODE1MMFLAT(GRANDCODEID) a foreign key to PAYCODE and supported by > an index? > > Is WFCTOTAL(PAYCODEID) a foreign key to PAYCODE1MMFLAT and supported by > an index? > > -- Thanks, Ram. -- //www.freelists.org/webpage/oracle-l