Dazu ein kleiner Test:
-- Anlage einer Testtabelle mit 6 Spalten unterschiedlicher Kardinalität
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;
Tabelle wurde erstellt.
SQL> select count(*) cnt
2 , count(distinct col1) cnt_col1
3 , count(distinct col2) cnt_col2
4 , count(distinct col3) cnt_col3
5 , count(distinct col4) cnt_col4
6 , count(distinct col5) cnt_col5
7 , count(distinct col6) cnt_col6
8 from test1;
CNT CNT_COL1 CNT_COL2 CNT_COL3 CNT_COL4 CNT_COL5 CNT_COL6
---------- -------- -------- -------- -------- -------- --------
1000000 1000000 100001 10001 1001 101 11
Jemand mit solideren mathematischen Kenntnissen hätte sich wahrscheinlich eine Rechnung ausgedacht, die nicht über den einen zusätzlichen Wert ab Ebene 2 gestolpert wäre, aber für meine Zwecke genügt das hier erst einmal.
Auf der Basis dieser Tabelle definiere ich zunächst einige Bitmap-Indizes mit jeweils einer Spalte.
SQL> create bitmap index test1_bidx1 on test1(col1); Index wurde erstellt. Abgelaufen: 00:00:04.14 SQL> create bitmap index test1_bidx2 on test1(col2); Index wurde erstellt. Abgelaufen: 00:00:01.29 SQL> create bitmap index test1_bidx3 on test1(col3); Index wurde erstellt. Abgelaufen: 00:00:00.34 SQL> create bitmap index test1_bidx4 on test1(col4); Index wurde erstellt. Abgelaufen: 00:00:00.32 SQL> create bitmap index test1_bidx5 on test1(col5); Index wurde erstellt. Abgelaufen: 00:00:00.29 SQL> create bitmap index test1_bidx6 on test1(col6); Index wurde erstellt. Abgelaufen: 00:00:00.30
Die aufgeführten Laufzeiten sind erst einmal mäßig interessant und zeigen zunächst nur, dass die Kardinalität der Spalte einen Einfluß auf die Laufzeit der Indexerstellung hat. Interessanter ist die Größe der erzeugten Objekte:
SQL> r 1 select segment_name 2 , segment_type 3 , blocks 4 from user_segments 5* where segment_name like 'TEST1%' SEGMENT_NAME SEGMENT_TYPE BLOCKS ------------------------------ ------------------ ---------- TEST1 TABLE 2048 TEST1_BIDX1 INDEX 1792 TEST1_BIDX2 INDEX 256 TEST1_BIDX3 INDEX 32 TEST1_BIDX4 INDEX 16 TEST1_BIDX5 INDEX 16 TEST1_BIDX6 INDEX 16
Der Bitmap Index auf der Spalte mit den eindeutigen Werten ist in diesem Fall fast so groß wie die ursprüngliche Tabelle. Im Vergleich zu normalen B*Tree-Indizes zeigt sich schon mal ein entscheidender Vorteil der bitmap Indizes: sie lassen sich deutlich schneller erzeugen (jedenfalls, wenn die Kardinalität der zugrunde liegenden Spalte im Verhältnis zur Gesamtzahl der Sätze in der Tabelle nicht sehr groß ist:
SQL> r 1 create table test2 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 Tabelle wurde erstellt. Abgelaufen: 00:00:05.93 SQL> create index test2_idx1 on test2(col1); Index wurde erstellt. Abgelaufen: 00:00:03.80 SQL> create index test2_idx2 on test2(col2); Index wurde erstellt. Abgelaufen: 00:00:02.92 SQL> create index test2_idx3 on test2(col3); Index wurde erstellt. Abgelaufen: 00:00:02.22 SQL> create index test2_idx4 on test2(col4); Index wurde erstellt. Abgelaufen: 00:00:02.19 SQL> create index test2_idx5 on test2(col5); Index wurde erstellt. Abgelaufen: 00:00:02.38 SQL> create index test2_idx6 on test2(col6); Index wurde erstellt. Abgelaufen: 00:00:01.72
In allen Fällen erfolgt das Einlesen der Daten über FTS, so dass die unterschiedlichen Laufzeiten von den unterschiedlichen Operationen abhängig sein müssen, die in den jeweiligen Fällen erforderlich sind, um die Indexstruktur aufzubauen - also den B-Baum oder das Bitmap-Muster:
SQL> r 1 select segment_name 2 , segment_type 3 , blocks 4 from user_segments 5* where segment_name like 'TEST%' SEGMENT_NAME SEGMENT_TYPE BLOCKS ------------------------------ ------------------ ---------- TEST1 TABLE 2048 TEST1_BIDX1 INDEX 1792 TEST1_BIDX2 INDEX 256 TEST1_BIDX3 INDEX 32 TEST1_BIDX4 INDEX 16 TEST1_BIDX5 INDEX 16 TEST1_BIDX6 INDEX 16 TEST2 TABLE 2048 TEST2_IDX1 INDEX 1152 TEST2_IDX2 INDEX 1152 TEST2_IDX3 INDEX 1088 TEST2_IDX4 INDEX 1088 TEST2_IDX5 INDEX 1024 TEST2_IDX6 INDEX 1024
Bereits der Index TEST1_BIDX2 mit 100.001 distinkten Werten für 1.000.000 Sätze ist demnach deutlich kleiner als jeder der B*Tree-Indizes, bei denen die Größe weitgehend unabhängig von der Anzahl distinkter Werte ist (was in einem B-Baum auch nicht weiter überrascht).
Jetzt zu den mehrspaltigen Varianten:
SQL> create index TEST1_BIDX7 on TEST1(col1, col2); Index wurde erstellt. Abgelaufen: 00:00:04.02 SQL> create index TEST1_BIDX8 on TEST1(col2, col3); Index wurde erstellt. Abgelaufen: 00:00:02.09 SQL> create index TEST1_BIDX9 on TEST1(col3, col4); Index wurde erstellt. Abgelaufen: 00:00:02.64 SQL> create index TEST1_BIDX10 on TEST1(col4, col5); Index wurde erstellt. Abgelaufen: 00:00:02.03 SQL> create index TEST1_BIDX11 on TEST1(col5, col6); Index wurde erstellt. Abgelaufen: 00:00:02.65
Auch in diesem Fall erfolgt das Einlesen der Daten über FTS, aber die Laufzeit für den Aufbau hat sich auch für die Kombinationen mit niedriger Kardinalität signifikant erhöht.
SEGMENT_NAME SEGMENT_TYPE BLOCKS ------------------------------ ------------------ ---------- TEST1 TABLE 2048 TEST1_BIDX1 INDEX 1792 TEST1_BIDX2 INDEX 256 TEST1_BIDX3 INDEX 32 TEST1_BIDX4 INDEX 16 TEST1_BIDX5 INDEX 16 TEST1_BIDX6 INDEX 16 TEST1_BIDX7 INDEX 1472 TEST1_BIDX8 INDEX 1408 TEST1_BIDX9 INDEX 1344 TEST1_BIDX10 INDEX 1280 TEST1_BIDX11 INDEX 1216
Interessanterweise ist der Index TEST1_BIDX7 kleiner als TEST1_BIDX1, was mir nicht unmittelbar einleuchtet. In jedem Fall ist jeder der neuen Indizes deutlich größer als die kleineren Indizes des ersten Versuchs.
SQL> create index TEST2_IDX7 on TEST2(col1, col2); Index wurde erstellt. Abgelaufen: 00:00:01.04 SQL> create index TEST2_IDX8 on TEST2(col2, col3); Index wurde erstellt. Abgelaufen: 00:00:01.10 SQL> create index TEST2_IDX9 on TEST2(col3, col4); Index wurde erstellt. Abgelaufen: 00:00:01.05 SQL> create index TEST2_IDX10 on TEST2(col4, col5); Index wurde erstellt. Abgelaufen: 00:00:01.12 SQL> create index TEST2_IDX11 on TEST2(col5, col6); Index wurde erstellt. Abgelaufen: 00:00:01.06
Der Aufbau der zweispaltigen B*Tree-Indizes erfolgt in diesen Fällen schneller als der der einspaltigen Indizes, aber die Laufzeiten scheinen mir in diesen Fällen keine sehr brauchbaren Indikatoren zu sein. In jedem Fall gibt es für die mehrspaltigen Indizes hinsichtlich der Aufbauzeit keinen deutlichen Vorteil mehr für die bitmap Indizes. Außerdem sind die mehrspaltigen B*Tree-Indizes auch nicht größer als die entsprechenden bitmap Indizes:
SEGMENT_NAME SEGMENT_TYPE BLOCKS ------------------------------ ------------------ ---------- TEST2 TABLE 2048 TEST2_IDX1 INDEX 1152 TEST2_IDX7 INDEX 1472 TEST2_IDX8 INDEX 1408 TEST2_IDX9 INDEX 1344 TEST2_IDX10 INDEX 1280 TEST2_IDX11 INDEX 1216
Ein extremerer Versuch: ein Index über alle Spalten:
SQL> create bitmap index TEST1_BIDX_All on TEST1(col1, col2, col3, col4, col5, col6); Index wurde erstellt. Abgelaufen: 00:08:05.18 --> also mehr als 8 Minuten!!! SQL> create index TEST2_IDX_All on TEST2(col1, col2, col3, col4, col5, col6); Index wurde erstellt. Abgelaufen: 00:00:03.38 SEGMENT_NAME SEGMENT_TYPE BLOCKS ------------------------------ ------------------ ---------- TEST1_BIDX_ALL INDEX 3072 TEST2_IDX_ALL INDEX 2432
Das ist jetzt doch - zumindest was die Laufzeit angeht - deutlich extremer als ich angenommen hatte; es entspricht aber meiner gestrigen Behauptung. Ein Blick auf die execution plans der beiden Operationen lässt bei mir den Verdacht entstehen, dass die BITMAP CONSTRUCTION der verantwortliche Teischritt ist:
-- gekürzte Darstellung des Plans aus dbms_xplan.display ---------------------------------------------------------- | Id | Operation | Name | Rows | ---------------------------------------------------------- | 0 | CREATE INDEX STATEMENT | | 327K| | 1 | INDEX BUILD NON UNIQUE | TEST2_BIDX_ALL | | | 2 | BITMAP COMPACTION | | | | 3 | SORT CREATE INDEX | | 327K| | 4 | BITMAP CONSTRUCTION | | | | 5 | TABLE ACCESS FULL | TEST2 | 327K| ---------------------------------------------------------- --------------------------------------------------------- | Id | Operation | Name | Rows | --------------------------------------------------------- | 0 | CREATE INDEX STATEMENT | | 327K| | 1 | INDEX BUILD NON UNIQUE| TEST2_BIDX_ALL | | | 2 | SORT CREATE INDEX | | 327K| | 3 | TABLE ACCESS FULL | TEST2 | 327K| ---------------------------------------------------------
Da die fragliche Datenbank Version 11.1.0.6 ist, kann ich über V$SQL_PLAN_MONITOR sehen, dass der FTS extrem langsam OUTPUT_ROWS liefert, und dass die folgenden Schritte noch keine Statusinformationen liefern.
Nachdem ich hier angekommen bin, sehe ich gerade, dass Jonathan Lewis dazu vor einigen Jahren im DBAzine eine umfassende Erläuterung geschrieben hat (damals ging's um 9.2). Dort findet man dann auch die Formel für die extremste Größe eines Bitmap-Index:
In the worst case, the size of a bitmap in bits would be:Und weiter heisst es dort:
Number of different possible values for the column *Number of rows that Oracle thinks could fit in a block *Number of blocks below high water mark.Add about 10 percent for checksum information and overhead, and divide by eight to get the number of bytes.
This dramatic variation in size is yet another reason for reviewing the claim about "low cardinality." The potential benefit of a bitmap index varies with the clustering of data (as does the potential benefit of a B*tree index, of course). When you are considering bitmap indexes, do not be put off by a column which has a "large" number of different values. If every value appears a "large" number of times, and if the rows for each value are reasonably clustered, then a bitmap index may be highly appropriate. In a table with 100M rows, a column with 10,000 different values could easily turn out to be a perfect candidate for a bitmap index.Vielleicht hätte ich zuerst recherchieren sollen - andererseits kann ich mir das, was ich selber herausgefunden habe, vermutlich besser merken...
Keine Kommentare:
Kommentar veröffentlichen