ora_rowscn

Mit Hilfe der Pseudo-Column ora_rowscn lässt sich (relativ zuverlässig) ermitteln, wann ein Datensatz einer Tabelle zuletzt verändert wurde. Dazu ein kleiner Test:

 -- Anlage einer Testtabelle
create table test1
as
select rownum rn
  from dual
connect by level < 10000;

select blockid
     , count(*)
  from (select dbms_rowid.rowid_block_number(rowid) blockid
          from test1)
 group by blockid
 order by blockid

   BLOCKID   COUNT(*)
---------- ----------
   1766180        657
   1766181        657
   1766182        657
   1766183        657
   1766184        657
   3339761        657
   3339762        657
   3339763        657
   3339764        657
   3339765        657
   3339766        657
   3339767        657
   3339768        657
   3339770        657
   3339771        657
   3339772        144

16 Zeilen ausgewählt.

Die Datensätze wurden also auf 16 Blöcke verteilt. Nicht besonders überrascht, dass alle Sätze nach Angabe der ora_rowscn zur gleichen Zeit eingefügt wurden:

select to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
     , count(*)
  from test1
 group by to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
 order by to_char (scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')

TO_CHAR(   COUNT(*)
-------- ----------
13:55:51       9999

Ein Update verändert die Versionierungsinformationen:

update test1 set rn = rn + 1 where rn < 10;
9 Zeilen wurden aktualisiert.

commit;  

--erst nach dem Commit lieferte die folgende Query das folgende Ergebnis
select to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
     , count(*)
  from test1
 group by to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
 order by to_char (scn_to_timestamp(ora_rowscn), 'hh24:mi:ss');

TO_CHAR(   COUNT(*)
-------- ----------
13:55:51       9342
13:57:48        657 

Das Ergebnis zeigt, dass nicht nur die korrigierten (neun) Sätze einen neuen Zeitstempel bekommen haben, sondern alle Sätze des betroffenen Blocks. Bei Tom Kyte wird erklärt, wozu die ora_rowscn eigentlich gedacht ist (Replikation, Optimistic Locking), und wie man dafür sorgen kann, dass die Änderung der Versionsangaben satzgenau erfolgt - nämlich durch Ergänzung des Schlüsselwortes ROWDEPENDENCIES bei der Tabellenanlage:

create table test1
rowdependencies
as
select rownum rn
  from dual
connect by level < 10000

select to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
     , count(*)
  from test1
 group by to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
 order by to_char (scn_to_timestamp(ora_rowscn), 'hh24:mi:ss');

TO_CHAR(   COUNT(*)
-------- ----------
14:09:39       9999

update test1 set rn = rn + 1 where rn < 10;
9 Zeilen wurden aktualisiert.

select to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
     , count(*)
  from test1
 group by to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
 order by to_char (scn_to_timestamp(ora_rowscn), 'hh24:mi:ss');
FEHLER in Zeile 4:
ORA-01405: Abgerufener Spaltenwert ist NULL

commit;

select to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
     , count(*)
  from test1
 group by to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
 order by to_char (scn_to_timestamp(ora_rowscn), 'hh24:mi:ss');

TO_CHAR(   COUNT(*)
-------- ----------
14:09:39       9990
14:10:13          9

Funktioniert also, wie der Herr Kyte es erklärt hat. Die Rolle des Commit ist mir noch nicht völlig klar. Verständlich ist, dass die SCN-Angabe erst nach dem Commit verändert ist, aber woher der Spaltenwert NULL im zweiten Fall kommt, kann ich nicht sagen.

Übrigens funktioniert die scn_to_timestamp-Funktion nur in einem beschränkten Zeitraum, da das Mapping offenbar nicht dauerhaft gespeichert wird.

Bitmap Indizes - Teil 5

Richard Foote könnte vielleicht mal ein Buch über Oracle-Indizes schreiben (meines Wissens hat er's noch nicht getan), jedenfalls findet man in seinem Blog genügend Stoff dafür. Im verlinkten Artikel zerlegt er allerlei dummes Zeug, das der Herr Burleson auf einer seiner Webseiten präsentierte, und erläutert, warum die Anzahl distinkter Werte für einen Bitmap Index weniger entscheidend ist als die Clusterung der Werte.

NVL, Coalesce, CASE

Warum man NVL besser in den Ruhestand entlassen sollte, erfährt man hier.

Statistics_Level

Ein - zumindest für mich - überraschendes Ergebnis einiger Tests, bei denen ich mir über die Abarbeitung von HASH JOINS und die Rolle der PGA-Dimensionierung Klarheit verschaffen wollte, ist der extreme Effekt des Statistics-Levels auf die Perfomance. Dazu folgende Resultate:

-- Anlage von Testtabellen 
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> 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.

SQL> sho parameter statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
statistics_level                     string      TYPICAL

SQL> select count(*)
  2    from test1, test2
  3   where test1.col1 = test2.col1;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:01.48

SQL> select count(*) from test1;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:00.07

So weit alles ganz harmlos. Aber jetzt ändere ich das STATISTICS_LEVEL:

SQL> alter system set statistics_level=all;

System wurde geändert.

SQL> select count(*)
  2    from test1, test2
  3   where test1.col1 = test2.col1;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:13.05
SQL> select count(*) from test1;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:02.47

Für den einfachen FTS erhöht sich die Laufzeit von 0,07 sec auf 2,47 sec. Für den Join (der als HASH JOIN als optimal execution komplett im Speicher durchgeführt werden kann) steigt die Laufzeit von 1,48 sec auf 13,05 sec (und diese Werte bestätigen sich bei wiederholter Ausführung). Offenbar ist der negative Effekt des erhöhten statistics_level auf die Performance weit größer als der Einfluß etwa eines 10046er Traces.

Nachtrag: der Hinweis darauf, dass man das Statistics_Level nur mit Bedacht auf 'ALL' setzen sollte, findet sich z.B. bei Tanel Poder und Chris Antognini.

Hints

Jonathan Lewis hat mal wieder ein interessantes Beispiel für das Verhalten von Hints gefunden. Im Blog-Eintrag finden sich auch allerlei Links zu anderen Beiträgen zum Thema.

Bitmap Indizes - Teil 4

Noch ein kleiner Test zum Thema der Bitmap-Indizes: im Fall von B*Tree-Indizes gehört zu meinen Lieblingstechniken die Definition umfangreicher Indizes, die einen Tabellenzugriff vollständig vermeiden (man nennt sie manchmal auch "Fat" Indexes). Hier jetzt der Test, ob das auch mit Bitmap Indizes funktioniert:

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> create bitmap index test1_bidx2 on test1(col2);
Index wurde erstellt.

SQL> create bitmap index test1_bidx3 on test1(col3);
Index wurde erstellt.

SQL> select col2, col3
  2    from test1
  3   where col2 = 10;

10 Zeilen ausgewõhlt.

Abgelaufen: 00:00:00.16

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 724021477

--------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |    10 |   260 |
|*  1 |  VIEW                         | index$_join$_001 |    10 |   260 |
|*  2 |   HASH JOIN                   |                  |       |       |
|   3 |    BITMAP CONVERSION TO ROWIDS|                  |    10 |   260 |
|*  4 |     BITMAP INDEX SINGLE VALUE | TEST1_BIDX2      |       |       |
|   5 |    BITMAP CONVERSION TO ROWIDS|                  |    10 |   260 |
|   6 |     BITMAP INDEX FULL SCAN    | TEST1_BIDX3      |       |       |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL2"=10)
   2 - access(ROWID=ROWID)
   4 - access("COL2"=10)

Note
-----
   - dynamic sampling used for this statement


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
        554  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

Der Zugriff erfolgt in diesem Fall komplett über die beiden Indizes, die alle Informationen enthalten, so dass der Tabellenzugriff entfällt. Die Index-Informationen müssen allerdings über eine BITMAP CONVERSION TO ROWIDS umgewandelt werden.

Jetzt der Versuch mit einem Bitmap Index über zwei Spalten:

SQL> select col2, col3
  2    from test1
  3   where col2 = 10;

10 Zeilen ausgewõhlt.

Abgelaufen: 00:00:00.02

Ausf³hrungsplan
--------------------------------------------------------
Plan hash value: 3301052306

--------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes |
--------------------------------------------------------
|   0 | SELECT STATEMENT |             |    22 |   572 |
|*  1 |  INDEX RANGE SCAN| TEST1_BIDX4 |    22 |   572 |
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COL2"=10)

Note
-----
   - dynamic sampling used for this statement


Statistiken
--------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        554  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

In diesem Fall genügt ein einfacher INDEX RANGE SCAN ohne weitere Konvertierung und der Zugriff wird billiger (4 statt 31 consistent gets; ohne die Indizes sind es 1970 consistent gets).Ein mehrspaltiger Index hat hier also durchaus Vorteile.

Gartner Magic Quadrant

Curt Monash scheint kein besonderer Freund der magischen Quadranten zu sein, jedenfalls fällt sein Kommentar zum Data Warehouse DBMS-Quadranten nicht sonderlich positiv aus...

Index Block Dumps

Richard Foote hat einen Blog-Artikel zum Thema Index Block Dumps and Index Tree Dumps geschrieben, der allerlei nützliches Analysewerkzeug erläutert.

DBMS_DATAPUMP

Bei Carsten Czarski findet sich eine Erläuterung dazu, wie man mit Hilfe des DBMS_DATAPUMP-Packages aus PL/SQL heraus Export- und Import-Operationen aufrufen kann.

Bitmap Indizes - Teil 3

Gestern Abend habe ich dann endlich das gemacht, was von Beginn an naheliegend gewesen wäre, nämlich an der richtigen Stelle nachgelesen. Die richtige Stelle war Cost Based Oracle Fundamentals von Jonathan Lewis, wo ich im Kapitel 8 zu den Bitmap Indizes unter anderem folgende Hinweise gefunden habe:

• wie allgemein bekannt bringen Bitmap Indizes jede Menge Ärger bei DML-Operationen, da sie massive Lock-Effekte (auch Deadlocks) hervorrufen; deshalb sind sie eher für DSS- als für OLTP-Systeme gedacht (dieser Punkt war mir auch ohne JL klar)
• der Clustering Factor spielt für Bitmap Indizes keine Rolle: er wird immer auf den Wert von NUM_ROWS gesetzt, was natürlich mit der Clusterung nichts zu tun hat, und er wird für die Kostenberechnung des cbo nicht berücksichtigt. Da der cbo auf diese Weise aber keine Hinweise auf die Clusterung der Daten hat, ist die Kostenberechnung für Bitmap Indizes in diesem Punkt recht fragwürdig.
• laut JL kann die Definition mehrspaltiger Bitmap Indizes durchaus sinnvoll sein, und unter Umständen ist der dabei erzeugte Index sogar kleiner als die entsprechenden Einzelspaltenindizes.
• die Zugriffsperformance für das Abrufen von größeren Datenmengen wird vom Indextyp nicht entscheidend beeinflusst, da die Hauptarbeit in diesem Fall beim Lesen der Tabellenblocks liegt.

Natürlich findet man bei Jonathan Lewis noch sehr viel mehr Informationen, aber das waren jetzt die Antworten auf die Fragen, die mich aktuell beschäftigt hatten.

Nachtrag: in Mr. Lewis' Blog wurde das Thema des Clustering Factors für Bitmap-Indizes vor einigen Monaten auch angesprochen, aber das hatte ich inzwischen auch schon wieder vergessen...

Noch ein Nachtrag: weitere Details findet man in einer Präsentation von Julian Dyke zu den Bitmap Internals.

Bitmap Indizes - Teil 2

Ok, Mr. Lewis sagt also, dass die physikalische Ordnung einer Tabelle für die Größe der zugehörigen Bitmap Indizes entscheidend ist (und er hat in aller Regel Recht, wenn er so etwas sagt). Probieren wir das einmal aus:

Meine bisher verwendeten Test-Tabellen waren sehr stark vorsortiert, so dass die Clusterung der Werte extrem ausfiel:

SQL> r
1  select INDEX_NAME
2       , INDEX_TYPE
3       , TABLE_NAME
4       , LEAF_BLOCKS
5       , DISTINCT_KEYS
6       , CLUSTERING_FACTOR
7    from user_indexes
8*  where table_name in ('TEST1', 'TEST2')

INDEX_NAME      INDEX_TYPE TABLE_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
--------------- ---------- ---------- ----------- ------------- -----------------
TEST2_IDX1      NORMAL     TEST2             1099       1000000              1962
TEST2_IDX2      NORMAL     TEST2             1093        100001              1962
TEST2_IDX3      NORMAL     TEST2             1030         10001              1962
TEST2_IDX4      NORMAL     TEST2             1024          1001              1962
TEST2_IDX5      NORMAL     TEST2              962           101              1962
TEST2_IDX6      NORMAL     TEST2              956            11              1962
TEST2_IDX7      NORMAL     TEST2             1436       1000000              1962
TEST2_IDX8      NORMAL     TEST2             1366        100001              1962
TEST2_IDX9      NORMAL     TEST2             1297         10001              1962
TEST2_IDX10     NORMAL     TEST2             1230          1001              1962
TEST2_IDX11     NORMAL     TEST2             1161           101              1962
TEST2_IDX_ALL   NORMAL     TEST2             2380       1000000              1962
TEST1_BIDX1     BITMAP     TEST1             1718       1000000           1000000
TEST1_BIDX2     BITMAP     TEST1              186        100001            100001
TEST1_BIDX3     BITMAP     TEST1               27         10001             10001
TEST1_BIDX4     BITMAP     TEST1               12          1001              1001
TEST1_BIDX5     BITMAP     TEST1               12           101               101
TEST1_BIDX6     BITMAP     TEST1               10            11                21
TEST1_BIDX7     NORMAL     TEST1             1436       1000000              1962
TEST1_BIDX8     NORMAL     TEST1             1366        100001              1962
TEST1_BIDX9     NORMAL     TEST1             1297         10001              1962
TEST1_BIDX10    NORMAL     TEST1             1230          1001              1962
TEST1_BIDX11    NORMAL     TEST1             1161           101              1962
TEST1_BIDX_ALL  BITMAP     TEST1             3003       1000000           1000000

Für die meisten der B*Tree-Indizes liegt der Clustering-Factor in der Nähe der Anzahl der Blöcke, was ein günstiges Zeichen ist (negativ wäre eine Annäherung an die Anzahl der Zeilen). Für Bitmap-Indizes bedeutet der Clustering-Factor etwas Anderes, aber ich habe gerade nicht mehr genau in Erinnerung, was das war (irgendwer hat dazu vor nicht allzu langer Zeit irgendwo irgendwas geschrieben - mag sein, dass es auch wieder der Herr Lewis war).

Jetzt definiere ich eine unsortierte Tabelle:

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

Und erzeuge dazu einen Bitmap Index:

create bitmap index test3_bidx2 on test3(col2);

SQL> select segment_name
  2       , segment_type
  3       , blocks
  4    from user_segments
  5   where segment_name like 'TEST%BIDX2';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS
------------------------------ ------------------ ----------
TEST1_BIDX2                    INDEX                     256
TEST3_BIDX2                    INDEX                     448

SQL> select INDEX_NAME
  2       , INDEX_TYPE
  3       , TABLE_NAME
  4       , LEAF_BLOCKS
  5       , DISTINCT_KEYS
  6       , CLUSTERING_FACTOR
  7    from user_indexes
  8   where INDEX_NAME like 'TEST__BIDX2';

INDEX_NAME      INDEX_TYPE TABLE_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
--------------- ---------- ---------- ----------- ------------- -----------------
TEST1_BIDX2     BITMAP     TEST1              186        100001            100001
TEST3_BIDX2     BITMAP     TEST3              397        100001            100001

Für den Fall der unsortierten Tabelle ist der Index deutlich größer, aber der Clustering_Factor bleibt unverändert. Für einen B*Tree-Index bleibt die Größe unverändert, aber der Clustering-Factor ändert sich (erwartungsgemäß) recht dramatisch:

SQL> r
  1  select segment_name
  2       , segment_type
  3       , blocks
  4    from user_segments
  5*  where segment_name like 'TEST__IDX2'

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS
------------------------------ ------------------ ----------
TEST2_IDX2                     INDEX                    1152
TEST3_IDX2                     INDEX                    1152

SQL> r
  1  select INDEX_NAME
  2       , INDEX_TYPE
  3       , TABLE_NAME
  4       , LEAF_BLOCKS
  5       , DISTINCT_KEYS
  6       , CLUSTERING_FACTOR
  7    from user_indexes
  8*  where INDEX_NAME like 'TEST__IDX2'

INDEX_NAME      INDEX_TYPE TABLE_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
--------------- ---------- ---------- ----------- ------------- -----------------
TEST2_IDX2      NORMAL     TEST2             1093        100001              1962
TEST3_IDX2      NORMAL     TEST3             1093        100001            997817

Bitmap Indizes - Teil 1

Gestern habe ich einem Kollegen gegenüber die Behauptung aufgestellt, dass der Aufbau mehrspaltiger Bitmap Indizes deutlich mehr Zeit benötigen würde als der Aufbau mehrerer Einzelspalten-Indizes, was er mir nicht glauben wollte, woraufhin ich einen kleinen Test durchführte und dabei feststellte, dass er im gegebenen Fall Recht hatte: die Laufzeitverlängerung schien nicht signifikant zu sein. Grund genug, mir die Eigenschaften von Bitmap Indizes noch einmal genauer anzuschauen (mehr anekdotisch hatte ich das offenbar hier schon einmal gemacht). Die Frage, die im Zusammenhang mit mehrspaltigen Bitmap-Indizes vielleicht zuerst auftreten könnte, ist wohl, wozu man sie überhaupt definieren sollte, und ob sie gegenüber mehreren verknüpften Bitmap-Einzelspalten-Indizes irgendeinen Vorteil haben. Aber das ist eine andere Geschichte, die ein andermal erzählt werden soll.

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:

    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.

Und weiter heisst es dort:

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...

Was passt in einen Block hinein?

Seit Jahren arbeite ich mit dem Basisverständnis, dass ein Oracle-Block neben den Tabellendaten allerlei Overhead enthält, darunter transaction header, table directory, row directory etc. - und Entsprechendes kann man z.B. auch hier lesen (wobei dort allerdings keine Angaben zur Version erscheinen, aber ich nehme an, dass sich an diesen Dingen seit Aeonen nichts mehr geändert hat). Im Rahmen meiner Tests zum Befehl Alter Table Shrink ist mir dann aber aufgefallen, dass meine Blocks deutlich weniger Sätze aufnehmen, als ich erwartet hatte. Dazu folgender Test:

-- Blockgröße 16 K
SQL> sho parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
db_block_size                        integer     16384

-- Anlage einer Testtabelle
SQL> create table t1
  2  as
  3  select rownum col1
  4    from dual
  5  connect by level < 10000;

Tabelle wurde erstellt.

SQL> r
  1  select blockid
  2       , count(*)
  3       , sum(colsize)
  4    from (select dbms_rowid.rowid_block_number(rowid) blockid
  5               , vsize(col1) colsize
  6            from t1)
  7   group by blockid
  8*  order by blockid

   BLOCKID   COUNT(*) SUM(COLSIZE)
---------- ---------- ------------
       224       1327         3869
       225       1327         3968
       226       1327         3968
       227       1327         3967
       228       1327         3968
       229       1327         3968
       230       1327         3968
       231        710         2123

8 Zeilen ausgewählt.

Die komplett gefüllten Blocks enthalten also jeweils 1327 Sätze, aber die Nettodaten der einzigen Tabellenspalte umfassen jeweils nur ca. 4K - also nur ein Viertel der Blocksize!

Eine Änderung der PCTFREE-Einstellung von 10 auf 0 verändert das Ergebnis im erwarteten Umfang von ~ 10%:

SQL> r
  1  select blockid
  2       , count(*)
  3       , sum(colsize)
  4    from (select dbms_rowid.rowid_block_number(rowid) blockid
  5               , vsize(col2) colsize
  6            from t2)
  7   group by blockid
  8*  order by blockid

   BLOCKID   COUNT(*) SUM(COLSIZE)
---------- ---------- ------------
       236       1475         4312
       237       1475         4410
       238       1475         4410
       239       1475         4410
       240       1475         4411
       241       1475         4410
       242       1149         3436

7 Zeilen ausgewählt.

Man kann sich den Inhalt eines Blocks ansehen, indem man einen Blockdump erstellt:

SQL> alter system dump datafile 4 block 224;
System wurde geändert.

Der Dump wird im user_dump_dest abgelegt und enthält diverse Repräsentationen der Blockinhalte, darunter eine hexadezimale Darstellung, in der man die Datenverteilung relativ gut erkennen kann. Dort sieht man im gegebenen Fall auch relativ große Lücken, die ich mir aktuell noch nicht erklären kann.

Deshalb noch ein dritter Versuch mit String-Werten fester Breite:

SQL> r
  1  create table t3
  2  as
  3  select 'AAAAAAAA' col3
  4    from dual
  5* connect by level < 10000

Tabelle wurde erstellt.

SQL> r
  1  select blockid
  2       , count(*)
  3       , sum(colsize)
  4    from (select dbms_rowid.rowid_block_number(rowid) blockid
  5               , vsize(col3) colsize
  6            from t3)
  7   group by blockid
  8*  order by blockid

   BLOCKID   COUNT(*) SUM(COLSIZE)
---------- ---------- ------------
       248       1043         8344
       249       1043         8344
       250       1043         8344
       251       1043         8344
       252       1043         8344
       253       1043         8344
       254       1043         8344
       255       1043         8344
       256       1043         8344
       257        612         4896

10 Zeilen ausgewählt.

Hier entspricht die vsize sehr genau den Erwartungen, aber die Blocks sind offenbar trotzdem nur halb gefüllt. Im Blockdump sieht man nach einem Blockheader von ca. 2000 Zeichen folgende Muster:

...
011CD8720 41414141 0801002C 41414141 41414141  [AAAA,...AAAAAAAA]
011CD8730 0801002C 41414141 41414141 0801002C  [,...AAAAAAAA,...]
011CD8740 41414141 41414141 0801002C 41414141  [AAAAAAAA,...AAAA]
011CD8750 41414141 0801002C 41414141 41414141  [AAAA,...AAAAAAAA]
011CD8760 0801002C 41414141 41414141 0801002C  [,...AAAAAAAA,...]
011CD8770 41414141 41414141 0801002C 41414141  [AAAAAAAA,...AAAA]
011CD8780 41414141 0801002C 41414141 41414141  [AAAA,...AAAAAAAA]
011CD8790 0801002C 41414141 41414141 0801002C  [,...AAAAAAAA,...]
011CD87A0 41414141 41414141 0801002C 41414141  [AAAAAAAA,...AAAA]
...

Offenbar ist die Speicherung für die Sätze weniger kompakt und die (row) directory Informationen sind umfangreicher, als ich das angenommen hätte. (Die verlinkte Seite erklärt "The row directory uses 2 bytes per stored row.", aber sicher zuordnen kann ich diese Struktur im Blockdump nicht)

Erwartungsgemäß lassen sich wenige große Sätze besser in den Block unterbringen:

SQL> create table t4
  2  as
  3  select rpad('*', 1000, '*') col4
  4    from dual
  5  connect by level <= 32;

Tabelle wurde erstellt.

SQL> select blockid
  2       , count(*)
  3       , sum(colsize)
  4    from (select dbms_rowid.rowid_block_number(rowid) blockid
  5               , vsize(col4) colsize
  6            from t4)
  7   group by blockid
  8   order by blockid;

   BLOCKID   COUNT(*) SUM(COLSIZE)
---------- ---------- ------------
       328         14        14000
       329         14        14000
       330          4         4000

Für kleine Rows ist der Overhead des row directory denmach recht signifikant, für größere spielt er eine weniger wichtige Rolle.

Disk rotation speed

Curt Monash nennt ein paar Zahlen zur Entwicklung der Geschwindigkeit von Festplattenzugriffen:

The first hard disks ever were introduced by IBM in 1956. They rotated 1,200 times per minute. Today’s state-of-the-art disk drives rotate 15,000 times per minute. That’s a 12.5-fold improvement since the first term of the Eisenhower Administration. (I understand that the reason for this slow improvement is aerodynamic — a disk that spins too fast literally flies off the spindle.)
Unfortunately, random seek time is bounded below, on average, by 1/2 of a disk’s rotation time. Hence disk seek times can never get below 2 milliseconds.

Ich verstehe zwar, dass diese Steigerung im Vergleich zu den Fortschritten so ziemlich aller anderen Computerkomponenten marginal erscheint (Moore's Law und dergleichen), halte es aber immer noch für extrem eindrucksvoll, dass man Hardware überhaupt so schnell bewegen kann.