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:

• Teil 1
• Teil 2
• Teil 3
• Teil 4

Mehrspaltige Bitmap Indizes

da ich mit der Nummerierung der Beiträge langsam Probleme bekomme, versuch ich's jetzt noch mal mit eher inhaltlich begründeten Titelangaben ...

Mich hat noch interessiert, wie die Speicherung mehrspaltiger Bitmap Indizes aussieht. Dazu folgender Test:

-- Anlage einer Testtabelle
create table test
as
 select rownum id
      , mod(rownum, 5) col1
      , trunc(rownum/ 200) col2
   from dual
 connect by level < 1000

-- Anlage von Bitmap Indizes
create bitmap index test_bidx1 on test(col1);

create bitmap index test_bidx2 on test(col2);

create bitmap index test_bidx3 on test(col1, col2);

Die Tabelle enthält 999 Sätze und zu col1 und col2 jeweils 5 verschiedene Werte, wobei die col2-Werte geclustert sind und die col1-Werte nicht. Über die dba_segments kann man sie zugehörigen Block ermitteln und anschließend einen Blockdump durchführen:

SQL> r
  1  select header_file
  2       , header_block
  3       , blocks
  4    from dba_segments
  5*  where segment_name = upper('test_bidx2')

HEADER_FILE HEADER_BLOCK     BLOCKS
----------- ------------ ----------
          4          239          8

SQL> alter system dump datafile 4 block min 239 block max 246;

System wurde geändert. 

Im Blockdump finden sich für die einspaltigen Indizes Einträge folgender Struktur:

row#0[16180] flag: ------, lock: 0, len=48
col 0; len 1; (1):  80
col 1; len 6; (6):  01 00 00 e0 00 00
col 2; len 6; (6):  01 00 00 e0 00 c7
col 3; len 29; (29): 
 cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff
 ff ff c8 7f

Für einen mehrspaltigen Index sieht der Dump folgendermaßen aus:

row#0[16179] flag: ------, lock: 0, len=49
col 0; len 1; (1):  80
col 1; len 1; (1):  80
col 2; len 6; (6):  01 00 00 e0 00 00
col 3; len 6; (6):  01 00 00 e0 00 c7
col 4; len 28; (28): 
 cf 10 42 08 21 84 10 42 08 cf 21 84 10 42 08 21 84 10 cf 42 08 21 84 10 42
 08 21 02

Der mehrspaltige Index enthält demnach eine zusätzliche col-Angabe. Und während für die einspaltigen Indizes jeweils 5 row-Einträge erscheinen, sind es für die Kombination beider Spalten 5 * 5 = 25 Einträge. Die zugehörige Bitmap (col4) gibt dann an, in welchen Datensätzen die gegebene Wertkombination erscheint. Da die beiden Spalten in Kombination nicht mehr geclustert sind, ist das Bitmap relativ komplex. Wenn man aber in der Testtabelle auch col1 clustert (also beide Spalten mit den jeweils identischen Werte bestückt), ergibt sich wieder ein Bitmap mit vielen ff-Einträgen, da die 0-Einträge gut komprimiert werden können (mehr dazu findet man in Julian Dykes berühmter Bitmap-Präsentation).

Bitmap Indizes - Teil 7

und ein weiteres Mal zurück zu den Bitmap Indizes: Richard Foote hat seine Serie zu den Eigenschaften dieses Indextyps fortgesetzt und die Frage untersucht, ab wieviele wiederholten Werten eine Spalte für einen Bitmap Index in Frage kommt. Üblicherweise heisst es ja, dass nur Spalten mit einer relativ niedrigen Kardinalität geeignete Kandidaten für diesen Indizierungstyp wären. In Footes Beispiel ist aber bereits ein Index mit 500.000 distinkten Werten bei 1.000.000 Sätzen kleiner als ein entsprechender B*Tree-Index. Demnach kommen Bitmap Indizes also fast in jedem Fall in Frage (sofern man sich in einem DWH befindet und Updates kein Thema sind).

