Tuning Correlated Subqueries for Fast Speed 

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.

•  Un-nest the subqueries:  Oracle allows you to add a hint that will un-nest the correlated subquery.

•  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.  

Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.                      Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services   Copyright ? 1996 -  2012  All rights reserved. Oracle ? is the registered trademark of Oracle Corporation.