OR 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 OR clause. When the result of one of the existence or non-existence tests is true, subsequent parts of the plan are not evaluated and the result of the plan for the OR clause is true. The plans for the existence and non-existence tests will immediately follow the OR EXISTS step and are indented to isolate the steps for the subquery iteration from the steps for the outer query.

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

Query:     SELECT title
           FROM titles
           WHERE advance > ANY
              (SELECT advance
              FROM publishers, titles
              WHERE titles.pub_id = publishers.pub_id
                 AND pub_name = 'Algodata Infosystems')
           OR 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
           OR EXISTS : nested iteration
             EXISTS : nested iteration
               FROM TABLE
               publishers
               Nested iteration
               Table Scan
               FROM TABLE
               titles
               Nested iteration
               Table Scan
             NOT EXISTS : nested iteration
               FROM TABLE
               publishers
               Nested iteration
               Table Scan