Compressing an index can be an excellent way to permanently
reduce the size of an index in a very cost effective manner. Index entries with
many duplicate values (or duplicate leading columns within the index) can be
“compressed” by Oracle to reduce both storage overheads and potentially access
overheads for large index scans. Oracle basically de-duplicates repeated
indexed column values within each individual leaf block by storing each unique
occurrence in a prefix section within the block.
But it’s important to compress the right indexes in the
right manner. If indexes do not have enough repeated data, it’s quite possible
to make certain indexes larger rather than smaller when using compression (as
the overheads of having the prefix section in the index block outweighs the
benefits of limited reduction of repeated values). So one needs to be very
selective on which indexes to compress and take care to compress the correct
number of columns within the index. Oracle will only protect you from yourself
if you attempt to compress all columns in a unique index, as in this scenario
there can be no duplicate values to compress.
So, wouldn’t it be nice if Oracle made it all a lot easier
for us and automatically decided which indexes to compress, which columns
within the index to compress and which indexes to simply not bother compressing
at all. Additionally, rather than an all or nothing approach in which all index
leaf blocks are compressed in the same manner, wouldn’t it be nice if Oracle
decided for each and every individual leaf block within the index how to best
compress it. For those index leaf block that have no duplicate entries, do
nothing, for those with some repeated columns just compress them and for those
leaf blocks with lots of repeated columns and values to compress all of them as
efficiently as possible.
All these are being addressed by the recent release of
Oracle database 12.1.0.2, one of the really cool new feature got introduced was
Advanced Index Compression
rajesh@PDB1> create table t
2 nologging
3 as
4 select
owner,object_name,subobject_name,object_id,
5
data_object_id,object_type,created,last_ddl_time,
6
timestamp,status,temporary,generated,secondary,
7
namespace,edition_name,sharing,editionable,
8 oracle_maintained, case when id between
9 0 and 250000 then 0
10 else id end id
11 from big_table;
Table created.
So, I have fabricated the data such that values in the ID column are
effectively unique within 75% of the table but the other 25% consist of
repeated values.
If we create a normal B-Tree index without compression:
rajesh@PDB1> create index t_idx on t(id) nologging ;
Index created.
rajesh@PDB1> column index_name format a10
rajesh@PDB1> select index_name,compression,leaf_blocks
2 from user_indexes
3 where index_name ='T_IDX';
INDEX_NAME COMPRESSION
LEAF_BLOCKS
---------- ------------- -----------
T_IDX DISABLED 2124
1 row selected.
rajesh@PDB1>
We noticed that the index consist of 2124 leaf blocks.
If we try to use normal compression on the index:
rajesh@PDB1> alter index t_idx rebuild compress nologging;
Index altered.
rajesh@PDB1> select index_name,compression,leaf_blocks
2 from user_indexes
3 where index_name ='T_IDX';
INDEX_NAME COMPRESSION
LEAF_BLOCKS
---------- ------------- -----------
T_IDX ENABLED 2685
1 row selected.
rajesh@PDB1>
We notice that the compressed index rather than decrease in size has
actually increased in size, up to 2685
leaf blocks. So the index has grown by some 25% due to the fact the index
predominately contains unique values which don’t compress at all and the resultant
prefix section in the leaf blocks becomes nothing more than additional
overhead. The 25% section of the index containing all the repeated values has
indeed compressed effectively but these savings are more than offset by the
increase in size associated with the other 75% of the index where the index
entries had no duplication.
However, if we use the new advanced index compression capability via
the COMPRESS ADVANCED LOW clause:
rajesh@PDB1> alter index t_idx rebuild compress advanced low
nologging;
Index altered.
rajesh@PDB1>
rajesh@PDB1> 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 2054
1 row selected.
rajesh@PDB1>
We notice the index has now indeed decreased in size from the original
2124 leaf blocks down to 2054. Oracle has effectively ignored all those leaf
blocks where compression wasn’t viable and compressed just the 25% of the index
where compression was effective. Obviously, the larger the key values
(remembering the rowids associated with the index entries can’t be compressed)
and the larger the percentage of repeated data, the larger the overall
compression returns.
Advanced Index Compression enables Oracle to automatically just
compress those index leaf blocks where compression is beneficial.
If we look at a couple of partial block dumps from this index, first a
dump from a leaf block that did have duplicate index entries
rajesh@PDB1> column header_file new_val hf
rajesh@PDB1> column header_block new_val hb
rajesh@PDB1>
rajesh@PDB1> select file_id as header_file , block_id +5 as
header_block
2 from dba_extents
3 where segment_name ='T_IDX'
4 and owner = user
5 and extent_id = 0 ;
HEADER_FILE HEADER_BLOCK
----------- ------------
12 17197
1 row selected.
rajesh@PDB1>
rajesh@PDB1> alter system flush buffer_cache;
System altered.
rajesh@PDB1> @tkfilename.sql
D:\APP\179818\diag\rdbms\cdb1\cdb1\trace\cdb1_ora_5384.trc
rajesh@PDB1> alter system dump datafile &hf block &hb
;
old 1: alter system dump
datafile &hf block &hb
new 1: alter system dump
datafile 12 block 17197
System altered.
rajesh@PDB1> exit
Disconnected from Oracle Database 12c Enterprise Edition Release
12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real
Application Testing options
Leaf block dump
===============
header address 408388196=0x18578264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 652
kdxcofbo 1348=0x544
kdxcofeo 2164=0x874
kdxcoavs 816
kdxlespl 0
kdxlende 0
kdxlenxt 50348846=0x300432e
kdxleprv 50348844=0x300432c
kdxledsz 0
kdxlebksz 8036
kdxlepnro 1
kdxlepnco 1 (Adaptive)
prefix row#0[8032] flag: -P-----, lock: 0, len=4
col 0; len 1; (1):
80
prc 652
row#0[8023] flag: -------, lock: 0, len=9
col 0; len 6; (6): 03 00
41 96 00 04
psno 0
row#1[8014] flag: -------, lock: 0, len=9
col 0; len 6; (6): 03 00
41 96 00 05
psno 0
row#2[8005] flag: -------, lock: 0, len=9
col 0; len 6; (6): 03 00
41 96 00 06
psno 0
row#3[7996] flag: -------, lock: 0, len=9
col 0; len 6; (6): 03 00
41 96 00 07
psno 0
row#4[7987] flag: -------, lock: 0, len=9
col 0; len 6; (6): 03 00
41 96 00 08
psno 0
. . . . . . . .
. . . . . . . .
row#650[2173] flag: -------, lock: 0, len=9
col 0; len 6; (6): 03 00
41 a2 00 10
psno 0
row#651[2164] flag: -------, lock: 0, len=9
col 0; len 6; (6): 03 00
41 a2 00 11
psno 0
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 12 minblk 17197 maxblk 17197
The red section is a portion of the
index header that determines the number of rows in the prefix table of the
index (kdxlepnro 1). The prefix table basically
lists all the distinct column values in the leaf blocks that are to be
compressed. The value 1 denotes there is actually only just the 1 distinct
column value in this specific leaf block (i.e. all index entries have the same
indexed value). This section also denotes how many of the indexed columns are
to be compressed (kdxlepnco 1). As this index
only has the one column, it also has a value of 1. Note this value can potentially
be anything between 0 (no columns compressed) up to the number of columns in
the index. The (Adaptive)
reference tells us that Index Advanced Compression has been used and that the
values here can change from leaf block to leaf block depending on the data
characteristics of the index entries within each leaf block (a dump of a basic
compressed index will not have the “Adaptive” reference).
The blue section is the compression
prefix table and details all the unique combinations of index entries to be
compressed within the leaf block. As all indexed values are the same in this
index (value 0, internally represented as 80 hex), the prefix table only has
the one row. prc 652 denotes that all 652 index entries in this leaf block have
this specific indexed value
Next follows all the actual index entries, which now only consist of
the rowid (the 6 byte col 0 column) as they all reference psno 0, which is the unique row id of the only row
within the prefix table (row#0).
So rather than storing the indexed value 652 times, we can just store
the index value (0) just the once within the prefix table and simply reference
it from within the actual index entries. This is why index compression can save
us storage, storing something once within a leaf block rather than multiple
times.
If we now look at a partial block dump of another index leaf block
within the index, that consists of many
differing (basically unique) index entries:
C:\Users\179818>sqlplus rajesh/oracle@pdb1
SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 21 11:25:55
2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sat Mar 21 2015 11:22:46 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 -
64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real
Application Testing options
rajesh@PDB1>
rajesh@PDB1>
rajesh@PDB1> set timing off
rajesh@PDB1>
rajesh@PDB1> column header_file new_val hf
rajesh@PDB1> column header_block new_val hb
rajesh@PDB1> select file_id as header_file , block_id +3 as
header_block
2 from dba_extents
3 where segment_name ='T_IDX'
4 and owner = user
5 and extent_id = 29 ;
HEADER_FILE HEADER_BLOCK
----------- ------------
12 36867
1 row selected.
rajesh@PDB1>
rajesh@PDB1> @tkfilename.sql
D:\APP\179818\diag\rdbms\cdb1\cdb1\trace\cdb1_ora_11688.trc
rajesh@PDB1> alter system dump datafile &hf block &hb
;
old 1: alter system dump
datafile &hf block &hb
new 1: alter system dump
datafile 12 block 36867
System altered.
rajesh@PDB1> exit
Disconnected from Oracle Database 12c Enterprise Edition Release
12.1.0.2.0 - 64bit Prod
With the Partitioning, OLAP, Advanced Analytics and Real
Application Testing options
C:\Users\179818>
Leaf block dump
===============
header address 462979684=0x1b988264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 449
kdxcofbo 938=0x3aa
kdxcofeo 1754=0x6da
kdxcoavs 816
kdxlespl 0
kdxlende 0
kdxlenxt 50368516=0x3009004
kdxleprv 50368514=0x3009002
kdxledsz 0
kdxlebksz 8036
kdxlepnro 0
kdxlepnco 0 (Adaptive)
row#0[8022] flag: -------, lock: 0, len=14
col 0; len 4; (4):
c3 57 36 32
col 1; len 6; (6): 03 00
7c 0a 00 40
row#1[8008] flag: -------, lock: 0, len=14
col 0; len 4; (4):
c3 57 36 33
col 1; len 6; (6): 03 00
7c 0a 00 41
row#2[7994] flag: -------, lock: 0, len=14
col 0; len 4; (4):
c3 57 36 34
col 1; len 6; (6): 03 00
7c 0a 00 42
row#3[7980] flag: -------, lock: 0, len=14
col 0; len 4; (4):
c3 57 36 35
col 1; len 6; (6): 03 00
7c 0a 00 43
row#4[7966] flag: -------, lock: 0, len=14
col 0; len 4; (4):
c3 57 36 36
col 1; len 6; (6): 03 00
7c 0a 00 44
. . . . . . . .
. . . . . . . .
row#447[1768] flag: -------, lock: 0, len=14
col 0; len 4; (4):
c3 57 3a 61
col 1; len 6; (6): 03 00
7d 03 00 20
row#448[1754] flag: -------, lock: 0, len=14
col 0; len 4; (4):
c3 57 3a 62
col 1; len 6; (6): 03 00
7d 03 00 21
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 12 minblk 36867 maxblk 36867
We notice that in the red section, both kdxlepnro
0 and kdxlepnco 0 (Adaptive) have a value of 0, meaning we have no rows
and no columns within the prefix table. As such, we have no prefix table at all
here and that this leaf block has simply not been compressed.
If we look at the actual index entries, they all have an additional
column now in blue, that being the actual
indexed value as all the index values in this leaf block are different from
each other. Without some form of index entry duplication, there would be no
benefit from compression and Index Advanced Compression has automatically
determined this and not bothered to compress this leaf block. An attempt to
compress this block would have actually increased the necessary overall storage
for these index entries, due to the additional overheads associated with the
prefix table (note it has an additional 2 byes of overhead per row within the
prefix table).
