Testing Different Access Paths : Concatenated Index

Oracle concatenated index deep dive

Concatenated IndexesHow composite indexes really work, why column order matters, and when skip scan changes the story

Concatenated indexes, also called composite indexes, are easy to explain badly and surprisingly rich to explain well. The usual summary is “Oracle can use the index only when the leading column is present,” but that is only the starting point. To design them properly, you need to think about leading portions, equality versus range predicates, ordering requirements, skip scan eligibility, covering behavior, and whether one composite index can replace several single-column indexes in a given workload.

Contents

01 What concatenated indexes are02 Leading edge and leading portion03 Why column order matters04 Skip scan and when it helps05 Access patterns and plan reading06 Covering and sort elimination07 Design rules that actually hold08 Common mistakes09 End-to-end demo10 Knowledge check

Section 01

What a concatenated index actually is

A concatenated index is a single index built on more than one column, such as (department_id, job_id) or (customer_id, order_date, status). These are also known as composite indexes, and their usefulness depends heavily on whether queries can use a leading portion of the key. That phrase - leading portion - is the heart of composite-index design.

Key idea

If an index is defined on (A, B, C), then the leading portions are (A), (A, B), and (A, B, C). This is why a query on B alone is fundamentally different from a query on A alone.

Recollect: A composite index is not just “three single-column indexes stuck together.” It is an ordered search structure, and order is the whole story.

Section 02

The leading-edge rule, explained properly

Oracle’s tuning guidance says a composite index can support an access path when the SQL statement uses a leading portion of the index. That is the basic rule behind range scans on composite keys. If the first column is missing, the optimizer usually cannot do a normal index range scan based on the composite ordering. This is why queries on A, A+B, or A+B+C behave differently from a query on B alone.

Classic range-scan friendly predicates

• WHERE A = :1
• WHERE A = :1 AND B = :2
• WHERE A = :1 AND B = :2 AND C = :3
• WHERE A = :1 AND B > :2

Not leading by default

• WHERE B = :1
• WHERE C = :1
• WHERE B = :1 AND C = :2
• WHERE C BETWEEN :1 AND :2

Important nuance

“Cannot use the index” is too strong in modern Oracle. A non-leading predicate often prevents a standard range scan, but the optimizer may still consider INDEX SKIP SCAN when the leading column has very few distinct values.

Section 03

Why column order matters so much

Column order affects far more than whether a predicate is legal for a range scan. It also affects selectivity usage, sort elimination opportunities, the usefulness of the index for partially specified predicates, and whether a range condition cuts off the effective use of columns that come after it. Oracle’s index guidance emphasizes designing composite indexes around actual query patterns rather than abstract rules.

Equality first

Columns commonly used in equality predicates are often strong candidates for earlier positions.

Range later

A range predicate often limits how much benefit later columns can provide for access.

Ordering-aware design

If queries often need ORDER BY A, B, matching index order can reduce or remove sorting work.

Avoid slogans

The advice “put the most selective column first” is not universally right. If your workload almost always filters on a less-selective column first and rarely on the more-selective one alone, query pattern can matter more than raw selectivity.

Section 04

Index skip scan: the exception that keeps confusing people

Index skip scan is why composite-index discussions often become muddled. Oracle can sometimes use a composite index even when the leading column is not present, but only by logically probing multiple subtrees for the missing leading-column values. Oracle documents this access path as INDEX SKIP SCAN and describes it as most useful when the leading column has low cardinality. In other words: skip scan is real, but it is not a reason to ignore column order.

SQL - Encourage skip scan for testing

EXPLAIN PLAN FOR
SELECT /*+ INDEX_SS(e idx_emp_dept_job) */ *
FROM   emp_test e
WHERE  job_id = 'ST_CLERK';

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

Healthy interpretation

If skip scan appears, treat it as an optimizer option that can sometimes rescue a non-leading predicate, not as proof that column order no longer matters.

Section 05

Reading execution plans for concatenated indexes

Oracle plans tell you a lot once you know what to look for. A composite index may be used via INDEX RANGE SCAN, INDEX UNIQUE SCAN, INDEX FULL SCAN, or INDEX SKIP SCAN depending on the predicate shape and selectivity. The goal is not to memorize plan names blindly, but to tie each access path back to the SQL pattern that produced it.

