Oracle Composite Index FAQs with examples

1. What is a Composite Index in Oracle?

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

It helps Oracle retrieve data faster when queries use multiple indexed columns together.

Example:

CREATE INDEX idx_emp_name_dept
ON employees(last_name, department_id);

2. Why do we use a Composite Index?

Composite indexes are used to:

• Speed up searches using multiple columns
• Improve JOIN performance
• Improve sorting (ORDER BY)
• Reduce Full Table Scans
• Improve query performance

3. How do you create a Composite Index?

Syntax:

CREATE INDEX index_name
ON table_name(column1, column2, ...);

Example:

CREATE INDEX idx_emp_job_dept
ON employees(job_id, department_id);

4. When will Oracle use a Composite Index?

Oracle uses the index when the query searches using the leading column(s) of the index.

Example index:

CREATE INDEX idx_emp
ON employees(last_name, department_id);

Query that uses the index:

SELECT *
FROM employees
WHERE last_name = 'Smith';

Or:

SELECT *
FROM employees
WHERE last_name = 'Smith'
AND department_id = 10;

5. What is the Leading Column Rule?

Oracle can efficiently use a composite index only when the query starts with the first (leading) column of the index.

Example:

CREATE INDEX idx_emp
ON employees(last_name, department_id);

Uses the index:

SELECT *
FROM employees
WHERE last_name = 'King';

Uses the index:

SELECT *
FROM employees
WHERE last_name = 'King'
AND department_id = 20;

May not use the index efficiently:

SELECT *
FROM employees
WHERE department_id = 20;

Because the leading column (last_name) is not included in the search condition.

6. Can a Composite Index contain more than two columns?

Yes.

Example:

CREATE INDEX idx_emp_full
ON employees(last_name,
first_name,
department_id);

This index contains three columns.

7. Difference between Single-Column Index and Composite Index

Single-Column Index	Composite Index
One column	Two or more columns
Faster for one-column searches	Faster for multi-column searches
Simpler	More efficient for related search conditions

8. Does the order of columns matter?

Yes.

Example:

CREATE INDEX idx_emp
ON employees(last_name, department_id);

Better query:

SELECT *
FROM employees
WHERE last_name = 'Scott'
AND department_id = 30;

Less efficient:

SELECT *
FROM employees
WHERE department_id = 30
AND last_name = 'Scott';

Note: The order of conditions in the WHERE clause does not matter. Oracle's optimizer can reorder predicates. What matters is that the query includes the leading indexed column (last_name).

9. Can Oracle use only the first column of a Composite Index?

Yes.

Example:

CREATE INDEX idx_emp
ON employees(last_name, department_id);

This query can use the index:

SELECT *
FROM employees
WHERE last_name = 'Jones';

10. Can Oracle use only the second column?

Usually no, unless Oracle uses special optimization techniques such as Index Skip Scan and the optimizer determines it is beneficial.

Example:

SELECT *
FROM employees
WHERE department_id = 20;

Normally, Oracle performs a Full Table Scan or uses another suitable index if available.

11. How do you view Composite Indexes?

SELECT index_name,
       column_name,
       column_position
FROM user_ind_columns
ORDER BY index_name,
         column_position;

12. How do you drop a Composite Index?

DROP INDEX idx_emp_name_dept;

13. Can a Composite Index be Unique?

Yes.

Example:

CREATE UNIQUE INDEX idx_unique_emp
ON employees(email, department_id);

Duplicate combinations of email and department_id are not allowed.

14. What are the advantages of Composite Indexes?

• Faster searches using multiple columns
• Better JOIN performance
• Faster sorting
• Reduced Full Table Scans
• Improved overall query performance

15. What are the disadvantages of Composite Indexes?

• Consume additional disk space
• Slow INSERT operations
• Slow UPDATE operations
• Slow DELETE operations
• Less useful if queries do not use the leading column

16. Interview Example

Create a table:

CREATE TABLE employees (
  employee_id   NUMBER,
  last_name     VARCHAR2(30),
  department_id NUMBER,
  salary        NUMBER
);

Create a composite index:

CREATE INDEX idx_emp_dept
ON employees(last_name, department_id);

Query using both columns:

SELECT *
FROM employees
WHERE last_name = 'Smith'
AND department_id = 10;

Oracle can use the composite index to retrieve matching rows efficiently.

17. Real-World Example

Suppose an HR application frequently runs:

SELECT *
FROM employees
WHERE department_id = 50
AND job_id = 'IT_PROG';

Create a composite index:

CREATE INDEX idx_dept_job
ON employees(department_id, job_id);

This improves the performance of queries filtering by both department_id and job_id.

Key Point:
Composite indexes work best when your queries search using the leading column or a combination of the indexed columns.