COMPRESSED option for indexes

Use the COMPRESSED keyword of the CREATE INDEX statement to compress the B-tree index if the index has 2000 or more keys.

You can create a compressed index on a fragmented or non-fragmented table.

You cannot create a compressed index that is also a cluster index. However, you can compress an existing cluster index by running an SQL administration API task() or admin() function with the index compress argument.

To be compressed, the index or fragment within the index must have at least 2000 keys. If you use the COMPRESSED option when you create an index that does not have enough keys, the database server does not compress the index or fragment when it creates the index. The index remains uncompressed even if new keys are added to it. If you want to compress the index, run an SQL administration API task() or admin() function with the index compress argument.

If a table does not have enough data to provide a large enough sample of index keys, the database server does not compress the index or fragment. Even the minimum required number of new keys are added to an existing index that is not compressed, the database server does not compress the index.

However, after an index is compressed, the database server compresses and inserts any new key added to the index.

The following example creates a compressed index named cust3_ix on the address column of the customer table. 
CREATE INDEX cust3_ix ON customer (address) COMPRESSED 
   EXTENT SIZE 32 NEXT SIZE 32;
The following example creates a unique, compressed index: 
CREATE UNIQUE INDEX cust3_ix ON customer (address) COMPRESSED ;