jump to navigation

12.2 Index Advanced Compression “High” – Part I (High Hopes) December 6, 2016

Posted by Richard Foote in 12c Rel 2, 12c Release 2 New Features, Advanced Index Compression, Oracle Indexes.

Oracle first introduced Advanced Compression for Indexes in 12.1 as I’ve discussed here a number of times.

With Oracle Database 12c Release 2, you can now use Index Advanced Compression “High” to further (and potentially dramatically) improve the index compression ratio.  Instead of simply de-duplicating the index entries within an index leaf block, High Index Compression uses more complex compression algorithms and stores the index entries in a Compression Unit (similar to that as used with Hybrid Columnar Compression). The net result is generally a much better level of compression, but at the potential cost of more CPU resources to both access and maintain the index structures.

To give you an idea on the possible compression improvements, let’s re-run the demo I used previously when I first discussed Advanced Index Compression.

So I first create a table, where the CODE column that has many distinct values, but a portion (25%) of data that is replicated:

SQL> create table bowie (id number, code number, name varchar2(30));

Table created.

SQL> insert into bowie select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <= 1000000;

1000000 rows created.

SQL> update bowie set code = 42 where id between 250000 and 499999;

250000 rows updated.

SQL> commit;

Commit complete.

I then create an index on the CODE column and check out its initial size:

SQL> create index bowie_code_i on bowie(code);

Index created.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

------------ ----------- -------------


If I just use normal compression on this index:

SQL> alter index bowie_code_i rebuild compress;

Index altered.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

------------ ----------- -------------


We notice the index actually increases in size (2684 up from 2158), as most (75%) of the CODE values are unique and so the overheads associated with the resultant prefix table in the leaf blocks used with normal index compression overrides the savings of compression on the 25% of the index where compression is beneficial.

If we use “Low” advanced index compression as introduced in 12.1:

SQL> alter index bowie_code_i rebuild compress advanced low;

Index altered.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

------------ ----------- -------------


We notice the index has now indeed decreased in size (2057 down from 2158), as Oracle has automatically compressed just the 25% of the index where compression was beneficial and not touched the 75% of the index where compression wasn’t possible when de-duplicating values.

If we now however use the new 12.2 Advanced Index Compression “High” option:

SQL> alter index bowie_code_i rebuild compress advanced high;

Index altered.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

------------ ----------- -------------

Wow, an index with now no leaf blocks, that’s unbelievably small. Actually, I don’t believe it as this is due to bug 22094934. We need to gather index statistics to see the new index size:


SQL> exec dbms_stats.gather_index_stats(ownname=>null, indname=>'BOWIE_CODE_I');

PL/SQL procedure successfully completed.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

------------ ----------- -------------


We notice that the index hasn’t just gone now a tad in size, but is now substantially smaller than before (down to just 815 leaf blocks, rather than the smaller 2057 from 2158 reduction we previously achieved with low index advanced compression.

So Index Advanced Compression, with the now default “HIGH” option can substantially reduce index sizes. Note this new capability of course requires the Advanced Compression Option.

More to come.


1. Adrian Angelov - December 6, 2016

Things are getting more complicated over time and my understanding is that this is the number one reason for the more bugs that one would expect(and this is actually my impression with 12c(12.1), they are all over the place, just my opinion, of course ).

It’s always good for the customer to have the software available as early as possible so that such issues are identified in advance and time permits to wait for fixes. Cannot understand the reasoning behind not making 12.2 generally available(is it too buggy or it’s just marketing) 🙂 it’s frustrating now, and will be even more once it’s generally available and we start to hit the bugs, funny times to come.


Richard Foote - December 7, 2016

Hi Adrian

In some ways things are more complicated, but in many ways they’re less so as well (think maxextents, LMTs, rollback segments, etc.), so I think the database is overall it’s usual fun self to manage. And issues with newer features is always more likely and has always been the case 🙂

Regarding availability, it is available to everyone right now (and not just those on certain OS), it’s just in the cloud where you have to setup a trial account if you don’t have one already. I see it as it being ready earlier than it would ordinarily might have been, but in a more controlled environment. If Oracle had not released it in the cloud, but releases it on say linux in say June next year, people would get it on premises at the same time anyways, but without the complaints 🙂 So I just view it as an opportunity to get access to it to play with earlier than previously.


2. sydoracle1 - December 7, 2016

We have an issue with index compression in 12.1 and I’ve looked through the 12.2 documentation and can’t see any change.

Pretty much all our big tables are interval partitioned with local indexes. We can change the default compression for new table partitions with the “alter table #### modify default attributes row store compress advanced logging;” command, but there’s no comparable option for alter index. all_part_tables has def_compression and def_compress_for columns, but that’s missing from all_part_indexes.

Do you know where Oracle gets the compression info when it automatically creates a new index partition, and is there anyway to change it (eg through a redefinition). If not, what would be the lowest impact mechanism to change existing indexes to use advanced high compression.


Richard Foote - December 7, 2016

Hi sydoracle1

You can rebuild your local indexes to use compression in the following manner:

alter index bowie_partitioned_idx rebuild partition last_partition_name online compress advanced high;

There’s a parameter called db_index_compression_inheritance that can be set to table or tablespace to determine from where the index gets its compression attributes. Note that a tablespace can now have a default index compression attribute, set as follows:

alter tablespace bowie default index compress advanced high;

user_ind_partitions shows you how each index partition is compressed.

I’ll blog about all this at some stage.


sydoracle1 - December 8, 2016

That’s useful to know, but doesn’t cover this situation. The parameter applies when creating the index, and the rebuild partition can only be done to partitions which already exist.

Where we’ve already got an index (50+ partitions individually rebuilt with advanced compression so they are just 100Gb) up to December. But it wasn’t created with ADVANCED compression initially. What can we set so that the new partition that Oracle adds for January automatically starts with advanced compression switched on.


3. Richard Foote - December 12, 2016

Hi sydoracle1

Response from the developers is that some of the new partitioning types such as partition by reference, automatic partition creation depend on unparse and inheritance is not working for compress and in memory with them because they have not yet added these attributes to unparse.

Future enhancement request perhaps ?


4. Index Compression | Oracle Scratchpad - December 14, 2016

[…] Foote has published a couple of articles in the last few days on the new (licensed under the advanced compression option) […]

Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: