Explain indexing strategies.
In the world of Oracle Database, an index is like the index at the back of a book. Without it, if you wanted to find every mention of the word "Optimizing," you'd have to read every single page (a Full Table Scan). With an index, you jump straight to the correct page numbers in a fraction of the time.
However, indexing isn't just about "adding more." It’s a strategy. Too many indexes can slow down your INSERT and UPDATE operations, while too few will make your SELECT queries crawl.
This is the default and most common index type. It’s structured like an upside-down tree.
Best For: High-cardinality columns (columns with many unique values, like EMAIL, EMPLOYEE_ID, or ORDER_NUMBER).
Strategy: Use this for primary keys and unique constraints. It is incredibly efficient for "Equal to" (=) and "Range" (<, >, BETWEEN) queries.
Unlike B-Trees, Bitmap indexes use a string of bits (0s and 1s) to represent data.
Best For: Low-cardinality columns (columns with few unique values, like GENDER, MARITAL_STATUS, or REGION).
Strategy: Use this primarily in Data Warehousing. Because they are very efficient for "AND" and "OR" logic, they are great for complex reporting.
Warning: Avoid Bitmaps in high-concurrency (OLTP) databases. Updating a single row can lock a "chunk" of the index, causing major performance bottlenecks for other users.
Normally, if you run SELECT * FROM users WHERE UPPER(last_name) = 'SMITH', Oracle cannot use a standard index on last_name because the UPPER function changes the data.
Strategy: Create an index on the function itself:
CREATE INDEX idx_upper_name ON users(UPPER(last_name));
Best For: Queries that consistently use transformations (math, case changes, or date truncations) in the WHERE clause.
A composite index is a single index on multiple columns, like (last_name, first_name).
Strategy: Follow the Leading Column Rule. An index on (A, B) is very useful for queries on A or A and B, but it is much less efficient for queries on B alone.
Best For: Queries that frequently filter by multiple specific columns together.
| Strategy | Index Type | Best Use Case | Performance Impact |
| Direct Search | B-Tree | Primary Keys, Unique IDs | Low overhead on inserts. |
| Reporting/DW | Bitmap | Gender, Active/Inactive flags | High overhead on inserts/updates. |
| Logic-Based | Function-Based | Case-insensitive searches | Small overhead; very specific. |
| Multi-Filter | Composite | Searching by Name + City | High efficiency for specific combos. |
Every time you INSERT a row, Oracle has to update the table and every index attached to it. If a table has 10 indexes, one insert becomes 11 writes. Only index what you actually query.
This is a classic DBA move. Indexing foreign keys prevents "Full Table Locks" when you delete rows from a parent table and helps speed up JOIN operations.
Oracle can track if an index is actually being used. If you have an index that hasn't been touched in six months, drop it. It’s just costing you storage and slowing down your updates.
ALTER INDEX idx_name MONITORING USAGE;
-- Check usage later in V$OBJECT_USAGE
Did you know that in modern Oracle versions, if you have a composite index on (Gender, Employee_ID), and you only search by Employee_ID, Oracle can still sometimes use that index? It’s called an Index Skip Scan. It’s not as fast as a direct scan, but it's much better than a Full Table Scan!