Testing Different Access Paths : With and Without Index

Oracle Performance Series

Oracle Different Access PathsHow the optimizer chooses between full scans, rowid access, range scans, fast full scans, skip scans, and related paths

One of the most important Oracle performance skills is recognizing that two queries with similar predicates can take very different routes to the data. This guide turns ?access paths? into something operational: what each path means, why Oracle chooses it, how to validate the choice, and how to teach your eye to read plans without falling for simplistic index folklore.

Contents

01 Access-path mindset02 Table access paths03 Index access paths04 Less obvious index paths05 Plan-reading workflow06 Practical lab07 Monitoring and comparison08 Quiz

Section 01

Access paths are the optimizer?s route choices, not moral judgments

An access path is simply the method Oracle uses to find rows in a table or index. The optimizer compares many possible plans, estimates cost, and picks the cheapest one it can see. The word ?cheap? here includes more than raw I/O count: it also reflects whether rows can be found in order, whether table blocks must be revisited repeatedly, and whether the answer can come from the index alone.

That is why ?use the index? is not a serious tuning strategy by itself. Oracle may choose a full table scan because the result set is large, because the table is small, because a predicate prevents index navigation, or because index-driven rowid fetches would be more expensive than scanning blocks directly.

What Oracle compares

Table scans, B-tree paths, bitmap paths, rowid fetches, and combinations of those options.

What DBAs should compare

Estimated rows, actual rows, block visits, sort steps, and whether the table had to be revisited after the index probe.

What to avoid

Fixed percentage myths, blind hinting, and conclusions based only on the presence of the word INDEX in a plan.

Recollect: An access path answers one question: what is the cheapest route from predicate to rows for this statement on this data shape?

Section 02

Table access paths: full table scan and table access by rowid

The most basic table access path is TABLE ACCESS FULL. Oracle reads all table blocks below the high water mark and filters rows as it goes. This is often the right choice when a query needs a large share of rows, when the table is small, or when no usable index path exists.

TABLE ACCESS BY INDEX ROWID is different. In that pattern, Oracle first uses an index to find rowids and then visits the table blocks containing the actual rows. This is common in index-driven plans because the index usually stores only the key plus rowid, not the rest of the table columns.

TABLE ACCESS FULL

• Reads the table directly
• Can use multiblock reads
• Strong candidate for broad result sets

TABLE ACCESS BY INDEX ROWID

• Follows an index access path
• Fetches base-table columns by rowid
• Can be excellent or expensive depending on rowid locality

Important nuance

If the index finds many scattered rowids, the expensive part of the plan may be the table fetch phase rather than the index probe itself. This is one reason clustering factor matters so much.

SQL - Compare table access patterns

EXPLAIN PLAN FOR
SELECT *
FROM   sales_path_test
WHERE  status = 'OPEN';

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
SELECT id, amount
FROM   sales_path_test
WHERE  id = 10042;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

Section 03

Core B-tree index paths: unique scan, range scan, full scan, and fast full scan

These are the access paths most DBAs meet first, and each solves a different problem.

Access path	Typical trigger	Why Oracle chooses it
INDEX UNIQUE SCAN	Equality on all columns of a unique index key	At most one rowid is expected, so the index probe is extremely tight.
INDEX RANGE SCAN	Non-unique equality, ranges, prefix LIKE, inequalities	Oracle can walk a subset of the ordered index and return matching rowids.
INDEX FULL SCAN	Broad ordered access, order-by elimination, or index-only retrieval in key order	The optimizer wants the index order itself.
INDEX FAST FULL SCAN	Broad index-only access where ordering is unnecessary	The index can be treated as a smaller structure than the table, often using multiblock reads.

A useful mental shortcut is this: range scan is about navigating to a subset; full scan is about preserving order; fast full scan is about reading the whole index cheaply when order is irrelevant.

SQL - Core index path examples

EXPLAIN PLAN FOR
SELECT *
FROM   sales_path_test
WHERE  id = 50001;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
SELECT *
FROM   sales_path_test
WHERE  order_date BETWEEN DATE '2025-01-01' AND DATE '2025-01-31';

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
SELECT id
FROM   sales_path_test
ORDER  BY id;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
SELECT COUNT(id)
FROM   sales_path_test
WHERE  id IS NOT NULL;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

