Oracle Index FAQs with examples

1. What is an Index in Oracle?

An index is a schema object that improves the speed of data retrieval by allowing Oracle to find rows quickly without scanning the entire table.

Example:

CREATE INDEX idx_emp_name
ON employees(first_name);

2. Why are indexes used?

Indexes are used to:

• Improve SELECT query performance
• Speed up WHERE clause searches
• Improve JOIN operations
• Speed up ORDER BY and GROUP BY operations
• Enforce uniqueness (using unique indexes)

3. What are the types of indexes in Oracle?

Oracle supports several types of indexes:

• B-Tree Index (Default)
• Unique Index
• Composite (Concatenated) Index
• Bitmap Index
• Function-Based Index
• Reverse Key Index
• Descending Index
• Partitioned Index
• Domain Index

4. What is a B-Tree Index?

A B-Tree index is the default index type and is best suited for columns with high cardinality (many distinct values).

Example:

CREATE INDEX idx_salary
ON employees(salary);

5. What is a Unique Index?

A unique index ensures that no duplicate values exist in the indexed column(s).

Example:

CREATE UNIQUE INDEX idx_email
ON employees(email);

Or using a constraint:

ALTER TABLE employees
ADD CONSTRAINT uk_email UNIQUE(email);

6. What is a Composite Index?

A composite index is created on two or more columns.

Example:

CREATE INDEX idx_name_dept
ON employees(last_name, department_id);

Useful when queries frequently search using both columns.

Example:

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

7. What is a Bitmap Index?

A bitmap index is suitable for columns with low cardinality, such as gender or status.

Example:

CREATE BITMAP INDEX idx_gender
ON employees(gender);

Common values:

• Male/Female
• Active/Inactive
• Yes/No

Bitmap indexes are commonly used in data warehouses but are generally not recommended for tables with frequent DML operations.

8. What is a Function-Based Index?

A function-based index indexes the result of an expression or function.

Example:

CREATE INDEX idx_upper_name
ON employees(UPPER(last_name));

Now this query can use the index:

SELECT *
FROM employees
WHERE UPPER(last_name)='SMITH';

9. What is a Reverse Key Index?

A reverse key index reverses the bytes of indexed values to reduce index block contention.

Example:

CREATE INDEX idx_rev_empid
ON employees(employee_id)
REVERSE;

Commonly used with sequence-generated primary keys in high-concurrency systems.

10. What is a Descending Index?

A descending index stores values in descending order.

Example:

CREATE INDEX idx_salary_desc
ON employees(salary DESC);

Useful for queries like:

SELECT *
FROM employees
ORDER BY salary DESC;

11. How do you create an index?

Syntax:

CREATE INDEX index_name
ON table_name(column_name);

Example:

CREATE INDEX idx_department
ON employees(department_id);

12. How do you view indexes?

View indexes:

SELECT index_name,
       table_name,
       status
FROM user_indexes;

View indexed columns:

SELECT index_name,
       column_name
FROM user_ind_columns;

13. How do you drop an index?

DROP INDEX idx_department;

14. How do you rebuild an index?

Rebuilding removes fragmentation and improves performance.

ALTER INDEX idx_department
REBUILD;

15. How do you make an index unusable?

ALTER INDEX idx_department
UNUSABLE;

Rebuild it later:

ALTER INDEX idx_department
REBUILD;

16. What is Index Selectivity?

Selectivity indicates how unique the indexed values are.

Example:

Column	Good Candidate?
Employee ID	Yes
Email	Yes
Gender	No (Bitmap Index)
Status	No (Bitmap Index)

Higher selectivity usually means better performance with B-Tree indexes.

17. When does Oracle use an index?

Oracle may use an index when:

• WHERE clause filters indexed columns
• JOIN uses indexed columns
• ORDER BY uses indexed columns
• GROUP BY uses indexed columns
• High selectivity exists

Example:

SELECT *
FROM employees
WHERE employee_id=100;

If employee_id is indexed, Oracle can perform an Index Scan instead of a Full Table Scan.

18. When will Oracle ignore an index?

Oracle may choose a full table scan when:

• Most rows are returned
• Table is very small
• Statistics are outdated
• Functions are used without a function-based index
• The optimizer estimates a full scan is cheaper

Example:

SELECT *
FROM employees
WHERE salary > 1000;

If almost every row satisfies the condition, Oracle may ignore the index.

19. What is the difference between a Primary Key and an Index?

Primary Key	Index
Enforces uniqueness	Improves performance
Cannot contain NULL values	Can contain NULL values (depending on type and usage)
Automatically creates a unique index	Can be unique or non-unique
Used for data integrity	Used for faster data access

20. Can multiple indexes exist on a table?

Yes.

Example:

CREATE INDEX idx_salary
ON employees(salary);

CREATE INDEX idx_dept
ON employees(department_id);

CREATE INDEX idx_name
ON employees(last_name);

A table can have many indexes, but too many indexes can slow INSERT, UPDATE, and DELETE operations because Oracle must maintain each index.

21. What are the advantages of indexes?

• Faster SELECT queries
• Faster JOIN operations
• Faster sorting
• Faster grouping
• Improved query performance
• Faster primary key lookups

22. What are the disadvantages of indexes?

• Consume additional disk space
• Slow INSERT operations
• Slow UPDATE operations
• Slow DELETE operations
• Require maintenance and rebuilding in some cases
• Too many indexes can reduce DML performance

23. How do you check whether Oracle is using an index?

Use the execution plan.

EXPLAIN PLAN FOR
SELECT *
FROM employees
WHERE employee_id = 101;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

If the plan shows INDEX RANGE SCAN, INDEX UNIQUE SCAN, or another index access path, Oracle is using an index.

24. What is Index Range Scan?

Oracle uses an Index Range Scan when multiple adjacent index entries satisfy the search condition.

Example:

SELECT *
FROM employees
WHERE salary BETWEEN 50000 AND 70000;

25. Interview Example: Improve Query Performance

Suppose the query is slow:

SELECT *
FROM employees
WHERE email = 'john@example.com';

Create an index:

CREATE INDEX idx_email
ON employees(email);

Now Oracle can retrieve the matching row much faster using the index.