Testing Different Access Paths : Compare Single Column Index Access

Oracle Performance Series

Oracle Single-Column Index AccessHow Oracle decides between unique scan, range scan, full table scan, full index scan, and fast full index scan

Single-column indexes look simple, but the execution plans they trigger are shaped by selectivity, row ordering, rowid lookup cost, requested columns, and whether the optimizer can get the answer from the index alone. This guide turns that apparently small topic into a practical mental model you can use when reading plans and designing indexes.

Contents

01 Foundations02 Unique vs range scan03 Why full scans win04 Clustering factor05 Full vs fast full06 Covering and ordering07 Reading real plans08 End-to-end lab09 Quiz

Section 01

The real question is not “is there an index?” but “what is the cheapest way to get the rows?”

Oracle does not reward a query just because an index exists. The optimizer compares access paths and chooses the one with the lowest estimated work. With a single-column B-tree index, that work may involve probing the index, collecting rowids, visiting table blocks, and optionally sorting. In other cases a full table scan or a full index scan is cheaper because it touches storage more efficiently.

A healthy DBA instinct is to separate index lookup cost from table row retrieval cost. Many plans look index-friendly at first glance, but the expensive part is actually the flood of table block visits that comes after the index returns rowids.

What an index helps with

Locating rowids quickly, preserving index-key order, and sometimes returning all required columns without touching the table.

What it does not guarantee

That Oracle will choose it, that it will outperform a full scan, or that an equality predicate will always show as a unique scan.

What the optimizer weighs

Estimated cardinality, I/O pattern, clustering factor, sort elimination, and whether the table must still be visited.

Recollect: A B-tree index is an access path, not a performance guarantee. The cost of fetching the actual table rows is often the deciding factor.

Section 02

Index unique scan versus index range scan

An INDEX UNIQUE SCAN is the tightest B-tree access path. Oracle uses it when an equality predicate can identify at most one entry in a unique index. An INDEX RANGE SCAN is broader: it walks a portion of the index in key order and returns one or more rowids. Equality predicates on non-unique indexes, range predicates, prefix LIKE predicates, and partial key matches naturally fit the range-scan family.

Unique scan in practice

• Equality on a unique or primary-key-backed index
• At most one rowid expected
• Usually followed by TABLE ACCESS BY INDEX ROWID unless the index alone satisfies the query

Range scan in practice

• Equality on a non-unique column
• BETWEEN, >, <, and prefix LIKE 'A%'
• Ordered retrieval of matching index entries

Subtle but important

A query can target a logical primary key and still show INDEX RANGE SCAN if the underlying index is non-unique. Do not over-interpret the word “range” as automatically meaning “many rows.”

SQL - Unique and range candidates

EXPLAIN PLAN FOR
SELECT *
FROM   access_test
WHERE  id = 12345;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
SELECT *
FROM   access_test
WHERE  id BETWEEN 1000 AND 1200;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
SELECT *
FROM   access_test
WHERE  region LIKE 'N%';

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

Predicate shape	Likely index access	Why
id = 12345	INDEX UNIQUE SCAN if the index is unique	The key can identify at most one entry.
status = 'ACTIVE'	INDEX RANGE SCAN or full table scan	Many rows may match, so rowid cost matters.
id BETWEEN 1 AND 500	INDEX RANGE SCAN	The optimizer can walk a start key to a stop key.
region LIKE 'NO%'	INDEX RANGE SCAN	Prefix matching preserves a navigable key range.

Section 03

Why Oracle may choose a full table scan even when the index is perfectly valid

A full table scan reads every formatted block below the high water mark, and Oracle can read those blocks efficiently with multiblock I/O. That can beat an index-driven path when the query returns a large fraction of the table, when the table is small, when parallelism makes the scan attractive, or when fetching rows by rowid would bounce across many table blocks.

This is why folklore like “Oracle stops using the index after 10%” is not reliable. The optimizer does not use a universal percentage cutoff. It estimates total work for the available alternatives.

Full scan strengths

Sequential access, multiblock reads, good fit for large result sets, and often simpler than many random rowid probes.

Index path strengths

Excellent for selective predicates, ordered retrieval, and index-only answers.

Common beginner mistake

Judging the plan only by predicate type instead of by the estimated number and distribution of returned rows.

