Indexing in Depth

47.1 B-tree Index Structure

Root → branch blocks → leaf blocks (key + ROWID, doubly linked). Balanced, usually 2–4 levels.

CREATE INDEX ix_orders_customer ON orders(customer_id);
CREATE UNIQUE INDEX ux_customers_email ON customers(email);
CREATE INDEX ix_orders_cust_date ON orders(customer_id, order_date DESC);   -- composite

47.2 Index Types

B-tree (normal) OLTP, high-cardinality columns Multi-column. Leading column matters (skip scan can help).

CREATE INDEX ix_upper_name ON customers(UPPER(last_name));

Low cardinality, data warehouse, read-mostly (bad for concurrent DML) Bitmap join index Precomputed join for star schemas Reverse key

Spreads hot right-hand inserts (RAC sequences). No range scans.

Mixed sort orders Index-Organized Table Table stored as an index Compressed (prefix / Advanced)

COMPRESS n / COMPRESS ADVANCED LOW/HIGH Test the effect of dropping: ALTER INDEX ix INVISIBLE;

Skip maintenance during loads, then rebuild Partitioned (local / global) Partial indexes (12c)

Index only some partitions (INDEXING OFF) Domain indexes Text, Spatial, JSON search, vector Multivalue indexes JSON arrays (21c) Cluster index Index cluster key

47.3 Index Access Paths

Index unique scan, index range scan, index full scan (ordered), index fast full scan (multiblock, unordered), index skip scan, index join, bitmap AND/OR/MINUS conversions.

TABLE ACCESS BY INDEX ROWID (BATCHED).

The clustering factor strongly affects the cost of index range scans (DBA_INDEXES.CLUSTERING_FACTOR).

47.4 When Indexes Are Not Used

Functions or expressions on an indexed column (unless function-based), implicit conversions, a leading wildcard LIKE '%x', != / NOT IN, IS NULL (B-tree indexes don't store all-NULL keys, unless it's composite with a NOT NULL column or you use (col, 0)), low selectivity, stale statistics, an optimizer decision that a full scan is cheaper, and hints.

47.5 Index Maintenance and Monitoring

Rebuilding indexes is rarely needed. Don't rebuild on a schedule without evidence.

Automatic Indexing (19c, Exadata/ADB): DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT').

ALTER INDEX ix_orders_customer REBUILD ONLINE;
ALTER INDEX ix_orders_customer COALESCE;
ALTER INDEX ix_orders_customer MONITORING USAGE;      -- legacy
SELECT * FROM dba_index_usage;                        -- 12.2+ index usage tracking
SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor, status, visibility FROM user_indexes;
ANALYZE INDEX ix_orders_customer VALIDATE STRUCTURE;   -- then query INDEX_STATS (locks the table!)