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';
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
Learning is not a spectator sport 
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)
Randolf
Yes you’re right – my interpretation was incorrect.
I’ll update the post to rectify.
Thanks for catching it.
Cheers,
Connor
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
See other comments – check your db_index_compression_inheritance parameter
Parameter is not set:
SQL> show parameter db_index_compression_inheritance;
NAME TYPE VALUE
——————————– —— —–
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.
That is the point of this parameter.
https://docs.oracle.com/database/121/REFRN/GUID-5238D586-B068-46F7-9D8F-E4C174E5D5B2.htm#REFRN10336