Bitmap Index Use Case

Oracle bitmap index deep dive

Bitmap IndexesWhen they are brilliant, when they are dangerous, and how to use them correctly in Oracle

Bitmap indexes are one of the most misunderstood indexing features in Oracle. People hear “good for low-cardinality columns” and stop there. In reality, bitmap indexes are about more than cardinality: they are about workload shape, concurrency, join patterns, query combinations, storage behavior, partitioning rules, and the optimizer’s ability to combine bitmaps efficiently before converting them back to rowids.

Contents

01 What bitmap indexes are02 Why they work well03 When not to use them04 Reading plans and bitmap operations05 Single-table bitmap indexes06 Bitmap join indexes07 Partitioning and restrictions08 Practical design rules09 End-to-end demo10 Knowledge check

Section 01

What a bitmap index actually is

A B-tree index stores keys with row location references one entry at a time. A bitmap index stores the row locations for each distinct key value as a bitmap. Conceptually, each distinct value owns a string of bits; a bit is set when the corresponding row contains that value. Oracle documents this internal model directly in the SQL language reference and concepts materials. A bitmap index is therefore not just “another index type” - it is a different access representation optimized for combining many predicates efficiently in read-heavy workloads.

Key idea

Bitmap indexes are strongest when the database can answer a query by combining several low-cardinality predicates with bitmap operations such as BITMAP AND, BITMAP OR, and BITMAP MERGE before touching the table rows.

Recollect: “Low cardinality” is the starting clue, not the full rule. The real win comes from combining multiple predicates cheaply in analytic workloads.

Section 02

Why bitmap indexes work so well in reporting and warehousing

Oracle’s documentation repeatedly positions bitmap indexes as a data warehousing feature because they are suited to low-concurrency, read-heavy environments. Their biggest advantage is that the optimizer can combine multiple bitmap indexes very efficiently, which is especially useful when users ask ad hoc questions with several equality predicates. Oracle also documents execution plan operators such as BITMAP AND, BITMAP OR, BITMAP MERGE, and BITMAP CONVERSION TO ROWIDS that show this process directly in plans.

Ad hoc filtering

Analysts often combine many low-cardinality filters such as region, channel, promotion, or status in different ways from one query to the next.

Star queries

Bitmap indexes are a natural fit in star-schema workloads, where many dimension attributes drive fact-table filtering.

Compact storage

Because values are encoded as bitmaps, bitmap indexes can be extremely space-efficient in the right data distribution.

Optimizer angle

The optimizer does not have to pick one bitmap index and ignore the others. It can combine several, which is exactly why bitmap indexes age well in analytic SQL with many optional filters.

Section 03

When bitmap indexes are the wrong choice

This is where many teams get into trouble. Oracle states clearly that bitmap indexes are not appropriate for applications with many concurrent transactions. The reason is locking behavior: a bitmap key can map to many rows, so updating a single row can lock a much larger bitmap entry range than you would see with a B-tree index. In heavy OLTP systems that means contention risk, not just slower maintenance.

Bad fit

• High-concurrency OLTP tables
• Frequent singleton INSERT/UPDATE/DELETE activity
• Columns whose usefulness is mainly range scans
• Workloads where locking side effects matter more than scan speed

Better fit

• Read-mostly reporting tables
• Fact tables in star schemas
• Batch-loaded or nightly-refreshed warehouse data
• Queries combining many equality predicates

Do not simplify too much

It is true that low cardinality often points toward bitmap indexes, but Oracle’s own materials also show that concurrency and DML behavior are just as important. A technically low-cardinality column on a hot OLTP table is still a poor bitmap candidate.

Recollect: The sentence “bitmap indexes are for low-cardinality columns” is incomplete. The better sentence is “bitmap indexes are for low-cardinality columns in the right workload.”

Section 04

How to read execution plans with bitmap operations

Once you know the plan operators, bitmap-index plans become much easier to reason about. Oracle’s plan language usually shows a sequence like bitmap single-value access, bitmap combination, and then a conversion to rowids before table access. If the query includes grouping or star transformations, the plan can become more complex, but these operators remain the main clues.

