Uncategorized

Compression default settings

Posted by

If you are loading a data warehouse or similar environment where your default approach would probably be to compress all objects because they are predominantly read only, you do not need to head back into your DDL scripts and add a “COMPRESS” clause to every table.

You can set the default compression level at the tablespace level. Here’s an example of that in action


SQL> create tablespace demo
  2      datafile 'X:\ORACLE\ORADATA\DB19\DEMO.DBF'
  3      size 100M
  4      default table compress;

Tablespace created.

SQL> create table t tablespace demo as
  2      select *
  3      from dba_objects;

Table created.

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

TABLE_NAME                     COMPRESS
------------------------------ --------
T                              ENABLED

The docs also suggest that the same can be done for indexes in that tablespace using the DEFAULT INDEX clause.

Lets give that a go:


SQL> drop tablespace demo including contents and datafiles ;

Tablespace dropped.

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

Tablespace created.

SQL> create table t tablespace demo as
  2      select *
  3      from dba_objects;

Table created.

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

Index created.

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

INDEX_NAME                     COMPRESSION
------------------------------ -------------
T_IDX                          DISABLED

Unfortunately that doesn’t seem to work at so by default you’ll need to specify it explicitly for each index you create.


SQL> drop index t_idx;

Index dropped.

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

Index created.

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

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

SQL> select INDEX_COMPRESS_FOR
  2  from   dba_tablespaces
  3  where  tablespace_name = 'DEMO';

INDEX_COMPRES
-------------
ADVANCED HIGH

Now it would seem odd that we have a clause for default index compression, yet it does nothing. I initially thought it was bug but AskTOM regular Rajeshwaran refreshed my memory to (embarrasingly) an AskTOM post! where we talked about the role the parameter that DB_INDEX_COMPRESSION_INHERITANCE plays in controlling the tablespace default settings.

Setting that parameter will achieve the expected behaviour 


SQL> drop index t_idx;

Index dropped.

SQL> alter session set db_index_compression_inheritance=tablespace;

Session altered.

SQL>
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

SQL> select INDEX_COMPRESS_FOR
  2  from   dba_tablespaces
  3  where  tablespace_name = 'DEMO';

INDEX_COMPRES
-------------
ADVANCED HIGH

If you are new to compression on indexes, check out this cool blog post series on it, but remember that depending on the type of compression you are performing, there may be licensing implications. Check with your local account representative if you need clarification.

Photo by Mark Timberlake on Unsplash