Small but important fact

A primary key constraint alone does not guarantee INDEX UNIQUE SCAN. If the underlying index is non-unique, Oracle can still show INDEX RANGE SCAN for an equality predicate.

Recollect: The path name tells you how Oracle moves through the structure. It does not, by itself, tell you whether the whole plan is good or bad.

Section 04

Less obvious paths: skip scan, index joins, and why predicate shape matters

Once you move beyond the most common paths, Oracle gets more interesting. One example is INDEX SKIP SCAN, which lets Oracle use a concatenated index without filtering on the leading column. This can happen when the leading column has relatively few distinct values and the optimizer estimates that probing each logical prefix is still cheaper than scanning the table.

Another pattern is an index-driven plan that combines information from indexes before visiting the table, or uses an index as a covering structure. The practical lesson is that predicates, nullability, and requested columns influence path choice just as much as the existence of an index.

Skip scan

• Works on a concatenated B-tree index
• Can help when the leading column is absent from the predicate
• Most likely when the leading column has low distinctness

Predicate-shape traps

• Functions on indexed columns can block ordinary index use
• Implicit conversions can sabotage access paths
• IS NULL and nullable columns need careful reading

SQL - Skip-scan style setup

CREATE INDEX idx_region_status ON sales_path_test(region, status);

EXPLAIN PLAN FOR
SELECT region, status, amount
FROM   sales_path_test
WHERE  status = 'OPEN';

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

Healthy caution

Do not create a composite index hoping for skip scan alone. Treat skip scan as a useful optimizer option, not as the main design goal.

Section 05

How to validate access-path choices: EXPLAIN PLAN, AUTOTRACE, and runtime cursor statistics

EXPLAIN PLAN is a good first look, but it shows the optimizer?s chosen plan for the explained statement, not necessarily the final runtime behavior of the exact cursor you executed. AUTOTRACE in SQL*Plus is handy for quick visibility into plan and session statistics. For the most trustworthy verification, use real execution plus DBMS_XPLAN.DISPLAY_CURSOR with runtime stats.

1. Gather stats

Make sure the optimizer is reasoning with current metadata.

2. Run the statement

Prefer a realistic bind or literal pattern.

3. Inspect cursor stats

Use ALLSTATS LAST to compare estimated and actual behavior.

4. Compare alternatives

Only then consider hints or index changes.

SQL*Plus and DBMS_XPLAN workflow

SET LINESIZE 200
SET PAGESIZE 100
SET AUTOTRACE ON STATISTICS
SET TIMING ON

ALTER SESSION SET statistics_level = ALL;

SELECT /*+ gather_plan_statistics */ *
FROM   sales_path_test
WHERE  order_date BETWEEN DATE '2025-01-01' AND DATE '2025-01-07';

SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_CURSOR(
           NULL,
           NULL,
           'ALLSTATS LAST +PREDICATE +COST +BYTES'
         )
       );

Good testing discipline

On a shared system, avoid destructive ?test hygiene? habits like flushing caches just to prove a point. It is usually better to compare plans and logical work than to force a cold-cache scenario outside an isolated lab.

Tool	Best use	Limitation
EXPLAIN PLAN	Quick first draft of the plan	Not the final word on actual runtime behavior
AUTOTRACE	Fast plan/statistics feedback in SQL*Plus	Convenient, but still not a substitute for reading the executed cursor carefully
DBMS_XPLAN.DISPLAY_CURSOR	Best way to inspect executed cursor details	Requires the statement to be executed first

Section 06

End-to-end lab: observe several access paths on one reproducible dataset

This lab avoids fake performance numbers. Instead of inventing exact block counts, it gives you reproducible SQL and tells you what plan shapes to verify. That makes it portable across environments.

Step 1: Create the lab table

SQL - Build demo data

DROP TABLE sales_path_test PURGE;

