Enhance Skills Daily

March 13, 2023  ·   ·   ·   ·   ·   ·   ·   · 

Testing Different Access Paths : With and Without Index

Oracle Different Access Paths - Practical Deep Dive
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.

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.
Predicate shapeequality, range, function,nullable, leading column?Work estimaterows, blocks, rowid hops,sort elimination, orderChosen pathtable full scan, rowid,unique, range, full, skip
Recollect: An access path answers one question: what is the cheapest route from predicate to rows for this statement on this data shape?

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);
Full table scanIndex then rowid fetchRead the table blocks directly, then filter rows.IndexProbe index first, then fetch matching table rows by rowid.

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 pathTypical triggerWhy Oracle chooses it
INDEX UNIQUE SCANEquality on all columns of a unique index keyAt most one rowid is expected, so the index probe is extremely tight.
INDEX RANGE SCANNon-unique equality, ranges, prefix LIKE, inequalitiesOracle can walk a subset of the ordered index and return matching rowids.
INDEX FULL SCANBroad ordered access, order-by elimination, or index-only retrieval in key orderThe optimizer wants the index order itself.
INDEX FAST FULL SCANBroad index-only access where ordering is unnecessaryThe 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.

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
How skip scan thinks about a composite indexLeading keySecond keyABC1001, 1002, 1003 ...1001, 1002, 1003 ...1001, 1002, 1003 ...Oracle may probe logical subindexes for each leading-key value.
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.

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.

ToolBest useLimitation
EXPLAIN PLANQuick first draft of the planNot the final word on actual runtime behavior
AUTOTRACEFast plan/statistics feedback in SQL*PlusConvenient, but still not a substitute for reading the executed cursor carefully
DBMS_XPLAN.DISPLAY_CURSORBest way to inspect executed cursor detailsRequires the statement to be executed first

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.

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.?

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.

March 06, 2023  ·   ·   ·   ·   ·   · 

Column Order in Composite Index

Column Order in Oracle Composite Indexes - Practical Deep Dive
Oracle Performance Series

Column Order in Composite IndexesHow leading portions, skip scans, ordering, and workload shape the right key sequence

Composite indexes are not just several columns bundled into one object. Their order determines which predicates form a usable key prefix, which statements can benefit from ordered access, and whether one index can support several important query patterns or only one narrow path. In Oracle, that makes column order a design choice with optimizer consequences.

Leading portionsThe central rule
Skip scanThe important exception
Workload fitDesign by query pattern
Plans decideValidate, then trust

Start with the right mental model: a composite index is one ordered key

Oracle documentation uses both composite and concatenated index. Both terms describe the same concept: Oracle stores one ordered key, not several independent mini-indexes. An index on (customer_id, order_date, status) is ordered first by customer_id, then by order_date within each customer, and then by status.

Leading portion controls navigability Column order changes usable SQL shapes One index can serve several prefixes
Key idea

If an index is defined as (A, B, C), the documented leading portions are (A), (A, B), and (A, B, C). That is why a predicate on B alone is fundamentally different from a predicate on A alone.

Index definition: (customer_id, order_date, status) Leading portion 1 customer_id Leading portion 2 customer_id order_date Leading portion 3 customer_id order_date status Columns farther to the right depend more heavily on earlier columns being useful to the statement.

Good instinct

Ask which predicate patterns must work when only part of the key is supplied.

Weak instinct

Treat the index as if Oracle can jump to any column position for free.
Bottom line: Column order matters because Oracle navigates the key from the left.

How order changes which query patterns the index can serve

Suppose two possible indexes exist on the same table: (customer_id, order_date) and (order_date, customer_id). They contain the same columns, but they do not support the same workload. The difference is concrete: each order exposes a different leading prefix.

Typical predicate Index (customer_id, order_date) Index (order_date, customer_id) Reason
WHERE customer_id = :b1 Strong fit No leading prefix The first index exposes customer_id as a documented leading portion.
WHERE customer_id = :b1 AND order_date >= :b2 Strong fit Possible for date-led SQL, but not aligned to the same prefix The first order matches the common “customer first, then date range” pattern.
WHERE order_date BETWEEN :d1 AND :d2 No leading prefix Strong fit Only the second index leads with order_date.
WHERE customer_id = :b1 ORDER BY order_date Often attractive Poor alignment The first order can both filter by customer and preserve date order within that customer slice.
What this means

A single composite index can satisfy several important statements only when the workload uses compatible leading prefixes. Same columns does not mean same usefulness.

SQL - The same columns in different orders
CREATE INDEX idx_orders_cust_date
  ON orders(customer_id, order_date);

