07 Performance Improvement Consideration


Use UNION instead of OR

Consider using UNION when you have to use OR condition on 2 index columns

Example


Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s  where

s.productname="panasonic" or s.totalamount > 100

Oracle tuning: Execution time for an or on 2 indexed columns. Exec Plan estimates 25 seconds

Oracle tuning: Execution time for an or on 2 indexed columns. The plan estimates 25 seconds

instead use


Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s  where

s.productname='panasonic'

union all

Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s  where

s.totalamount > 100

Oracle tuning: replace or with union all if the or involves 2 indexed columns

Oracle tuning: replace or with union all if the or involves 2 indexed columns. This takes only 3 seconds

Remember this only applies only when both the columns are indexed.

Use UNION ALL instead of UNION

  • Avoid un-necessary sorts
  • don’t sort if order is not important
  • Union performs an extra operation called SORT UNIQUE, which is really expensive

Note that this is recommended when the result set for both the operations are the same.


Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s  where

s.productname='panasonic'

Union

Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s  where

s.totalamount > 100

Oracle union operation uses an extra sort unique operation which takes up more CPU

Oracle union operation uses an extra sort unique operation which takes up more CPU

instead use


Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s  where

s.productname='panasonic'

Union all

Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s where

s.totalamount > 100

Oracle tuning , union all operation is faster than union. In this example it takes 1% lesser of the CPU

Oracle tuning , union all operation is faster than union. In this example it takes 1% lesser of the CPU

Minimize Table lookups in a query

  • Where eve possible try to reduce the number of  table lookups in a query
  • this mostly applies to sub queries

Example

Select s.orderid, s.salesdate,s. totalamount, s.productname
from saleshistory s where
s.productID in
(select productID from product where name='mobile')
and
s.productID in
(select productID from product where color='black')
// The product table is used twice for look ups in 2 separate queries
multiple table look ups

multiple table look ups  returning almost 18 million bytes

instead use

Select s.orderid, s.salesdate,s. totalamount, s.productname
from saleshistory s where
s.productID in (select productID from product where name='mobile'
and color='black')
// instead use the product table once to do both the lookups
oracle tuning : single table lookups vs multiple table lookups in sub query

oracle tuning : single table lookups vs multiple table lookups in sub query. Returns 11 Million bytes compared to 18 M

EXIST vs IN & NOT EXIST vs NOT IN

General rule of thumb

  • Big Outer Query and small inner Query -> IN
  • Small Outer query and big Inner Query ->  EXIST

IN CLAUSE

Small Table  T2  and big table T1

select * from T1 where  x in (select y from T2)

is processed as

select * from t1, (select  distinct y from T2) t2  where t1.x=t2.y;

Exists Clause

Good index on T2.y

Small table T1 and big Table T2

select * from  t1 where exists (select null from t2 where y=x)

is processed as

for x  in (select * from T1)

loop

if (exists(select null from t2 where y=x.x)

then

output the record

end if

end loop

Use Exist Instead of Distinct

When eve you are joining tables with one to many relationship and trying to find DISTINCT values, try to use EXISTS instead of DISTINCT clause.

In the example below product has about 7 rows where as Sales has about a million rows.


SELECT distinct p.product_name

FROM sales a , product p

where p.product_ID=s.product_ID

Oracle tuning :Explain Plan using distinct in a one to many relationship

Oracle tuning : using distinct in a one to many relationship (Explain Plan)

It has used 3% of cpu, did a hash unique, hash join etc

Better SQL


select p.product_name 

FROM product p

where EXISTS (Select 'x' from sales s where s.product_ID=p.product_ID)

Oracle tuning explain plan for were exist instead of join

Oracle tuning explain plan for were exist instead of join

Notice the obvious differences. So when ever you are joining a one to many relationship try to find distinct values using the DISTINCT clause.

Reading same table multiple times

Reading identical data multiple times slows down the query . Why would you query the same table multiple times?

  • Used to get detail and summary
  • Used to get current and prior data
SELECT s.order_id, s.Sales_date, s. productname, s.total_amount, sr.total_jan_amount
from sales s (select product_name,  sum (totalamount) as total_jan_amount from sales 
where salesdate between '1-1-2011' and '1-1-2012'
Grou by productname) SR
where salesdate between '1-1-2011' and '1-1-2012') SR
where salesdate between '1-1-2011' and '1-1-2012' and
s.productname =sr.productname
order by salesdate
oracle tuning reading same table multiple times.  Use analytical functions where needed

oracle tuning reading same table multiple times. Use analytical functions where needed

instead use analytic functions which require only one pass through the table and no join


Select orderid, salesdate, productname, totalamount, sum(totalamount) over  (partition by productname) as totalJanamount

FROM saleshistory

 WHERE salesdate between  '1-1-2011' and '1-1-2012'

ORDER BY salesdate

Oracle tuning : using analytical function to improve query performance

Oracle tuning : using analytical function to improve query performance

Notice that when you use the analytical function the table and its index column are only used once. Also notice the difference in cost and time. Use analytical functions as much as possible.

Truncate instead of Delete

Whenever we  delete data from table , rollback segments  are used to UNDO information. If you do not commit  the transaction,  Oracle restores the data to the state it was in before the transaction

 

With Truncate, no undo information is generated. Once the table is truncated, the data cannot be restored back. hence its faster and needs fewer resources.

so use, TRUNCATE  rather than DELETE when you need to UNDO information.

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help  elevate my companies Search engine rankings)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s