Index FAQS

1. What is an Index in Oracle?

An Index is a database object that improves the speed of data retrieval from a table. It works like a book index:

• Instead of scanning the entire table, Oracle uses the index to quickly locate rows.

Example:

CREATE INDEX emp_name_idx ON employees(name);

2. Why do we need an index?

Without index: Oracle performs a Full Table Scan, reading every row.
With index: Oracle performs an Index Scan, locating matching rows quickly.

Indexes improve:

• SELECT performance
• JOIN performance
• WHERE clause filtering
• ORDER BY performance

3. How does an index work internally?

Oracle uses a B-Tree structure by default.

Structure:

• Root Block
• Branch Blocks
• Leaf Blocks (store key + ROWID)

Process:

1.    Search value in root

2.    Navigate branches

3.    Reach leaf node

4.    Use ROWID to fetch actual row

This allows very fast (logarithmic) search.

4. What is ROWID?

ROWID is the physical address of a row, pointing to data block, file, and row location.

Index stores: Indexed Column Value + ROWID. Oracle uses ROWID to fetch the row directly.

5. Types of indexes in Oracle

B-Tree Index – Default, good for high-cardinality columns and range searches.
Bitmap Index – Best for low-cardinality columns, used in Data Warehousing, not good for OLTP.
Unique Index – Enforces uniqueness; automatically created for PRIMARY KEY/UNIQUE constraints.
Composite Index – Index on multiple columns; order of columns matters.
Function-Based Index – Index on expressions; improves searches like case-insensitive queries.
Reverse Key Index – Reverses byte order; prevents block contention in high insert systems.

6. What is Cardinality?

Cardinality = Number of distinct values in a column.

• High cardinality: emp_id, email → B-Tree index
• Low cardinality: gender, status → Bitmap index

7. When does Oracle use an index?

Oracle uses index when:

• WHERE clause filters small % of rows
• Column has good selectivity
• Statistics are up to date

It may ignore index if a large % of table is needed.

8. Do indexes improve all queries?

No. They help SELECT queries but can slow INSERT, UPDATE, DELETE because index entries must be maintained.

9. What is Index Selectivity?

Selectivity = Distinct Values / Total Rows.
Higher selectivity → better index performance.

10. Composite Index and Column Order Rule

CREATE INDEX emp_idx ON employees(dept_id, salary);

• Efficient for WHERE dept_id = 10 OR dept_id = 10 AND salary > 5000
• Inefficient for WHERE salary > 5000 alone
• First column in composite index is critical

11. Index Range Scan

Used for queries like WHERE salary BETWEEN 5000 AND 10000. Scans part of index; efficient for ranges.

12. Full Index Scan

Scans entire index; used when index is smaller than table or query can be satisfied entirely from index.

13. Index Skip Scan

Used when first column not used; Oracle skips blocks intelligently but slower than normal scan.

14. Covering Index

All required columns present in index → table not accessed. Example:

CREATE INDEX emp_cover_idx ON employees(dept_id, salary, emp_id);

15. Index Clustering Factor

Measures how ordered table rows are compared to index.

• Low clustering factor → better performance
• High clustering factor → more block reads, lower performance

16. Index Rebuild

Reorganizes index structure:

ALTER INDEX emp_name_idx REBUILD;

Useful when index is fragmented or there are many deletes.

17. When NOT to create an index

Avoid indexing:

• Very small tables
• Low-selectivity columns (unless bitmap)
• Frequently updated columns
• Columns not used in WHERE/JOIN

Too many indexes slow DML and waste storage.

18. Index Performance Impact

Operation	Impact
SELECT	Faster
INSERT	Slightly slower
UPDATE	Slower if indexed column updated
DELETE	Slower

Tradeoff: read vs write performance.

19. Best Practices for Indexing

• Index columns used in WHERE and JOIN
• Index foreign key columns
• Avoid over-indexing
• Use composite indexes carefully
• Monitor execution plans
• Keep statistics updated
• Use function-based index for expressions
• Bitmap index for data warehouse only

20. Common Mistakes

• Indexing every column
• Wrong column order in composite index
• Not indexing foreign keys
• Using bitmap index in OLTP
• Not analyzing execution plans

21. Real-world Example

Query:

SELECT * FROM employees WHERE UPPER(name) = 'JOHN';

Without function-based index → full table scan.

Better:

CREATE INDEX emp_upper_idx ON employees(UPPER(name));

Query now uses the index efficiently.

22. Interview Tip

“An index is a database object that improves data retrieval speed by storing column values with row locations using a B-Tree structure. It reduces full table scans and allows faster searches via index scans. Indexes add overhead during insert, update, and delete because entries must be maintained.”