Plan operator	Meaning	Why you care
BITMAP INDEX SINGLE VALUE	Fetches a bitmap for one equality predicate	Shows Oracle is using the bitmap index directly for a specific value
BITMAP AND	Intersects multiple bitmap vectors	Classic sign that several low-cardinality filters are being combined efficiently
BITMAP OR	Unions multiple bitmap vectors	Useful when queries have alternative filter branches
BITMAP MERGE	Merges bitmaps, often from range or more complex conditions	Helps explain more advanced bitmap processing
BITMAP CONVERSION TO ROWIDS	Transforms the final bitmap into rowids	This is the bridge from bitmap filtering to actual row access

SQL - Explain a bitmap-friendly query

EXPLAIN PLAN FOR
SELECT product_category, SUM(sale_amount)
FROM   sales_fact
WHERE  region = 'WEST'
  AND  order_status = 'COMPLETE'
  AND  channel = 'ONLINE'
GROUP BY product_category;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

A representative bitmap plan usually includes one single-value bitmap access per indexed predicate, then BITMAP AND, then conversion to rowids. The exact cost, cardinality, and row counts vary by data and statistics, so the safe way to teach this is to focus on the operator pattern rather than inventing fixed numbers.

Section 05

Designing single-table bitmap indexes well

The most common starting point is a single-table bitmap index on a fact or reporting table. Oracle’s SQL reference supports syntax such as CREATE BITMAP INDEX ... ON table(column), including local partitioned variants. For learners, the important design question is not simply “Is the column low cardinality?” but “Will users combine this column with other low-cardinality predicates often enough to justify the index?”

Strong candidates

Region, channel, gender, status, flag columns, yes/no attributes, small category sets.

Borderline candidates

Columns with moderate distinct counts that still participate in multi-filter warehouse queries; these must be tested rather than assumed.

Weak candidates

High-cardinality identifiers, transaction IDs, rapidly changing values, and columns primarily used for range predicates.

SQL - Create bitmap indexes on fact attributes

CREATE BITMAP INDEX bix_sales_region
  ON sales_fact(region);

CREATE BITMAP INDEX bix_sales_status
  ON sales_fact(order_status);

CREATE BITMAP INDEX bix_sales_channel
  ON sales_fact(channel);

SQL - Inspect column distinctness

SELECT owner,
       table_name,
       column_name,
       num_distinct,
       density,
       histogram
FROM   dba_tab_col_statistics
WHERE  owner = 'SH'
  AND  table_name = 'SALES'
ORDER BY num_distinct;

Design habit

Use statistics as a clue, not a rigid threshold. Oracle’s own warehousing guidance emphasizes workload context more than a magical numeric cutoff.

Section 06

Bitmap join indexes: one of the most powerful warehouse features

Bitmap join indexes extend the idea further: Oracle can index a fact table using values from joined dimension tables. This is particularly useful in star schemas because it can remove the need to visit some dimension tables during execution. Oracle’s SQL reference documents bitmap join index syntax with joins, and its warehousing guidance positions them as a performance feature for star-query environments.

SQL - Create a bitmap join index

CREATE BITMAP INDEX bix_sales_cust_country
ON sales(c.country_id)
FROM sales s, customers c
WHERE s.cust_id = c.cust_id;

The key idea is subtle but important: the index belongs to the fact table, but its indexed attribute comes from the dimension table. That is why bitmap join indexes are a warehouse optimization rather than a generic OLTP tactic.

Join-index caution

Bitmap join indexes are powerful, but they add maintenance complexity. They are best used where data refresh is controlled and query benefit is clear. They are not a casual default.

Section 07

Partitioning, restrictions, and the rules that bite later

Oracle supports bitmap indexes on partitioned tables, including local bitmap indexes, but there are important restrictions around global partitioned bitmap indexes and around temporary tables. The SQL reference explicitly documents several of these constraints. If you work in warehouses, this matters because bitmap indexes and partitioning often appear together in the same design conversation.

Topic	Practical takeaway	Why it matters
Local bitmap indexes	Common and natural on partitioned fact tables	Partition pruning plus bitmap combination can work very well together
Global partitioned bitmap indexes	Restricted by Oracle rules	You cannot assume every partitioning pattern is valid for bitmap indexes
Temporary tables	Bitmap indexes are not generally the right tool here	Warehouse-style read patterns, not transient OLTP staging, are the primary target
Heavy DML	Still a red flag even if the table is partitioned	Partitioning does not magically erase bitmap locking behavior

