Index Extent Sizes

The database server determines the extent size of an index based on the extent size for the corresponding table, regardless of whether the index is fragmented or not fragmented.

Estimating Extent Size of Attached Index

For an attached index, the database server uses the ratio of the index key size to the row size to assign an appropriate extent size for the index, as the following formula shows:

Index extent size = (index_key_size /
table_row_size) *
   table_extent_size

index_key_size

is the total widths of the indexed column or columns plus 4 for a key descriptor.

table_row_size

is the sum of all the columns in the row.

table_extent_size

is the value that you specify in the EXTENT SIZE keyword of the CREATE TABLE statement.

The database server also uses this same ratio for the next-extent size for the index:

Index next extent size =
(index_key_size/table_row_size)* 
   table_next_extent_size

Estimating Extent Size of Detached Index

For a detached index, the database server uses the ratio of the index key size plus some overhead bytes to the row size to assign an appropriate extent size for the index, as the following formula shows:

Detached Index extent size = ( (index_key_size + 
9) /                              table_row_size) *
 table_extent_size

For example, suppose you have the following values:

index_key_size = 8 bytes
table_row_size = 33 bytes
table_extent_size = 150 * 2-kilobyte page

The above formula calculates the extent size as follows:

Detached Index extent size = ( (8 + 9) /
33) * 150 * 2-kilobyte page
                           = (17/33) * 300 kilobytes
                           = 154 kilobytes