Mittwoch, April 28, 2010

Index Compression

Nachdem ich in Harald van Breederodes Blog ein kleines Beispiel zum Thema der Index-Compression gesehen habe, kam mir der Gedanke, die Rolle der Spaltenreihenfolge anhand eines simplen Tests zu überprüfen:

-- eine Testtabelle mit 1.000.000 Sätzen 
create table test
as
select rownum col1
     , trunc(rownum/10) col2
     , trunc(rownum/100) col3
     , trunc(rownum/1000) col4
     , trunc(rownum/10000) col5
     , trunc(rownum/100000) col6
  from dual
connect by level <= 1000000;

-- ein Index mit den Spalten col2 (100.001 Sätze) bis col5 (101 Sätze)
create index test_idx1 on test(col2, col3, col4, col5);

-- initiale Größe des Index
SQL> select leaf_blocks from user_indexes where index_name = 'TEST_IDX1';

LEAF_BLOCKS
-----------
       1839

Beim Vorgehen habe ich mich munter bedient bei den Beispielen des Herrn van Breederode und folglich jetzt auch die Validierung durchgeführt, um einen Vorschlag für die Komprimierung zu bekommen:

SQL> validate index TEST_IDX1;

Index wurde analysiert.

SQL> select opt_cmpr_count,opt_cmpr_pctsave from index_stats;

OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------- ----------------
             4               50

SQL> alter index TEST_IDX1 rebuild compress 4;

Index wurde geändert.

SQL> select leaf_blocks from user_indexes where index_name = 'TEST_IDX1';

LEAF_BLOCKS
-----------
        907

Das war also eine sehr akkurate Schätzung der Größe des Komprimierungseffekts. Jetzt der gleiche Versuch bei einem Index mit veränderter Spaltenfolge:

SQL> create index test_idx2 on test(col5, col4, col3, col2);

Index wurde erstellt.

-- die Größe ist identisch, was mich nicht wundert, da der Clustering Factor identisch ist
-- (und sein muss, da die Ordnung der Tabelle ja unverändert ist)
SQL> select leaf_blocks from user_indexes where index_name = 'TEST_IDX2';

LEAF_BLOCKS
-----------
       1839

SQL> validate index TEST_IDX2;

Index wurde analysiert.

SQL> select opt_cmpr_count,opt_cmpr_pctsave from index_stats;

OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------- ----------------
             4               50

SQL> alter index TEST_IDX2 rebuild compress 4;

Index wurde geändert.

SQL> select leaf_blocks from user_indexes where index_name = 'TEST_IDX2';

LEAF_BLOCKS
-----------
        907

Die Komprimierung ist somit in diesem Fall unabhängig von der Reihenfolge der Spalten, was mich erst mal ein wenig überrascht. Bei Richard Foote kann man nachlesen, wie die Index-Komprimierung grundsätzlich funktioniert:
Basically, Oracle stores each distinct combination of compressed column values found within a specific index leaf block in a “Prefix” table within the leaf block and assigns each combination a unique prefix number.
[...]
Oracle now no longer stores these compressed columns within the actual index row entries. These compressed columns are substituted and referenced with the details stored in the prefix table entry, which are shared by all index row entries with the same corresponding prefix value.
Aber dann kommt dort der Hinweis, an den ich mich erinnert hatte, und der der Anlass für meinen Test war:
Only leading columns (which in a Non-Unique Index can potentially be all the columns in an index, in a Unique Index can potentially be all but the last column in the index) can be compressed.
Deshalb noch ein Versuch:

SQL> create index test_idx3 on test(col5, col4, col3, col2, col1);

Index wurde erstellt.

SQL> validate index TEST_IDX3;

Index wurde analysiert.

SQL> select opt_cmpr_count,opt_cmpr_pctsave from index_stats;

OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------- ----------------
             4               42

SQL> create index test_idx4 on test(col1, col2, col3, col4, col5);

Index wurde erstellt.

SQL> validate index TEST_IDX4;

Index wurde analysiert.

SQL> select opt_cmpr_count,opt_cmpr_pctsave from index_stats;

OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------- ----------------
             0                0

Mit der führenden eindeutigen Spalte wird die Komprimierung also unmöglich, während der umgekehrt sortierte Index mit den gleichen Spalten gut komprimierbar ist. Ich nehm das mal wieder als Beleg dafür, dass ich meinem Gedächtnis nur mit einem gewissen Vorbehalt trauen kann, und dass es nicht schaden würde, Richard Footes Serie zur Index-Komprimierung noch mal zu lesen:

Keine Kommentare:

Kommentar veröffentlichen