BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










Timing Subquery Execution

Oracle Tips by Burleson Consulting

To see the difference between the execution plans for correlated and non-correlated subqueries, let’s design an experiment where we use the count(*) SQL function and take a look at the explain plan for the queries to see which is the most efficient:

In our test, outer_table was created with 14,000 rows, and inner_table was created with 7,000 rows. A non-unique index was created on the key for both tables, and we used Oracle’s rule-based optimizer to generate the paths to the data. All of the queries are in the following form:

(select yyy from inner_table)


Now, let’s take a look at each form of this query, and see the relative differences in execution speed.

Timing Subqueries with the IN Clause

We expect that each of the count(*) queries should return 7,000 rows, one for each row that exists in outer_table, which was found in inner_table. To factor out variances in elapsed times, each query was run three times and the total elapsed time for the queries were recorded.

Drawing on our previous discussion on issues of scale, we expect the parent query to return 14,000 rows and the subquery to return 7,000 rows. Consequently, the correlated subquery would need to execute the inner query 14,000 times to service all of the rows in the parent table. The non-correlated subquery will need to store 7,000 rows in a temporary segment, but the result set is small enough that the sort can take place in memory, and we will only need to execute the subquery one time. Therefore, we expect that the non-correlated subquery would probably run faster, since there will be fewer fetches. To test the execution speed, we executed our queries three times, and generated the execution plans for each query.

Speed of Non-correlated Subqueries with the IN Clause

Here are the output timings for the non-correlated subquery using the IN clause:


Elapsed: 00:00:01.09
Elapsed: 00:00:01.46
Elapsed: 00:00:01.30

Here you see that the query completed in slightly more than one second. The non-correlated subquery begins by performing a full-table scan on inner_table and sorting the table in memory, storing the sorted result in a system view (temporary segment). We next move into nested loops where the index on outer_table is used to retrieve the key for outer_table and this key is looked up in the temporary segment. As I have noted, the subquery is executed only once, and the result set is kept and used for each test from the parent query.

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

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter