Re: SQL Tuning Case .... Basic Qs ?
Date: Thu, 20 Nov 2008 07:32:24 +0000
Rather than speculating about whether index use might be being prevented by some construction and whether it would help (sorry, reduce the CPU metric) to use them, it would be better to see actual explain plans and test timings along with table and index definitions etc.
I can't really see where you are going with "joining first not null columns then null allowed columns" - not a rule of thumb I've come across before.
Separate bitmap indexes MIGHT be worth a look for owneruserid, assigneduserid and salesownerid if there are enough queries like this to offset the additional index maintenance and locking overhead on insert/update/delete. However bitmap indexes are normally only recommended in warehouse type applications where tables can be populated in one shot, rather than in OLTP systems where rows are constantly being individually added and updated, so I would be very cautious about using them. (Also they are only available in Enterprise Edition, if that makes a difference here.)
From: Subodh Deshpande
Date: 19/11/08 09:12
-- http://www.freelists.org/webpage/oracle-l Received on Thu Nov 20 2008 - 01:32:24 CSTHi 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 possibleIf you can treat NULLs with some different value (eg nvl (c1,'ZZ') that index can understand and will not affect your result set use itSorry 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@infosys.com>
To: ORACLE-L <firstname.lastname@example.org>
Sent: Wednesday, 19 November, 2008 1:48:13
Subject: SQL Tuning Case .... Basic Qs ?
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") )