SQL - Local bitmap index on a partitioned fact table

CREATE BITMAP INDEX bix_sales_channel_local
  ON sales_fact(channel)
  LOCAL;

Recollect: Bitmap-index design is never just about one column. In real systems you must think about partitioning strategy, refresh pattern, and maintenance operations together.

Section 08

Practical design rules that hold up in real systems

There is no single cardinality percentage that decides everything, and Oracle’s own materials stop short of giving a magical threshold. That is good engineering discipline. Instead, use a set of practical questions.

Question 1

Is the workload mostly read-heavy and analytic rather than highly concurrent OLTP?

Question 2

Will users combine this column with other low-cardinality predicates often?

Question 3

Would locking side effects from updates be acceptable on this table?

Question 4

Can the benefit be seen in execution plans and measured in actual reports?

Rule of thumb that ages well

If the table is warehouse-style, the predicates are mostly equality filters, the columns are low cardinality, and users combine several of them in ad hoc queries, bitmap indexes deserve strong consideration. If the table is OLTP-hot, they usually do not.

Section 09

End-to-end demo: adding bitmap indexes to a reporting fact table

This demo stays close to what Oracle actually supports and to how DBAs really test bitmap indexes. Imagine a reporting schema with a fact table SALES_FACT loaded nightly. Users filter frequently by REGION, ORDER_STATUS, and CHANNEL. The goal is to check distinctness, create bitmap indexes on those attributes, validate the execution plan pattern, and compare the plan shape before and after.

Demo principle

This demo uses real Oracle syntax and real verification steps. It avoids invented elapsed times or fake cardinality outputs because those depend on data size, distribution, statistics, and hardware.

Step 1: inspect column statistics and gather stats if needed

SQL - Check distinctness and stats

SELECT column_name,
       num_distinct,
       histogram,
       density
FROM   user_tab_col_statistics
WHERE  table_name = 'SALES_FACT'
  AND  column_name IN ('REGION', 'ORDER_STATUS', 'CHANNEL')
ORDER BY column_name;

-- If stats are stale, gather them before testing
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'SALES_FACT',
    cascade          => TRUE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO');
END;
/

The success condition here is not a specific number; it is that the candidate columns have relatively small NUM_DISTINCT values compared with the table size and that statistics are current enough for a fair plan test.

Step 2: capture the pre-index plan

SQL - Baseline plan before bitmap indexes

EXPLAIN PLAN FOR
SELECT channel, SUM(sale_amount)
FROM   sales_fact
WHERE  region = 'WEST'
  AND  order_status = 'COMPLETE'
  AND  channel = 'ONLINE'
GROUP BY channel;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

Before the bitmap indexes exist, the plan may show a full table scan or another access path. Record the operator pattern so you can compare it after the index creation.

Step 3: create the bitmap indexes

SQL - Create bitmap indexes

CREATE BITMAP INDEX bix_sales_fact_region
  ON sales_fact(region);

CREATE BITMAP INDEX bix_sales_fact_status
  ON sales_fact(order_status);

CREATE BITMAP INDEX bix_sales_fact_channel
  ON sales_fact(channel);

Step 4: verify the indexes exist and are bitmap indexes

SQL - Verify index metadata

SELECT index_name,
       index_type,
       table_name,
       status,
       visibility
FROM   user_indexes
WHERE  table_name = 'SALES_FACT'
  AND  index_name IN (
         'BIX_SALES_FACT_REGION',
         'BIX_SALES_FACT_STATUS',
         'BIX_SALES_FACT_CHANNEL')
ORDER BY index_name;

The success condition is straightforward: INDEX_TYPE should show BITMAP, and the indexes should be valid and usable.

Step 5: re-run the plan and look for bitmap operators

SQL - Validate the new operator pattern

EXPLAIN PLAN FOR
SELECT channel, SUM(sale_amount)
FROM   sales_fact
WHERE  region = 'WEST'
  AND  order_status = 'COMPLETE'
  AND  channel = 'ONLINE'
GROUP BY channel;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

