Re: SQL Tuning Case .... Basic Qs ?

Hi Vivek,
primafacie, I think the design is incorrect as you are using 'OR' on different columns to get your resultset.
coming back to the query, usuage of OR is definately going to affect the performance and check whether usuage of ROWUN will affect the behavour of default indexes on your query. Think of joining first not null columns then null allowed columns, that too, as far as possible keeping the column sequence (from c1 to c10, c1, c3 are NN take first, then say c2, c4,c5 etc) in table unchanged.
By breaking down the sql I assume you mean inline or pivoting do it if possible
If you can treat NULLs with some different value (eg nvl (c1,'ZZ') that index can understand and will not affect your result set use it
Sorry I do not think partitioning can help unless it is application specific requirement, as again there are different partitions that you have to decide.
think of bitmap or reversey key index as per the occurance of data elements.

thanks and take care..subodh



________________________________
From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
To: ORACLE-L <oracle-l_at_freelists.org>
Sent: Wednesday, 19 November, 2008 1:48:13
Subject: SQL Tuning Case .... Basic Qs ?


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