Bitmap Indizes - Teil 6

Noch mal zurück zu den Bitmap Indizes: einmal mehr hat Richard Foote dazu eine sehr erhellende Erläuterung geliefert, in der er das Verhalten von Bitmap und B*Tree-Indizes untersucht und noch mal darauf hinweist, dass nicht die Anzahl der distinkten Sätze, sondern die Clusterung der Werte in der Tabelle über die Qualität der Indizes (in diesem Fall beider Typen) entscheidet.

Etwas extrem kommt mir der sbschließende Hinweis vor: "Let me repeat: There is no limit to the number of distinct values in a column for it to be considered for a Bitmap index." Wären demnach auch eindeutige Spalten Kandidaten für Bitmap-Indizes. Dazu ein kurzer Versuch:

 SQL> create table test1
  2  as
  3  select rownum col1
  4       , trunc(rownum/10) col2
  5       , trunc(rownum/100) col3
  6       , trunc(rownum/1000) col4
  7       , trunc(rownum/10000) col5
  8       , trunc(rownum/100000) col6
  9    from dual
 10  connect by level <= 1000000
 11   order by col1;

Tabelle wurde erstellt.

create bitmap index test1_bidx1 on test1(col1);
create bitmap index test1_bidx2 on test1(col2);

SQL> r
  1  select index_name
  2       , index_type
  3       , distinct_keys
  4       , clustering_factor
  5       , blevel
  6       , leaf_blocks
  7    from user_indexes
  8   where table_name = 'TEST1'
  9*  order by 1

INDEX_NAME   INDEX_TYPE   DISTINCT_KEYS CLUSTERING_FACTOR     BLEVEL LEAF_BLOCKS
------------ ------------ ------------- ----------------- ---------- -----------
TEST1_BIDX1  BITMAP             1000000           1000000          2        1718
TEST1_BIDX2  BITMAP              100001            100001          1         187

drop index TEST1_BIDX1;
drop index TEST1_BIDX2;
create index test1_idx1 on test1(col1);
create index test1_idx2 on test1(col2);

 SQL> select index_name
  2       , index_type
  3       , distinct_keys
  4       , clustering_factor
  5       , blevel
  6       , leaf_blocks
  7    from user_indexes
  8   where table_name = 'TEST1'
  9   order by 1;

INDEX_NAME   INDEX_TYPE   DISTINCT_KEYS CLUSTERING_FACTOR     BLEVEL LEAF_BLOCKS
------------ ------------ ------------- ----------------- ---------- -----------
TEST1_IDX1   NORMAL             1000000              1962          1        1099
TEST1_IDX2   NORMAL              100001              1966          2        1093

Die B*Tree-Indizes haben jeweils einen guten Clustering-Factor, sind aus Sicht des cbo also günstig. Für den Extremfall eines eindeutigen Wertes für col1 ist der Bitmap Index größer als der entsprechende B*Tree-Index (1718 zu 1099 Leaf-Blocks), aber schon für col2 (mit 100.000 distinkten Werten unter 1.000.000 Sätzen) ist der Bitmap Index deutlich kompakter (187 zu 1093 Leaf-Blocks).

Jetzt der Versuch mit einer ungeordneten Tabelle:

create table test3
as
select *
  from test1
 order by dbms_random.value;

create bitmap index test3_bidx1 on test3(col1);
create bitmap index test3_bidx2 on test3(col2);

SQL> r
  1  select index_name
  2       , index_type
  3       , distinct_keys
  4       , clustering_factor
  5       , blevel
  6       , leaf_blocks
  7    from user_indexes
  8   where table_name = 'TEST3'
  9*  order by 1

INDEX_NAME   INDEX_TYPE   DISTINCT_KEYS CLUSTERING_FACTOR     BLEVEL LEAF_BLOCKS
------------ ------------ ------------- ----------------- ---------- -----------
TEST3_BIDX1  BITMAP             1000000           1000000          2        1718
TEST3_BIDX2  BITMAP              100001            100001          1         397

drop index TEST3_BIDX1;
drop index TEST3_BIDX2;
create index test3_idx1 on test3(col1);
create index test3_idx2 on test3(col2);

SQL> select index_name
  2       , index_type
  3       , distinct_keys
  4       , clustering_factor
  5       , blevel
  6       , leaf_blocks
  7    from user_indexes
  8   where table_name = 'TEST3'
  9   order by 1;

INDEX_NAME   INDEX_TYPE   DISTINCT_KEYS CLUSTERING_FACTOR     BLEVEL LEAF_BLOCKS
------------ ------------ ------------- ----------------- ---------- -----------
TEST3_IDX1   NORMAL             1000000            999456          1        1099
TEST3_IDX2   NORMAL              100001            997675          2        1093

Erwartungsgemäß ändert sich die Größe des Bitmap Index für die eindeutige Spalte nicht, da für jeden Wert ein Bitmap aus sehr vielen Nullen und einer 1 erzeugt wird (0000001000000...). Für die B*Tree-Indizes ändert sich die Größe ebenfalls nicht (was ebenfalls zu erwarten war), aber der Clustering-Factor steigt extrem (und nähert sich der Anzahl der Tabellenzeilen). Deutlich größer wird der zweite Bitmap-Index, der aber immer noch sehr viel kleiner ist als der entsprechende B*Tree-Index.