Table Scan

This statement indicates which method was used to retrieve the physical result rows from the given table. When the Table Scan method is used, execution begins with the first row in the table; each row is then retrieved and compared with the conditions in the WHERE clause, and then returned to the front-end if it meets the given criteria. Regardless of how many rows qualify, every row in the table must be looked at, so for very large tables, a table scan can be very costly in terms of page I/Os.

If a table has one or more indexes on it, the query optimizer might still perform a table scan instead of using one of the available indexes if the optimizer determines that the indexes are too costly or are not useful for the given query.

The following query shows a typical Table Scan:

Query:

SELECT au_lname, au_fname
FROM authors

SHOWPLAN:

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