教你如何成為Oracle 10g OCP - 第九章 物件管理(10) - 點陣圖(Bitmap)索引

9.2.6  點陣圖(Bitmap)索引

建立bitmap索引的語法：
create bitmap index idx_emp_gender on emp(gender) ;

點陣圖索引是另外一個索引型別，組織形式和b-tree索引相同，也是一顆平衡樹，區別
在於葉子節點裡存放索引條目的方式不同。前面知道，B樹索引的葉子節點對於表裡
的每個資料行，如果索引列的值不為空，則會為這個記錄行在葉子節點中維護一個
對應的索引條目。 而點陣圖索引則不是這樣，葉子節點中存放的索引條目如下：
http://space.itpub.net/9842/viewspace-343286

點陣圖索引非常適用於DSS及資料倉儲系統，他們可以使用較少的基數(唯一鍵數目，
比如男和女，基數為2)列，訪問非常大的表，儘管點陣圖索引最多可以達到30個列，
但是一般都只用於少量的列。對於那些有較低基數的列要使用點陣圖索引。點陣圖索引
對於低基數(少量不同值)的列來說非常快，這是因為索引的大小相對於B樹索引來說
小很多，因為這些索引是低基數的B樹索引。

在表上放置單獨的點陣圖索引意義不大，點陣圖索引的作用來源於與許多其他點陣圖索引
的結合，只有當我們執行布林運算AND和OR的時候，它們才開始為我們產生效益，即
只有許多列具有索引的時候，使用者才能夠有效的利用它們。由於Bitmap索引首先不
應該在有大量DML表中使用(高併發)，所以不會因為有多個Bitmap索引帶來很大的性
能問題 。點陣圖索引僅僅存在於Oracle企業版本中。

 

---------------------------------------------------------- 
注意：讀這一節之前，需要複習一下rowid的結構。
例子：  OOOOOOFFFBBBBBBRRR

其中：
OOOOOO： 六位表示data object id，根據data object id可以確定segment。
FFF：  三位表示相對檔案號。根據該相對檔案號可以得到絕對檔案號，從而確定datafile。
BBBBBB：六位表示data block number。這裡的data block number是相對於datafile
的編號，而不是相對於tablespace的編號。
RRR：三位表示row number(行號), 這裡的行號是表示行在block中儲存的位置，
它與rownum是完全不同的兩個概念。行號越大的記錄在block中存放的位置越靠後，
但並非越晚插入的記錄行號越大。oracle在一個block中插入資料時，總是優先考
慮block前面的空閒空間的。

---------------------------------------------------------- 

 

bitmap聯合索引及單獨索引 ---

那麼如果是WHERE後查詢有多個條件都適合建立bitmap索引，是建立聯合索引好呢，
還是建立單獨索引好 ？ 

比如A有x種值，B有y種值，C有z種值, 空間使用量大致應該是
聯合索引 ： x*y*z
單獨索引 ： x+y+z
一般來講第一種空間會大一些。從空間上來講，用單獨索引要要一些。

從bitmap的特性來說，對於A B C的聯合查詢仍然建議建立3個單獨的點陣圖索引。
因為點陣圖索引的BITMAP AND/OR效率很高，不需要去建立聯合索引提升效率，建
立複合索引意味著你人為構造一個選擇度很高的鍵值，這與BITMAP索引的特性
衝突。

 

點陣圖索引的結構 ---

在表上建立索引後，葉子節點的點陣圖索引條目中還包含表裡第一條記錄所對應的rowid
及最後一條記錄所對應的rowid, 所以條目的最後一部分則是由多個bit位組成的bitmap,
每個bit位就對應一條記錄。 格式：

bitmap葉子節點的結構大概如下(有多少記錄，bitmap就有多少位)：


.....

例如：
Key     start_rowid    end_rowid      理論上的bitmap        轉儲檔案的bitmap
<01     00c01ce4.0    00c01ce4.0017   00100110000110000010 >    ca 64 18 04
<02     00c01ce4.0    00c01ce4.0017   01000001010000110100 >    ca 82 c2 02
<03     00c01ce4.0    00c01ce4.0017   10011000101001001001 >    ca 19 25 09

其實dump出來的start-rowid及end-rowid分別如 srid=00c01ce4.0  erid=00c01ce4.17，
左邊的表示檔案號（從左邊第一個位元組開始的4個位元組表示）和資料塊號（從左邊第五個
位元組開始的4個位元組表示），點右邊表示資料塊裡的行號。

