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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

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

Last updated: February 10, 2021 - 4:49 am UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

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 Connor 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.




Rating

  (10 ratings)

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

Comments

Rebuild index

Rajeshwaran, April 19, 2017 - 4:39 am UTC

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

Rajeshwaran, April 27, 2017 - 7:21 am UTC

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

Advanced index compress from Parent Table

Rajeshwaran Jeyabal, October 13, 2018 - 7:06 am UTC

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

Rajeshwaran, Jeyabal, February 25, 2019 - 12:06 pm UTC

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

Rajeshwaran, Jeyabal, July 27, 2020 - 4:08 pm UTC

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


How to verify the compression is in effect on Index Segments

Rajeshwaran, jeyabal, January 25, 2021 - 11:36 am UTC

Team:

Though we define the compresion at partition level, the real space saving of compression will be in effect post the rebuild of index/partition/subpartition.
so how best one could verify the compression is in effect, based on the result from data dictionary apart from block dump trace.
Kindly advice.

demo@PDB1> alter index t_idx modify partition P_2019 compress advanced low;

Index altered.

demo@PDB1>
demo@PDB1> select partition_name,subpartition_name,compression,leaf_blocks
  2  from user_ind_subpartitions
  3  where index_name ='T_IDX';

PARTITION_ SUBPARTITI COMPRESSION   LEAF_BLOCKS
---------- ---------- ------------- -----------
P_2018     P_2018_SP1 DISABLED                0
P_2018     P_2018_SP2 DISABLED                0
P_2019     P_2019_SP1 ADVANCED LOW         1984
P_2019     P_2019_SP2 ADVANCED LOW         1940
P_2020     P_2020_SP1 DISABLED                3
P_2020     P_2020_SP2 DISABLED                4
P_2021     P_2021_SP1 DISABLED                0
P_2021     P_2021_SP2 DISABLED                0

8 rows selected.

demo@PDB1> alter index t_idx rebuild subpartition P_2019_SP1;

Index altered.

demo@PDB1> select partition_name,subpartition_name,compression,leaf_blocks
  2  from user_ind_subpartitions
  3  where index_name ='T_IDX';

PARTITION_ SUBPARTITI COMPRESSION   LEAF_BLOCKS
---------- ---------- ------------- -----------
P_2018     P_2018_SP1 DISABLED                0
P_2018     P_2018_SP2 DISABLED                0
P_2019     P_2019_SP1 ADVANCED LOW          818
P_2019     P_2019_SP2 ADVANCED LOW         1940
P_2020     P_2020_SP1 DISABLED                3
P_2020     P_2020_SP2 DISABLED                4
P_2021     P_2021_SP1 DISABLED                0
P_2021     P_2021_SP2 DISABLED                0

8 rows selected.

demo@PDB1>


incase if you need a full test case to play with here it is.

drop table t purge;

create table t 
partition by range( created )
subpartition by hash( object_id )
subpartition template( subpartition sp1,subpartition sp2)
( partition p_2018 values less than 
                ( to_date('01-jan-2019','dd-mon-yyyy')) ,
  partition p_2019 values less than 
                ( to_date('01-jan-2020','dd-mon-yyyy')) ,
  partition p_2020 values less than 
                ( to_date('01-jan-2021','dd-mon-yyyy')) ,               
  partition p_2021 values less than 
                ( to_date('01-jan-2022','dd-mon-yyyy')) )
as
select a.* 
from all_objects a,
 all_users
where rownum <=1000000; 
                
create index t_idx on t( owner,object_type,object_id ) local;               

col partition_name for a10
col subpartition_name for a10
select partition_name,subpartition_name,compression,leaf_blocks
from user_ind_subpartitions
where index_name ='T_IDX';

alter index t_idx rebuild subpartition P_2019_SP1 compress advanced low;
alter index t_idx rebuild partition P_2019 compress advanced low;

alter index t_idx modify partition P_2019 compress advanced low;

