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.

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 number of rows increases. eg. The same query with 50 rows per customer will process 50 + 50 x 50 = 2550 rows.
• The SQL is performed in a loop that processes thousands of different keys. For example, an end of month invoicing program may run the SQL above for every single customer.
• The sub-query contains a similar sub-query. Consider for example a query with correlated sub-queries nested 3 deep, each selecting 5 rows per key (I have actualy seen this!). The top level will select 5 rows. The second level will select 5 rows 5 times = 25 rows. The third level will select 5 rows 25 times = 125 rows. The final level will select 5 rows 125 times = 625 rows. A total of 625 + 125 + 25 + 1 = 776 rows processed for a single "low volume" query.

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:

• The table and index must be analysed.
• You may not use the Rule-Based Optimizer. This means that the OPTIMIZER_MODE (or OPTIMIZER_GOAL) session parameter cannot be RULE, nor may you use the /*+ RULE*/ hint.
• The sub-query may only SELECT the MAX or MIN of one of the indexed columns.
• The WHERE clause 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 WHERE conditions.

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.