Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Combining Multiple Scans with CASE Statements
Oracle Tips by Burleson
 

It is often necessary to calculate different aggregates on various sets of tables. This is generally done with multiple scans on the table, but it is easy to calculate all the aggregates with one single scan. Eliminating n–1 scans can greatly improve performance.

Combining multiple scans into one scan can be done by moving the WHERE condition of each scan into a CASE statement, which filters the data for the aggregation. For each aggregation, there could be another column that retrieves the data.

The following example asks for the count of all employees who earn less than $1000 per month, all those who earn between $1000 and $5000 per month, and the DBAs who earn more than $5000 each month. This can be done with three separate queries:

select
   count(*)
from
   emp
where
   sal < 1000;

select
   count(*)
from
   emp
where
   sal between 1000 and 5000;

select
   count (*)
from
   emp
where
   sal > 5000;

Here is the output:

  COUNT(*)
----------
         1


  COUNT(*)
----------
        12


  COUNT(*)
----------
         1

Of course, because each of these queries does a count(*), there will be three full-table scans against the emp table.

However, it is more efficient to run the entire query in a single statement using the case BIF. Each number is calculated as one column. The count uses a filter with the case statement to count only the rows where the condition is valid. For example:

select
   count (case when sal < 1000
         then 1 else null end)                    count_poor,
   count (case when sal between 1001 and 5000
         then 1 else null end)                    count_blue_collar,
   count (case when sal > 5001
          then 1 else null end)                   count_dba
from
   emp;

Here is the output from this query. Note that we get our counts on a single line of output.

COUNT_POOR COUNT_BLUE_COLLAR  COUNT_DBA
---------- ----------------- ----------
         1                12          1

Now let’s examine the execution plan for this query to see how the case clause improves the original SQL execution of three full-table scans:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     1
  SORT
AGGREGATE                                                            1
    TABLE ACCESS
FULL                           EMP                                   1

Here you see that we have replaced three full-table scans with a single full-table scan, resulting in a 3× performance improvement.

The moral of this story is that the case statement can be used to speed up multiple counting operations when a common column is involved.

Next, let’s look at the true built-in functions of Oracle SQL and see how they behave.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter