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.
SELECT * FROM cust a WHERE cust_seq = ( SELECT max(cust_seq) FROM cust WHERE cust_no = a.cust_no) AND cust_no = :a
If the table contains an average of 5 rows per customer, then the outer query will return 5 rows, and then execute the inner query for each of the 5 rows. The inner query also return 5 rows, but it does this 5 times. ie. The above query will read 5 + 5 x 5 = 30 rows, even though the table only contains 5 rows for the customer. Evidence of this processing can be seen in TK*Prof, with seemingly small queries processing hundreds of blocks in the Disk and/or Query columns.
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:
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:
OPTIMIZER_GOAL) session parameter cannot be
RULE, nor may you use the
MINof one of the indexed columns.
WHEREclause must have equality (=) conditions on all of the index columns preceding MAX/MIN column and it may optionally have an equality or range condition (
<, >, BETWEEN, LIKE) on the MIN/MAX column, but may have no other
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.