When you create a join index, the database transfers any column multivalue compression defined on its base table set to the join index definition, according to the following following rules and restrictions.
- Base table column multivalue compression transfers to a join index definition even if there is an alias name specified for the columns in the join index definition.
- Base table column multivalue compression transfers to a multitable join index definition only until the maximum table header length of the join index is exceeded.
The CREATE JOIN INDEX request does not stop at that point, but the transfer of column multivalue compression from the base table to the join index definition stops.
- Base table column multivalue compression does not transfer to a join index definition if the column is a component of the primary index for the join index.
- Base table column multivalue compression does not transfer to a join index definition for any of the columns that are components of a partitioned primary index or the partitioning expression for the join index.
- Base table column multivalue compression does not transfer to a join index column if the column is specified as the argument for aggregate or EXTRACT functions.
- Base table column multivalue compression does not transfer to a column in a compressed join index if the column is a component of a NUSI defined on the column_1 and column_2 indexes.
- If you use an ALTER TABLE request to modify the compression characteristics of any base table columns that are also components of a join index, the database returns a warning message advising you that you must recreate the join index to reflect those compression changes. See ALTER TABLE Usage Notes (Basic Table Parameters).
- Base table column multivalue compression does not transfer to a join index definition if the column is a component of an ORDER BY clause in the join index definition.
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 (never for a primary key or unique index) repeats. Foreign key and status code columns are highly nondistinct: 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 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.
See Row Compression for more information about row compression for join indexes.