Plan operator	Typical meaning here	What it suggests
INDEX RANGE SCAN	Oracle is traversing a leading portion of the index	Classic composite-index success case
INDEX UNIQUE SCAN	All key columns of a unique index are specified	Most precise access pattern
INDEX SKIP SCAN	Oracle is probing non-leading predicates through multiple leading-key branches	Possible rescue path, but workload-dependent
INDEX FULL SCAN	Oracle may be using the index for ordering or covering needs	Useful in some report and sort-elimination cases

SQL - Compare leading and non-leading plans

EXPLAIN PLAN FOR
SELECT * FROM emp_test WHERE department_id = 50;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
SELECT * FROM emp_test WHERE job_id = 'ST_CLERK';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Recollect: Plans are the truth serum for composite-index design. If the plan shape contradicts your mental model, trust the plan and revisit the model.

Section 06

Covering behavior and sort elimination

Composite indexes are useful not just for filtering but also for returning rows in a useful order and, in some cases, avoiding table access when all required columns are present in the index. Oracle documents that concatenated indexes can help satisfy ORDER BY clauses efficiently when the ordering matches the index structure closely enough.

Sort elimination

If a query filters and orders in a way that matches index order, Oracle may avoid a separate sort step.

Covering potential

If the query can be answered from the index alone, Oracle may choose an index-only style access path or at least reduce table visits.

SQL - Composite index that helps filter and order

CREATE INDEX idx_orders_cust_date_status
  ON orders(customer_id, order_date, status);

EXPLAIN PLAN FOR
SELECT customer_id, order_date, status
FROM   orders
WHERE  customer_id = 101
ORDER BY order_date, status;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

This is one reason blindly splitting a composite index into multiple single-column indexes can make a workload worse: you may lose both access efficiency and ordering benefit.

Section 07

Design rules that hold up in real workloads

The best rules are those that survive contact with real applications. Oracle’s performance guidance consistently points toward designing indexes around actual predicate patterns and optimizer behavior rather than slogans.

Rule 1

Lead with columns that the workload commonly constrains first, especially equality predicates.

Rule 2

Push range columns later if earlier equality predicates are what make the access path useful.

Rule 3

Consider ORDER BY and grouping needs, not just the WHERE clause.

Rule 4

Do not assume one composite index can replace every access path you care about. Plans decide that, not hope.

Balanced rule of thumb

Put columns first because the workload needs them first, not because an isolated selectivity formula told you to.

Section 08

Common mistakes and how to avoid them

Mistake 1

Assuming a query on the second column will always ignore the index entirely.

Mistake 2

Believing “most selective first” is always the right answer.

Mistake 3

Creating many overlapping composite indexes without checking whether they duplicate each other.

Operational caution

Too many overlapping composite indexes increase DML cost, maintenance overhead, and optimizer complexity. Composite indexes should simplify the workload, not create an index graveyard.

Section 09

End-to-end demo: testing a composite index on department and job

This demo uses a safe and familiar sample pattern: build a larger test table from HR data, create a composite index on (department_id, job_id), gather stats, and compare plan shapes for leading-column, full-key, and non-leading-column predicates. The goal is not to invent fixed costs or elapsed times, because those depend on environment. The goal is to show real commands and the exact plan patterns you should verify.

Demo principle

Use exact commands, then verify with metadata and plans. Do not teach fake outputs where Oracle’s optimizer would legitimately vary by data volume and statistics.

Step 1: create the demo table and gather stats

SQL - Build test data

CREATE TABLE emp_test AS
SELECT employee_id,
       department_id,
       job_id,
       salary,
       last_name
FROM   hr.employees
       CROSS JOIN (
         SELECT level AS n
         FROM   dual
         CONNECT BY level <= 1000);

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'EMP_TEST',
    cascade => FALSE);
END;
/

Step 2: create the concatenated index and verify it exists

SQL - Create and verify index

CREATE INDEX idx_emp_dept_job
  ON emp_test(department_id, job_id);

SELECT index_name,
       index_type,
       status,
       visibility
FROM   user_indexes
WHERE  index_name = 'IDX_EMP_DEPT_JOB';

The success condition here is one visible row in USER_INDEXES with the expected index name and a usable status.

Step 3: test the leading-column predicate

SQL - Leading column only

EXPLAIN PLAN FOR
SELECT *
FROM   emp_test
WHERE  department_id = 50;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

The plan you hope to see is an index access path such as INDEX RANGE SCAN on IDX_EMP_DEPT_JOB, because the leading column is present.

Step 4: test both indexed columns together

SQL - Full leading portion

