Check the performance of Index Skip Scan

Oracle Performance Series

Index Skip ScanHow Oracle can use a composite index without the leading column, when that helps, and when it does not

Index skip scan is one of those optimizer features that is often summarized too loosely. It does not make column order unimportant, and it does not mean every predicate on a nonleading column will suddenly benefit from a composite index. What it does mean is that Oracle has an extra access path in specific cases: when the leading column is absent, the later column is selective enough, and the leading column has relatively few distinct values.

Leading key absentCore prerequisite

Few distinct prefixesWhy it can work

Later key selectiveWhy it can be worth it

Still cost-basedNot a guarantee

Table of contents

01. Mental modelWhat skip scan actually is02. Optimizer criteriaWhen Oracle considers it03. How it worksLogical subindexes and why distinctness matters04. When it helpsGood fits, weak fits, and design implications05. Reading plansHints, DBMS_XPLAN, and safe testing06. Design guidanceHow skip scan should influence index design07. MisconceptionsWhat skip scan does not mean08. Hands-on labReproducible SQL to observe the access path09. Quiz and takeawaysCheck your instincts

Section 01

Start with the right mental model: skip scan is still one composite index

Oracle documentation describes index skip scan as a way to use a composite index even when the initial column is not specified in the predicate. The optimizer does this by treating the index as a set of logical subindexes, one for each distinct leading-key value. That is why skip scan is closely tied to the number of distinct values in the leading column.

Composite index stays compositeLeading column is absentOracle probes logical slices

Key point

Skip scan is not a normal leading-prefix lookup. It is a fallback access path that becomes interesting when the leading column has relatively few distinct values and the later column is selective enough to justify probing those logical slices.

Bottom line: Skip scan works by probing a composite index through a small number of logical leading-key slices.

Section 02

When the optimizer considers a skip scan

Oracle’s SQL Tuning Guide gives two central conditions. First, the leading column of the composite index is not specified in the query predicate. Second, the leading key has relatively few distinct values while the nonleading key has many. Those two conditions explain both the appeal and the limits of skip scan.

1. Leading key missing

The predicate does not reference the first indexed column.

2. Few leading-key values

A small number of logical subindexes keeps the probe count manageable.

3. Many later-key values

The later column needs enough distinctness to make the probes worthwhile.

4. Cost still wins

Oracle still compares skip scan against other access paths, including full table scan.

Condition	Why it matters
Leading column is absent from the predicate	Without that, Oracle would normally prefer a standard leading-prefix access path instead of skip scan.
Few distinct values in the leading column	This limits the number of logical slices the optimizer must probe.
Many distinct values in the nonleading key	This makes the later-column predicate more selective and therefore more likely to justify skip scan.
Favorable cost estimate	Skip scan is optional. Oracle may still choose a full table scan or another index path.

What matters

A skip scan is usually easiest to picture on an index such as (gender, email), where gender has two values and email is much more selective.

Section 03

How skip scan works and why distinctness changes everything

Oracle documentation says the number of logical subindexes is determined by the number of distinct values in the leading column. That is the performance hinge. Two logical slices can be manageable. Hundreds or thousands can make skip scan unattractive very quickly.

Good fit

Index (cust_gender, cust_email) with two gender values and a highly selective email predicate.

Weak fit

Index (region_code, account_id) where the leading column has many distinct values, multiplying the slice probes.

Bottom line: The leading column’s distinctness drives how many logical slices Oracle has to examine.

Section 04

When skip scan helps, and when it should make you rethink the index

Skip scan is useful when it saves you from discarding a composite index entirely for a later-column predicate. It is much less useful as an excuse to ignore column order. If a query pattern on the nonleading column is common and important, that may still point to a different index design rather than hoping skip scan always carries the workload.

Workload pattern	How skip scan fits	Design instinct
Occasional nonleading-column lookup on an index whose leading column has very few values	Often reasonable	Skip scan may be enough if the pattern is occasional and selective.
Frequent business-critical lookup on the nonleading column	Possible, but not necessarily ideal	Consider whether a dedicated index or a different column order better matches the workload.
Leading column has many distinct values	Usually weak	Do not assume skip scan will rescue the design.
Broad result set where full table scan is cheap	Often unnecessary	Oracle may rightly prefer a table scan.

