RBO Path 15: Full Table Scan

This access path is available for any SQL statement, regardless of its WHERE clause conditions, except when its FROM clause contains SAMPLE or SAMPLE BLOCK.

Note that the full table scan is the lowest ranked access path on the list. This means that the RBO always chooses an access path that uses an index if one is available, even if a full table scan might execute faster.

The following conditions make index access paths unavailable:

• column1 > column2
• column1 < column2
• column1 >= column2
• column1 <= column2

where column1 and column2 are in the same table.

• column IS NULL
• column IS NOT NULL
• column NOT IN
• column != expr
• column LIKE '%pattern'

regardless of whether column is indexed.

• expr = expr2

where expr is an expression that operates on a column with an operator or function, regardless of whether the column is indexed.

• NOT EXISTS subquery
• ROWNUM pseudocolumn in a view
• Any condition involving a column that is not indexed

Any SQL statement that contains only these constructs and no others that make index access paths available must use full table scans.

For example: The following statement uses a full table scan to access the emp table:

SELECT * 
    FROM emp; 

The EXPLAIN PLAN output for this statement might look like this:

OPERATION                 OPTIONS        OBJECT_NAME 
----------------------------------------------------- 
SELECT STATEMENT 
  TABLE ACCESS            FULL           EMP