12.2 Index Advanced Compression “High” Part II (One Of My Turns) December 12, 2016Posted by Richard Foote in 12c Rel 2, 12c Release 2 New Features, Advanced Index Compression, Oracle Indexes.
In Part I, I introduced the new Index Advanced Compression default value of “HIGH”, which has the potential to significantly compress indexes much more than previously possible. This is due to new index compression algorithms that do more than simply de-duplicate indexed values within a leaf block.
Previously, any attempt to completely compress a Unique Index was doomed to failure as a Unique Index by definition only has unique values and so has nothing to de-duplicate. As such, you were previously restricted (quite rightly) to only being able to compress n-1 columns within a Unique Index. An attempt compress all columns in a Unique Index would only result in a larger index structure due to the associated overheads of the prefix-table within the leaf blocks.
But what happens if we now use Index Advanced Compression set to “HIGH” on a Unique Index ?
Let’s first create a simple table with a unique ID column:
SQL> create table bowie (id number, code number, name varchar2(42)); Table created. SQL> insert into bowie select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <= 1000000; 1000000 rows created. SQL> commit; Commit complete.
Let’s start by creating an uncompressed unique index on the ID column:
SQL> create unique index bowie_id_i on bowie(id); Index created. SQL> select index_name, leaf_blocks, compression from user_indexes where index_name='BOWIE_ID_I'; INDEX_NAME LEAF_BLOCKS COMPRESSION ------------ ----------- ------------- BOWIE_ID_I 2088 DISABLED
So the uncompressed unique index has 2088 leaf blocks.
If we try and use normal compression on the index:
SQL> alter index bowie_id_i rebuild compress; alter index bowie_id_i rebuild compress * ERROR at line 1: ORA-25193: cannot use COMPRESS option for a single column key
We get an error saying we’re not allowed to compress a single column unique index. Doing so makes no sense, as there’s no benefit in de-duplicating such an index.
If we attempt to use advanced index compression with a value of “LOW”:
SQL> alter index bowie_id_i rebuild compress advanced low; alter index bowie_id_i rebuild compress advanced low * ERROR at line 1: ORA-25193: cannot use COMPRESS option for a single column key
We get the same error. Although advanced index compression of LOW is clever enough to automatically compress only those leaf blocks where there is a benefit in compression, there can be no such index leaf block that benefits from compression via the de-duplication method. Therefore, the error is really there to just let you know that you’re wasting your time in attempting to do this on a unique index.
If however we use the new HIGH option with index advanced compression:
SQL> alter index bowie_code_i rebuild compress advanced high; Index altered. SQL> exec dbms_stats.gather_index_stats(ownname=>null, indname=>'BOWIE_ID_I'); PL/SQL procedure successfully completed. SQL> select index_name, leaf_blocks, compression from user_indexes where index_name='BOWIE_ID_I'; INDEX_NAME LEAF_BLOCKS COMPRESSION ------------ ----------- ------------- BOWIE_ID_I 965 ADVANCED HIGH
Not only does it not give us an error, but it has actually managed to successfully compress such a unique index containing nothing but a bunch of unique numbers to just 965 leaf blocks, down from 2088. The index is now less than half its previous size.
So any Oracle B-tree index, even if it’s a single column unique index, is a possible candidate to be compressed with “High” advanced index compression.
More to come.