Indexes in Oracle SQL are database objects that improve the performance of SQL queries by
enabling faster data retrieval. They function similarly to an index in a book, allowing the
database to locate rows in a table more efficiently without scanning the entire table. Indexes are
particularly beneficial for large tables and queries with selective conditions, such as those using
WHERE clauses or joins.
Types of Indexes in Oracle SQL
1. B-tree Index (Default)
o The most commonly used index type.
o Organized in a balanced tree structure, making it efficient for equality and range
queries.
o Example:
o CREATE INDEX idx_emp_name ON employees(employee_name);
2. Bitmap Index
o Uses a bitmap for each unique value.
o Suitable for columns with a low cardinality (few unique values).
o Often used in data warehousing scenarios.
o Example:
o CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
3. Unique Index
o Ensures all indexed values are unique.
o Created automatically when a UNIQUE or PRIMARY KEY constraint is added.
o Example:
o CREATE UNIQUE INDEX idx_emp_email ON employees(email);
4. Composite Index
o An index on two or more columns.
o Useful for queries that filter or sort on multiple columns.
o Example:
o CREATE INDEX idx_emp_name_dob ON employees(last_name,
date_of_birth);
5. Function-based Index
o Based on expressions or functions applied to columns.
o Useful for optimizing queries involving functions like UPPER, LOWER, etc.
o Example:
o CREATE INDEX idx_upper_emp_name ON
employees(UPPER(employee_name));
6. Invisible Index
o Indexes that exist but are not used by the optimizer unless explicitly referenced.
o Useful for testing without impacting existing queries.
o Example:
o CREATE INDEX idx_emp_test ON employees(test_column) INVISIBLE;
7. Reverse Key Index
o Stores the index keys in reverse order to avoid contention on sequential values.
o Useful for high-insert scenarios like sequence-generated numbers.
o Example:
o CREATE INDEX idx_rev_emp_id ON employees(employee_id) REVERSE;
Benefits of Indexes
Faster Query Performance: Speeds up SELECT statements and WHERE clause
operations.
Efficient Sorting: Improves ORDER BY and GROUP BY performance.
Optimized Joins: Helps when joining tables on indexed columns.
Drawbacks of Indexes
Increased Storage: Indexes require additional disk space.
Slower DML Operations: INSERT, UPDATE, and DELETE operations can be slower
due to index maintenance.
Overhead Management: Requires monitoring and potential rebuilding as data changes.
Best Practices
1. Use indexes selectively: Index only columns frequently used in WHERE, JOIN, or
ORDER BY clauses.
2. Avoid indexing low-selectivity columns: Columns with few unique values may not
benefit significantly.
3. Regular maintenance: Rebuild indexes periodically if fragmentation occurs.
Query to View Indexes
To view indexes on a table:
SELECT index_name, index_type, table_name
FROM user_indexes
WHERE table_name = 'EMPLOYEES';
Indexes are a powerful tool but must be used wisely to balance query performance and
maintenance overhead.
