AND EXISTS : nested iteration

This SHOWPLAN step indicates either a nested iteration on a subquery that is part of an existence test in a query, or a nested iteration on a correlated scalar subquery involving an aggregate, or a nested iteration on the plan for evaluating OR clauses involving subqueries. The plan for the subquery or the OR clause will immediately follow this step and is indented to isolate the steps for the subquery, or the steps for the OR clause, from the steps for the outer query.

The following example demonstrates the SHOWPLAN output for an AND EXISTS nested iteration on a subquery that is part of an existence test:

Query:

SELECT title
FROM titles
WHERE EXISTS
(SELECT *
FROM publishers p, titles t
WHERE t.pub_id = p.pub_id
AND p.pub_name = 'Algodata Infosystems'
AND titles.advance > t.advance)
AND pub_id NOT IN
(SELECT pub_id
FROM publishers
WHERE city  LIKE 'B%')

SHOWPLAN:

STEP 1
The type of query is SELECT
FROM TABLE
titles
Nested iteration
Table Scan
AND EXISTS : nested iteration
FROM TABLE
publishers p
EXISTS TABLE : nested iteration
Table Scan
FROM TABLE
titles t
EXISTS TABLE : nested iteration
Table Scan
AND NOT EXISTS : nested iteration
FROM TABLE
publishers
EXISTS TABLE : nested iteration
Table Scan

The following example demonstrates the SHOWPLAN output for an AND EXISTS nested iteration on a subquery involving aggregates:

Query:

SELECT ord_num, title_id, qty
FROM sales s1
WHERE qty <
(SELECT avg(qty)
FROM sales s2
WHERE s2.title_id = s1.title_id)

SHOWPLAN:

STEP 1
The type of query is SELECT
FROM TABLE
sales s1
Nested iteration
Table Scan
AND EXISTS : nested iteration
FROM TABLE
Worktable 1
SUBQUERY : nested iteration
GROUP BY
Vector Aggregate
FROM TABLE
sales s2
Nested iteration
Table Scan
TO TABLE
Worktable 1

The following example demonstrates the SHOWPLAN output for an AND EXISTS nested iteration on a plan for evaluating an OR clause involving subqueries:

Query:

SELECT au_lname, au_fname
FROM authors
WHERE    EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)
OR NOT EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)

SHOWPLAN:

STEP 1
The type of query is SELECT
FROM TABLE
authors
Nested iteration
Table Scan
AND EXISTS : nested iteration
OR EXISTS : nested iteration
FROM TABLE
publishers
EXISTS TABLE : nested iteration
Table Scan
OR NOT EXISTS : nested iteration
FROM TABLE
publishers
EXISTS TABLE : nested iteration
Table Scan