Design caution

Skip scan is a useful exception, not a replacement for workload-driven index design. A composite index should still be ordered primarily for the predicates you care about most.

Good use

A low-distinctness leading column and a highly selective later-column predicate.

Borderline use

A later-column predicate that matters often enough to deserve its own design discussion.

Bad assumption

Treating skip scan as proof that column order no longer matters.

Section 05

How to read and test skip scans safely

The safest way to reason about skip scan is to observe real plans and compare alternatives. Oracle gives you both plan-display tools and explicit hints for skip scans, including INDEX_SS, INDEX_SS_ASC, INDEX_SS_DESC, and NO_INDEX_SS. Use those hints for controlled testing, not as a substitute for understanding why the plan changed.

1. Gather stats

Give the optimizer current table and index metadata.

2. Run the real statement

Prefer representative predicates and runtime stats.

3. Inspect DBMS_XPLAN

Look for INDEX SKIP SCAN and compare actual row-source behavior.

4. Compare responsibly

Use hints to test alternatives, then remove them and decide from evidence.

SQL - Runtime plan verification

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

ALTER SESSION SET statistics_level = ALL;

SELECT /*+ gather_plan_statistics */ customer_id, cust_email
FROM   customer_demo
WHERE  cust_email = 'user50000@example.com';

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

SQL - Hints for controlled testing

-- Encourage a skip scan on the composite index.
SELECT /*+ INDEX_SS(c customer_demo_gdr_email_ix) */ customer_id
FROM   customer_demo c
WHERE  cust_email = :email_value;

-- Exclude skip scan for the same index and compare the chosen path.
SELECT /*+ NO_INDEX_SS(c customer_demo_gdr_email_ix) */ customer_id
FROM   customer_demo c
WHERE  cust_email = :email_value;

Testing tip

INDEX_SS_ASC and INDEX_SS_DESC exist for skip-scan variants too, but the main design question is usually whether skip scan is appropriate at all.

Section 06

Design guidance: let skip scan inform decisions, not replace them

A composite index that occasionally benefits from skip scan can still be a good design. But if skip-scan-driven queries are frequent enough to matter, you should examine whether column order, overlap with other indexes, or a dedicated index on the later column would better fit the workload.

Healthy interpretation

Skip scan is one more reason not to oversimplify how Oracle uses composite indexes.

Unhealthy interpretation

Skip scan means the leading column does not matter, so any column order will do.

SQL - Test an alternative safely with an invisible index

CREATE INDEX customer_demo_email_ix_inv
  ON customer_demo(cust_email)
  INVISIBLE;

ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

-- Now compare plans carefully in a controlled session.

Why this is useful

Invisible indexes are maintained by DML but ignored by the optimizer unless you enable them explicitly. That makes them useful for comparing a dedicated alternative against a skip-scan path without changing the rest of the workload immediately.

SQL - Monitor whether the existing index is actually used

ALTER INDEX customer_demo_gdr_email_ix MONITORING USAGE;

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

Section 07

Common misconceptions about skip scan

Misconception	Better reading
“A predicate on the second column can never use the composite index.”	Oracle may consider skip scan when the leading column is absent and the distinctness pattern is favorable.
“If skip scan exists, column order is no longer important.”	Column order is still fundamental. Skip scan is an exception, not the main design rule.
“If skip scan is possible, Oracle should always prefer it.”	Skip scan is cost-based. A full table scan or another access path may still be cheaper.
“Few leading-key values are enough by themselves.”	The later key also needs to be selective enough to justify the probes.
“Hints prove the right production design.”	Hints are useful for testing, but the durable decision comes from plan evidence and workload fit.

Predicate nuance

Skip scan is about missing leading keys, not about bypassing every normal rule of composite-index design.

Cost nuance

Even a real skip-scan candidate can lose if the overall cost favors a table scan.

Design nuance

A frequent nonleading-column query may still justify a different index.

