Indexes and Reorganization - Relational Database Index Design and the Optimizers - page 201

Chapter 11

Indexes and Reorganization

ž Physical structure of indexes
ž How index rows are accessed by the database management system
ž Effect of table row inserts on an index
ž Index leaf page splits
ž Reorganization
ž Serious effect of splits on index scans
ž Leaf page split ratio and its prediction using binomial distribution
ž Insert patterns
ž End of index, random, and hot spots
ž Free space recommendations and how to determine reorganization
frequencies
ž Special cases
ž Volatile index columns and long index rows
ž Effect of a disorganized index and table on large batch programs
ž Effect of leaf page splits on table rows stored in leaf pages with SQL
Server and Oracle
ž Cost of index reorganization
ž Monitoring splits

PHYSICAL STRUCTURE OF A B-TREE INDEX

In previous chapters, the indexes have been described as tables containing a
subset of the columns from the tables on which the indexes are built, together
with pointers to the table rows. We have assumed that the DBMS goes directly
to the first row of the index slice defined by the matching columns and stops as
soon as it finds an index row that does not have the right values of the matching
columns. The diagrams have always shown one index row per table row, even
when the index key is not unique. Furthermore, the index rows have always been
shown in the sequence defined by the index columns. This mental image is very