Aus der Tatsache, dass die Bitmap Indizes deutlich kleiner sind als entsprechende B*Tree-Indizes, ergibt sich dann auch der Effekte, dass ihr Aufbau in der Regel deutlich schneller durchgeführt werden kann.

Saldo-Rechnung

Zur Beantwortung einer Frage im Tutorials-Forum habe ich heute ein kleines Beispiel zur Ermittlung von Saldo-Werten mit Hilfe der analytischen Summenfunktion gebastelt, das ich hier im Blog wahrscheinlich eher wiederfinden werde.

Gegeben sind zwei Tabellen mit asynkronen Datumsangaben, die miteinander verknüpft werden sollen, und zu deren Wert-Angaben (also Soll und Haben) ein Saldo-Wert gebildet werden soll:

-- Anlage der Testtabellen

create table test as
select to_date('01.01.2010') - 1 + (rownum * 2) datum
     , 100 wert
  from dual
connect by level < 10

DATUM            WERT
---------- ----------
02.01.2010        100
04.01.2010        100
06.01.2010        100
08.01.2010        100
10.01.2010        100
12.01.2010        100
14.01.2010        100
16.01.2010        100
18.01.2010        100

create table test2 as
select to_date('01.01.2010') + (rownum * 2) datum
     , 90 wert2
  from dual
connect by level < 10

DATUM           WERT2
---------- ----------
03.01.2010         90
05.01.2010         90
07.01.2010         90
09.01.2010         90
11.01.2010         90
13.01.2010         90
15.01.2010         90
17.01.2010         90
19.01.2010         90

Um die Tabellen zusammen zu bringen, definiere ich innerhalb der Abfrage eine Datumsreferenz, an die ich Test und Test2 dann über outer join verknüpfe und ermittle die laufenden Summen und ihre Differenz schließlich über die analytische Summenfunktion:

select t4.*
     , nvl(t4.sum_wert, 0) - nvl(t4.sum_wert2, 0) wert_diff
  from (select t3.*
             , sum(wert) over(order by datum) sum_wert
             , sum(wert2) over(order by datum) sum_wert2
          from (select ref.datum
                     , t1.wert
                     , t2.wert2
                  from (select to_date('01.01.2010') - 1 + rownum datum
                          from dual
                       connect by level < 20) ref
                  left outer join
                       test t1
                    on (ref.datum = t1.datum)
                  left outer join
                       test2 t2
                    on (ref.datum = t2.datum)
                ) t3
        ) t4;

DATUM            WERT      WERT2   SUM_WERT  SUM_WERT2  WERT_DIFF
---------- ---------- ---------- ---------- ---------- ----------
01.01.2010                                                      0
02.01.2010        100                   100                   100
03.01.2010                    90        100         90         10
04.01.2010        100                   200         90        110
05.01.2010                    90        200        180         20
06.01.2010        100                   300        180        120
07.01.2010                    90        300        270         30
08.01.2010        100                   400        270        130
09.01.2010                    90        400        360         40
10.01.2010        100                   500        360        140
11.01.2010                    90        500        450         50
12.01.2010        100                   600        450        150
13.01.2010                    90        600        540         60
14.01.2010        100                   700        540        160
15.01.2010                    90        700        630         70
16.01.2010        100                   800        630        170
17.01.2010                    90        800        720         80
18.01.2010        100                   900        720        180
19.01.2010                    90        900        810         90

Aphorismen

da es in vielen technischen Foren offenbar Sitte geworden ist, einen geistreichen Aphorismus in die Signatur zu setzen, habe ich nach meinem Lieblingssatz von Mark Twain gefahndet und ihn hier wiedergefunden:

Always do right. This will gratify some people, and astonish the rest. (To the Young People's Society, Greenpoint Presbyterian Church, Brooklyn (February 16, 1901))

Die Übersetzung trifft den Klang entsprechender Sinnsprüche auch sehr schön:

Handle immer richtig. Das wird einige Leute zufrieden stellen und den Rest in Erstaunen versetzen.