SAP supports index key compression since Oracle 10.2. This feature is very useful in some cases, so let's take a closer look how it works and in which cases it can improve your performance or save disk space.
How does it work theoretical?
Index compression is implemented at index leaf block level (only available for b-tree indexes). Each unique combination of the compressed column values is stored in an "internal" (=prefix) table and replaced in the index row by a pointer to that prefix table entry.
The benefit of compression depends on the number of unique combinations:
- Few unique value combinations - good compression
- Many unique value combinations - bad compression (and maybe more space is needed)
The logical structure for a non unique index on the columns TABNAME and LOGDATE (Columns of DBTABLOG):
Proof of the logical concept
This example is created on an ERP 2005 system with Oracle 10.2.0.2.0 on AIX.
1) Create the test example
shell> sqlplus SAPSR3/<pass>
SQL> create table ZTEST as select * from dbtablog where tabname in ('HRP1000', 'HRP1002');
SQL> desc ZTEST;
Name Null? Type
----------------------------------------- -------- ----------------------------
LOGDATE NOT NULL VARCHAR2(24)
LOGTIME NOT NULL VARCHAR2(18)
LOGID NOT NULL VARCHAR2(54)
TABNAME NOT NULL VARCHAR2(90)
LOGKEY NOT NULL VARCHAR2(750)
HOSTNAME NOT NULL VARCHAR2(60)
USERNAME NOT NULL VARCHAR2(36)
TCODE NOT NULL VARCHAR2(60)
PROGNAME NOT NULL VARCHAR2(120)
OPTYPE NOT NULL VARCHAR2(3)
VERSNO NOT NULL VARCHAR2(6)
LANGUAGE NOT NULL VARCHAR2(3)
DATALN NOT NULL NUMBER(5)
LOGDATA BLOB
SQL> create index I_ZTEST_NO_COMP on ZTEST(TABNAME,LOGDATE);
SQL> exec dbms_stats.gather_table_stats(USER, 'ZTEST');
SQL> create index I_ZTEST_COMP on ZTEST(TABNAME,LOGDATE) COMPRESS 2;
SQL> exec dbms_stats.gather_table_stats(USER, 'ZTEST');
2) Review the statistics for the two indexes
3) Block dump of an uncompressed leaf block
Leaf block dump
===============
header address 4567527524=0x1103ef064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 0
kdxconro 256 <== 256 entries are stored in this leaf block
kdxcofbo 548=0x224
kdxcofeo 1376=0x560
kdxcoavs 828
kdxlespl 0
kdxlende 0
kdxlenxt 138415458=0x8400d62
kdxleprv 138415456=0x8400d60
kdxledsz 0
kdxlebksz 8032
row#0[8006] flag: ------, lock: 0, len=26
col 0; len 7; (7): 48 52 50 31 30 30 30 <== Column TABNAME (ASCII: HRP1000)
col 1; len 8; (8): 32 30 30 37 31 31 32 35 <== Column LOGDATE (ASCII: 20071125)
col 2; len 6; (6): 08 40 11 cb 00 07 <== ROWID
row#1[7980] flag: ------, lock: 0, len=26
col 0; len 7; (7): 48 52 50 31 30 30 30 <== Column TABNAME (ASCII: HRP1000)
col 1; len 8; (8): 32 30 30 37 31 31 32 35 <== Column LOGDATE (ASCII: 20071125)
col 2; len 6; (6): 08 40 11 cb 00 08 <== ROWID
row#2[7954] flag: ------, lock: 0, len=26
col 0; len 7; (7): 48 52 50 31 30 30 30 <== Column TABNAME (ASCII: HRP1000)
col 1; len 8; (8): 32 30 30 37 31 31 32 35 <== Column LOGDATE (ASCII: 20071125)
col 2; len 6; (6): 08 40 11 cb 00 09 <== ROWID
4) Block dump of a compressed leaf block
Leaf block dump
===============
header address 4567527524=0x1103ef064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y
kdxconco 3
kdxcosdc 0
kdxconro 646 <== 646 entries are stored in this leaf block
kdxcofbo 1344=0x540
kdxcofeo 2161=0x871
kdxcoavs 817
kdxlespl 0
kdxlende 0
kdxlenxt 138415490=0x8400d82
kdxleprv 138415488=0x8400d80
kdxledsz 0
kdxlebksz 8032
kdxlepnro 3
kdxlepnco 2
prefix row#0[8013] flag: -P----, lock: 0, len=19 <== Prefix table entry 0
col 0; len 7; (7): 48 52 50 31 30 30 30 <== Column TABNAME (ASCII: HRP1000)
col 1; len 8; (8): 32 30 30 37 31 32 32 39 <== Column LOGDATE (ASCII: 20071229)
prc 72 <== 72 times of this combination
prefix row#1[7346] flag: -P----, lock: 0, len=19 <== Prefix table entry 1
col 0; len 7; (7): 48 52 50 31 30 30 30 <== Column TABNAME (ASCII: HRP1000)
col 1; len 8; (8): 32 30 30 38 30 31 30 32 <== Column LOGDATE (ASCII: 20080102)
prc 172 <== 172 times of this combination
prefix row#2[5779] flag: -P----, lock: 0, len=19 <== Prefix table entry
col 0; len 7; (7): 48 52 50 31 30 30 30 <== Column TABNAME (ASCII: HRP1000)
col 1; len 8; (8): 32 30 30 38 30 31 30 35 <== Column LOGDATE (ASCII: 20080105)
prc 402 <== 402 times of this combination
row#0[8004] flag: ------, lock: 0, len=9
col 0; len 6; (6): 08 40 2a 72 00 03 <== ROWID
psno 0 <== Pointer to prefix table entry 0
row#149[6644] flag: ------, lock: 0, len=9
col 0; len 6; (6): 08 40 24 0f 00 05 <== ROWID
psno 1 <== Pointer to prefix table entry 1
row#244[5770] flag: ------, lock: 0, len=9
col 0; len 6; (6): 08 40 23 e7 00 02 <== ROWID
psno 2 <== Pointer to prefix table entry 2
Summary
Oracle index key compression can be a very useful feature to save disk space or reduce the logical reads for an index range / index (fast) full scans. Actually i am researching this feature for our productive database with the main focus on performance improvements.
SAP is providing a nice pl/sql package ind_comp to calculate the optimal key compression factor. (see sapnote #1109743).
But be careful: If you compress an index, the execution plan can be changed or the index key compression is creating a bigger index (many distinct key values). You have to test it in your quality system with nearly the same amount of data.
If someone has already implemented index key compression in his environment - please feel free to post your experiences.
References
- SAP Managed Tags:
3 Comments
