Creating or altering an index to cluster

Depending on the circumstances, you can eliminate extent interleaving if you create a clustered index or alter a clustered index. When you use the TO CLUSTER clause of the CREATE INDEX or ALTER INDEX statement, the database server sorts and reconstructs the table.

The TO CLUSTER clause reorders rows in the physical table to match the order in the index. For more information, see Clustering.

The TO CLUSTER clause eliminates interleaved extents under the following conditions:
  • The chunk must contain contiguous space that is large enough to rebuild each table.
  • The database server must use this contiguous space to rebuild the table.

    If blocks of free space exist before this larger contiguous space, the database server might allocate the smaller blocks first. The database server allocates space for the ALTER INDEX process from the beginning of the chunk, looking for blocks of free space that are greater than or equal to the size that is specified for the next extent. When the database server rebuilds the table with the smaller blocks of free space that are scattered throughout the chunk, it does not eliminate extent interleaving.

To display the location and size of the blocks of free space, execute the oncheck -pe command.

To use the TO CLUSTER clause of the ALTER INDEX statement:

  1. For each table in the chunk, drop all fragmented or detached indexes except the one that you want to cluster.
  2. Cluster the remaining index with the TO CLUSTER clause of the ALTER INDEX statement. This step eliminates interleaving the extents when you rebuild the table by rearranging the rows.
  3. Re-create all the other indexes.

You do not need to drop an index before you cluster it. However, the ALTER INDEX process is faster than CREATE INDEX because the database server reads the data rows in cluster order using the index. In addition, the resulting indexes are more compact.

To prevent the problem from recurring, consider increasing the size of the tblspace extents.

Copyright© 2019 HCL Technologies Limited