Index compression–quick tip

Posted by

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';

---------- ------------- 

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';

---------- ------------- 

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';

--------------- ------------------------------ -------------
DEMO                                           ADVANCED HIGH


  1. Hi Connor,

    not sure I get this right, but don’t you mix up two different things:

    – In 12.1 the “db_index_compression_inheritance” parameter was added, but to understanding is that this is about table objects using OLTP compression inheriting the “advanced index low compression” to its indexes

    – In 12.2 the new “default index compression” clause was added to define a default index compression setting on tablespace level

    So your demo seems to demonstrate the latter (new default index compression on tablespace level in 12.2) whereas your text seems to refer to the former (inheriting the index compression from the table compression, introduced in 12.1 with the “db_index_compression_inheritance” parameter)


  2. Hi Connor,

    My question is related to index compression inheritance and not. Let me explain: if you rebuild an index partition, that already has advanced compression enabled, will it inherit the applied compression when you omit the compression clause? Or will it use the default, which is NOCOMPRESS? For example => ALTER INDEX REBUILD TABLESPACE PCTFREE 0 NOLOGGING ONLINE PARALLEL 4;

    We are on db version 12.1, and it looks like it is using NOCOMPRESS, which is not what I would expect…

    Regards, John

      1. Parameter is not set:
        SQL> show parameter db_index_compression_inheritance;
        ——————————– —— —–
        db_index_compression_inheritance string NONE

        I played around with this, but the index inheritance only works when the table compression is defined as ‘ADVANCED’. With any other compression (‘COLUMN STORE COMPRESS FOR QUERY LOW’ for example, it won’t.

        My workaround now is to rebuild the indexes, with exactly the compression I want, and not relying on any defaults.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.