To change the compression state of the table for future index inserts or updates, use alter table or alter index.
Existing index pages are not affected, whether or not they are compressed. To change the compression state of a table, you must have exclusive access to the table.
Changing the local index partition's compression state affects only index rows that are newly inserted or updated in the partition.
The alter table command permits many combinations of schema modifications and property modifications. Some of these commands require only a catalog update, whereas others need data movement, along with the rebuilding of any existing indexes. If indexes require rebuilding, and index compression is set to on, index pages are compressed as part of the index rebuilding. After index rebuilding, the resulting index contains compressed or uncompressed index rows according to the index compression state.
The set index_compression clause specifies the index compression to be enabled or disabled on the table, index, or local index partition. If table is modified to be index compressed, newly created indexes are compressed.
The modify partition partition_name clause names local index partitions for which the compression state is being modified as specified by the set compression clause that follows.
The permissions for alter index defaults to the index owner and cannot be transferred except to the database owner, who can impersonate the index owner by running the setuser command. A system administrator can also alter user indexes.
APL-clustered indexes are not supported by index compression.
Unique indexes that have only one column are not compressed.
To remove compression from indexes, the indexes must be dropped and then re-created with set index_compression off.