AND EXISTS: nested iteration

This SHOWPLAN step indicates a nested iteration on one or more subqueries that are part of an existence test or a non-existence test, and that are also part of an AND clause, which is also part of an OR clause. When the result of one of the existence or non-existence tests is false, subsequent parts of the plan are not evaluated and the result of the plan for the AND clause is false. The plans for the existence and non-existence tests will immediately follow the AND EXISTS step and are indented to isolate the steps for the subquery from the steps for the outer query.

The following example demonstrates the SHOWPLAN output for queries with AND clauses within OR clauses that test for the existence or non-existence of values:

Query:   SELECT title
         FROM titles
         WHERE pub_id IN
            (SELECT pub_id
            FROM publishers
            WHERE country <> 'USA')
         OR     (NOT EXISTS
            (SELECT *
            FROM publishers
            WHERE city LIKE 'B%'
               AND publishers.pub_id = titles.pub_id)
               AND advance > ANY
            (SELECT advance
            FROM publishers, titles
                WHERE titles.pub_id = publishers.pub_id
               AND pub_name = 'Algodata Infosystems'))
  
SHOWPLAN: STEP 1
          The type of query is SELECT
          FROM TABLE
          titles
          Nested iteration
          Table Scan
          OR EXISTS : nested iteration
          EXISTS : nested iteration
             FROM TABLE
             publishers
             Nested iteration
             Table Scan
                AND EXISTS : nested iteration
                NOT EXISTS : nested iteration
                FROM TABLE
                publishers
                Nested iteration
                Table Scan
                EXISTS : nested iteration
                   FROM TABLE
                   publishers
                   Nested iteration
                   Table Scan
                          FROM TABLE
                          titles
                          Nested iteration
                          Table Scan