Avoid slogan tuning

If a plan uses TABLE ACCESS FULL, your first question should be “how many rows and blocks did Oracle expect to touch?” not “why is Oracle ignoring my index?”

SQL - A low-selectivity example

EXPLAIN PLAN FOR
SELECT *
FROM   access_test
WHERE  status = 'INACTIVE';

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
SELECT *
FROM   access_test
WHERE  status = 'ACTIVE';

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

Recollect: Index access has two costs: reading index entries and then visiting table blocks. Full scans avoid that second step-by-step chase.

Section 04

Clustering factor explains why two equally selective indexes can behave very differently

The clustering factor is Oracle’s way of estimating how table rows are physically scattered relative to the index key order. If neighboring index entries tend to point to rows stored in the same or nearby table blocks, the clustering factor is low and index-driven table access is attractive. If adjacent index entries point all over the table, the clustering factor is high and Oracle estimates many table block visits.

Think of it as a measure of rowid locality. A good clustering factor does not mean the index is “more selective”; it means the trip from index entry to table row is cheaper because the rowids are physically less chaotic.

SQL - Inspect clustering factor

SELECT i.index_name,
       i.num_rows,
       i.clustering_factor,
       t.blocks,
       t.num_rows AS table_rows
FROM   user_indexes i
JOIN   user_tables  t
       ON t.table_name = i.table_name
WHERE  i.table_name = 'ACCESS_TEST'
ORDER  BY i.index_name;

How to read it

When the clustering factor is closer to the number of table blocks, index-driven row retrieval is usually more locality-friendly. When it drifts closer to the number of rows, Oracle expects more scattered table access.

Recollect: Selectivity answers “how many rows?” Clustering factor answers “how expensive is the table walk after the index finds those rows?”

Section 05

Index full scan versus index fast full scan: same object, different behavior

These two plan operations are commonly confused because both can read a large part of the index. They are not interchangeable.

INDEX FULL SCAN

• Reads the index in key order
• Can support ordering requirements
• Useful when Oracle wants the index order itself

INDEX FAST FULL SCAN

• Reads index blocks as a skinny structure
• Does not preserve sorted key order
• Often attractive for index-only aggregation or broad filtering

An easy mental model is this: a full scan walks the index as an ordered tree; a fast full scan reads the index as a compact segment. If Oracle needs sorted keys, plain full scan remains relevant. If it only needs the indexed columns and wants fast broad access, fast full scan may win.

SQL - Contrasting full and fast full behavior

EXPLAIN PLAN FOR
SELECT id
FROM   access_test
ORDER  BY id;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

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

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

Do not misread the word “full”

INDEX FULL SCAN does not mean “bad plan.” It means Oracle decided that scanning the index in order was cheaper or more useful than another path.

Section 06

Covering access and sort elimination: when a single-column index does more than filtering

A single-column index becomes especially valuable when the query can be answered from the index alone or when the requested order matches the index order. In those cases Oracle may avoid table access, sorting, or both. This is one reason execution-plan reading should focus on the whole row source tree, not just the first access operation.

Covering effect

If the statement only needs the indexed column or an aggregate that can be answered from indexed entries, Oracle may avoid touching the table.

Ordering effect

If rows are already available in the required order from the index, an explicit sort step may disappear.

Trade-off

Once the query requests non-indexed columns for many rowids, the table visit cost returns to center stage.

SQL - Seeing index-only and ordering benefits

EXPLAIN PLAN FOR
SELECT id
FROM   access_test
WHERE  id BETWEEN 10000 AND 10100
ORDER  BY id;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
SELECT COUNT(status)
FROM   access_test
WHERE  status = 'ACTIVE';

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

Practical reading tip

If you see TABLE ACCESS BY INDEX ROWID, the query still needed table data after probing the index. If that table access disappears, the index is acting like a smaller, cheaper structure for that particular statement.

Section 07

How to read real plans without fooling yourself

Execution-plan names are useful, but they are not enough by themselves. Good plan reading combines the access operation, estimated and actual rows, predicates, and whether the table was revisited many times after the index probe. The safest habit is to compare estimated behavior with observed behavior from cursor statistics.

