Oracle 10.2.0.4 INDEX (Primary key) versus BITMAP INDEX

[planb2000]

Hallo,

durch Zufall ist mir heute aufgefallen das ein von mir angenommenes Verhalten nicht eingetreten ist.

Es gibt eine Tabelle, dort ist ein primary key gesetzt und auf einer Spalte gibt es einen BITMAP INDEX, siehe DDL:

CREATE UNIQUE INDEX pk_idx_type
  ON tabelle (
    concrete_id
  )
/


CREATE BITMAP INDEX idx_bm_type
  ON tabelle (
    ein_attribute_bitmap
  )
/

Problem:

• Bei einem select count(*) from tabelle nimmt Oralce leider nicht wie angenommen den PK INDEX.
• Auch ein select count(concrete_id) from tabelle benutzt nicht den primary key index
• Erst als ich auf eine HINT wechsle mit SELECT /*+ index(t_alias pk_idx_type) */ COUNT (concrete_id) from tabelle t_alias benutzt den Index (was ja nicht der Weg ist um alle Einträge einer Tabelle zu zählen)

Wer kann mir sagen was hier passiert, haben BITMAP Indeces vorrang for PK?

Bin für jeden TIP dankbar,

viele Grüße

 

[MPr]

um mit Sicherheit sagen zu können, was sich der cost based optimizer da denkt, bräuchte man den execution plan der Query, aber Folgendes dürfte zutreffen:

Ein bitmap Index ist in der Regel deutlich kleiner als ein B*Tree-Index (weil ein bitmap Index - abhänig von der Clusterung der zugehörigen Werte in der Tabelle - relativ gut komprimiert werden kann).


Um die Anzahl der Sätze in der Tabelle zu ermitteln, kann der Optimizer einen INDEX FAST FULL SCAN auf einem verwendbaren Index durchführen, wobei in diesem Fall jeder Index für eine Spalte, die als NOT NULL definiert worden ist, verwendet werden kann (Spalten, die NULL-Werte enthalten, kommen nicht in Frage, da bei einspaltigen Indizes NULL-Werte nicht indiziert werden - und deshalb würde die Anzahl der Leaf-Elemente im Index nicht der Anzahl der Sätze der Tabelle entsprechen).


Für diesen INDEX FAST FULL SCAN wird der cbo den kleinsten verfügbaren Index benutzen.

Dazu ein kleiner Test:

-- Anlage einer Testtabelle mit zwei Spalten
create table test1
(col1 number(10) not null,
 col2 number(10));

-- Füllung mit fast 1.000.000 Sätzen
insert into test1
select rownum col1
     , mod(rownum, 100) col2
  from dual
connect by level < 1000000

-- Anlage eines PK
alter table test1 add constraint test1_pk primary key (col1);

-- Anlage eines zweiten Index auf der Spalte col2, die NULL-Werte enthalten darf
create index test1_idx1 on test1(col2);

-- Ermittlung von Statistiken
exec dbms_stats.gather_table_stats(user, 'TEST1')

-- Die Größe der Indizes
select table_name
     , index_name
     , LEAF_BLOCKS
  from user_indexes
 where table_name like 'TEST%';

TABLE_NAME                     INDEX_NAME                     LEAF_BLOCKS
------------------------------ ------------------------------ -----------
TEST1                          TEST1_PK                              1030
TEST1                          TEST1_IDX1                             962

Wie vermutet, ist der bitmap Index kleiner als der B*Tee-Index des PK. Jetzt die Testquery:

select count(*) from TEST1;

-- und der von autotrace gelieferte Plan:
--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    37  (38)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| TEST1_PK |  1012K|    37  (38)| 00:00:01 |
--------------------------------------------------------------------------


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

Der cbo verwendet hier den Index des Primary Keys, obwohl der Bitmap Index (in diesem Fall nur geringfügig) kleiner ist. Wenn ich jetzt aber die Spalte col2 als NOT NULL definiere, ändert der cbo seine Meinung:

-- NOT NULL für col2
alter table TEST1 modify col2 not null;

-- Query
select count(*) from TEST1;

-- Autotrace:
----------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    35  (40)| 00:00:01 |
|   1 |  SORT AGGREGATE       |            |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| TEST1_IDX1 |  1012K|    35  (40)| 00:00:01 |
----------------------------------------------------------------------------


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

