 |
|
Oracle Tips by Burleson |
Correlated Subqueries
A correlated subquery is a subquery that
uses values from the outer query. The Oracle database wants to
execute the subquery once and use the results for all the
evaluations in the outer query. With a correlated subquery, the
database must run the subquery for each evaluation because it is
based on the outer query’s data.
SQL> select
2 book_key,
3 store_key,
4 quantity
5 from
6 sales s
7 where
8 quantity < (select max(quantity)
9 from sales
10 where book_key =
s.book_key);
BOOK_K STOR QUANTITY
------ ---- ----------
B101 S101 1000
B102 S102 10
B102 S103 200
…
B116 S105 100
B101 S105 8000
B109 S109 100
81 rows selected.
In the example above, the subquery
references the book_key in the outer query. The value of the
book_key changes by row of the outer query, so the database must
rerun the subquery for each row comparison. This has a significant
performance impact on the execution time of the query, and for that
reason, correlated subqueries should be avoided if possible.
The outer query knows nothing about the
inner query except its results. For that reason, the outer query
cannot reference any columns in the subquery. However, the subquery
has access to the outer query and can reference outer query columns,
thus the correlated subquery.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
