Skip to Main Content
  • Questions
  • Advanced index compression HIGH in 12.2

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: April 18, 2017 - 2:12 pm UTC

Answered by: Connor McDonald - Last updated: July 28, 2020 - 5:49 am UTC

Category: Database Development - Version: 12.2.0.1

Viewed 1000+ times

You Asked

Team,

Reading through this documentation link on Advanced index compression HIGH.

http://docs.oracle.com/database/122/REFRN/DB_INDEX_COMPRESSION_INHERITANCE.htm#REFRN10336

<quote>
TABLESPACE

Index inheritance is based on tablespace attributes.
</quote>

Setting this parameter at the session level, doesn't work for me, did i missing something here? kindly advice.

rajesh@ORA12C> create tablespace ts_index
  2  datafile 'D:\app\Vnameit\virtual\oradata\ORA12c\ts_index.dbf'
  3  size 500M
  4  default index compress advanced high;

Tablespace created.

rajesh@ORA12C> alter user demo quota unlimited on ts_index;

User altered.

rajesh@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> drop table t purge;

Table dropped.

demo@ORA12C> column index_name format a10
demo@ORA12C> column tablespace_name format a15
demo@ORA12C> create table t as
  2  select *
  3  from big_table;

Table created.

demo@ORA12C> create index t_idx on t(id) nologging;

Index created.

demo@ORA12C> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      DISABLED             2226

demo@ORA12C> alter index t_idx rebuild nologging tablespace ts_index;

Index altered.

demo@ORA12C> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      DISABLED             2226

demo@ORA12C> select tablespace_name,index_compress_for
  2  from user_tablespaces;

TABLESPACE_NAME INDEX_COMPRES
--------------- -------------
TS_DATA
TS_INDEX        ADVANCED HIGH

demo@ORA12C> show parameter db_index_compression_inher

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_index_compression_inheritance     string      NONE
demo@ORA12C> alter session set db_index_compression_inheritance=tablespace;

Session altered.

demo@ORA12C> alter index t_idx rebuild nologging tablespace ts_index;

Index altered.

demo@ORA12C> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      DISABLED             2226

demo@ORA12C> select tablespace_name,index_compress_for
  2  from user_tablespaces;

TABLESPACE_NAME INDEX_COMPRES
--------------- -------------
TS_DATA
TS_INDEX        ADVANCED HIGH

demo@ORA12C>


with LiveSQL Test Case:

and we said...

You set your quota, but did you make the ts your default ?

SQL> alter session set db_index_compression_inheritance=tablespace;

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

Tablespace created.

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,leaf_blocks
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      ADVANCED HIGH           0

1 row selected.

SQL> alter index t_idx rebuild nologging tablespace demo;

Index altered.

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

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      ADVANCED HIGH           0

1 row selected.

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

1 row selected.




and you rated our response

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Rebuild index

April 19, 2017 - 4:39 am UTC

Reviewer: Rajeshwaran

while creating the index on the INDEX COMPRESSED tablespace things looks good, but while rebuild they are not working as expected.

demo@ORA12C> select tablespace_name,index_compress_for
  2  from user_tablespaces;

TABLESPACE_NAME INDEX_COMPRES
--------------- -------------
TS_DATA
TS_INDEX        ADVANCED HIGH

demo@ORA12C> drop table t purge;

Table dropped.

demo@ORA12C>
demo@ORA12C> create table t as
  2  select *
  3  from big_table;

Table created.

demo@ORA12C> show parameter db_index_compression_inher

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_index_compression_inheritance     string      NONE
demo@ORA12C> alter session set db_index_compression_inheritance=tablespace;

Session altered.

demo@ORA12C> show parameter db_index_compression_inher

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_index_compression_inheritance     string      TABLESPACE
demo@ORA12C> create index t_idx on t(id) tablespace TS_INDEX;

Index created.

demo@ORA12C> exec dbms_stats.gather_index_stats(user,'T_IDX');

PL/SQL procedure successfully completed.

demo@ORA12C> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      ADVANCED HIGH         975


Now, index compression high got inherited from the tablespace.

while trying to create the index in a different tablespace, then rebuilding into this index compression high tablespace then things doesn't work as expected.

demo@ORA12C> drop index t_idx;

Index dropped.

demo@ORA12C> create index t_idx on t(id) tablespace TS_DATA;

Index created.

demo@ORA12C> exec dbms_stats.gather_index_stats(user,'T_IDX');

PL/SQL procedure successfully completed.

demo@ORA12C> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      DISABLED             2226

demo@ORA12C> alter index t_idx rebuild nologging tablespace ts_index;

Index altered.

demo@ORA12C> exec dbms_stats.gather_index_stats(user,'T_IDX');

PL/SQL procedure successfully completed.

demo@ORA12C> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      DISABLED             2226

demo@ORA12C>


did you see anything missing in the above test ?
Connor McDonald

Followup  

April 28, 2017 - 1:06 am UTC

I dont think attributes at tspace level have ever worked that way - they are assigned at object creation time *only* and they remain like that for the life of the object (unless changed), eg

