B-Tree Index Structure, Leaf Blocks, Branches, Rowids, Selectivity, and Clustering Factor

Learning outcomes

ServiceHub’s work-order table has grown enough that a query on a highly distinct ticket number is fast with an index, while a report on a similarly distinct timestamp still prefers a full scan. A developer concludes that one index is “fragmented” and proposes rebuilding it nightly. That diagnosis confuses three separate ideas: the B-tree’s structure, the fraction of rows a query needs, and how index-key order correlates with the table’s physical row placement. This lesson makes those mechanisms observable before any tuning decision.

1. The B-tree is an ordered access structure, not a copy of the table

Oracle’s default normal index is a balanced B-tree. The top root and any intermediate branch blocks direct a search toward the correct range of leaf blocks. Leaf entries hold ordered key values plus rowids for heap-organized rows. A rowid identifies a row’s physical location sufficiently for Oracle to fetch the table block after locating the key.

Because the tree stays height-balanced as keys are inserted, the existence of deleted space or leaf splits does not imply that a periodic rebuild is required. Rebuild decisions need measured symptoms and a reason, not a calendar.

SELECT    work_order_id,    status_code,    ROWID AS heap_rowidFROM servicehub_ix_work_orderORDER BY work_order_idFETCH FIRST 5 ROWS ONLY;SELECT    index_name,    blevel,    leaf_blocks,    distinct_keys,    num_rows,    clustering_factor,    last_analyzedFROM user_indexesWHERE table_name = 'SERVICEHUB_IX_WORK_ORDER'ORDER BY index_name;

BLEVEL is the B-tree depth from root to leaves (with zero meaning root and leaf are the same block in the current dictionary definition). LEAF_BLOCKS counts leaf blocks. These statistics are useful evidence, but none by itself means “healthy” or “fragmented.”

2. Access-path names describe how Oracle consumes the index

A full scan and a fast full scan are not synonyms: a normal full index scan preserves index order; a fast full scan does not promise that order. Never remove an ORDER BY because a current plan happens to read an ordered index.

EXPLAIN PLAN SET STATEMENT_ID = 'SH08L1_PK'FORSELECT work_order_id, status_codeFROM servicehub_ix_work_orderWHERE work_order_id = 10042;SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH08L1_PK', 'BASIC +PREDICATE'));EXPLAIN PLAN SET STATEMENT_ID = 'SH08L1_RANGE'FORSELECT work_order_id, created_atFROM servicehub_ix_work_orderWHERE created_at >= TIMESTAMP '2026-08-01 00:00:00'  AND created_at <  TIMESTAMP '2026-09-01 00:00:00';SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH08L1_RANGE', 'BASIC +PREDICATE'));

EXPLAIN PLAN shows an estimated plan for teaching. It does not prove an executed cursor used that plan or reveal actual rows. Chapter 09 will separate estimated plans from runtime evidence in depth.

3. Selectivity is necessary reasoning, but not sufficient reasoning

Selectivity is the fraction of rows a predicate is expected to return. A predicate matching one row out of one million is highly selective; one matching half the table is not. Yet cardinality of the indexed column is only one input. The optimizer also considers available statistics, clustering, table/index sizes, required columns, ordering, and the cost of fetching table blocks.

A high-cardinality column can still be a poor access path for a query that requests most rows. Conversely, a lower-cardinality composite index can be excellent when a highly selective combination of predicates is common.

4. Clustering factor measures table correlation, not index fragmentation

Oracle calculates CLUSTERING_FACTOR by walking index entries and observing how often adjacent entries point to different table blocks. A value closer to the number of table blocks means neighboring keys often lead to the same or nearby table blocks; a value closer to the number of rows means the keys point around the heap more randomly. That can make large range scans through the index more expensive because many table blocks must be visited.

The factor is a property of the relationship between one index’s key order and the current heap row placement. Rebuilding the index does not reorder heap rows, so a rebuild is not a general repair for a high clustering factor.

SELECT table_name, num_rows, blocksFROM user_tablesWHERE table_name = 'SERVICEHUB_IX_WORK_ORDER';SELECT    index_name,    distinct_keys,    clustering_factor,    num_rows,    leaf_blocksFROM user_indexesWHERE table_name = 'SERVICEHUB_IX_WORK_ORDER'ORDER BY index_name;