bitmap字元長度與start-rowid和end-rowid之間包含的rowid數量(表的資料行)一
樣，每個塊的大小是8192*8bit*90%*87%(如果資料塊大小為8K，pctfree=10%, 塊
頭部佔用一些)，那麼一個bitmap索引的葉子節點大概能索引51314 條記錄。

bitmap中沒有直接記錄rowid, 而是記錄了一個點陣圖, 點陣圖中的每一個bit位都對應一
個rowid, 之間有轉換關係的.所以用bitmap索引的時候你可以在執行計劃裡看到 bitmap
to rowid 這樣的步驟.

當我們發出where c1='01'這樣的SQL時，oracle會搜尋01所在的索引條目，然後
掃描該索引條目中的bitmap裡的所有bit位。第一個bit位是1，表示第一條上的c1
的值是01，於是返回第一條記錄所在的rowid(根據該索引條目裡記錄的start rowid
加上行號得到該記錄所在的rowid), 第二個bit位為0, 則說明第二條記錄上的c1值不
為01,依此類推。特別注意，如果索引列為空，也會在點陣圖索引中記錄，對應的bit
位為0 ；

如果索引列上不同值的個數比較少的時候，比如對於性別列（男或女）等，則使用位
圖索引會比較好，因為它對空間的佔用非常少(因為都是用bit位來表示表裡的資料行)，
從而在掃描索引的時候，掃描的索引塊的個數也比較少。可以試想一下，如果在列的
不同值非常多的列上，比如主鍵列上，建立點陣圖索引，則產生的索引條目就等於表裡
記錄的條數，同時每個索引條目裡的bitmap裡，只有一個1，其它都是0。這樣還不如
B樹索引的效率高。

如果被索引的列經常被更新的話，則不適合使用點陣圖索引。當更新點陣圖索引所在的
列時，由於要在不同的索引條目之間修改bit位，比如將第一條記錄從01變為02，
則必須將01所在的索引條目的第一個bit位改為0，再將02所在的索引條目的第一個
bit位改為1。因此，在更新索引條目的過程中，會鎖定點陣圖索引裡多個索引條目。
也就是同時只能有一個使用者能夠更新表T，從而降低了併發性。

所以點陣圖索引比較適合用在資料倉儲系統裡，不適合用在OLTP系統裡。

 

真值表 ---

在建立bitmap索引的時候，整個表上會有DML鎖定。Oracle會為索引欄位列中遇到
的各個值構建 "真值表" ，比如下面的表：

ID    Mannager    Dept   Gender   etc...
----------------------------------------------
70      QS         10       M
10      RW         10       F
30      RW         30       M
50      QS         20       F
40      QS         10       M
30      RW         30       M
50      RW         20       F
60      QS         10       M 

 

在Mananger上的真值表如下：

QS      RW    對應emp表中的ID列
--------------------------------------
1 0 70
0 1 10
0 1 30
1 0 50
1 0 40
0 1 30
0 1 50
1 0 60

在Gender上的真值表如下：

M       F    對應emp表中的ID列
--------------------------------------
1 0 70
0 1 10
1 0 30
0 1 50
1 0 40
1 0 30
0 1 50
1 0 60

 

bitmap index的使用 --- 

select * from emp where gender='F' and mananger='RW' ;
當做以上語句查詢時，最佳化器會從gender點陣圖索引中獲取F點陣圖，從Manager
點陣圖索引中獲取RW點陣圖，讓後再在他們上面執行AND操作，具體過程如下：

F 01010010
RW 01100110
------------------------------
AND 01000010 (結果) 

根據該索引條目裡記錄的start rowid加上行號(塊中行號)得到該記錄所在的rowid，
然後可以找到這些記錄，

 

 

Dump 點陣圖索引 --- 

SQL> create table t_bitmap_test as
  2  select rownum as id,trunc(dbms_random.value(1,4)) as bitcol
  3  from dba_objects where rownum<=20;
SQL> select * from t_bitmap_test;

        ID     BITCOL
---------- ----------
         1          3
         2          2
         3          1
         4          3
         5          3
         6          1
         7          1
         8          2
         9          3
        10          2
        11          3
        12          1
        13          1
        14          3
        15          2
        16          2
        17          3
        18          2
        19          1
        20          3

