Oracle Indexes: B-Tree, Bitmap, Function-Based and Invisible
Why Indexes?
Without an index Oracle reads every block of a table to find your rows (a full table scan). An index is a small sorted structure that points directly to the rows, so lookups become very fast.
Simple example: To find 'Oracle' in a 500 page book you could read every page, or use the index at the back. The index costs a few extra pages but saves a huge amount of time.
Index Types
| Type | Best for | Watch out |
|---|---|---|
| B-tree (default) | Columns with many different values, most OLTP queries | Slows down INSERT and UPDATE a little |
| Composite | Queries that filter on several columns together | Column order matters |
| Function-based | Searches like WHERE UPPER(name) = 'ASHA' | Query must use the same expression |
| Bitmap | Few distinct values (gender, status) in reporting systems | Bad for heavy concurrent DML. Enterprise Edition. |
| Invisible | Testing the effect of dropping an index | Optimizer ignores it by default |
B-Tree and Composite Indexes
sql
CREATE INDEX idx_orders_cust ON shop.orders (customer_id);
CREATE INDEX idx_orders_cust_status ON shop.orders (customer_id, status);
CREATE UNIQUE INDEX uq_cust_email ON shop.customers (email);
Column order: Put the column you filter by equality most often first. The index on
(customer_id, status) helps queries on customer_id alone and on both columns, but not on status alone.
Function-Based Index
sql
CREATE INDEX idx_cust_upper_name ON shop.customers (UPPER(full_name));
SELECT * FROM shop.customers WHERE UPPER(full_name) = 'ASHA RAO';
Bitmap Index
sql
CREATE BITMAP INDEX bix_orders_status ON shop.orders (status);
Invisible Index: Test Before You Drop
sql
ALTER INDEX shop.idx_orders_cust INVISIBLE;
-- run your workload and watch for slow queries
ALTER INDEX shop.idx_orders_cust VISIBLE;
If nothing gets slower with the index invisible, it is safe to drop. If something breaks, make it visible again instantly, with no rebuild.
Check Indexes
sql
SELECT index_name, index_type, uniqueness, status, visibility
FROM dba_indexes
WHERE owner = 'SHOP';
SELECT index_name, column_name, column_position
FROM dba_ind_columns
WHERE table_owner = 'SHOP' AND table_name = 'ORDERS'
ORDER BY index_name, column_position;
Rebuild an Index
sql
ALTER INDEX shop.idx_orders_cust REBUILD ONLINE;
Do you need to rebuild? Not on a schedule. B-tree indexes balance themselves. Rebuild only after a large mass delete, after a table move on older versions or when an index is marked
UNUSABLE.
Find Unused Indexes
sql
SELECT name, total_access_count, last_used
FROM dba_index_usage
WHERE owner = 'SHOP'
ORDER BY total_access_count;
When NOT to Index
- Very small tables (a full scan is just as fast)
- Columns that are rarely used in WHERE or JOIN
- Tables with very heavy inserts where read speed does not matter
- Columns with extremely low selectivity on OLTP tables
Foreign keys: Oracle does not index foreign key columns for you. An unindexed foreign key on a busy table often causes locking and slow joins. Index them.
Conclusion
Create indexes for the columns you search and join on, keep them few and test removal with invisible indexes.
Quick Summary: B-tree is the default, function-based matches expressions, invisible tests removal, index your foreign keys.

