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

 

 

   
 

Speed of Correlated Subqueries with the IN Clause
Oracle Tips by Burleson
 

This is the output for a correlated subquery using the IN clause. Here you see the counts for the query, and the three timings for the query with the set timing on SQL*Plus command. Note that the database was stopped and re-started between timings.

COUNT(*)
----------
      7000

Elapsed: 00:00:02.82
Elapsed: 00:00:04.72
Elapsed: 00:00:04.83

Here you see roughly twice the execution time as with the non-correlated subquery. As I have discussed, the correlated subquery is executed once for each row that is returned from the parent query. Hence, if we return 14,000 rows from the parent query, the inner query would be executed one for each row. As you see from the execution plan, a full-table scan is invoked for outer_table. As each row is fetched, the key column from outer_table is used in an index-only merge scan to return the counts from our query.

Timing Subqueries with the EXISTS Clause

The EXISTS clause can sometimes be used instead of the IN clause for subqueries, but there are some important differences in the behavior of the queries. When using EXISTS with a subquery, the Boolean operator in the parent statement becomes true if any rows are returned by the subquery, and this may not give the desired result. Consequently, the following query would fail to count the 7,000 matching rows:

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

Speed of Non-correlated Subqueries with the EXISTS Clause

Here are the output timings for the query as a non-correlated subquery using the EXISTS clause. Note that we get the wrong answer for our count!

COUNT(*)
----------
     14000


Elapsed: 00:00:00.12
Elapsed: 00:00:00.11
Elapsed: 00:00:00.13

Here we note that using the EXISTS clause in a non-correlated subquery gives us the wrong number of rows. In this non-correlated subquery, using the EXISTS clause results in returning the number of rows from the parent table (outer_table in this case), and the subquery seems to be disregarded.

Speed of Correlated Subqueries with the EXISTS Clause

But what about using the EXISTS clause as a part of a correlated subquery? Since the correlated subquery is executed once for each row in the parent table, we would expect that this approach would work in identifying our 7,000 rows:

select
   count(*)
from
   outer_table
where
   EXISTS
   (select key from inner_table where outer_table.key = inner_table.key)
;

This is the execution timing for our correlated subquery with the EXISTS clause:

COUNT(*)
----------
      7000


Elapsed: 00:00:03.73
Elapsed: 00:00:03.36
Elapsed: 00:00:03.36


Basic Characteristics of Subquery Execution

The immediate conclusion about subqueries is that each SQL subquery must be evaluated on an individual basis, with an emphasis on the number of expected rows that will be returned from both the inner table and the outer table. Let’s review the conclusions about subqueries:

When using a correlated subquery, the execution plans are often identical for subqueries that use the IN clause or the EXISTS clause.

  • The EXISTS clause is always inappropriate for a non-correlated subquery.

  • When the outer query returns a relatively small number of rows, the correlated subquery will perform faster than the non-correlated subquery.

  • When the subquery returns more than one row, the query cannot be automatically transformed into a join.

  • With a small number of rows in the inner query, a non-correlated subquery will run faster than a correlated subquery.

Now that we have reviewed the basics of subqueries, let’s take a closer look and examine the execution plans for each type of subquery.


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