CREATE TABLE sales_path_test (
  id         NUMBER       NOT NULL,
  region     VARCHAR2(10) NOT NULL,
  status     VARCHAR2(10) NOT NULL,
  category   VARCHAR2(12) NOT NULL,
  amount     NUMBER       NOT NULL,
  order_date DATE         NOT NULL
);

INSERT INTO sales_path_test
SELECT level,
       CASE MOD(level,5)
         WHEN 0 THEN 'NORTH'
         WHEN 1 THEN 'SOUTH'
         WHEN 2 THEN 'EAST'
         WHEN 3 THEN 'WEST'
         ELSE 'CENTRAL'
       END,
       CASE
         WHEN MOD(level,10) IN (0,1) THEN 'OPEN'
         WHEN MOD(level,10) IN (2,3,4) THEN 'PENDING'
         ELSE 'CLOSED'
       END,
       CASE MOD(level,4)
         WHEN 0 THEN 'HARDWARE'
         WHEN 1 THEN 'SOFTWARE'
         WHEN 2 THEN 'SERVICE'
         ELSE 'SUPPORT'
       END,
       ROUND(DBMS_RANDOM.VALUE(50,5000)),
       DATE '2025-01-01' + MOD(level,365)
FROM   dual
CONNECT BY level <= 120000;

COMMIT;

ALTER TABLE sales_path_test
  ADD CONSTRAINT sales_path_test_pk PRIMARY KEY (id);

CREATE INDEX idx_spt_status     ON sales_path_test(status);
CREATE INDEX idx_spt_order_date ON sales_path_test(order_date);
CREATE INDEX idx_spt_region_cat ON sales_path_test(region, category);

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => USER,
    tabname    => 'SALES_PATH_TEST',
    cascade    => TRUE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;
/

Step 2: Verify data distribution

SQL - Profile the lab data

SELECT status, COUNT(*)
FROM   sales_path_test
GROUP  BY status
ORDER  BY status;

SELECT region, COUNT(*)
FROM   sales_path_test
GROUP  BY region
ORDER  BY region;

Step 3: Observe a unique scan

SQL - Unique access test

ALTER SESSION SET statistics_level = ALL;

SELECT /*+ gather_plan_statistics */ *
FROM   sales_path_test
WHERE  id = 42424;

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

What to verify: expect a primary-key-driven path, usually INDEX UNIQUE SCAN plus rowid access because the query asks for all columns.

Step 4: Observe a range scan

SQL - Range access test

SELECT /*+ gather_plan_statistics */ id, order_date
FROM   sales_path_test
WHERE  order_date BETWEEN DATE '2025-03-01' AND DATE '2025-03-10'
ORDER  BY order_date;

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

What to verify: expect an ordered date-based path, usually INDEX RANGE SCAN. Depending on the environment, Oracle may avoid an explicit sort because the index order already matches the request.

Step 5: Observe a broad filter where a full table scan may win

SQL - Broad filter test

SELECT /*+ gather_plan_statistics */ *
FROM   sales_path_test
WHERE  status = 'CLOSED';

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

What to verify: because CLOSED covers a large fraction of rows in this data pattern, Oracle may prefer TABLE ACCESS FULL. If it uses the index, compare actual work before assuming the plan is better.

Step 6: Observe a possible fast full scan

SQL - Fast full style test

SELECT /*+ gather_plan_statistics */ COUNT(order_date)
FROM   sales_path_test
WHERE  order_date IS NOT NULL;

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

What to verify: because the query can be answered from indexed values alone, Oracle may consider an index full or fast full scan rather than scanning the table.

Step 7: Observe a skip-scan candidate

SQL - Skip-scan candidate

SELECT /*+ gather_plan_statistics */ region, category, amount
FROM   sales_path_test
WHERE  category = 'SERVICE';

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

What to verify: with the composite index on (region, category), Oracle may consider skip scan because the predicate starts from the second column. Whether it chooses it depends on your statistics and data profile.

Section 07

Monitoring index usage and comparing alternatives responsibly

Once you understand access paths, the next practical question is whether an index is earning its keep. Oracle can track index usage monitoring for you, which is helpful when deciding whether an index is idle or whether a change is worth testing further.

