When you create a join index, the database automatically transfers any column multivalue compression defined on its base table set, with a few exceptions, to the join index definition. This is different from the situation for hash indexes, which cannot inherit the multivalue compression characteristics of their parent base tables.
The following rules and restrictions apply to automatically transferring the column compression characteristics of a base table to its underlying join index columns. All of these rules and restrictions must be met for base table column multivalue compression to transfer to the join index definition.
- 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 up to the point that the maximum table header length of the join index is exceeded.
The CREATE JOIN INDEX request does not abort 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 (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.