12.2 Index Advanced Compression “High”.

12.2 Index Advanced Compression “High”

Let’s begin by creating a table and explicitly creating a NOCOMPRESS index in the WHALE tablespace:

SQL> create table WHALE (id number, Slno number, name varchar2(38));
Table created.

SQL> insert into WHALE select rownum, mod(rownum,10), 'BLUE WHALE' from dual connect by level <= 1000000;
1000000 rows created.

SQL> commit;
Commit complete.

SQL> create index WHALE_Slno_idx on WHALE(Slno) tablespace WHALE nocompress;
Index created.

If we look at the size and compression type of this index:

SQL> select index_name, tablespace_name, leaf_blocks, compression from dba_indexes where index_name='WHALE_Slno_IDX';

We notice the index has 1431 leaf blocks and that index compression is indeed disabled as expected.
Let’s now drop the index and recreate again it in the WHALE tablespace, but this time without explicitly stating any compression option:

SQL> drop index WHALE_Slno_idx;
Index dropped.

SQL> create index WHALE_Slno_idx on WHALE(Slno) tablespace WHALE;
Index created.

SQL> exec dbms_stats.gather_index_stats(ownname=>null, indname=>'WHALE_Slno_IDX');
PL/SQL procedure successfully completed.

If we look at the index now:

SQL> select index_name, tablespace_name, leaf_blocks, compression from dba_indexes where index_name='WHALE_Slno_IDX';

We notice the index now only has 208 leaf blocks (down from 1431 leaf blocks) and that it has automatically used the new index advanced compression option of “HIGH”.
The secret lies with the following new settings.
Firstly, with the new db_index_compression_inheritance parameter, you can specify how during index creation the index inherits its index compression attributes (tablespace or table or not at all):

SQL> alter system set db_index_compression_inheritance=tablespace scope=both;
System altered.

Secondly, you can give a tablespace an index compression attribute on how indexes are compressed by default within the tablespace:

SQL> alter tablespace WHALE default index compress advanced high;
Tablespace altered.

SQL> select tablespace_name, def_index_compression, index_compress_for from dba_tablespaces where tablespace_name = 'WHALE';

So in this database, all indexes created within the WHALE tablespace are automatically created
with index advanced compression set to HIGH.

There are however some disadvantages with high index advanced compression that need to be considered.