SQL Tuning Case .... Basic Qs ?

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Wed, 19 Nov 2008 01:48:13 +0530
Message-ID: <69E1360E54B50C4A828A136C158E4742056059E18F@BLRKECMBX02.ad.infosys.com>

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***

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 18 2008 - 14:18:13 CST