SQL Tuning Case .... Basic Qs ?

  • From: VIVEK_SHARMA <VIVEK_SHARMA@xxxxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 19 Nov 2008 01:48:13 +0530

Folks

What standard best practices may be followed to Tune (Minimize the CPU 
Consumption) of the below SQL having 2 OR conditions?
NOTE - All Tables are relatively small in size i.e. upto 1 GB (NON-partitioned)

Fields which are part of the OR Conditions i.e.  
bo16_opportunities.assigneduserid, bo16_opportunities.salesownerid may have 
some NULL Values.
Qs Is breaking the single SQL into 3 separate SQLs & hence removing the OR 
statements Advisable? .. (the Outputs of these 3 break-down SQLs can later be 
merged at the Application level,)
Qs How can the Nulls (which would seemingly prevent index scan, if Null input 
value is passed) issue be overcome?
Qs Will partitioning help?
Other ideas pls?

Config - Oracle 10gR2 (NON-RAC) on HP-UX

Bad SQL:-

SELECT bo24_sales.applcreationdate, ...
  FROM opportunities bo16_opportunities,
       opportunity_products bo16_opportunity_products,
       products bo21_products,
       sales bo24_sales
 WHERE bo16_opportunity_products.productid = bo21_products.productid
   AND bo16_opportunities.opportunityid = bo16_opportunity_products.jobid(+)
   AND bo16_opportunities.opportunityid = bo24_sales.opportunityid(+)
   AND bo16_opportunity_products.bank_id = bo21_products.bank_id
   AND bo16_opportunities.bank_id = bo16_opportunity_products.bank_id(+)
   AND bo16_opportunities.bank_id = bo24_sales.bank_id(+)
   AND ((    (    (    (bo16_opportunities.mergedwith IS NULL)
                   AND (bo16_opportunities.status IN (:"SYS_B_11", :"SYS_B_12"))
                  )
              AND ((   (bo16_opportunities.owneruserid = :"SYS_B_13")
                    OR (   (bo16_opportunities.assigneduserid = :"SYS_B_14")
                       OR (bo16_opportunities.salesownerid = :"SYS_B_15")
                       )                   )                  )             )
         AND (ROWNUM <= :"SYS_B_16")
         AND bo16_opportunities.bank_id = :"SYS_B_17") )


**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely 
for the use of the addressee(s). If you are not the intended recipient, please 
notify the sender by e-mail and delete the original message. Further, you are 
not 
to copy, disclose, or distribute this e-mail or its contents to any other 
person and 
any such actions are unlawful. This e-mail may contain viruses. Infosys has 
taken 
every reasonable precaution to minimize this risk, but is not liable for any 
damage 
you may sustain as a result of any virus in this e-mail. You should carry out 
your 
own virus checks before opening the e-mail or attachment. Infosys reserves the 
right to monitor and review the content of all messages sent to or from this 
e-mail 
address. Messages sent to or from this e-mail address may be stored on the 
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

Other related posts: