How To Find Optimal Index Key COMPRESS Level For Indexes

Key compression on an index eliminates repeated occurrence of key column values.The amount of compression is dependent on repeated occurrences.The COMPRESS integer specifies the number of prefix columns to compress (up to the number of primary-key columns minus 1.)


Run the following SQL and select the COMPRESS level that shows the greatest percentage of savings:


analyze index {INDEX_NAME} validate structure;


select opt_cmpr_count, opt_cmpr_pctsave from index_stats where name = '{INDEX_NAME}';

To compress  the index

alter index  {INDEX_NAME} rebuild online compress  N;