Index compression–quick tip

If you’re appropriately licensed and want to use advanced index compression, you can take advantage of the setting a tablespace to automatically add compression as a default at a nominated level in the database.  From the docs:

Here is an example of that in action.   We’ll set our tablespace default accordingly


SQL> create tablespace demo
  2  datafile 'C:\ORACLE\ORADATA\DB122\DEMO.DBF'
  3  size 100M
  4  default index compress advanced high;

Tablespace created.

Now we’ll create a table and an index on it in that tablespace


SQL> column index_name format a10
SQL> column tablespace_name format a15
SQL> create table t tablespace demo as
  2  select *
  3  from dba_objects;

Table created.

SQL> create index t_idx on t(owner) tablespace demo;

Index created.

SQL> select index_name,compression
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION  
---------- ------------- 
T_IDX      ADVANCED HIGH   

1 row selected.

You can see that even though we did not specify any kind of compression for the index, it picked up the setting from the tablespace. Thus existing DDL scripts you have for indexes et al will not need to be changed.

Similarly, rebuilding the index does not lose the setting


SQL> alter index t_idx rebuild tablespace demo;

Index altered.

SQL> select index_name,compression
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION   
---------- ------------- 
T_IDX      ADVANCED HIGH 

1 row selected.

You can see what the defaults are for a tablespace by querying DBA_TABLESPACES as per normal


SQL> select tablespace_name,compress_for,index_compress_for
  2  from user_tablespaces where tablespace_name = 'DEMO';

TABLESPACE_NAME COMPRESS_FOR                   INDEX_COMPRES
--------------- ------------------------------ -------------
DEMO                                           ADVANCED HIGH