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

 

 

   
 

Correlated Versus Non-Correlated Subqueries
Oracle Tips by Burleson
 

Since we have the choice of using a correlated subquery or a non-correlated one, we need to examine the basic properties of these constructs. In essence a “correlated” subquery references the outer table inside the subquery, while the non-correlated subquery does not reference the outer table. A correlated subquery is evaluated once per row processed by the parent query, while a non-correlated subquery is only executed once and the result set is kept in memory (if the result set is small), or in an Oracle temporary segment (if the result set is large). A “scalar” subquery is a non-correlated subquery that returns a single row. Where the subquery only returns one row, the Oracle optimizer will reduce this result to a constant and only execute the subquery one time.

For our example, an identical query could be written either as a correlated subquery or a non-correlated subquery, and both will return the same result set.

Here is a non-correlated subquery:

select
   count(*)
from
   outer_table
where
   key in
   (select key from inner_table)
;

For the purpose of this discussion, we will call the main query (the select count(*) ) the “outer” query, as opposed to the “inner” subquery that is enclosed in parenthesis. The choice of using a correlated or non-correlated subquery depends upon the number of rows that are returned by the outer query and the subquery. Given that both of these queries will return the same result, which is the most efficient? Let’s begin our answer to this question by exploring the number of rows that we get back from the query.

Issues of Scale and Subqueries

In practice, the choice between a correlated or non-correlated subquery depends upon the expected number of rows that is returned by both the parent query and the subquery. Our goal is to balance the overhead that is associated with each type of subquery.

  • Correlated subquery overhead  The subquery will be re-executed once for each row that is returned by the outer query. Hence, we must ensure that the subquery uses an index whenever possible.

  • Non-correlated subquery overhead  The subquery is executed once, and the result set is usually sorted and kept in an Oracle temporary segment where it is referenced as each row is returned by the parent query. In cases where the subquery returns a large number of rows, a significant overhead will be involved with sorting this result set and storing the temporary segment.

In sum, our choice depends wholly on the number of rows returned by the parent query. If our parent query returns only a few rows, the overhead of re-executing the subquery is not so great. On the other hand, if the parent query returns a million rows, the subquery would be executed a million times. The same concept applies to the number of rows returned by the subquery. If our subquery returns only a few rows, then there is not much overhead in keeping the result set in memory for the parent query. On the other hand, if the subquery returns a million rows, then Oracle would need to store this result set as a temporary segment on disk, and then sort the segment in order to service each row of the parent query.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

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. 

Hit Counter