Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  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); 
------ ---- ----------
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


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.