 |
|
Timing Subquery Execution
Oracle Tips by Burleson Consulting |
To see the difference between the execution
plans for correlated and non-correlated subqueries, let’s design an
experiment where we use the count(*) SQL function and take a
look at the explain plan for the queries to see which is the most
efficient:
In our test, outer_table was created
with 14,000 rows, and inner_table was created with 7,000
rows. A non-unique index was created on the key for both tables, and
we used Oracle’s rule-based optimizer to generate the paths to the
data. All of the queries are in the following form:
select
count(*)
from
outer_table
where
XXX
(select yyy from inner_table)
;
-- Where XXX=EXISTS, IN, NOT
IN, and NOT EXISTS
Now, let’s take a look at each form of this
query, and see the relative differences in execution speed.
Timing Subqueries with the IN Clause
We expect that each of the count(*)
queries should return 7,000 rows, one for each row that exists in
outer_table, which was found in inner_table. To factor
out variances in elapsed times, each query was run three times and
the total elapsed time for the queries were recorded.
Drawing on our previous discussion on issues
of scale, we expect the parent query to return 14,000 rows and the
subquery to return 7,000 rows. Consequently, the correlated subquery
would need to execute the inner query 14,000 times to service all of
the rows in the parent table. The non-correlated subquery will need
to store 7,000 rows in a temporary segment, but the result set is
small enough that the sort can take place in memory, and we will
only need to execute the subquery one time. Therefore, we expect
that the non-correlated subquery would probably run faster, since
there will be fewer fetches. To test the execution speed, we
executed our queries three times, and generated the execution plans
for each query.
Speed of Non-correlated Subqueries with the IN Clause
Here are the output timings for the
non-correlated subquery using the IN clause:
COUNT(*)
----------
7000
Elapsed: 00:00:01.09
Elapsed: 00:00:01.46
Elapsed: 00:00:01.30
Here you see that the query completed in
slightly more than one second. The non-correlated subquery begins by
performing a full-table scan on inner_table and sorting the
table in memory, storing the sorted result in a system view
(temporary segment). We next move into nested loops where the index
on outer_table is used to retrieve the key for outer_table
and this key is looked up in the temporary segment. As I have noted,
the subquery is executed only once, and the result set is kept and
used for each test from the parent query.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
