How to create a compressed index using advanced index compression

Applicable for Oracle 12.1 and onwards.

Basic syntax:

CREATE INDEX MYIDX1 
ON MYTABLE(COL1, COL2)
COMPRESS ADVANCED LOW;

The above statement will create a compressed index in the connected user's default tablespace, using Oracle Advanced Index Compression.

In my environment, I tried this on a B-tree index on a 62 million row table. The size dropped from 3136 to 2368 MB, a 25 percent reduction.

An index can also be altered to use advanced index compression, like this:

ALTER INDEX MYIDX1 REBUILD COMPRESS ADVANCED LOW;

If it's important to allow DML on the index during rebuild, add ONLINE, like this:

ALTER INDEX MYIDX1 REBUILD COMPRESS ADVANCED LOW ONLINE;