Reorg operators
The reorg operator reorganizes a table or an index.
Table reorganization
This option reorganizes a table by reconstructing rows to eliminate fragmented data, and by compacting information.
When several changes are made to table data, the logical sequential data can get stored on
nonsequential physical data pages. This forces the database manager to perform additional read
operations to access data. In such cases, you can reorganize the table to match the index and to
reclaim space. Reorganizing of tables could be required in the following situations:
- A high volume of insert, update, and delete activity occurred on tables accessed by queries.
- Significant changes are noticed in the performance of queries that use an index with a high cluster ratio.
- Little or no improvement is noticed in performance after executing runstats to refresh statistical information
Index reorganization
The index option reorganizes all indexes that are defined on a table by rebuilding the index data
into unfragmented, physically contiguous pages. If you specify the Cleanup Only option while
reorganizing an index, cleanup is performed without rebuilding the indexes.
Restriction: The reorg operation cannot be used against indexes on created temporary tables and declared
temporary tables.
Reorganizing of indexes could be required in the following situations:
- Leaf pages are fragmented. Fragmentation causes I/O costs to increase because more leaf pages must be read to fetch table pages.
- The index is badly clustered. When leaf pages are badly clustered, sequential prefetching is inefficient and results in more I/O waits.
- The index develops more than its maximally efficient number of levels.