Query plans for subqueries

The optimizer can change a subquery to a join automatically if the join provides a lower cost.

For example, Figure 1 sample output of the SET EXPLAIN ON statement shows that the optimizer changes the table in the subquery to be the inner table in a join.

Figure 1. Partial SET EXPLAIN output for a flattened subquery

QUERY:
------
SELECT company, fname, lname, phone
FROM customer c
WHERE EXISTS(
   SELECT customer_num FROM cust_calls u
      WHERE c.customer_num = u.customer_num)

Estimated Cost: 6
Estimated # of Rows Returned: 7

  1) virginia.c: SEQUENTIAL SCAN

  2) virginia.u: INDEX PATH  (First Row)

    (1) Index Keys: customer_num call_dtime   (Key-Only)  
                                              (Serial, fragments: ALL)
        Lower Index Filter: virginia.c.customer_num = virginia.u.customer_num
NESTED LOOP JOIN  (Semi Join)

For more information about the SET EXPLAIN ON statement, see Report that shows the query plan chosen by the optimizer.

When the optimizer changes a subquery to a join, it can use several variations of the access plan and the join plan:
First-row scan
A first-row scan is a variation of a table scan. When the database server finds one match, the table scan halts.

Skip-duplicate-index scan
The skip-duplicate-index scan is a variation of an index scan. The database server does not scan duplicates.

Semi join
The semi join is a variation of a nested-loop join. The database server halts the inner-table scan when the first match is found. For more information about a semi join, see Nested-loop join.