select partition_name,subpartition_name,compression,leaf_blocks
from user_ind_subpartitions
where index_name ='T_IDX';

alter index t_idx rebuild subpartition P_2019_SP1;

select partition_name,subpartition_name,compression,leaf_blocks
from user_ind_subpartitions
where index_name ='T_IDX';

Connor McDonald
February 01, 2021 - 4:53 am UTC

We can use some rough estimates to validate things, eg

SQL> create table t as
  2  select a.*
  3  from all_objects a,
  4   ( select 1 from dual connect by level <= 10 )
  5  order by  owner,object_type,object_id;

Table created.

SQL>
SQL> create index t_idx on t( owner,object_type,object_id );

Index created.

SQL> select avg_col_len
  2  from   user_tab_cols
  3  where  table_name = 'T'
  4  and    column_name in ('OWNER','OBJECT_TYPE','OBJECT_ID' );

AVG_COL_LEN
-----------
          6
          5
         10


So based on that, we would expect a typical index entry to be 6+5+10 bytes plus the rowid (6 bytes in this case). Allowing pctfree=10, then roughly we'd expect

SQL> select 8192*0.9/(6+5+10+6) from dual;

8192*0.9/(6+5+10+6)
-------------------
         273.066667


index entries per leaf block. Obviously we're dealing here in averages, but lets say between 260 and 300. Now we can test that by looking at the index data

SQL> select object_id
  2  from   user_objects
  3  where  object_name = 'T_IDX';

 OBJECT_ID
----------
    132008

SQL> select cnt, count(*)
  2  from (
  3  select /*+ index_ffs(t t_idx) */
  4    sys_op_lbid( 132008 ,'L',t.rowid) as blk,
  5    count(*) cnt
  6  from t
  7  group by sys_op_lbid( 132008 ,'L',t.rowid)
  8  )
  9  group by cnt
 10  order by 2 desc;

       CNT   COUNT(*)
---------- ----------
       231       1455
       232        432
       211        359
       287        207
       239        177
       199        153
       217        140
       299        123
....


The bulk of the entries are in that ballpark.

Now I'll compress the index and we can look again

SQL> alter index t_idx rebuild compress advanced low;

Index altered.

SQL> select cnt, count(*)
  2  from (
  3  select /*+ index_ffs(t t_idx) */
  4    sys_op_lbid( 132008 ,'L',t.rowid) as blk,
  5    count(*) cnt
  6  from t
  7  group by sys_op_lbid( 132008 ,'L',t.rowid)
  8  )
  9  group by cnt
 10  order by 2 desc;

       CNT   COUNT(*)
---------- ----------
       527        336
       525        335
       526        167
       515        142
       530         77
       520         68
       550         56
       551         53
       554         34
       556         33
...


This a reasonable validation that compression has yielded a benefit.

Hence this could be used in reverse, ie, see how many entries per leaf block you are getting, and then compare to the size of a index key + rowid.

sys_op_lbid ?!?!?!?

Rajeshwaran, Jeyabal, February 01, 2021 - 7:48 am UTC

Thanks for the above response, but what is "sys_op_lbid" ?

dont see listed here in docs - https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/index.html

Please let us know where we can get details about this "sys_op_lbid" ?
Connor McDonald
February 02, 2021 - 1:21 am UTC

Its not a documented function

How to verify the compression is in effect on Index Segments

Rajeshwaran, Jeyabal, February 08, 2021 - 7:03 am UTC

Tried this, but on our local database ( 19.9) ended up with this error. Kindly advice.

demo@QES1> select object_id,subobject_name
  2  from user_objects
  3  where object_name ='T'
  4  and subobject_name like 'P_2019%'
  5  and object_type ='TABLE SUBPARTITION';

 OBJECT_ID SUBOBJECT_NAME
---------- ---------------
     80088 P_2019_SP1
     80089 P_2019_SP2