SQL> connect hr/hr
已連線。
SQL> alter session set events '10608 trace name context forever, level 10';
會話已更改。

SQL> create bitmap index idx_t_bitmap_test on t_bitmap_test(bitcol);
索引已建立。

SQL> alter session set events '10608 trace name context off';
會話已更改。

SQL> select object_id from user_objects where object_name='IDX_T_BITMAP_TEST';

 OBJECT_ID
----------
     24499

SQL> alter session set events 'immediate trace name TREEDUMP level 24499';
會話已更改。

10608事件用來跟蹤建立bitmap索引的過程。而TREEDUMP則用來轉儲索引的
樹狀結構。開啟轉儲出來的檔案：

*** SESSION ID:(7.13) 2008-06-10 14:33:46.000
qerbiARwo: bitmap size is 8168
qerbiIPI default pctfree=10
qerbiIPI length=0
qerbiAllocate pfree=127 space=8168
qerbiStart first start
qerbiRop: rid=00c01ce4.0000, new=Y , key: (2):  c1 04
qerbiCmpSz notfound pctfree=10
qerbiCmpSz adjblksize=7351 length=0
qerbiRop keysize=4 maxbm=3531
kdibcoinit(3116714): srid=00c01ce4.0000
qerbiRop: rid=00c01ce4.0001, new=Y , key: (2):  c1 03
kdibcoinit(3116698): srid=00c01ce4.0001
qerbiRop: rid=00c01ce4.0002, new=Y , key: (2):  c1 02
kdibcoinit(311661c): srid=00c01ce4.0002
qerbiRop: rid=00c01ce4.0003, new=N, key: (2):  c1 04
qerbiRop: rid=00c01ce4.0004, new=N, key: (2):  c1 04
qerbiRop: rid=00c01ce4.0005, new=N, key: (2):  c1 02
qerbiRop: rid=00c01ce4.0006, new=N, key: (2):  c1 02
qerbiRop: rid=00c01ce4.0007, new=N, key: (2):  c1 03
qerbiRop: rid=00c01ce4.0008, new=N, key: (2):  c1 04
qerbiRop: rid=00c01ce4.0009, new=N, key: (2):  c1 03
qerbiRop: rid=00c01ce4.000a, new=N, key: (2):  c1 04
qerbiRop: rid=00c01ce4.000b, new=N, key: (2):  c1 02
qerbiRop: rid=00c01ce4.000c, new=N, key: (2):  c1 02
qerbiRop: rid=00c01ce4.000d, new=N, key: (2):  c1 04
qerbiRop: rid=00c01ce4.000e, new=N, key: (2):  c1 03
qerbiRop: rid=00c01ce4.000f, new=N, key: (2):  c1 03
qerbiRop: rid=00c01ce4.0010, new=N, key: (2):  c1 04
qerbiRop: rid=00c01ce4.0011, new=N, key: (2):  c1 03
qerbiRop: rid=00c01ce4.0012, new=N, key: (2):  c1 02
qerbiRop: rid=00c01ce4.0013, new=N, key: (2):  c1 04
kdibcoend(3116714): erid=00c01ce4.0017status=3
qerbiCon: key: (2):  c1 04
 srid=00c01ce4.0 erid=00c01ce4.17 bitmap: (4):  ca 19 25 09
kdibcoend(3116698): erid=00c01ce4.0017status=3
qerbiCon: key: (2):  c1 03
 srid=00c01ce4.0 erid=00c01ce4.17 bitmap: (4):  ca 82 c2 02
kdibcoend(311661c): erid=00c01ce4.0017status=3
qerbiCon: key: (2):  c1 02
 srid=00c01ce4.0 erid=00c01ce4.17 bitmap: (4):  ca 64 18 04

這一段是建立bitmap索引的過程。我們先把被索引的列的值換算成十六進位制：

SQL> select dump(3),dump(2),dump(1) from dual;

DUMP(3)            DUMP(2)            DUMP(1)
------------------ ------------------ ------------------
Typ=2 Len=2: 193,4 Typ=2 Len=2: 193,3 Typ=2 Len=2: 193,2

