Composite Index FAQS

1. What is a Composite Index in Oracle?

A Composite Index (also called Concatenated Index) is an index created on two or more columns of a table.

Example:

CREATE INDEX emp_dept_salary_idx

ON employees(department_id, salary);

This index stores values in the order:

(department_id, salary) → ROWID

2. Why use a Composite Index?

Use when:

• Queries filter on multiple columns together
• Columns are frequently used in JOIN + WHERE
• You want to avoid creating multiple single-column indexes

Instead of:

CREATE INDEX idx1 ON employees(department_id);

CREATE INDEX idx2 ON employees(salary);

You can create:

CREATE INDEX emp_dept_salary_idx

ON employees(department_id, salary);

More efficient for combined conditions.

3. How does Composite Index work internally?

Composite index is still a B-Tree index.

It stores entries like:

(10, 5000) → ROWID

(10, 6000) → ROWID

(20, 4500) → ROWID

Sorting happens:

1.    First by department_id

2.    Then by salary within department

4. Does column order matter?

YES — very important.

Index:

CREATE INDEX emp_dept_salary_idx

ON employees(department_id, salary);

Works efficiently for:

WHERE department_id = 10;

WHERE department_id = 10 AND salary = 5000;

WHERE department_id = 10 AND salary > 4000;

But NOT efficient for:

WHERE salary = 5000;

Because salary is not leading column.

This is called the Leading Column Rule.

5. What is Leading Column Rule?

Oracle can use composite index only if query includes:

• First column
  OR
• First + subsequent columns

It cannot skip the first column.

Example:

Index: (A, B, C)

Valid:

• WHERE A = ?
• WHERE A = ? AND B = ?
• WHERE A = ? AND B = ? AND C = ?

Invalid (index not fully usable):

• WHERE B = ?
• WHERE C = ?

6. When should you use Composite Index?

Best scenarios:

• Columns frequently queried together
• JOIN + WHERE combinations
• Filtering and sorting on same columns
• High-selectivity leading column

Example:

SELECT *

FROM orders

WHERE customer_id = 100

AND order_date > SYSDATE - 30;

Index:

CREATE INDEX orders_cust_date_idx

ON orders(customer_id, order_date);

7. Composite Index vs Multiple Single-Column Indexes

Feature	Composite Index	Separate Indexes
Multi-column filtering	Excellent	Moderate
Index merge possible	N/A	Yes
Storage	Smaller	Larger
Performance	Better for combined filters	Slower

Composite index is usually better for combined conditions.

8. What is Index Skip Scan?

If first column has low distinct values, Oracle may still use index for second column using Index Skip Scan.

Example:

Index: (gender, salary)

Query:

WHERE salary = 5000;

Oracle may:

• Scan gender = M
• Scan gender = F
• Combine results

But this is slower than proper leading column usage.

9. How many columns can Composite Index have?

Up to:

• 32 columns (depending on Oracle version)

Best practice:

• Keep it small (2–4 columns)
• Avoid very wide composite indexes

10. How does Composite Index affect DML?

INSERT:

• Adds entry with all indexed column values

UPDATE:

• If any indexed column changes → delete + insert

DELETE:

• Removes index entry

More columns = more overhead.

11. What is Composite Unique Index?

You can enforce uniqueness on combination:

CREATE UNIQUE INDEX emp_dept_email_uk

ON employees(department_id, email);

Ensures:

• Same email allowed in different departments
• But not duplicate in same department

12. Composite Index and ORDER BY

If query:

SELECT *

FROM employees

WHERE department_id = 10

ORDER BY salary;

Index:

(department_id, salary)

Oracle can avoid sort operation.

Because:

• Index already sorted by salary within department

Very efficient.

13. Composite Index and GROUP BY

Query:

SELECT department_id, salary, COUNT(*)

FROM employees

GROUP BY department_id, salary;

Composite index helps reduce sorting cost.

14. What is Clustering Factor impact?

If table data is physically ordered similar to index:

• Low clustering factor
• Better performance

If scattered:

• More block reads
• Slower performance

Composite indexes can have different clustering impact depending on leading column.

15. Storage considerations

Composite index:

• Stores all indexed column values
• Larger than single-column index
• Consumes more space
• Often better than multiple indexes

16. Common mistakes

• Wrong column order
• Indexing too many columns
• Creating redundant indexes
• Indexing low-selectivity leading column
• Ignoring query patterns

17. Best practices

• Place most selective column first
• Match index order with WHERE clause order
• Match index order with ORDER BY if possible
• Avoid over-indexing
• Gather statistics after creation
• Monitor execution plans

18. Real-world Example

Application query:

SELECT *

FROM transactions

WHERE account_id = 100

AND transaction_date BETWEEN SYSDATE-30 AND SYSDATE;

Correct index:

CREATE INDEX txn_acc_date_idx

ON transactions(account_id, transaction_date);

Wrong index:

CREATE INDEX txn_date_acc_idx

ON transactions(transaction_date, account_id);

Because most filtering happens by account_id first.

19. Composite Index vs Covering Index

If composite index contains all columns needed in SELECT, Oracle can perform:

Index Only Scan (Fast Full Index Scan)

Example:

CREATE INDEX emp_cover_idx

ON employees(department_id, salary, employee_id);

Query:

SELECT employee_id

FROM employees

WHERE department_id = 10;

Oracle may not access table at all.

20. Interview Tip

If asked:

“What is a Composite Index and why is column order important?”

Answer:

“A Composite Index is an index created on multiple columns. It is stored as a B-Tree sorted first by the leading column and then by subsequent columns. Column order is critical because Oracle can use the index only when the query includes the leading column. Proper column ordering significantly improves query performance and avoids unnecessary full table scans.”