Skip to Content

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):

/wp-content/uploads/2008/04/ic_log_structure_101086.jpg

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

     /wp-content/uploads/2008/04/ic_statistics_101087.jpg

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

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Russell Brooks
    Hi Stefan,
    Great blog!  The dumps clearly showed the impact.

    Do you have any information on why Index Key Compression is not supported prior to Oracle 10.2?

    Thanks and best regards,
    Russ Brooks

    (0) 
  2. Jim Spath
    Stefan:

      Before I wrote up a blog on our Indexes lose weight, wait, waist, and waste. I looked to see if this topic was already covered, so I didn’t duplicate prior writers.  Your content seems to be the theoretical and testing aspect, while I have a different slant, showing performance impacts.  I hope this answers your question about experiences!

      The question that Russ Brooks asked on why this is not back ported to prior versions is simple, I think.  Vendors are always working on the next version; retrofitting code to a prior stable version has little investment potential.  And support always asks if you’re on the latest version anyway.

    Jim

    (0) 
  3. Stefan Koehler Post author

    UPDATE 02.10.2014

    Oracle introduced the Advanced Index Compression with release 12.1.0.2. You don’t need to (globally) figure out and specify the compression factor anymore.

    Oracle will automatically compress only the worth leaf blocks depending on the repeating values in each leaf block. Richard Foote has written a nice blog article called “Index Compression Part V: 12c Advanced Index Compression (Little Wonder)” about that new feature.

    Regards

    Stefan

    (0) 

Leave a Reply