4、3、2對應的十六進位制則是04、03、02。也就是上面轉儲部分顯示的key部
分的鍵值。可以看到，oracle在建立bitmap索引時，先從第一條記錄開始掃
描，取出第一條記錄的鍵值（bitcol=3），也就是“qerbiRop: rid=00c01ce4.0000,
new=Y , key: (2):  c1 04”。new=Y說明這是一個新的鍵值，因此會對應到
一個索引條目。掃描第二條記錄時，發現bitcol=2，該鍵值也是一個新的鍵值，
因此產生一個新的索引條目，對應“qerbiRop: rid=00c01ce4.0001, new=Y ,
key: (2):  c1 03”。掃描到第三條記錄時，發現bitcol=1，該鍵值也是一個
新的鍵值，因此產生第三個索引條目，對應“qerbiRop: rid=00c01ce4.0002,
new=Y , key: (2):  c1 02”。接下來掃描到的記錄所對應的bitcol的值都是
1、2、3中的一個，因此都不會產生新的索引條目，因此它們的new都為N。

然後掃描完表裡的所有記錄以後，開始建立bitmap索引條目，也就是下面的部分：
qerbiCon: key: (2):  c1 04
 srid=00c01ce4.0 erid=00c01ce4.17 bitmap: (4):  ca 19 25 09
kdibcoend(3116698): erid=00c01ce4.0017status=3
qerbiCon: key: (2):  c1 03
 srid=00c01ce4.0 erid=00c01ce4.17 bitmap: (4):  ca 82 c2 02
kdibcoend(311661c): erid=00c01ce4.0017status=3
qerbiCon: key: (2):  c1 02
 srid=00c01ce4.0 erid=00c01ce4.17 bitmap: (4):  ca 64 18 04

這裡的srid表示start rowid，erid表示end rowid。
可以看到總共產生了3個索引條目，其key分別為：04、03、02。

這3個索引條目的start rowid和end rowid的格式分兩部分，中間用點隔開，點
左邊的表示檔案號（從左邊第一個位元組開始的4個位元組表示）和資料塊號（從左邊
第五個位元組開始的4個位元組表示），點右邊表示資料塊裡的行號。這裡的顯示可以
看到，這20條記錄都位於相同的資料塊裡。這裡的00c0表示檔案號：

SQL> select sys.pkg_number_trans.f_hex_to_dec('c')/4 file# FROM dual;

     FILE#
----------
         3

SQL> select sys.pkg_number_trans.f_hex_to_dec('1ce4') as blk# FROM dual;

BLK#
----------------------
7396

因此這20條記錄在3號資料檔案的7396號資料塊裡。我們可以使用dbms_rowid來驗證。

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) as file#,
  2  dbms_rowid.rowid_block_number(rowid) as block#
  3  from t_bitmap_test;

     FILE#     BLOCK#
---------- ----------
       3    7396

可以看到，完全符合。

每個索引條目的“bitmap : (4)”部分表示的也就是前面說到的bit位了，由1、0組成。
按照前面bitmap的理論，這20條記錄所對應的三個索引條目的bitmap的樣子應該為：

Key_value    start_rowid    end_rowid      理論上的bitmap       轉儲檔案的bitmap
1          00c01ce4.0    00c01ce4.0017   00100110000110000010  ca 64 18 04
2          00c01ce4.0    00c01ce4.0017   01000001010000110100  ca 82 c2 02
3          00c01ce4.0    00c01ce4.0017   10011000101001001001  ca 19 25 09

轉儲檔案裡的bitmap如何對應到bit位呢 ？首先第一個位元組的ca可以不考慮，暫時不知
道第一個位元組是什麼意思。只考慮後三個位元組。比如對於key_value=3來說，19，25，09
對應的二進位制為：

SQL> col c1 format a10
SQL> col c2 format a10
SQL> col c3 format a10
SQL> select sys.pkg_number_trans.f_hex_to_bin(19) as c1,
  2  sys.pkg_number_trans.f_hex_to_bin(25) as c2,
  3  sys.pkg_number_trans.f_hex_to_bin(09) as c3 from dual;

C1         C2         C3
---------- ---------- ----------
11001      100101     1001
2
其中不足8位的前面用0補齊，因此，C1=00011001，C2=00100101，C3=00001001
在二進位制裡，每個應該倒過來，從右到左排列，因此為：

C3         C2        C1
00001001   00100101   00011001

然後將它們組織為一個由多個bit位組成的bitmap的話，仍然從右到左，
依次取出每個bit位，於是我們有：100110001010010010010000。我們
可以把這個bit串與理論上的bitmap比較一下：
100110001010010010010000
10011000101001001001
很明顯，除了最後多出來的4個0以外，其餘部分完全一致。而最後多出
的0並不影響這個索引條目的使用。

類似的，我們可以使用相同的方法來依次驗證另外兩個鍵值，都是符合理論值的。
資料都在一個資料塊裡的情況比較容易理解。如果被索引的資料分佈在多個資料
塊裡呢？

 

經常會有人問到，只記錄start rowid和end rowid，如果被索引的記錄分佈在多
個資料塊裡，那麼oracle如何根據start rowid來找到bitmap裡的bit=1所對應的
記錄的rowid呢？

建立一個表：
SQL> create table t_bitmap_2(id number,bitcol char(2000));
insert into t_bitmap_2 values(1,'A');
insert into t_bitmap_2 values(2,'A');
insert into t_bitmap_2 values(3,'A');
insert into t_bitmap_2 values(4,'B');
insert into t_bitmap_2 values(5,'A');
insert into t_bitmap_2 values(6,'A');
insert into t_bitmap_2 values(7,'B');
insert into t_bitmap_2 values(8,'A');
insert into t_bitmap_2 values(9,'A');
insert into t_bitmap_2 values(9,'A');
insert into t_bitmap_2 values(10,'B');
insert into t_bitmap_2 values(11,'B');
insert into t_bitmap_2 values(12,'B');
insert into t_bitmap_2 values(13,'B');
insert into t_bitmap_2 values(14,'B');
insert into t_bitmap_2 values(15,'B');
COMMIT;

獲得這15條記錄所在的資料塊號：

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) as file#,
  2  dbms_rowid.rowid_block_number(rowid) as block#
  3  from t_bitmap_2;

     FILE#     BLOCK#
---------- ----------
         3       7428
         3       7429
         3       7430
         3       7431
         3       7432
         3       7433

可以知道，這15條記錄分佈在6個資料塊裡。
然後跟蹤對於bitcol列上的bitmap索引的建立過程：

alter session set events '10608 trace name context forever, level 10';
create bitmap index idx_t_bitmap_2 on t_bitmap_2(bitcol);
alter session set events '10608 trace name context off';

從轉儲出來的檔案可以看到，最終形成了兩個索引條目：
……
qerbiCon: key: (2000):
 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
……
 srid=00c01d04.0 erid=00c01d08.7 bitmap: (11):  c8 06 c0 44 f8 b3 01 07 f8 56 06
……
qerbiCon: key: (2000):
 42 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
……
srid=00c01d04.0 erid=00c01d09.7 bitmap: (12):  00 f8 56 06 f8 56 07 c0 a1 01 c0 44
*** 2008-06-10 11:21:08.000

很明顯，這裡的兩個索引條目的start rowid和end rowid是不相同的。
鍵值為A的索引條目為：
srid=00c01d04.0 erid=00c01d08.7 bitmap: (11):  c8 06 c0 44 f8 b3 01 07 f8 56 06
其資料塊從1d04到1d08，也就是：

SQL> select sys.pkg_number_trans.f_hex_to_dec('1d04') as s_blk#,
  2  sys.pkg_number_trans.f_hex_to_dec('1d08') as e_blk#
  3  from dual;

S_BLK#     E_BLK#
---------- ----------
7428    7432

而鍵值B的索引條目為：
srid=00c01d04.0 erid=00c01d09.7 bitmap: (12):  00 f8 56 06 f8 56 07 c0 a1 01 c0 44
其資料塊從1d04到1d09，也就是：

SQL> select sys.pkg_number_trans.f_hex_to_dec('1d04') as s_blk#,
  2  sys.pkg_number_trans.f_hex_to_dec('1d09') as e_blk#
  3  from dual;

S_BLK#     E_BLK#
---------- ----------
7428       7433

這個時候，
SQL> select substr(bitcol,1,1) as bitcol,dbms_rowid.rowid_block_number(rowid)
as block# from t_bitmap_2;

BI     BLOCK#
-- ----------
B        7428
A        7428
A        7428
A        7429
B        7429
B        7429
B        7430
B        7430
B        7430
A        7431
A        7431
A        7431
B        7432
A        7432
A        7432
B        7433

這時，oracle放了很多的bit來對應這15條記錄，但是oracle如何根據這些bit位來找
對應的rowid就猜不出了。