Index Best Practices FAQS

1. What is an index in Oracle?

An index is a database object that improves query performance by allowing fast access to rows in a table.

• Acts like a table of contents for the database table.
• Reduces the number of blocks read for SELECT queries.
• Maintains pointers (ROWIDs) to table rows.

2. Why are index best practices important?

Indexes improve SELECT performance but can negatively impact DML (INSERT/UPDATE/DELETE).
Best practices help to:

• Maximize query performance
• Minimize storage overhead
• Reduce unnecessary maintenance
• Avoid performance degradation

3. When should you create an index?

Create an index on:

• Columns frequently used in WHERE clauses
• Columns used in JOIN conditions
• Columns used in ORDER BY or GROUP BY
• Primary keys and foreign keys

Avoid indexing:

• Columns updated frequently
• Low cardinality columns (few distinct values)
• Temporary or small tables

4. What types of indexes should you use?

• B-Tree Index – Default, best for OLTP, high-cardinality columns
• Bitmap Index – Best for low-cardinality columns, data warehousing
• Function-Based Index – Index on expressions or functions
• Composite (Concatenated) Index – Multiple columns, respects column order
• Reverse Key Index – Helps avoid hot blocks for sequential inserts

5. How to design indexes for queries?

• Single-column index: For queries filtering on one column.
• Composite index: For queries filtering on multiple columns. Ensure the leading column is most selective.
• Covering index: Include all columns used in query to avoid table access.

Example:

CREATE INDEX emp_dept_idx ON employees(department_id, salary);

Efficient if queries filter on department_id and salary.

6. How many indexes per table is recommended?

• Minimal but sufficient: Only create indexes that benefit critical queries.
• Too many indexes:
• Slows INSERT/UPDATE/DELETE
• Increases storage usage
• Complicates optimizer decisions

Rule of thumb: Only index frequently queried or joined columns.

7. How do indexes affect DML operations?

• INSERT: Each index must be updated → slower inserts
• UPDATE: If indexed column changes → delete old entry + insert new entry
• DELETE: Each index entry must be removed

Minimize unnecessary indexes on heavily modified tables.

8. What about index maintenance?

• Gather statistics regularly:

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

• Rebuild indexes if fragmented:

ALTER INDEX emp_dept_idx REBUILD;

• Monitor index usage:

ALTER INDEX emp_dept_idx MONITORING USAGE;

SELECT * FROM V$OBJECT_USAGE;

• Drop unused indexes to reduce overhead.

9. What are common mistakes to avoid?

• Indexing every column (“over-indexing”)
• Indexing low-cardinality columns in OLTP systems
• Ignoring index selectivity and clustering factor
• Not using bind variables → increases parsing overhead
• Forgetting to monitor index usage and performance

10. How to optimize composite indexes?

• Order columns by selectivity (most selective first)
• Only include columns used in WHERE, JOIN, ORDER BY, or GROUP BY
• Avoid including too many columns → larger index size

Example:

-- Good: department_id is selective

CREATE INDEX emp_comp_idx ON employees(department_id, job_id);

11. Best practices for function-based indexes

• Use when queries involve functions:

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

• Only useful if queries use the same function.
• Always ensure deterministic functions.

12. Best practices for bitmap indexes

• Suitable for data warehouses or low-cardinality columns (gender, status)
• Avoid in OLTP because of locking issues during DML

13. Monitoring index performance

• Use EXPLAIN PLAN to check query execution
• Monitor V$OBJECT_USAGE for index usage
• Check clustering factor:

SELECT INDEX_NAME, CLUSTERING_FACTOR

FROM USER_INDEXES WHERE TABLE_NAME='EMPLOYEES';

• Low clustering factor → better range scan performance

14. Recommended index practices summary

• Index critical columns only
• Use B-Tree for OLTP, Bitmap for DW
• Prefer composite indexes for multi-column queries
• Keep statistics updated
• Avoid over-indexing
• Monitor index usage periodically
• Rebuild indexes only when fragmented
• Consider function-based indexes for queries with functions

15. Interview Tip

If asked:

“What are the best practices for Oracle indexes?”

Answer:

“Create indexes only on frequently queried or joined columns. Use B-Tree for high-cardinality columns and Bitmap for low-cardinality in data warehouses. Keep composite indexes ordered by selectivity, maintain statistics, monitor usage, avoid over-indexing, and rebuild indexes only if necessary. Proper index design balances SELECT performance and DML overhead.”