Composite index, try to use the “Leading” column in the “WHERE” clause

 

If it is required to use a composite index, try to use the “Leading” column in the “WHERE” clause. Though Index skip scan is possible, it incurs extra cost in creating virtual indexes and may not be always possible depending on the cardinality of the leading columns.

Rule

Suppose you have a composite index:

CREATE INDEX EMP_IDX1
ON EMP(DEPTNO, JOB);

Here:

• DEPTNO = Leading column
• JOB = Second column

Oracle can efficiently use the index when the leading column is present in the WHERE clause.

---

Sample EMP Data

EMPNO	ENAME	JOB	DEPTNO
7369	SMITH	CLERK	20
7499	ALLEN	SALESMAN	30
7521	WARD	SALESMAN	30
7566	JONES	MANAGER	20
7654	MARTIN	SALESMAN	30
7698	BLAKE	MANAGER	30
7782	CLARK	MANAGER	10
7788	SCOTT	ANALYST	20
7839	KING	PRESIDENT	10
7902	FORD	ANALYST	20

Composite Index:

SQL> CREATE INDEX EMP_IDX1 ON EMP(DEPTNO, JOB);

---

Scenario 1 (Best Case)

Query:

SELECT * FROM EMP WHERE DEPTNO=20;

Explain Plan

INDEX RANGE SCAN EMP_IDX1

Why?

Oracle directly jumps to:

DEPTNO = 20

inside index.

Imagine index like:

10
  MANAGER
  PRESIDENT

20
  ANALYST
  ANALYST
  CLERK
  MANAGER

30
  MANAGER
  SALESMAN
  SALESMAN
  SALESMAN

Oracle directly goes to section 20.

Very fast.

---

Scenario 2 (Ideal Case)

Query:

SELECT * FROM EMP WHERE DEPTNO=20 AND JOB='ANALYST';

Explain Plan

INDEX RANGE SCAN EMP_IDX1

Oracle first finds:

DEPTNO=20

Then inside that section finds:

JOB='ANALYST'

Fastest possible access.

---

Scenario 3 (Leading Column Missing)

Query:

SELECT * FROM EMP WHERE JOB='ANALYST';

Notice:

DEPTNO not specified

but index is:

(DEPTNO, JOB)

Oracle cannot directly locate ANALYST.

Index structure:

10 -> MANAGER
10 -> PRESIDENT

20 -> ANALYST
20 -> ANALYST
20 -> CLERK
20 -> MANAGER

30 -> MANAGER
30 -> SALESMAN
30 -> SALESMAN

Oracle must search every DEPTNO block.

Possible plan:

INDEX SKIP SCAN EMP_IDX1

or

TABLE ACCESS FULL

depending on data volume.

---

What is Index Skip Scan?

Oracle creates virtual searches.

Instead of:

Search JOB='ANALYST'

Oracle does:

Search (10, ANALYST)

Search (20, ANALYST)

Search (30, ANALYST)

Internally:

(DEPTNO=10, JOB='ANALYST')

(DEPTNO=20, JOB='ANALYST')

(DEPTNO=30, JOB='ANALYST')

and combines results.

This is called:

INDEX SKIP SCAN

Plan:

INDEX SKIP SCAN EMP_IDX1

---

Why is Skip Scan Costly?

Suppose:

DEPTNO values = 3

Only:

10
20
30

Oracle performs 3 searches.

Still acceptable.

---

Now imagine a table with:

1,000 different DEPTNO values

Oracle must perform:

1000 index searches

instead of one.

Much more work.

Cost increases.

---

Real-Life Example

Imagine a library.

Books arranged:

Floor -> Subject

Example:

Floor 1 -> Science
Floor 2 -> History
Floor 3 -> Computers

You ask:

Find all Computer books on Floor 3

Easy.

But if you ask:

Find all books named Oracle

without floor number,

Librarian must search:

Floor 1
Floor 2
Floor 3
...

That is exactly what Oracle does during an Index Skip Scan.

---

Large Table Example (10 Million Rows)

Index:

CREATE INDEX EMP_IDX1 ON EMP(DEPTNO, JOB);

Query:

SELECT * FROM EMP WHERE JOB='CLERK';

Suppose:

DEPTNO distinct values = 500

Oracle may decide:

TABLE ACCESS FULL

instead of:

INDEX SKIP SCAN

because 500 virtual searches are expensive.

---

Interview Question

Q. Composite index exists on (DEPTNO, JOB). Query filters only JOB. Can Oracle still use the index?

Answer: Yes. Oracle may use:

INDEX SKIP SCAN

if the leading column (DEPTNO) has low cardinality (few distinct values). If the leading column has high cardinality, Oracle usually prefers a Full Table Scan because Skip Scan becomes expensive.

---

DBA Golden Rule

If the query frequently uses:

WHERE JOB='ANALYST'

then create:

CREATE INDEX EMP_IDX2 ON EMP(JOB);

or

CREATE INDEX EMP_IDX3 ON EMP(JOB,DEPTNO);

instead of relying on Skip Scan.

Easy Formula to Remember

For index:

(A,B,C)

Best:

WHERE A=...
WHERE A=... AND B=...
WHERE A=... AND B=... AND C=...

Possible but Costly:

WHERE B=...
WHERE C=...

Oracle may use:

INDEX SKIP SCAN

or

FULL TABLE SCAN

depending on statistics, cardinality, and cost.