Sub-query blowout

A correlated sub-query is one where the sub-query references columns in the outer query. With few exceptions, Oracle will evaluate a correlated sub-query as a Nested Sub-Query.

For example, the table CUST stores historical images of changes to the table, and differentiates rows with cust_seq. The latest details are on the row with the highest cus_seq.

This effect tends to go unnoticed, because it is still pretty quick to query 30 rows; it's not like you have to wait 10 minutes for the query to return. Problems start to occur when:

How to fix it

The example above can be fixed with an index on CUST_NO, CUST_SEQ. Rather than scanning the entire range of rows with a matching CUST_NO, Oracle will just pick the first (MIN(CUST_SEQ)) or last (MAX(CUST_SEQ)) one from the index range without having to read the rest. There are restrictions on this technique though:

If these conditions are not met, then the index solution will not work; the query must be re-written. The best solution is usually to process the results in PL/SQL without using a sub-query. Alternatively, if it is possible to write the SQL as a table join or with analytic functions instead of a sub-query, then it will usually run without blowing out like the sub-query.

When you re-write the SQL to eliminate the sub-query, compare the new version to the old in TK*Prof to make sure you have fixed the problem.

©Copyright 2003