Need help in tuning [message #417201] |
Thu, 06 August 2009 08:33 |
prashas_d
Messages: 66 Registered: February 2007
|
Member |
|
|
Hi All,
I have a sql query with outer joins as shown below:
SELECT *
FROM PVACCOUNT14 PVA /* +use_index(PVA, ACCOUNT_PK) */,
PVACCOUNTATTRIBUTES PVT /* +use_index(PVT, ACCOUNTATTRIBUTES_PK) */,
PVACCOUNTPAYMENT PAP /* +use_index(PAP, ACCOUNTPAYMENT_PK) */,
PVPHYSICALPAYMENT PPP /* +use_index(PPP, PHYSICALPAYMENT_PK) */
WHERE (PVA.RANDOM_HASH BETWEEN 0 AND 999999 )
AND PAP.ACCOUNT_NUM = PVA.ACCOUNT_NUM
AND ((PAP.CREATED_DTM BETWEEN TO_DATE('20060101' ,'YYYYMMDDHH24MISS') AND TO_DATE('20090101' ,'YYYYMMDDHH24MISS') )
OR
(PAP.CANCELLED_DTM BETWEEN TO_DATE('20060101' ,'YYYYMMDDHH24MISS') AND TO_DATE('20090101' ,'YYYYMMDDHH24MISS') )
OR
(PAP.FAILED_DTM BETWEEN TO_DATE('20060101' ,'YYYYMMDDHH24MISS') AND TO_DATE('20090101' ,'YYYYMMDDHH24MISS') )
)
AND PVA.CURRENCY_CODE = 'IDR'
AND PVA.CUSTOMER_REF <> 'INTERNAL'
AND PVA.ACCOUNT_NUM = PVT.ACCOUNT_NUM (+)
AND PAP.CUSTOMER_REF = PPP.CUSTOMER_REF (+)
AND PAP.PHYSICAL_PAYMENT_SEQ = PPP.PHYSICAL_PAYMENT_SEQ (+)
ORDER BY PAP.ACCOUNT_NUM;
PVACCOUNT14 has primary key on ACCOUNT_NUM
PVACCOUNTATTRIBUTES has primary key on ACCOUNT_NUM
PVACCOUNTPAYMENT has primary key on PVACCOUNTPAYMENT
PVPHYSICALPAYMENT has primary key on CUSTOMER_REF
Can someone please let me know whether we can tune this query further?
Thanks in advance.
prashas_d.
|
|
|
|
Re: Need help in tuning [message #417287 is a reply to message #417201] |
Thu, 06 August 2009 23:39 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
I might have missed some change in syntax, but that was never the way hints were coded.
Probably a good thing too, because now the optimizer can do its job, instead of you trying to force it to do stuff.
Make sure your statistics are up-to-date, both table & index statistics and system statistics.
|
|
|
Re: Need help in tuning [message #417326 is a reply to message #417201] |
Fri, 07 August 2009 04:19 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Hints go after the SELECT, not after the table, and there is no USE_INDEX hint.
It's just as well, as (unless rows where VA.CURRENCY_CODE = 'IDR'
& PVA.CUSTOMER_REF <> 'INTERNAL' are a very small %age of your total) you probably want to use Full table scans and Hash joins.
Show us the execution plan for this query please.
|
|
|