Chapter 11. Indexes and Reorganization
Published Online: 27 JAN 2005
DOI: 10.1002/0471721379.ch11
Copyright © 2005 John Wiley & Sons, Inc.
Book Title
Relational Database Index Design and the Optimizers: DB2, Oracle, SQL Server, et al.
Additional Information
How to Cite
Lahdenmäki, T. and Leach, M. (2005) Indexes and Reorganization, in Relational Database Index Design and the Optimizers: DB2, Oracle, SQL Server, et al., John Wiley & Sons, Inc., Hoboken, NJ, USA. doi: 10.1002/0471721379.ch11
Publication History
- Published Online: 27 JAN 2005
- Published Print: 24 JUN 2005
ISBN Information
Print ISBN: 9780471719991
Online ISBN: 9780471721376
- Summary
- Chapter
Keywords:
- index physical structure;
- index rows;
- index access;
- table row inserts;
- index leaf page splits;
- splits;
- reorganization;
- index scans;
- leaf page split ratio;
- leaf page split ratio prediction;
- binomial distributions;
- insert patterns;
- end of index inserts;
- random inserts;
- hot spot inserts;
- free space;
- free space recommendations;
- reorganization;
- reorganization frequency;
- volatile index columns;
- long index rows;
- disorganized indexes;
- disorganized tables;
- batch programs;
- SQL Server;
- Oracle;
- index reorganization cost;
- monitoring splits
Summary
The physical structure of indexes. How index rows are accessed by the database management system. The effect of table row inserts on an index; index leaf page splits; reorganization. The 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. The effect of a disorganized index and table on large batch programs. The effect of leaf page splits on table rows stored in leaf pages with SQL Server and Oracle. The cost of index reorganization. Monitoring splits.