demo@QES1> select cnt, count(*)
  2  from (
  3  select /*+ index_ffs(t,t_idx) */ sys_op_lbid(80088,'L',t.rowid) blk,
  4      count(*) cnt
  5  from t subpartition(P_2019_SP1) t
  6  group by sys_op_lbid(80088,'L',t.rowid)
  7       )
  8  group by cnt
  9  order by 2 desc
 10  fetch first 10 rows only ;
group by sys_op_lbid(80088,'L',t.rowid)
                     *
ERROR at line 6:
ORA-01760: illegal argument for function



Connor McDonald
February 10, 2021 - 4:49 am UTC

Check my demo - it the index object not the table

How to verify the compression is in effect on Index Segments

Rajeshwaran, Jeyabal, February 10, 2021 - 10:18 am UTC

Thanks., but still getting the error, not possible to have work at subpartition levels?

demo@QES1> select object_id,subobject_name
  2  from user_objects
  3  where object_name ='T_IDX'
  4  and subobject_name like 'P_2019%'
  5  and object_type ='INDEX SUBPARTITION';

 OBJECT_ID SUBOBJECT_NAME
---------- ---------------
     80220 P_2019_SP1
     80221 P_2019_SP2

demo@QES1> select cnt, count(*)
  2  from (
  3  select /*+ index_ffs(t,t_idx) */ sys_op_lbid(80220,'L',t.rowid) blk,
  4      count(*) cnt
  5  from t subpartition(P_2019_SP1) t
  6  group by sys_op_lbid(80220,'L',t.rowid)
  7       )
  8  group by cnt
  9  order by 2 desc
 10  fetch first 10 rows only;
group by sys_op_lbid(80220,'L',t.rowid)
                     *
ERROR at line 6:
ORA-01760: illegal argument for function


How to verify the compression is in effect on Index Segments

Rajeshwaran, Jeyabal, February 10, 2021 - 11:33 am UTC

was, checking the docs, and it seems that dictionary "index_stats" can report the compressed and uncompressed blocks in indexes.

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/INDEX_STATS.html#GUID-9C031B8C-8A30-4B84-BF96-A605DE731145

demo@QES1> alter index t_idx modify partition P_2019 compress advanced low;

Index altered.

demo@QES1> select partition_name,subpartition_name,compression,leaf_blocks,status
  2  from user_ind_subpartitions
  3  where index_name ='T_IDX'
  4  and partition_name ='P_2019';

PARTITION_ SUBPARTITI COMPRESSION   LEAF_BLOCKS STATUS
---------- ---------- ------------- ----------- --------
P_2019     P_2019_SP1 ADVANCED LOW         1978 UNUSABLE
P_2019     P_2019_SP2 ADVANCED LOW         1937 UNUSABLE

demo@QES1>  analyze index t_idx subpartition (P_2019_SP1) validate structure;
 analyze index t_idx subpartition (P_2019_SP1) validate structure
*
ERROR at line 1:
ORA-14517: subpartition of index 'DEMO.T_IDX' is in unusable state

demo@QES1> alter index t_idx rebuild subpartition P_2019_SP1;

Index altered.

demo@QES1> select partition_name,subpartition_name,compression,leaf_blocks,status
  2  from user_ind_subpartitions
  3  where index_name ='T_IDX'
  4  and partition_name ='P_2019';

PARTITION_ SUBPARTITI COMPRESSION   LEAF_BLOCKS STATUS
---------- ---------- ------------- ----------- --------
P_2019     P_2019_SP1 ADVANCED LOW          816 USABLE
P_2019     P_2019_SP2 ADVANCED LOW         1937 UNUSABLE

demo@QES1> analyze index t_idx subpartition (P_2019_SP1) validate structure ;

Index analyzed.

demo@QES1> select name,partition_name,lf_cmp_blks,lf_uncmp_blks
  2  from index_stats ;

NAME       PARTITION_NAME  LF_CMP_BLKS LF_UNCMP_BLKS
---------- --------------- ----------- -------------
T_IDX      P_2019_SP1              816             0

demo@QES1>