In diesem Test ist der Unterschied nicht groß (971 statt 1041 Blockzugriffe), aber oft sind bitmap Indizes deutlich kleiner als B*Tree-Indizes, und dann ist der bitmap Index deutlich effektiver.

Im Netz findet man eine sehr interessante Präsentation von Julian Dyke, die Details zu den technischen Details der bitmap Indizes und ihrer Arbeitsweise liefert (der Link findet sich am Ende eines Blog-Eintrags, den ich damit auch noch hier unterbringe...

Nachtrag: sehr viel Wissenswertes zu bitmap (und B*Tree) Indizes findet man auch in Richard Footes (englischsprachigem) Blog

Gruß

MP

 

[MPr]

tja, das ist das Nette an Testcases: man kann ganz schnell erkennen, ob jemand Unsinn redet - so wie ich in diesem Fall...

Statt eines bitmap Index hatte ich einen zweiten B*Tree-Index angelegt und damit nur bewiesen, dass Oracle einen B*Tree-Index nur dann zum Zählen verwenden kann, wenn er als NOT NULL definiert ist, was zwar auch halbwegs interessant, aber doch eher banal ist - und außerdem nicht gefragt war.

Bei Julian Dyke habe ich außerdem noch gelesen, dass bitmap Indizes NULL-Werte ohnehin enthalten, was meine Argumentation dann auch in diesem Punkt hinfällig macht. Was bleibt ist die Feststellung, dass bitmap Indizes tatsächlich sehr viel kompakter sind als B*Tree-Indizes und zum Zählen der Sätze einer Tabelle sehr geeignet. Dazu wieder eine Test:

drop table test1;

create table test1
(col1 number(10) not null,
 col2 number(10),
 col3 varchar2(256));

-- Füllung mit 1.000.000 Sätzen
insert into test1
select rownum col1
     , mod(rownum, 100) col2
     , lpad('*', 256, '*')
  from dual
connect by level < 1000000;

-- Anlage eines PK
alter table test1 add constraint test1_pk primary key (col1);

-- Anlage eines zweiten Index auf der Spalte col2, die NULL-Werte enthalten darf
create bitmap index test1_idx1 on test1(col2);

-- Ermittlung von Statistiken
exec dbms_stats.gather_table_stats(user, 'TEST1')

-- Die Größe der Indizes
select table_name
     , index_name
     , LEAF_BLOCKS
  from user_indexes
 where table_name like 'TEST%';

TABLE_NAME                     INDEX_NAME                     LEAF_BLOCKS
------------------------------ ------------------------------ -----------
TEST1                          TEST1_PK                              2088
TEST1                          TEST1_IDX1                             400

Der bitmap Index ist also tatsächlich sehr viel kleiner als der B*Tree Index. Die zusätzliche col3 habe ich übrigens aufgenommen, weil der cbo ohne sie gar nicht auf die Idee kam, einen Index zu verwenden, sondern einen FULL TABLE SCAN durchführte. Für die testquery ergibt sich folgender Zugriff:

select count(*) from TEST1 t;

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=162 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'TEST1_PK' (INDEX (UNIQUE)) 
              (Cost=162 Card=972908)

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

Den bitmap Index verwendet die DB nur bei Verwendung eines Hints:

select /*+ index (t TEST1_IDX1) */ count(*) from TEST1 t;

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=401 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     BITMAP CONVERSION (COUNT) (Cost=401 Card=972908)
   3    2       BITMAP INDEX (FULL SCAN) OF 'TEST1_IDX1' 
                (INDEX (BITMAP))

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

Obwohl die Blockzugriffe von 2102 auf 401 sinken wählt der cbo immer noch den B*Tree-Index, weil die Kosten 162 zu 401 lauten. Warum das so ist, kann man vermutlich in Jonathan Lewis' Buch über den cbo nachlesen, aber das habe ich gerade nicht zur Hand. Jedenfalls würde ich mir keine Sorgen machen, wenn eine Count-Query via bitmap Index absolviert wird, da das in vielen Fällen das effektivste Vorgehen sein dürfte. Um das für den gegebenen Fall genau sagen zu können, bräuchte man aber den Execution Plan.

Gruß

MP