Question: I have a complex SQL statements with
two correlated subqueries that runs very slow:
table_a.o_id = table_c.o_id
table_b.e_nb = table_c.e_nb)
order by <columns>;
The correlated subqueries are making this SQL very slow to
execute. How do I tune a correlated subquery to make it run faster?
Answer: Correlated subqueries are usually
used for EXISTS Booleans, and scalar subqueries (e.g.
subqueries in the SELECT clause).
Correlated subqueries and slow because the sub-query is executed ONCE
for each row returned by the outer query.
Start by comparing the number of rows returned to the number of
There are several ways to tune a correlated subquery:
- Query rewrite: Inspect the correlated
subquery execution plan for the subqueries, and see if the explain
plan is re-writing the correlated subquery internally (set
query_rewrite_enabled = true) into a more efficient form, a
- Check indexes: If you MUST use a
correlated subquery, MAKE SURE that each of the referenced columns
in the subquery has an index! Without indexes on the correlated
subquery columns, the correlated subquery might be forced to do an
expensive full-table scan, executed over and over, once for each
outer row returned.
- Replace correlated query with a standard join:
See if you can replicate the output without the subqueries, using
- Materialize the correlated subqueries with global
temporary tables of the WITH clause: See if you can create
temporary tables to represent the contents of the subquery. If
you break down the query into its component pieces and use the
WITH clause, it may run faster.
See the book
Advanced Oracle SQL Tuning for complete details on tuning correlated
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.