Guidelines for Using Row Compression With Join Index Columns
A column set with a high number of distinct values cannot be row compressed because it rarely (and in the case of a primary key, never) repeats. Other column sets, notably foreign key and status code columns, are generally highly non-distinct: their values repeat frequently. Row compression capitalizes on the redundancy of frequently repeating column values by storing them once in the fixed part of the index row along with multiple repeated values in the repeated part of the index row.
Typically, primary key table column values are specified as the repeating part and foreign key table columns are specified in the fixed part of the index definition.
“Using Outer Joins to Define Join Indexes” on page 559 and “Creating Join Indexes Using Outer Joins” on page 561 are examples of how you can use row compression to take advantage of repeated column values.
A row-compressed join index might be used for a query when the join index would be used if it were not row compressed, Check the EXPLAIN for a query to see if join indexes are being used.