Index Performance FAQS

1. How do indexes improve performance in Oracle?

Indexes improve performance by:

·        Reducing Full Table Scans

·        Minimizing I/O operations

·        Quickly locating row data using ROWID

·        Supporting efficient joins

·        Enabling fast range queries

Instead of scanning millions of rows, Oracle navigates a B-Tree structure in O(log n) time.

2. Why does Oracle sometimes ignore an index?

Oracle may choose Full Table Scan if:

·        Query returns large percentage of rows

·        Index selectivity is poor

·        Table is small

·        Statistics are outdated

·        Clustering factor is high

·        Cost-based optimizer finds full scan cheaper

Oracle always chooses the lowest cost plan, not automatically the index.

3. What is Selectivity and why is it important?

Selectivity measures how selective a condition is.

High Selectivity:

WHERE employee_id = 100

Returns 1 row → Index very useful.

Low Selectivity:

WHERE status = 'ACTIVE'

Returns 80% rows → Full scan may be better.

High selectivity = better index performance.

4. What is Clustering Factor?

Clustering Factor measures how physically ordered table rows are compared to index order.

Low clustering factor:

·        Table rows stored in same order as index

·        Fewer block reads

·        Better performance

High clustering factor:

·        Rows scattered

·        More I/O

·        Slower performance

Clustering factor heavily impacts index range scan cost.

5. How do indexes affect SELECT performance?

Equality search

Very fast (Index Unique Scan).

Range search

Efficient (Index Range Scan).

ORDER BY

Can avoid sort operation if index matches order.

GROUP BY

May reduce sorting cost.

Indexes significantly improve selective queries.

6. How do indexes affect INSERT performance?

When inserting:

1.    Row inserted into table

2.    Entry inserted into each index

More indexes = slower inserts.

Sequential inserts into B-Tree:

·        May cause right-hand block contention

In high concurrency systems:

·        Reverse key index may help.

7. How do indexes affect UPDATE performance?

If updating indexed column:

·        Oracle deletes old index entry

·        Inserts new index entry

If updating non-indexed column:

·        No index impact

Updating indexed columns frequently can degrade performance.

8. How do indexes affect DELETE performance?

DELETE:

·        Removes row from table

·        Removes entry from each index

Many indexes = slower deletes.

9. What is Over-Indexing?

Creating too many indexes:

·        Slows DML

·        Increases storage

·        Complicates optimizer decisions

Best practice:

Only index columns that improve critical queries.

10. What is Index Fragmentation?

Occurs when:

·        Many deletes

·        Many updates

·        Leaf block splits

Symptoms:

·        Increased index size

·        Decreased efficiency

Solutions:

ALTER INDEX index_name REBUILD;

But rebuild only when necessary.

11. What is Index Block Split?

When leaf block is full:

·        Oracle splits it into two

·        Increases tree height

·        Slight performance overhead

Common with:

·        Sequential inserts

12. What is Index Skip Scan?

If composite index:

(department_id, salary)

Query:

WHERE salary = 5000;

Oracle may scan each department group.

Useful but slower than proper leading column usage.

13. What is Covering Index?

If index contains all columns needed:

Oracle may perform:

Fast Full Index Scan

Without accessing table.

Example:

CREATE INDEX emp_cover_idx

ON employees(department_id, salary, employee_id);

Query:

SELECT employee_id

FROM employees

WHERE department_id = 10;

Table access avoided.

14. How do statistics affect index performance?

Optimizer relies on:

·        Table statistics

·        Index statistics

·        Histogram data

If stats outdated:

·        Wrong execution plan

·        Index ignored

Always gather stats after:

·        Large data load

·        Bulk delete

·        Major updates

15. What is Bitmap vs B-Tree performance difference?

Bitmap:

·        Excellent for low-cardinality

·        Best in Data Warehouse

·        Poor for high DML

B-Tree:

·        Best for OLTP

·        Good for high-cardinality

·        Better concurrency

16. What is Index Monitoring?

To check if index is used:

ALTER INDEX index_name MONITORING USAGE;

Then check:

SELECT * FROM V$OBJECT_USAGE;

Helps identify unused indexes.

17. When does Full Table Scan perform better than index?

Full table scan may be better when:

·        Query returns >15–20% of rows

·        Table is small

·        Parallel query used

·        Index clustering factor high

Indexes are not always faster.

18. What are common index performance problems?

·        Unused indexes

·        Redundant indexes

·        High clustering factor

·        Low selectivity indexes

·        Outdated statistics

·        Excessive DML overhead

·        Hot block contention

19. Performance tuning best practices

·        Index only frequently filtered columns

·        Index foreign keys in OLTP

·        Use composite index for multi-column filters

·        Avoid indexing low-cardinality columns (use bitmap in DW)

·        Keep statistics updated

·        Avoid over-indexing

·        Monitor execution plans

·        Use reverse key for sequence-heavy inserts

·        Periodically review unused indexes

20. Real-world scenario

Problem:

Slow query:

SELECT *

FROM transactions

WHERE account_id = 500;

Table has 10 million rows.

Solution:

CREATE INDEX txn_acc_idx

ON transactions(account_id);

Result:

·        Full table scan replaced with index range scan

·        Dramatic performance improvement

But:

If 90% rows match condition → index may not help.

21. Interview Tip

If asked:

“How do indexes impact performance?”

Answer:

“Indexes improve SELECT performance by reducing I/O and enabling fast row lookup through B-Tree navigation. However, they add overhead to INSERT, UPDATE, and DELETE operations because index entries must be maintained. Proper index design depends on selectivity, clustering factor, workload type (OLTP vs DW), and query patterns. Over-indexing can degrade performance.”