Question: I have a complex SQL statements with
two correlated subqueries that runs very slow:
select
<columns>
from
table_c
inner join
table_a
on
table_a.o_id = table_c.o_id
. .
and
(table_b.date_dt
=
(select
max(table_b.date_dt)
from
table_b
where
table_b.o_id
= table_c.o_id)
or
table_b.date_ts =
(select
max(table_b.date_ts)
from
table_b
where
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
consistent gets
using autotrace.
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
standard join.
- 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
vanilla joins.
- 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
subqueries.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
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
buy it
for 30% off directly from the publisher.
|
