Space Costs for Join Indexes
The following formula estimates the space overhead required for a join index.
where:
|
Parameter |
Description |
|
F |
Length of the fixed column join_index_column_1 |
|
R |
Length of a single repeating column join_index_column_2 |
|
A |
Average number of repeated fields for a given value in join_index_column_1 |
|
U |
Number of unique values in the specified join_index_column_1 |
|
O |
Row overhead (assume 14 bytes) |
Updates to the base tables can cause a physical join index row to split into multiple rows. The newly formed rows each have the same fixed field value but contain a different list of repeated field values. This applies specifically when the compressed join index format is being used.
The system, however, does not automatically recombine logically related split rows. To re-compact such rows, you must drop and recreate the join index.