Detect Full-Table Scans with EXPLAIN PLAN

A full-table scan occurs when Oracle reads table blocks to evaluate a query instead of using an index to navigate directly to a small set of rows. In an execution plan, this typically appears as TABLE ACCESS FULL.

In Oracle 23ai, full-table scans are still used and often appropriate. The tuning task is not “eliminate all full scans,” but to detect unexpected full scans and decide whether the optimizer made the right trade-off.

What you are looking for in the plan

• TABLE ACCESS FULL — Oracle will scan the table (or a large portion of it).
• Predicate details — confirm whether filtering happens early or late (and whether an index could help).
• Estimated rows and cost — mismatches often indicate stale stats or skewed data.

Step 1: Generate an execution plan with EXPLAIN PLAN

EXPLAIN PLAN records the optimizer’s predicted plan into a plan table. A common rendering approach is DBMS_XPLAN.DISPLAY.

EXPLAIN PLAN FOR
SELECT *
FROM   customers
WHERE  last_name = 'Smith';

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC'));

If the output includes TABLE ACCESS FULL, you have detected a full-table scan for the statement. The next step is to determine whether it is appropriate.

Step 2: Read the plan output and confirm the access path

A simplified example (your output will vary):

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |   250 (100)|          |
|   1 |  TABLE ACCESS FULL | CUSTOMERS |  1000 |  5000 |   250  (50)| 00:00:03 |
--------------------------------------------------------------------------------

The key indicator is the Operation column. When you see TABLE ACCESS FULL, Oracle expects scanning the table to be the best access path (given its estimates).

Important: EXPLAIN PLAN is a prediction, not proof of what executed

EXPLAIN PLAN shows what the optimizer would do, given the environment at explain time. To see what actually executed (including runtime row counts), execute the SQL and display the cursor plan.

-- Run the query (example)
SELECT *
FROM   customers
WHERE  last_name = 'Smith';

-- Then display the actual plan for the last cursor in your session
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Use DISPLAY_CURSOR when you are diagnosing real performance, because it helps you spot cardinality misestimates (estimated vs actual rows) that frequently lead to unexpected full scans.

Why Oracle chooses full-table scans

A full scan is often the correct plan choice, including:

1. Small tables: scanning can be cheaper than index + table lookups.
2. Low selectivity predicates: if the query returns a large percentage of rows, an index can be slower.
3. Aggregation patterns: some queries benefit from scanning (or scanning via partition pruning) to compute results efficiently.
4. Stale or missing statistics: Oracle may pick a scan because it cannot correctly estimate selectivity.

Common causes of unexpected full scans

• Missing or unusable index on the predicate column(s).
• Functions on indexed columns (or implicit datatype conversions) that prevent index usage.
• Stale statistics causing poor row-count estimates.
• Skewed data where histogram or extended stats would improve selectivity estimates.

Typical corrective actions

1. Gather stats so the optimizer can estimate correctly:

   BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(
       ownname => 'HR',
       tabname => 'CUSTOMERS'
     );
   END;
   /
   

2. Add an index when the query is selective and the workload benefits:

   CREATE INDEX idx_customers_last_name
   ON customers(last_name);
   

3. Rewrite predicates to remain sargable (avoid hiding columns behind functions), or use a function-based index when appropriate.
4. Use hints sparingly; prefer fixing stats/schema design first. If you must validate a hypothesis, test with a hint and confirm with DISPLAY_CURSOR.

Oracle 23ai note: plan verification remains essential

Oracle 23ai introduces new features and access paths, but it does not remove the need to verify execution plans. For example:

• AI Vector Search: Oracle’s optimizer can choose vector indexes to avoid scanning when evaluating similarity queries; you still validate the chosen path by inspecting the plan.
• SODA / document APIs: execution plan support is broadened beyond the traditional surfaces, which reinforces the same tuning habit—detect scans and validate access paths.

Oracle slow-downs and I/O reality

Appropriate full-table scans

Viewing the execution plan cleanly

EXPLAIN PLAN FOR
SELECT e.ename, d.dname
FROM   emp e
JOIN   dept d ON d.deptno = e.deptno
ORDER  BY d.dname;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'TYPICAL'));

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));