SQL - Monitor index usage

ALTER INDEX idx_spt_status MONITORING USAGE;

SELECT index_name,
       used,
       start_monitoring,
       end_monitoring
FROM   user_object_usage
WHERE  index_name = 'IDX_SPT_STATUS';

How to use this wisely

?Unused? during a short observation window does not automatically mean ?safe to drop.? Pair usage monitoring with workload knowledge, AWR or SQL history where available, release calendars, and rollback plans.

A reliable comparison workflow

• Gather current statistics before drawing plan conclusions.
• Run the exact SQL you care about, not a simplified cousin if you can avoid it.
• Capture DBMS_XPLAN.DISPLAY_CURSOR output for the executed statement.
• Compare logical work and row-source behavior before reaching for hints.
• If you test a hint, treat it as an experiment that explains optimizer behavior, not automatic production policy.
• Only after that should you decide whether the right answer is a new index, different SQL shape, fresher stats, or no change at all.

Recollect: The mature DBA move is not ?force the plan I expected.? It is ?prove which path is cheaper on this data, then tune from evidence.?

Section 08

Quiz: can you recognize the right access-path instinct?

Answer these based on optimizer thinking, not index superstition

Q1. Why can TABLE ACCESS FULL be the best plan even when an index exists?

Because Oracle only uses indexes after midnight.

Because scanning the table directly can be cheaper than many index probes and rowid fetches for broad result sets.

Because indexes are ignored when statistics exist.

Because full scans always preserve ordering better.

Correct answer: cost wins. Oracle compares work, not slogans about indexes.

Q2. Which statement best distinguishes INDEX FULL SCAN from INDEX FAST FULL SCAN?

They are identical names for the same operation.

Fast full scan is always slower because it reads more blocks.

Only fast full scan can return indexed columns.

Full scan preserves index-key order; fast full scan treats the index more like a compact segment and does not preserve sorted order.

Correct answer: order is the separator. If Oracle needs the index order, plain full scan stays relevant.

Q3. What does TABLE ACCESS BY INDEX ROWID tell you?

Oracle used an index to find rowids and then fetched base-table rows.

Oracle skipped the table completely.

Oracle read the table in parallel.

Oracle found duplicate keys in the index.

Correct answer: the index was only the locator. The table was still needed afterward.

Q4. When is INDEX UNIQUE SCAN considered?

Whenever a table has any primary key.

Only for descending indexes.

When equality predicates identify all columns of a unique index key.

Whenever the query returns one row at runtime.

Correct answer: it depends on the indexed key definition and predicate form.

Q5. Why can a function on an indexed column change the access path?

Because Oracle disables the optimizer when functions are present.

Because an ordinary index stores the base column value, not necessarily the transformed value used by the predicate.

Because functions automatically trigger bitmap conversion.

Because functions always make the query a full scan.

Correct answer: predicate shape matters. Function-based indexing exists precisely because transformed predicates need matching indexed expressions.

Q6. What is the best role for AUTOTRACE?

It is the only tool you ever need for SQL tuning.

It should replace all runtime plan inspection.

It is useful only for DDL, not for queries.

It is a convenient quick-look tool, but executed-cursor statistics from DBMS_XPLAN.DISPLAY_CURSOR remain more authoritative.

Correct answer: quick visibility, not the final verdict.

Q7. What is the most sensible interpretation of skip scan?

It is a useful optimizer option on some composite-index patterns, not a license to ignore leading-column design forever.

It means leading columns in composite indexes no longer matter.

It is automatically chosen whenever a second indexed column appears in a predicate.

It only works on unique indexes.

Correct answer: treat it as a helpful fallback, not the main design principle.

Q8. What is the healthiest tuning workflow when a plan surprises you?

Immediately hint the path you expected.

Drop every unused-looking index first and check later.

Compare estimated and actual behavior, inspect cursor statistics, and only then decide whether the issue is SQL shape, stats, or indexing.

Judge the plan solely by elapsed time from one run.

Correct answer: evidence first. Strong Oracle tuning is a measurement discipline.