CREATE INDEX idx_orders_date_cust
  ON orders(order_date, customer_id);

-- Query pattern A: customer lookup with optional date range
SELECT *
FROM   orders
WHERE  customer_id = :cust_id
AND    order_date >= :start_date;

-- Query pattern B: reporting by date range
SELECT *
FROM   orders
WHERE  order_date BETWEEN :d1 AND :d2;

How to choose the order without falling for bad rules of thumb

“Put the most selective column first” is too blunt to be a complete design rule. Selectivity matters, but Oracle’s own guidance starts with workload shape: which keys appear together, which keys appear alone, and which leading prefixes let multiple important statements use the same index.

1. Lead with reused prefixes

If the workload often filters by customer_id alone and also by customer_id + order_date, leading with customer_id gives both patterns a useful prefix.

2. Keep equality predicates early

Earlier equality predicates usually preserve a longer navigable prefix than earlier broad range predicates.

3. Include ordering needs

If important statements filter on one key and return rows ordered by the next key, the index order can help twice.

4. Validate with plans

When two orders are plausible, compare real plan behavior instead of relying on rules of thumb.
When (A, B) is usually better

A appears by itself often, and the common two-column statements are A = ... AND B ....

When (B, A) may be better

The real workload mostly starts with B, or reporting statements are driven primarily by B and only sometimes add A.

Observed workload patternUsually stronger orderReasoning
WHERE account_id = :x and WHERE account_id = :x AND tran_date >= :d(account_id, tran_date)The shorter and longer patterns share the same leading prefix.
WHERE status = :s is rare, but WHERE status = :s AND created_on BETWEEN ... is commonDepends on whether status must ever drive access aloneIf not, another order may better reflect the real access path.
WHERE region = :r ORDER BY sale_date(region, sale_date)That order can align both filtering and sorting needs.
Queries frequently omit column A but use B heavilyDo not assume (A, B) is enoughThe missing leading column may require a different index or may rely only on skip scan in narrow cases.
Design caution

A selective column placed first is not automatically the right answer if that order makes the workload’s common partial predicates unusable. Column order is about useful prefixes, not just raw distinct-count trivia.

Bottom line: Choose the order that matches how SQL reaches the table in production.

Equality first, broader ranges later, and never ignore ORDER BY

Once you understand leading portions, a practical pattern becomes clear: earlier equality predicates usually let Oracle navigate deeper into the key before the scan opens into a range. That is why an index such as (customer_id, order_date) is often a stronger fit for statements like customer_id = :x AND order_date >= :d than the reverse order.

Index order (customer_id, order_date) Equality customer_id Range order_date Oracle can navigate to one customer slice and then scan the relevant date range inside it. Index order (order_date, customer_id) Range first order_date Later key customer_id Useful for date-driven SQL, but less aligned to the “one customer, then date range” pattern.
Ordering benefit

Oracle documentation also notes that an index can eliminate a sort when the ORDER BY columns match the order of the leading index columns. Column order therefore has to be evaluated against sorting needs as well as filtering predicates.

SQL - One index helping filter and order
CREATE INDEX idx_sales_region_date
  ON sales(region, sale_date);

SELECT sale_id, sale_date, amount
FROM   sales
WHERE  region = :region
ORDER BY sale_date;

-- This order is attractive because the region slice is narrow first,
-- and rows inside that slice are already arranged by sale_date.
Design questionWhy it matters
Does the workload frequently do equality on one column and range on the next?That pattern often benefits from putting the equality-driving column first.
Does the query need rows back in key order?The right composite order can reduce or remove an explicit sort.
Is the query broad enough that Oracle may prefer a full scan anyway?Even a well-ordered index remains optional to the optimizer, not mandatory.

Index skip scan is the exception that softens the old oversimplification

A common simplification is that Oracle can use a composite index only when the leading column is present. Oracle documentation qualifies that statement: index skip scan is the main exception. Skip scan lets Oracle consider a composite index even when the leading column is missing by conceptually probing logical subindexes based on the leading key’s distinct values.

Conceptual skip scan on index (gender, email) Logical slice 1 gender = 'F' probe email inside the F slice Logical slice 2 gender = 'M' probe email inside the M slice Predicate email = :target leading key not supplied Skip scan is most plausible when the leading column has few distinct values and the later column is selective enough to justify those probes.
Skip-scan limit

Skip scan does not make column order irrelevant. It adds another optimizer option in some cases. It is not a blanket substitute for a properly ordered index.

Good skip-scan candidate

