Formula for estimating the extent size of a 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.

The following formula shows how the database server uses the ratio of the index key size plus some overhead bytes to the row size:
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
Important: For a non-unique index, the formula calculates an extent size that is reduced by 20 percent.

Copyright© 2019 HCL Technologies Limited