Reorganization and Free Space Strategies

With current disks, sequential read is much faster than random read (down to 0.1 ms per 4K). To get the benefit of this, your sequential scans should be really sequential. Leaf page splits add random reads to sequential index scans and misplaced table rows add random reads to clustered index scans with table access.

Some indexes and tables need lots of distributed free space, some do not need any. It makes sense to classify your tables and indexes according to the insert pattern. Row length plays a role as well. Both index and table rows are getting longer than they used to be. How should you specify free space if the average row length is 1,000 bytes?

The presentation recommends reorganization and free space strategies for both indexes and tables. It is based on a study by the DB2 User Group in Finland.