 |
|
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.
|
