Nested iteration

The Nested iteration method is the default technique used to join tables and/or to return rows from a table. It simply indicates that the optimizer is using one or more sets of loops to go through a table and retrieve a row, qualify the row based on the search criteria given in the WHERE clause, return the row to the front-end, and loop again to get the next row. The method in which it gets the rows (such as using an available index) is discussed later.

The following example shows the optimizer doing nested iterations through each of the tables in the join:

Query:

SELECT title_id, title
FROM titles, publishers
WHERE titles.pub_id = publishers.pub_id
AND publishers.pub_id = '1389'

SHOWPLAN:

STEP 1
The type of query is SELECT
FROM TABLE
publishers
Nested iteration
Table Scan
FROM TABLE
titles
Nested iteration
Table Scan