jump to navigation

12.2 Index Advanced Compression “High” Part II (One Of My Turns) December 12, 2016

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

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

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.

Comments»

1. Jonathan Lewis - December 12, 2016

Richard,

It would be interesting to see an example of the compression ratio when the values used for the ID were much larger (in the order of 10M, say) and given a degree of randomness. I can’t help thinking that the >2 factor here is largely because sequential key values have sequential rowids – which means lots of repeated 4 or 5 byte runs.

Like

Richard Foote - December 13, 2016

Hi Jonathan

Good point.

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

Table created.

SQL> insert into bowie select rownum, ceil(dbms_random.value(10000000,1000000000000)), ‘ZIGGY STARDUST’ from dual connect by level commit;

Commit complete.

SQL> create unique index bowie_code_i on bowie(code);

Index created.

SQL> select index_name, leaf_blocks, compression from user_indexes where index_name=’BOWIE_CODE_I’;

INDEX_NAME LEAF_BLOCKS COMPRESSION
————— ———– ————-
BOWIE_CODE_I 2506 DISABLED

SQL> alter index bowie_code_i rebuild compress advanced high;

Index altered.

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 index_name=’BOWIE_CODE_I’;

INDEX_NAME LEAF_BLOCKS COMPRESSION
————— ———– ————-
BOWIE_CODE_I 1755 ADVANCED HIGH

So yes, with larger, random numbers, the savings is now reduced to 30%, still impressive, but not as good as previous example. So absolutely, the nature of the data is critical for the level of actual compression.

Liked by 1 person

2. Jonathan Lewis - December 13, 2016

Richard,
After typing the comment I thought I’d just run up a couple of tests myself. Just reading the final draft of the resulting blog post now, sitting in a train on my way to Leeds.

Got similar results to your 30%, of course, though I did an order by dbms_random.value while still using integer ids

Like

3. 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) compression […]

Like


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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: