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
SELECT bo24_sales.applcreationdate, ...
FROM opportunities bo16_opportunities,
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***