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


 

HTML Text

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

  
 

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.