Leading key has very few values and the later key sharply narrows the search.

Weak skip-scan candidate

Leading key has many distinct values, so the conceptual slice probes become expensive.

Still workload-driven

If the nonleading-column query is frequent and important, a dedicated index may still be the better design.

Be cautious

Skip scan is an optimizer choice, not a guarantee that every missing-leading-column predicate will perform well.
SQL - A skip-scan style setup
CREATE INDEX idx_customer_gender_email
  ON customers(gender, email);

-- Leading column GENDER is absent here.
-- Oracle may consider skip scan if gender has few distinct values
-- and email is selective enough.
SELECT customer_id
FROM   customers
WHERE  email = :email_value;

How to validate column order responsibly

Index design needs plan evidence. Once two plausible orders exist, validate them with current statistics, representative SQL, and runtime plan inspection. Oracle also gives you operational tools to observe whether an index is used and to test an alternative order without exposing it broadly right away.

1. Gather current stats

Make sure the optimizer is reasoning with current object metadata.

2. Run representative SQL

Use realistic predicates, not synthetic statements unrelated to production patterns.

3. Inspect runtime plans

Use DBMS_XPLAN.DISPLAY_CURSOR with actual row-source stats where possible.

4. Compare alternatives safely

Use usage monitoring or invisible indexes before dropping or promoting a design.
SQL - Runtime plan verification
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'ORDERS',
    cascade => TRUE);
END;
/

ALTER SESSION SET statistics_level = ALL;

SELECT /*+ gather_plan_statistics */ order_id, order_date, amount
FROM   orders
WHERE  customer_id = :cust_id
AND    order_date >= :start_date
ORDER BY order_date;

SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_CURSOR(
           NULL,
           NULL,
           'ALLSTATS LAST +PREDICATE +ALIAS +BYTES +COST'
         )
       );
SQL - Monitor usage and test safely
-- Observe whether an existing index is actually used.
ALTER INDEX idx_orders_cust_date MONITORING USAGE;

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

-- Build an alternative order invisibly.
CREATE INDEX idx_orders_date_cust_inv
  ON orders(order_date, customer_id)
  INVISIBLE;

-- In a controlled session, allow the optimizer to consider it.
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;
Why invisible indexes help

An invisible index is maintained by DML but ignored by the optimizer unless OPTIMIZER_USE_INVISIBLE_INDEXES is enabled. That makes it useful for comparing an alternative column order without changing general optimizer choices immediately.

Edge cases and misconceptions that trip people up

MisconceptionBetter interpretation
“A query on the second column can never use the composite index.”Not as a normal leading-prefix range lookup, but Oracle may still choose skip scan or a broader index scan in specific circumstances.
“Most selective first” is always right.Not reliably. The more durable question is which leading prefix the workload needs most often.
“If all needed columns are in the index, leading-column rules stop mattering.”Covering can reduce table visits, but it does not create a missing leading prefix for navigational access.
“Rows with null key values are always in the B-tree index.”Oracle does not index rows where all key columns are null in a normal B-tree index. That matters for some query designs.
“One big composite index replaces every smaller index.”Sometimes, but not automatically. Overlapping indexes can still differ in size, maintenance cost, and suitability for important SQL.

Null nuance

All-null key rows are absent from normal B-tree entries, so be careful when reasoning about scans that would need those rows represented in the index.

Covering nuance

A composite index can still be a smaller, index-only source for some statements even when a nonleading predicate weakens navigational use.

Maintenance nuance

Each extra overlapping index adds DML work, storage, and design complexity. “Just add another index” should not be the default answer.
Operational warning

Do not drop a shorter overlapping index only because a longer composite index starts with the same columns. Oracle may still prefer the shorter one for some statements because it is smaller or because it satisfies the statement more cheaply.

Bottom line: Learn the documented rules, then verify the exact workload.

Hands-on lab: compare two column orders in a controlled test

This lab works well in a sandbox. It gives you a repeatable setup and highlights the plan shapes worth inspecting when you compare two plausible index orders.

Step 1: build a table with mixed access patterns

SQL - Create demo data
DROP TABLE order_demo PURGE;

CREATE TABLE order_demo AS
SELECT level AS order_id,
       MOD(level, 5000) + 1 AS customer_id,
       DATE '2024-01-01' + MOD(level, 365) AS order_date,
       CASE
         WHEN MOD(level, 20) = 0 THEN 'CLOSED'
         WHEN MOD(level, 5) = 0 THEN 'PENDING'
         ELSE 'OPEN'
       END AS status,
       MOD(level, 2000) AS amount