Use the numbers comparatively. If the table has 1,000 data blocks and 100,000 rows, a clustering factor near 1,000 indicates strong physical correlation for that index; near 100,000 indicates weak correlation. There is no universal “bad percentage” that mandates a rebuild.

5. Deliberately wrong approach: rebuild because the clustering factor is high

Suppose SH08_CREATED_IX has a high clustering factor and a date-range report reads many table blocks. Rebuilding only the B-tree can compact or reorganize the index segment, but it does not reorder the heap rows to match created_at. The clustering factor therefore may remain materially unchanged.

SELECT index_name, blevel, leaf_blocks, clustering_factor, last_analyzedFROM user_indexesWHERE index_name = 'SH08_CREATED_IX';SELECT table_name, num_rows, blocks, last_analyzedFROM user_tablesWHERE table_name = 'SERVICEHUB_IX_WORK_ORDER';

A safe repair starts by proving the query is important, gathering representative statistics, comparing full-scan versus index-access cost, and considering whether data organization or partitioning is actually the mechanism needed. Do not reorganize production data merely to improve one clustering factor without evaluating all dependent workloads and recovery/maintenance cost.

6. Hands-on lab: create two distributions and compare evidence

The lab builds 10,000 small rows. One index key follows insertion order; another key deliberately scrambles the mapping to table blocks. Statistics make the difference visible without requiring privileged tracing.

DROP TABLE servicehub_ix_work_order IF EXISTS PURGE;CREATE TABLE servicehub_ix_work_order (    work_order_id NUMBER PRIMARY KEY,    scrambled_key NUMBER NOT NULL,    status_code   VARCHAR2(12) NOT NULL,    created_at    TIMESTAMP NOT NULL,    payload       VARCHAR2(80));INSERT INTO servicehub_ix_work_orderSELECT    LEVEL,    MOD(LEVEL * 7919, 10000),    CASE MOD(LEVEL,4)      WHEN 0 THEN 'OPEN'      WHEN 1 THEN 'CLOSED'      WHEN 2 THEN 'HOLD'      ELSE 'ASSIGNED'    END,    TIMESTAMP '2026-01-01 00:00:00'      + NUMTODSINTERVAL(LEVEL, 'MINUTE'),    RPAD('x', 40, 'x')FROM dualCONNECT BY LEVEL <= 10000;CREATE INDEX sh08_created_ixON servicehub_ix_work_order(created_at);CREATE INDEX sh08_scrambled_ixON servicehub_ix_work_order(scrambled_key);BEGIN    DBMS_STATS.GATHER_TABLE_STATS(        ownname => USER,        tabname => 'SERVICEHUB_IX_WORK_ORDER',        cascade => TRUE    );END;/SELECT    i.index_name,    i.num_rows,    i.distinct_keys,    i.leaf_blocks,    i.clustering_factor,    t.blocks AS table_blocksFROM user_indexes iJOIN user_tables t ON t.table_name = i.table_nameWHERE i.table_name = 'SERVICEHUB_IX_WORK_ORDER'ORDER BY i.index_name;

You should expect the insertion-correlated CREATED_AT index to have a clustering factor much closer to table-block count than the deliberately scrambled key. Exact numbers vary by block size and storage; the relationship is the evidence.

DROP TABLE servicehub_ix_work_order PURGE;

7. Production judgment

Use B-trees when the workload benefits from ordered key lookup, range access, uniqueness enforcement, or index-only access. Measure the fraction of rows retrieved and the table-block work behind rowids. Rebuild an index only for a diagnosed mechanism—never because BLEVEL, free space, or clustering factor “looks high.” Indexes add storage, redo/undo, buffer activity, and DML maintenance on every changed key.

No extra option, pack, restart, or COMPATIBLE change is required for the mandatory Free lab. The next lesson moves from one-column B-trees to design choices that deliberately shape keys: composite order, descending keys, expressions, visibility, and compression.

Authoritative references

• Indexes and Index-Organized Tables — B-tree structure, rowids, clustering factor, and index scans
• Optimizer Access Paths — cost-based choice of index and full-scan access paths
• PLAN_TABLE Reference — INDEX UNIQUE/RANGE/FULL/FAST FULL/SKIP SCAN operation meanings
• ALL_INDEXES — BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR and visibility metadata
• DBMS_STATS — optimizer statistics collection