Compression default settings

October 2, 2020

2 minutes

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

5 responses to “Compression default settings”

Rajeshwaran Jeyabal

October 2, 2020 at 8:10 pm

Connor,

We need to set the parameter “DB_INDEX_COMPRESSION_INHERITANCE” for the default index compression to kickin from Tablespace attribute.

Here is my test case demonstrating that.

demo@PDB1> create tablespace ts_demo
2 datafile size 100m
3 default index compress advanced high;

Tablespace created.

demo@PDB1> create table t as select * from all_objects;

Table created.

demo@PDB1> show parameter db_index_compression_inheritance

NAME TYPE VALUE
———————————— ———– ——————————
db_index_compression_inheritance string NONE
demo@PDB1> create index t_idx on t(object_id) tablespace ts_demo;

Index created.

demo@PDB1> select compression
2 from user_indexes
3 where index_name =’T_IDX’;

COMPRESSION
————-
DISABLED

demo@PDB1> alter session set db_index_compression_inheritance=tablespace;

Session altered.

demo@PDB1> create index t_idx2 on t(owner,object_id) tablespace ts_demo;

Index created.

demo@PDB1> select compression
2 from user_indexes
3 where index_name =’T_IDX2′;

COMPRESSION
————-
ADVANCED HIGH

Also this compression inheritance from tablespace will kick in only during “create index” statement, and not during “index rebuilds”
details @ https://asktom.oracle.com/pls/apex/asktom.search?tag=advanced-index-compression-high-in-122#9534472000346307879

Reply
1. Connor McDonald
  
  October 2, 2020 at 8:13 pm
  
  Excellent stuff. I had totally forgotten about this parameter.
  
  Reply
Autumn is on its way – Oracle Business Intelligence

October 9, 2020 at 3:24 am

[…] 10. Compression default settings […]

Reply
Jason Bucata

February 5, 2021 at 4:27 am

The railroad track syntax diagrams in the documentation appears to be wrong… you said and demonstrated that it’s legal to just specify DEFAULT TABLE COMPRESS, but according to the docs that’s not allowed: You’d have to specify DEFAULT COMPRESS FOR [something], which seems to imply using the licensable Advanced Compression option.

I was searching the net to find out how to use basic compression by default in a tablespace, and I eventually found this post. I’m glad to see it’s possible to do what I want… but meanwhile if you have any pull with the doc team could you get them to correct that flaw in the syntax diagram?

Reply
1. Connor McDonald
  
  February 5, 2021 at 7:40 am
  
  will pass along. thanks
  
  Reply