SQL> create tablespace COMPRESS_TS datafile 'C:\ORACLE\ORADATA\DB122\COMPRESS.DBF' size 200m
  2  default table compress basic;

Tablespace created.

SQL> create table uncomp tablespace users as select * from dba_objects;

Table created.

SQL> alter table uncomp move tablespace COMPRESS_TS;

Table altered.


SQL> select compression from user_tables
  2  where table_name = 'UNCOMP';

COMPRESS
--------
DISABLED

1 row selected.

SQL>



additional info

April 27, 2017 - 7:21 am UTC

Reviewer: Rajeshwaran

are you looking for any additional information here to respond this?

Advanced index compress from Parent Table

October 13, 2018 - 7:06 am UTC

Reviewer: Rajeshwaran Jeyabal

Team,

was reading this product documentation. concepts guide.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/indexes-and-index-organized-tables.html#GUID-0D732C46-AB22-4C46-8631-7B0F65FA41F9

it has this

<quote>
Indexes can inherit advanced compression from a parent table or containing tablespace.
</quote>

could you show us how to inherit advance compression from parent table? any testcase or demo please?
Connor McDonald

Followup  

October 14, 2018 - 4:56 am UTC

I believe they referring to parent in respect to partitioned tables (not referential integrity).

eg

CREATE INDEX my_test_idx ON test(a, b) COMPRESS ADVANCED HIGH local
(PARTITION p1 COMPRESS ADVANCED LOW,
PARTITION p2 COMPRESS,
PARTITION p3,
PARTITION p4 NOCOMPRESS);


ie, you can inherit or choose at the partition level.

Advanced index compression on FBI

February 25, 2019 - 12:06 pm UTC

Reviewer: Rajeshwaran, Jeyabal

Team,

Was reading this blog content today and it has mentioned that Function based indexes cannot be compressed using advanced index compression technique.

https://blogs.oracle.com/dbstorage/advanced-index-compression-high-new-with-oracle-database-12c-release-2-on-oracle-cloud

<quote>
ยท You cannot compress your
Functional Indexes with Advanced Index Compression
</quote>

However running a testcase in 12.2 database, we see the other way around. kindly advice.

Table T is a copy of all_objects.

demo@ORA12C> create index t_idx on t( lower(owner) );

Index created.

demo@ORA12C> col index_name format a10
demo@ORA12C> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where index_name ='T_IDX';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      DISABLED              164

demo@ORA12C> alter index t_idx rebuild
  2  compress
  3  advanced LOW
  4  nologging ;

Index altered.

demo@ORA12C> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where index_name ='T_IDX';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      ADVANCED LOW          112

demo@ORA12C> alter index t_idx rebuild
  2  compress
  3  advanced HIGH
  4  nologging ;

Index altered.

demo@ORA12C> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where index_name ='T_IDX';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      ADVANCED HIGH           0

demo@ORA12C> exec dbms_stats.gather_index_stats( user, 'T_IDX' );

PL/SQL procedure successfully completed.

demo@ORA12C> select index_name,compression,leaf_blocks
  2  from user_indexes
  3  where index_name ='T_IDX';

INDEX_NAME COMPRESSION   LEAF_BLOCKS
---------- ------------- -----------
T_IDX      ADVANCED HIGH          18

demo@ORA12C>

Connor McDonald

Followup  

February 26, 2019 - 2:52 am UTC

The article is incorrect (in my opinion).

You can LOW on 12.1 for FBIs and LOW/HIGH on 12.2.


Index compression at subpartition level

July 27, 2020 - 4:08 pm UTC

Reviewer: Rajeshwaran, Jeyabal

Team:

Is it not possible to have advanced index compression at subpartition level?

looked into the docs it show " rebuild subpartition " can be followed by "index compression" clause. kindly advice.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ALTER-INDEX.html#GUID-D8F648E7-8C07-4C89-BB71-862512536558__BGEEJJJJ

demo@PDB1> create table t
  2  partition by list( owner )
  3  subpartition by hash(object_id)
  4  subpartition template(
  5     subpartition sp1,
  6     subpartition sp2 )
  7  ( partition p values (default) )
  8  as
  9  select * from all_objects
 10  where rownum <=1000;

Table created.

demo@PDB1> create index t_idx on t( owner,object_type,object_name ) local unusable;

Index created.

demo@PDB1> alter index t_idx rebuild subpartition p_sp1 compress advanced low;
alter index t_idx rebuild subpartition p_sp1 compress advanced low
                                             *
ERROR at line 1:
ORA-14189: this physical attribute may not be specified for an index subpartition


Connor McDonald

Followup  

July 28, 2020 - 5:49 am UTC

Interestingly in older versions of the documentation we had this explicitly listed:

eg in 10.2

"You can specify this clause only at the partition level. You cannot change the compression attribute for an individual subpartition"

but this is no longer present in the current documentation, yet it appears the restriction is still valid. We do have this

"You can subsequently enable and disable the compression setting for individual partitions of such a partitioned index"

which could be argued is implicitly saying "partitions and NOT subpartitions", but I agree, that is a little vague.