What you are looking for here is not an invented cost value. You are looking for plan operators such as BITMAP INDEX SINGLE VALUE, BITMAP AND, and BITMAP CONVERSION TO ROWIDS. If those appear, Oracle is combining the bitmap indexes the way you intended.

Step 6: compare real execution with statistics

SQL - Get actual execution stats

SELECT /*+ gather_plan_statistics */
       channel,
       SUM(sale_amount)
FROM   sales_fact
WHERE  region = 'WEST'
  AND  order_status = 'COMPLETE'
  AND  channel = 'ONLINE'
GROUP BY channel;

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

This is the right place to validate whether the operator path is actually being used at runtime and whether row counts make sense. Again, the exact row and timing values depend on your data, so do not teach fake outputs here.

Step 7: prove why bitmap indexes are not for hot OLTP tables

The safest teaching approach is not to stage artificial lock contention numbers but to explain the rule clearly: if this same table were updated heavily by many concurrent sessions, the bitmap indexes could create contention that would be unacceptable in OLTP. Oracle’s documentation makes that design warning explicit.

What counts as a successful demo?

A successful demo means the metadata verifies that the indexes are bitmap indexes, the plan shape changes in the expected bitmap-oriented way, and the test is done on a reporting-style table where bitmap locking behavior is not a hidden time bomb.

Section 10

Knowledge check

These questions aim to sharpen decision-making, not just vocabulary. Read the explanations after you submit.

Q1. What is the strongest reason bitmap indexes are useful in reporting workloads?

They always make every query faster than a B-tree.

Oracle can combine several low-cardinality predicates with efficient bitmap operations before accessing rows.

They are required for all partitioned tables.

They remove the need for optimizer statistics.

Correct answer: Bitmap combination is the real superpower. The feature is most valuable when several low-cardinality predicates are combined efficiently in analytic SQL.

Q2. Why are bitmap indexes usually a poor fit for busy OLTP tables?

Because Oracle cannot create them on transactional tables.

Because they cannot be used in SQL execution plans.

Because update activity can create locking and maintenance behavior that is unacceptable under high concurrency.

Because they only work on numeric columns.

Correct answer: Concurrency is the problem. Bitmap indexes are not rejected because they are low-cardinality; they are rejected because their locking behavior is a poor fit for highly concurrent row-by-row OLTP changes.

Q3. Which plan operator is the clearest sign that Oracle is intersecting multiple bitmap predicates?

BITMAP AND

NESTED LOOPS

WINDOW SORT

HASH UNIQUE

Correct answer: BITMAP AND. That operator is the classic signature that several bitmap filters are being combined before row access.

Q4. What is the best summary of a bitmap join index?

A B-tree index that contains only joined rowids.

An index that always replaces fact tables.

An index that exists only in memory.

A bitmap index stored on a fact table but built using values from joined dimension tables.

Correct answer: Fact storage, dimension-driven values. That is exactly why bitmap join indexes are so closely associated with star-schema warehouses.

Q5. In the end-to-end demo, what is the safest way to verify the bitmap-index design actually worked?

Assume it worked because the CREATE INDEX commands succeeded.

Check metadata in USER_INDEXES and inspect the plan for bitmap operators before and after.

Restart the instance and wait for Smart Scan messages.

Drop all B-tree indexes automatically.

Correct answer: Trust, then verify. Metadata tells you the index type; the execution plan tells you whether the optimizer is actually using bitmap operations as intended.

Q6. Which statement about “low cardinality” is the most accurate?

Low cardinality alone is enough to guarantee a bitmap index is the right answer.

Only columns with fewer than exactly 100 distinct values qualify.

Low cardinality is an important clue, but workload pattern and concurrency still decide whether bitmap indexes are appropriate.

Low cardinality only matters for temporary tables.

Correct answer: Context decides. Cardinality is necessary to think about, but it is not enough by itself. Workload and concurrency are just as important.

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

Use them when the table is read-heavy, predicates are mostly equality filters, columns are low cardinality, and combined filtering is common.

Use them on every column in a fact table.

Never use them with partitioned tables.

Always replace all B-tree indexes with bitmap join indexes.

Correct answer: Match the technology to the workload. That rule is broad enough to be useful and specific enough to prevent common bitmap-index mistakes.

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.