Home » SQL & PL/SQL » SQL & PL/SQL » Need help in tuning (Oracle 10)
Need help in tuning [message #417201] Thu, 06 August 2009 08:33 Go to next message
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 #417202 is a reply to message #417201] Thu, 06 August 2009 08:34 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The execution plan might be able to tell you that.
Re: Need help in tuning [message #417287 is a reply to message #417201] Thu, 06 August 2009 23:39 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Inquiry on calling procedures within a procedure
Next Topic: unresolved error
Goto Forum:
  


Current Time: Thu Nov 07 23:59:17 CST 2024