Plan line	What it means	What to ask next
INDEX UNIQUE SCAN	At most one matching index entry is expected.	Did Oracle still need a table lookup afterward?
INDEX RANGE SCAN	A navigable subset of the index is read in key order.	How many rowids came back, and how scattered were the table blocks?
INDEX FULL SCAN	The index is read in order.	Was Oracle using the index for ordering or broad but ordered access?
INDEX FAST FULL SCAN	The index is scanned as a compact structure without order preservation.	Was the query index-only, such as a count or projection of indexed columns?
TABLE ACCESS BY INDEX ROWID	Oracle found rowids from the index and then fetched table rows.	Is that rowid phase the real cost center?
TABLE ACCESS FULL	The table is read directly, usually with multiblock I/O.	Was the result set large enough that this was reasonable?

SQL - Use runtime plan statistics

ALTER SESSION SET statistics_level = ALL;

SELECT /*+ gather_plan_statistics */ *
FROM   access_test
WHERE  region = 'NORTH';

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

A modern plan nuance

On some systems you may see TABLE ACCESS BY INDEX ROWID BATCHED. That means Oracle is grouping rowid-based table fetches more efficiently. It does not change the basic logic; it refines the table-lookup phase.

1. Read the access line

Which object gets probed first?

2. Read predicates

What keys or ranges are navigable?

3. Read rows

How many rows did Oracle expect versus actually process?

4. Read table visits

Did rowid lookups dominate the work?

Recollect: A plan name is the headline. Cardinality, predicates, and row-source statistics are the actual story.

Section 08

End-to-end lab: one table, three indexes, several access paths

This lab is designed for a learner’s sandbox. It does not invent fake benchmark numbers. Instead, it gives you a reproducible setup, tells you what plan shapes to expect, and shows you how to verify the optimizer’s choice with runtime statistics.

Step 1: Build a table with mixed selectivity

SQL - Create demo data

DROP TABLE access_test PURGE;

CREATE TABLE access_test (
  id         NUMBER       NOT NULL,
  status     VARCHAR2(10) NOT NULL,
  region     VARCHAR2(20) NOT NULL,
  amount     NUMBER       NOT NULL,
  created_on DATE         NOT NULL
);

INSERT INTO access_test
SELECT level,
       CASE
         WHEN MOD(level,10) = 0 THEN 'ACTIVE'
         ELSE 'INACTIVE'
       END,
       CASE MOD(level,5)
         WHEN 0 THEN 'NORTH'
         WHEN 1 THEN 'SOUTH'
         WHEN 2 THEN 'EAST'
         WHEN 3 THEN 'WEST'
         ELSE 'CENTRAL'
       END,
       ROUND(DBMS_RANDOM.VALUE(100,10000)),
       DATE '2025-01-01' + MOD(level,365)
FROM   dual
CONNECT BY level <= 100000;

COMMIT;

ALTER TABLE access_test
  ADD CONSTRAINT access_test_pk PRIMARY KEY (id);

CREATE INDEX idx_access_status ON access_test(status);
CREATE INDEX idx_access_region ON access_test(region);

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

Step 2: Confirm the object statistics

SQL - Inspect the data profile

SELECT COUNT(*) AS total_rows,
       SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_rows,
       SUM(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END) AS inactive_rows
FROM   access_test;

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

You should observe a very selective key column ID, a low-cardinality STATUS column with a 10/90 split, and a five-value REGION column. That mix is excellent for seeing why Oracle uses different access paths.

Step 3: Probe a unique lookup

SQL - Unique lookup verification

ALTER SESSION SET statistics_level = ALL;

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

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

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

Step 4: Probe a range lookup

SQL - Range lookup verification

SELECT /*+ gather_plan_statistics */ id, amount
FROM   access_test
WHERE  id BETWEEN 42000 AND 42100
ORDER  BY id;

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

What to verify: expect INDEX RANGE SCAN. Because the requested order matches the key order, Oracle may not need a separate sort operation.

Step 5: Test low selectivity and watch for a table scan

SQL - Low-selectivity filter

SELECT /*+ gather_plan_statistics */ COUNT(*)
FROM   access_test
WHERE  status = 'INACTIVE';

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

What to verify: depending on your environment, Oracle may prefer a full table scan because the predicate returns most of the table. If it chooses the index, compare logical I/O and actual rows carefully before concluding that the plan is better.