Bottom line: Skip scan expands Oracle’s options, but it does not erase the underlying importance of leading-column design.

Section 08

Hands-on lab: observe skip scan on a realistic composite index

This lab gives you a repeatable setup for testing when a later-column predicate may trigger skip scan and how that compares with a dedicated alternative.

Step 1: create a table with a low-distinctness leading column

SQL - Build demo data

DROP TABLE customer_demo PURGE;

CREATE TABLE customer_demo AS
SELECT level AS customer_id,
       CASE
         WHEN MOD(level, 2) = 0 THEN 'M'
         ELSE 'F'
       END AS cust_gender,
       'user' || level || '@example.com' AS cust_email,
       MOD(level, 1000) AS dept_id
FROM   dual
CONNECT BY level <= 100000;

CREATE INDEX customer_demo_gdr_email_ix
  ON customer_demo(cust_gender, cust_email);

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

Step 2: query on the nonleading column

SQL - Candidate skip-scan query

ALTER SESSION SET statistics_level = ALL;

SELECT /*+ gather_plan_statistics */ customer_id, cust_email
FROM   customer_demo
WHERE  cust_email = 'user50000@example.com';

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

Step 3: compare against a dedicated invisible alternative

SQL - Controlled comparison

CREATE INDEX customer_demo_email_ix_inv
  ON customer_demo(cust_email)
  INVISIBLE;

ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

SELECT /*+ gather_plan_statistics */ customer_id, cust_email
FROM   customer_demo
WHERE  cust_email = 'user50000@example.com';

What to inspect

Look for whether the runtime plan shows INDEX SKIP SCAN and how that compares with the dedicated alternative.

What not to assume

Do not assume one plan will win everywhere. Data volume, caching, and statistics still matter.

Section 09

Quiz: do you have the right skip-scan instinct?

Answer from documented Oracle behavior and careful design reasoning

Q1. What has to be absent for a classic skip-scan case to arise?

The table itself.

The leading column of the composite index from the predicate.

All nonleading columns from the predicate.

The index itself.

Correct answer: the leading indexed column is absent. That is the prerequisite that makes Oracle consider skip scan rather than a normal leading-prefix lookup.

Q2. Why does a low-distinctness leading column matter so much?

Because Oracle can then ignore the rest of the index.

Because low distinctness makes every query index-only.

Because it limits how many logical leading-key slices Oracle has to probe.

Because the optimizer requires exactly two values.

Correct answer: fewer distinct leading-key values mean fewer logical probes.

Q3. Which statement best reflects healthy index-design judgment?

Use skip scan as one documented option, but still design indexes around the workload’s main predicate patterns.

Once skip scan exists, column order stops mattering.

A dedicated later-column index is never worth testing if skip scan appears once.

Hints are enough to prove the right production design.

Correct answer: skip scan expands Oracle’s options; it does not replace workload-driven design.

Q4. Why might Oracle still choose a full table scan for a real skip-scan candidate?

Because skip scan is illegal on composite indexes.

Because the optimizer never compares access paths once an index exists.

Because low leading-key distinctness automatically disables indexes.

Because skip scan is still cost-based and another path may be cheaper.

Correct answer: skip scan is optional. Oracle still chooses the access path it estimates as cheapest.

Q5. Which hint excludes skip scan for a specified index during a controlled test?

NO_FULL

NO_INDEX_SS

LEADING

NO_SORT

Correct answer: NO_INDEX_SS.

Q6. What is the safest way to compare a skip-scan path with a dedicated alternative?

Drop the composite index immediately.

Assume skip scan wins because the leading column has two values.

Test in a controlled session with current stats, runtime plans, and if needed an invisible alternative index.

Use only an EXPLAIN PLAN screenshot and stop there.

Correct answer: controlled comparison wins. Use current stats, runtime plan evidence, and safe testing techniques.

Takeaways

Skip scan is useful when a composite index has a missing leading key, a low-distinctness prefix, and a later predicate selective enough to justify probing logical slices. It broadens Oracle’s options, but it does not remove the need to design indexes around real workload patterns and to validate plans carefully.