One issue with SQL is the
wide variety of ways that a SQL query can be written. Any complex
query might be written in a half-dozen different forms, each giving the
correct results, but with widely different execution speeds.
For example, it's widely known that you can
re-write a
not exists subquery as an outer join with a NOT NULL test.
However, there are also new SQL tuning tools with the
Oracle analytic functions, and there is a case whereby an exists
subquery can be re-written with the analytic rank and partition
clauses. In the example below, we will show how the exists
subquery can be replaced with a rank function, but more important, we will
see how there is a tradeoff between I/O and CPU overhead:
-
Exists subquery - The exists query
has far more logical reads, but little internal computational overhead.
-
Rank. . . over. . . partition - The
analytic form of the SQL greatly reduces I/O, but at the expense of
higher internal processing for the SQL statement.
Consider this simple SQL
by Oracle ACE Laurent Schneider (author of the bestselling
Advanced Oracle SQL Programming book).
The goal is to find out the best paid employees in each
department:
create table
lsc_emp
as
select * from emp;
create index lsc_i on lsc_emp(deptno,sal);
exec dbms_stats.gather_table_stats(user,'LSC_EMP',cascade=>true)
Before
Oracle introduced analytic functions, one way to find the highest paid
employees in a department is to use an exists
subquery. The subquery would
compute the max salary and serve as the value against which the outer query
measured salaries:
select
*
from
lsc_emp e1
where
exists (
select
1
from
lsc_emp e2
where
e1.deptno=e2.deptno
having
e1.sal=max(e2.sal)
);
Notice
here that the subquery causes us to invoke multiple index range scans inside
of the full table scan of our emp
table:
Execution Plan
----------------------------------------------------------
Plan hash value:
2224773357
-------------------------------------------------------------------------------
| Id
| Operation
| Name |
Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
| 1
| 68 |
56 (0)|
00:00:01 |
|*
1 | FILTER
|
|
|
|
|
|
|
2 | TABLE
ACCESS FULL | LSC_EMP |
107 | 7276 |
2 (0)| 00:00:01
|
|*
3 | FILTER
|
|
|
|
|
|
|
4 | SORT AGGREGATE
|
| 1
| 7 |
|
|
|*
5 |
INDEX RANGE SCAN| LSC_I
| 10 |
70 |
1 (0)| 00:00:01
|
-------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
1 - filter( EXISTS
(SELECT MAX("E2"."SALARY") FROM "LSC_EMP" "E2"
WHERE "E2"."DEPARTMENT_ID"=:B1 HAVING MAX("E2"."SALARY")=:B2))
3 - filter(MAX("E2"."SALARY")=:B1)
5 - access("E2"."DEPARTMENT_ID"=:B1)
Finally, note that this query required 79 consistent
gets:
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
79
consistent gets
0 physical reads
0 redo size
1991 bytes sent via
SQL*Net to client
520 bytes received
via SQL*Net from client
2 SQL*Net roundtrips
to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
Next,
let’s run the same for of the query using the Oracle analytic functions.
In this example, we replace the exists subquery with an in-line view
(a select
inside the from
clause), and we use the rank analytic function to determine the highest
salaried employees:
select
*
from
(
select
e.*,
rank()
over
(partition by deptno order by sal desc) r
from
lsc_emp e)
where
r=1
and
deptno
is NOT NULL;
Note: While this
query runs much faster than the original
exists
clause, it is much harder
to understand, and consequently, much harder to maintain!
Let’s take a closer look and see why this form of the query
runs faster. Here we see a very
different execution plan with the “window
sort pushed rank”
execution plan operation.
Execution Plan
----------------------------------------------------------
Plan hash value:
151729177
------------------------------------------------------------------------------------
| Id
| Operation
| Name |
Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
| 107 | 15622 |
3 (34)| 00:00:01 |
|*
1 | VIEW
|
| 107 | 15622 |
3 (34)| 00:00:01 |
|*
2 | WINDOW SORT
PUSHED RANK|
| 107 |
7276 |
3 (34)| 00:00:01 |
|
3 | TABLE
ACCESS FULL
| LSC_EMP | 107
| 7276 |
2 (0)| 00:00:01
|
------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
1 - filter("R"=1)
2 - filter(RANK()
OVER ( PARTITION BY "DEPARTMENT_ID" ORDER BY
INTERNAL_FUNCTION("SALARY") DESC )<=1)
But more importantly, note that the Statistics show only 4
consistent gets, as opposed to 78 consistent gets in the original query.
While a reduction in consistent gets does not always correlate to
faster execution time, it’s fair to say that this form of the query does 19
times less I/O to retrieve the desired rows!
Statistics
----------------------------------------------------------
1
recursive calls
0 db block gets
4
consistent gets
0 physical reads
0 redo size
2151 bytes sent via
SQL*Net to client
520 bytes received
via SQL*Net from client
2 SQL*Net roundtrips
to/from client
1 sorts (memory)
0 sorts (disk)
12 rows processed
Now, you may believe the analytic query runs faster because
it scans the table only once, but even though it does less I/O, the
analytics operation is quite expensive.
Remember, the best way to judge the speed of a query is to measure
it, using the SQL*Plus set timing on
command.
Again, always remember
that your execution plans costs and consistent gets may not always tell you
the fastest execution speed, and what appears to be the fastest execution
plan may run slower than other alternative forms of the same query.