FROM   dual
CONNECT BY level <= 200000;

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

Step 2: create two competing orders

SQL - Competing composite indexes
CREATE INDEX idx_od_cust_date_status
  ON order_demo(customer_id, order_date, status);

CREATE INDEX idx_od_date_cust_status
  ON order_demo(order_date, customer_id, status)
  INVISIBLE;

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

Step 3: run three representative statements

SQL - Queries to compare
ALTER SESSION SET statistics_level = ALL;

-- Query A: pure leading-column lookup
SELECT /*+ gather_plan_statistics */ order_id, order_date, status
FROM   order_demo
WHERE  customer_id = 1001;

-- Query B: customer plus date range and ordered output
SELECT /*+ gather_plan_statistics */ order_id, order_date, amount
FROM   order_demo
WHERE  customer_id = 1001
AND    order_date BETWEEN DATE '2024-03-01' AND DATE '2024-03-31'
ORDER BY order_date;

-- Query C: broad date-driven reporting
SELECT /*+ gather_plan_statistics */ order_id, customer_id, status
FROM   order_demo
WHERE  order_date BETWEEN DATE '2024-03-01' AND DATE '2024-03-31';

Step 4: what to inspect in the plan output

Expect for Query A

The (customer_id, order_date, status) order is naturally aligned because customer_id is the leading portion.

Expect for Query B

That same order remains attractive because it aligns the customer filter, the date range, and potentially the returned order.

Expect for Query C

The date-led alternative may become more interesting because the predicate begins with order_date.

Keep in mind

Fixed costs or universal winners. Your environment, stats, and row counts decide the exact plan.
SQL - Show the last runtime plan
SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_CURSOR(
           NULL,
           NULL,
           'ALLSTATS LAST +PREDICATE +BYTES +COST'
         )
       );

Quiz: do you have the right instincts about column order?

Answer from Oracle behavior, not rules of thumb

Q1. Why is (customer_id, order_date) usually better than (order_date, customer_id) for queries that often filter by customer_id alone?
Because the second column is ignored completely.
Because the first order exposes customer_id as a leading portion that single-column predicates can use.
Because Oracle always prefers customer IDs alphabetically.
Because date columns cannot appear first in a composite index.
Correct answer: leading portions decide partial usability. If the workload often starts with customer_id, placing it first lets that shorter predicate use the index.
Q2. What does skip scan change?
It makes column order irrelevant.
It guarantees fast access whenever the leading column is missing.
It gives Oracle an additional way to consider a composite index even without the leading column, in suitable cases.
It converts a composite index into several permanent separate indexes.
Correct answer: it adds an exception, not a free pass. Skip scan is real, but it does not erase the importance of the leading key.
Q3. Which statement is healthiest when deciding the order of columns?
Start from the workload’s common predicate prefixes and validate with plans.
Always put the most selective column first and stop thinking.
Always place date columns last, no matter what the SQL does.
Always combine every frequently filtered column into one large index.
Correct answer: start with the workload. The best order is the one that gives the real workload the most useful leading prefixes.
Q4. Why can (region, sale_date) be appealing for WHERE region = :r ORDER BY sale_date?
Because every index forces the same global sort order.
Because ORDER BY stops mattering after an index is created.
Because range predicates are forbidden on date columns.
Because the same order may align the filter and help Oracle avoid or reduce an extra sort.
Correct answer: index order can help twice. The right sequence can support row location and useful ordering at the same time.
Q5. What is true about B-tree composite indexes and nulls?
Every row is always stored, even if all indexed columns are null.
Rows in which all key columns are null are not indexed in a normal B-tree index.
Nulls are forbidden in every composite index.
Any row with one null component is removed from the index automatically.
Correct answer: all-null B-tree keys are absent. That detail affects how some queries can or cannot rely on the index alone.
Q6. What is the safest way to compare two plausible column orders in production-like testing?
Drop the current index immediately and hope the new one is better.
Rely only on a verbal rule such as “more selective first.”
Use current stats, compare runtime plans, and consider monitoring usage or invisible indexes for controlled testing.
Create both indexes permanently without reviewing DML cost.
Correct answer: measure before you commit. Oracle gives you monitoring and invisible-index tools so you can test more safely.
Takeaways

Think in prefixes, not column lists. Put columns first because the workload needs them first. Treat skip scan as a documented exception, not as a design strategy. And do not finalize an order until the plans, row counts, and operational trade-offs agree with the theory.

Vocabulary Revision 6 — Lessons 21 to 24

Lessons 21–24 · 16 words Watch on YouTube Contents https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily Open ...