EXPLAIN PLAN FOR
SELECT *
FROM   emp_test
WHERE  department_id = 50
  AND  job_id = 'ST_CLERK';

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

This is the classic sweet spot. The plan should normally show the composite index used very efficiently because both leading columns are constrained.

Step 5: test the non-leading column alone

SQL - Non-leading predicate only

EXPLAIN PLAN FOR
SELECT *
FROM   emp_test
WHERE  job_id = 'ST_CLERK';

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

At this point the plan may show a full table scan, or it may show an INDEX SKIP SCAN if Oracle decides the leading column has few enough distinct values for that to be worthwhile. Either result is consistent with Oracle’s documented behavior; that is why the demo avoids pretending one outcome is guaranteed on every system.

Step 6: test skip scan explicitly

SQL - Encourage skip scan

EXPLAIN PLAN FOR
SELECT /*+ INDEX_SS(emp_test idx_emp_dept_job) */ *
FROM   emp_test
WHERE  job_id = 'ST_CLERK';

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

The success condition here is not a specific cost. It is whether the plan now shows INDEX SKIP SCAN, which proves the optimizer can sometimes use the composite index even without the leading column predicate.

Step 7: test with actual execution statistics

SQL - Runtime stats

SELECT /*+ gather_plan_statistics */ *
FROM   emp_test
WHERE  department_id = 50
  AND  job_id = 'ST_CLERK';

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

What counts as a successful demo?

Success means the index exists, plans for leading-column predicates show normal composite-index access, the non-leading-only case demonstrates the leading-edge rule, and skip scan can be demonstrated honestly as a conditional exception rather than a guarantee.

Section 10

Knowledge check

These questions are built to sharpen judgment, not just terminology. Submit answers and read the explanations carefully.

Q1. For an index on (A, B, C), which set correctly describes the leading portions?

(B), (C), (B,C)

(A), (A,B), (A,B,C)

(A,C), (B,C), (C)

Any subset of the columns is a leading portion.

Correct answer: The leading portions start from the first column. That is the central structural rule behind how concatenated indexes are searched efficiently.

Q2. Why can a query on the second column alone behave differently from a query on the first column alone?

Because Oracle forbids references to the second column in SQL.

Because the second column is automatically invisible.

Because a standard range scan depends on the ordered leading structure of the index, and the first column anchors that order.

Because Oracle always rewrites second-column predicates into bitmap scans.

Correct answer: The first column anchors the index ordering. That is why the leading-edge rule exists in the first place.

Q3. What is the healthiest way to think about INDEX SKIP SCAN?

It is a useful exception that can sometimes help with non-leading predicates, but it does not remove the importance of column order.

It proves the leading-column rule is obsolete.

It always beats a full table scan.

It only works on unique indexes.

Correct answer: Skip scan is conditional, not magical. It can help when the leading column has low cardinality, but it is not a substitute for good composite-index design.

Q4. Which statement about column order is the most accurate?

The most selective column must always be first, no exceptions.

Column order only matters for unique indexes.

Order matters only for sorting, never for filtering.

Column order should reflect real predicate patterns, equality versus range usage, and ordering needs.

Correct answer: Workload pattern decides. Selectivity matters, but so do predicate shape and ordering behavior. Good design balances all of them.

Q5. In the demo, what is the safest way to validate that the concatenated index behaved as expected?

Assume success because the index creation statement completed.

Verify index metadata and compare execution-plan operators for leading, full-key, and non-leading predicates.

Only look at table row counts.

Drop all single-column indexes automatically.

Correct answer: Plans plus metadata tell the real story. Composite-index design is validated by what Oracle actually does, not by what we hoped it would do.

Q6. Why can a composite index help with ORDER BY as well as filtering?

Because every index automatically sorts all result sets.

Because ORDER BY is ignored when an index exists.

Because if the query’s order matches the index order closely enough, Oracle may reduce or avoid an explicit sort.

Because concatenated indexes are always covering indexes.

Correct answer: Index order can do double duty. It can help locate rows and sometimes help return them in a useful order too.

Q7. What is the healthiest rule of thumb for composite-index design?

Design around actual workload patterns and verify with execution plans rather than relying on slogans.

Put columns in alphabetical order for clarity.

Never create a composite index if single-column indexes already exist.

Always replace every single-column index with one large composite index.

Correct answer: Workload first, slogans second. Oracle indexing decisions become much clearer when you let real SQL patterns and real plans drive the design.

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.

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.