Step 6: See when the index acts like a skinny structure

SQL - Broad index-only work

SELECT /*+ gather_plan_statistics */ COUNT(id)
FROM   access_test
WHERE  id IS NOT NULL;

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

What to verify: this is a classic situation where Oracle may consider an index full or fast full scan, because the query can potentially be satisfied from index entries without fetching the whole table row.

Step 7: Compare plans before forcing hints

SQL - A controlled comparison

SELECT /*+ gather_plan_statistics */ COUNT(*)
FROM   access_test
WHERE  region = 'NORTH';

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

SELECT /*+ gather_plan_statistics INDEX(access_test idx_access_region) */ COUNT(*)
FROM   access_test
WHERE  region = 'NORTH';

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

Important tuning discipline

Use hints here only as a teaching tool to compare alternatives. In real tuning, force an index only after proving that the optimizer’s estimate is wrong or that statistics and data distribution information are incomplete.

Design rules that survive real workloads

• Create a single-column index when the application frequently filters by that column and the resulting row set is selective enough to justify rowid lookups.
• Do not expect a low-cardinality column to be helped automatically by a B-tree index; the result-set size and clustering factor often dominate.
• Gather fresh optimizer statistics after meaningful data changes.
• Read the full plan tree. A beautiful index access line may still lead to expensive table fetches.
• Do not rely on folklore thresholds. Verify with actual plans and cursor statistics.
• Avoid wrapping indexed columns in functions unless you intentionally designed a matching function-based index.

Section 09

Quiz: build plan-reading instincts

Check whether the concepts feel operational, not just familiar

Q1. Why can a full table scan beat an index on a column that appears in the predicate?

Because Oracle ignores indexes on weekdays.

Because reading many rowids and then many scattered table blocks can cost more than scanning the table directly.

Because B-tree indexes only work for primary keys.

Because a full scan always returns sorted results.

Correct answer: table-row retrieval matters. Index access is often cheap; the expensive part can be the follow-up table block visits.

Q2. What is the best interpretation of clustering factor?

It is the number of leaf blocks in the index.

It measures only how many distinct values the column has.

It estimates how table rows are physically ordered relative to the index key sequence, affecting rowid lookup cost.

It determines whether Oracle can use bind variables.

Correct answer: it is about rowid locality. Good locality makes index-driven table access cheaper.

Q3. Which access path preserves index-key order?

INDEX FULL SCAN

INDEX FAST FULL SCAN

TABLE ACCESS FULL

None of them can preserve order.

Correct answer: INDEX FULL SCAN. Fast full scan treats the index more like a compact segment and does not preserve sorted key order.

Q4. A query asks for SELECT id FROM access_test ORDER BY id. Why might Oracle favor an index-based plan?

Because Oracle must use the primary key for every ordered query.

Because sorting is forbidden if an index exists.

Because all full scans are slower than index scans.

Because the index may already provide the needed column in the needed order, reducing both table work and sorting.

Correct answer: the index can do double duty. It can provide both access and ordering.

Q5. What should you verify before hinting Oracle to force a single-column index?

Only whether the column is mentioned in the WHERE clause.

Whether statistics, cardinality estimates, and actual row-source behavior really show the optimizer made a poor choice.

Whether the table name is short enough.

Nothing; hints are the first tuning step.

Correct answer: prove the optimizer is wrong first. Otherwise you often turn a teachable plan into a brittle one.

Q6. Why can a predicate on a non-unique indexed column still use INDEX RANGE SCAN for equality?

Because Oracle converts every equality predicate into a full scan.

Because equality is impossible on non-unique columns.

Because Oracle still navigates a key range that may contain multiple matching entries.

Because range scans are only for descending indexes.

Correct answer: equality on a non-unique key still means “scan the matching range.”

Q7. Which mindset leads to the best indexing decisions?

Study workload patterns, read the full plan, compare estimated and actual behavior, and then design or tune the index.

Create indexes on every searchable column and let storage figure it out.

Always prefer single-column indexes to any other kind.

Judge success only by whether the word “INDEX” appears in the plan.

Correct answer: workload-aware design wins. Good Oracle tuning comes from plan literacy, not from slogans.