位圖索引與B-tree 索引

1、DBA：性能與可用性位圖索引與 B-tree 索引：選擇與時間Vivek Sharma了解每個索引的正確應(yīng)用對性能會有很大影響。一般認(rèn)為，位圖索引最適合于具有低相異值的列（如 GENDER、MARITAL_STATUS 和 RELATION）。然合，這種觀點并不完全正確。實際上，對于那些并不經(jīng)常由許多并行系統(tǒng)更新其數(shù)據(jù)的系統(tǒng)來說，總是建議采用位圖索引。事實上，正如我此處所闡明的，在具有 100% 唯一值（主鍵的列候選鍵）的列上的位圖索引與 B-tree 索引同樣有效。在本文中，我將提供一些示例和優(yōu)化程序決策，它們對于低基數(shù)列和高基數(shù)列上的索引類型是通用的。這些示例將幫助 DBA 了解位圖索引的

2、使用實際上并不依賴于基數(shù)，而是依賴于應(yīng)用程序。比較索引在唯一列上使用位圖索引（ Oracle 不建議這種方法）有幾個缺點，其中一個對充足空間的需求。然而，位圖索引的大小依賴于列的基數(shù)，位圖索引是在該列上創(chuàng)建,同時進(jìn)行數(shù)據(jù)分配。因此，GENDER 列上的位圖索引將小于相同列上的 B-tree 索引。相反，EMPNO（主鍵的候選鍵）上的位圖索引將比此列上的 B-tree 索引大得多。但是因為訪問決策支持系統(tǒng) (DSS 的用戶比訪問交易處理 (OLTP 系統(tǒng)的用戶要少，所以資源對于這些應(yīng)用程序不是問題。為演示這一點，我創(chuàng)建了兩個表 TEST_NORMAL 和 TEST_RANDOM。我使用 PL/S

3、QL 塊在 TEST_NORMAL 表中插入一百萬行，然后隨機在 TEST_RANDOM 表中插入這些行：Create table test_normal (empno number(10, ename varchar2(30, sal number(10;BeginFor i in 1.1000000LoopInsert into test_normal values(i, dbms_random.string('U',30, dbms_random.value(1000,7000;If mod(i, 10000 = 0 thenCommit;End if;End loop;

4、End;/Create table test_random as select /*+ append */ * from test_normal order by dbms_random.random;SQL> select count(* "Total Rows" from test_normal;Total Rows-1000000Elapsed: 00:00:01.09SQL> select count(distinct empno "Distinct Values" from test_normal;Distinct Values-1

5、000000Elapsed: 00:00:06.09SQL> select count(* "Total Rows" from test_random;Total Rows-1000000Elapsed: 00:00:03.05SQL> select count(distinct empno "Distinct Values" from test_random;Distinct Values-1000000Elapsed: 00:00:12.07注意 TEST_NORMAL 是條理的，而 TEST_RANDOM 表是隨機創(chuàng)建的，因此 TEST

6、_RANDOM 表的數(shù)據(jù)是無條理的。在上面的表中，列 EMPNO 包含完全相異的值，是很好的一個候選主鍵。如果您將此列定為主鍵，則您將創(chuàng)建一個 B-tree 索引而非位圖索引，因為 Oracle 不支持位圖主鍵索引。要分析這些索引的行為，我們將執(zhí)行下列步驟：1. 在 TEST_NORMAL 上：A. 在 EMPNO 列上創(chuàng)建一個位圖索引，并使用等式謂詞執(zhí)行某些查詢。B. 在 EMPNO 列上創(chuàng)建一個 B-tree 索引，使用等式謂詞執(zhí)行某些查詢，并比較由查詢做出的邏輯和物理輸入/輸出，以取出不同值集的結(jié)果。2. 在 TEST_RANDOM 上：A. 與步驟 1A 相同。B. 與步驟 1B 相同

7、。3. 在 TEST_NORMAL 上：A. 與步驟 1A 相同，除了查詢是使用在一定范圍的謂詞執(zhí)行之外。B. 與步驟 1B 相同，除了查詢是使用一定范圍的謂詞執(zhí)行之外?，F(xiàn)在比較統(tǒng)計值。4. 在 TEST_RANDOM 上：A. 與步驟 3A 相同。B. 與步驟 3B 相同。5. 在 TEST_NORMAL 上：A. 在 SAL 列上創(chuàng)建一個位圖索引，然后使用等式謂詞執(zhí)行某些查詢，及使用范圍謂詞執(zhí)行某些查詢。B. 在 SAL 列上創(chuàng)建一個 B-tree 索引，然后使用等式謂詞執(zhí)行某些查詢，和使用范圍謂詞（與步驟 5A 中相同的某些值集）執(zhí)行某些查詢。比較由查詢完成的輸入/輸出以取出結(jié)果。6.

8、將 GENDER 列添加到全部兩個表中，并使用三個可能值更新列：M 代表男，F(xiàn) 代表女，null 代表不適用。基于一定條件使用這些值對列進(jìn)行更新。7. 在此列上創(chuàng)建一個位圖索引，并使用等式謂詞執(zhí)行某些查詢。8. 在 GENDER 列上創(chuàng)建一個 B-tree 索引，并使用等式謂詞執(zhí)行某些查詢。與步驟 7 的結(jié)果比較。步驟 1 到步驟 4 涉及一個高基數(shù)（100% 不同）列，步驟 5 涉及一個普通基數(shù)列，而步驟 7 和步驟 8 涉及一個低基數(shù)列。步驟 1A（在 TEST_NORMAL 上）在此步驟中，我們將在 TEST_NORMAL 表上創(chuàng)建一個位圖索引，然后檢查此索

9、引的大小、其群集因子和表的大小。然后我們將使用等式謂詞運行某些查詢，并使用此位圖索引注明這些查詢的輸入/輸出。SQL> create bitmap index normal_empno_bmx on test_normal(empno;Index created.Elapsed: 00:00:29.06SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;Table analyzed.Elapsed: 00:00:19.01SQL>

10、 select substr(segment_name,1,30 segment_name, bytes/1024/1024 "Size in MB"2 from user_segments3* where segment_name in ('TEST_NORMAL','NORMAL_EMPNO_BMX'SEGMENT_NAME Size in MB- -TEST_NORMAL 50NORMAL_EMPNO_BMX 28Elapsed: 00:00:02.00SQL> select index_name, clustering_fact

11、or from user_indexes;INDEX_NAME CLUSTERING_FACTOR- -NORMAL_EMPNO_BMX 1000000Elapsed: 00:00:00.00您會看到在前面表中索引的大小是 28 MB，而群集因子等于表中的行數(shù)?，F(xiàn)在讓我們使用等式謂詞針對不同值集執(zhí)行查詢：SQL> set autotrace onlySQL> select * from test_normal where empno=&empno;Enter value for empno: 1000old 1:select * from test_normal where

12、 empno=&empnonew 1:select * from test_normal where empno=1000Elapsed: 00:00:00.01Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=341 0 TABLE ACCESS (BY INDEX ROWID OF 'TEST_NORMAL' (Cost=4 Card=1 Bytes=342 1 BITMAP CONVERSION (TO ROWIDS3 2 BITMAP INDEX (SINGLE VAL

13、UE OF 'NORMAL_EMPNO_BMX'Statistics-0 recursive calls0 db block gets5 consistent gets0 physical reads0 redo size515 bytes sent via SQL*Net to client499 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk1 rows processed步驟 1B（在 TEST_NORMAL 上）現(xiàn)在

14、我們將丟棄此位圖索引并在 EMPNO 列上創(chuàng)建一個 B-tree 索引。同以前一樣，我們將檢查索引的大小及其群集因子并針對相同的值集執(zhí)行相同的查詢，以比較輸入/輸出。SQL> drop index NORMAL_EMPNO_BMX;Index dropped.SQL> create index normal_empno_idx on test_normal(empno;Index created.SQL> analyze table test_normal compute statistics for table for all indexes for all indexed

15、 columns;Table analyzed.SQL> select substr(segment_name,1,30 segment_name, bytes/1024/1024 "Size in MB"2 from user_segments3 where segment_name in ('TEST_NORMAL','NORMAL_EMPNO_IDX'SEGMENT_NAME Size in MB- -TEST_NORMAL 50NORMAL_EMPNO_IDX 18SQL> select index_name, clust

16、ering_factor from user_indexes;INDEX_NAME CLUSTERING_FACTOR- -NORMAL_EMPNO_IDX 6210很清楚在此表中，B-tree 索引比 EMPNO 列上的位圖索引更小。B-tree 索引的群集因子更加接近表中的塊數(shù)；由于這一原因，B-tree 索引對于范圍謂詞查詢效率更佳。現(xiàn)在我們將使用我們的 B-tree 索引針對相同的值集運行相同的查詢。SQL> set autot traceSQL> select * from test_normal where empno=&empno;Enter value fo

17、r empno: 1000old 1:select * from test_normal where empno=&empnonew 1:select * from test_normal where empno=1000Elapsed: 00:00:00.01Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=341 0 TABLE ACCESS (BY INDEX ROWID OF 'TEST_NORMAL' (Cost=4 Card=1 Bytes=342 1 INDEX

18、(RANGE SCAN OF 'NORMAL_EMPNO_IDX' (NON-UNIQUE (Cost=3 Card=1Statistics-29 recursive calls0 db block gets5 consistent gets0 physical reads0 redo size515 bytes sent via SQL*Net to client499 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk1 r

19、ows processed正如您可以看到的那樣，針對不同的值集執(zhí)行查詢時，對于在完全唯一列上的位圖和 B-tree 索引，一致性讀請求和物理讀取的數(shù)量是相同的。位圖EMPNOB-TREE一致性讀數(shù)物理讀數(shù)一致性讀數(shù)物理讀數(shù)50100050522398525285455252980085252853425252128444525285852步驟 2A（在 TEST_RANDOM 上）現(xiàn)在我們將在 TEST_RANDOM 上執(zhí)行相同的實驗：SQL> create bitmap index random_empno_bmx on test_random(empno;Index created.

20、SQL> analyze table test_random compute statistics for table for all indexes for all indexed columns;Table analyzed.SQL> select substr(segment_name,1,30 segment_name, bytes/1024/1024 "Size in MB"2 from user_segments3* where segment_name in ('TEST_RANDOM','RANDOM_EMPNO_BMX&

21、#39;SEGMENT_NAME Size in MB- -TEST_RANDOM 50RANDOM_EMPNO_BMX 28SQL> select index_name, clustering_factor from user_indexes;INDEX_NAME CLUSTERING_FACTOR- -RANDOM_EMPNO_BMX 1000000再次，統(tǒng)計值（大小和群集因子）對于 TEST_NORMAL 表上的那些索引是相同的。SQL> select * from test_random where empno=&empno;Enter value for empn

22、o: 1000old 1:select * from test_random where empno=&empnonew 1:select * from test_random where empno=1000Elapsed: 00:00:00.01Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=341 0 TABLE ACCESS (BY INDEX ROWID OF 'TEST_RANDOM' (Cost=4 Card=1 Bytes=342 1 BITMAP CONVE

23、RSION (TO ROWIDS3 2 BITMAP INDEX (SINGLE VALUE OF 'RANDOM_EMPNO_BMX'Statistics-0 recursive calls0 db block gets5 consistent gets0 physical reads0 redo size515 bytes sent via SQL*Net to client499 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (

24、disk1 rows processed步驟 2B（在 TEST_RANDOM 上）現(xiàn)在，與步驟 1B 中一樣，我們將丟棄該位圖索引并在 EMPNO 列上創(chuàng)建一個 B-tree 索引。SQL> drop index RANDOM_EMPNO_BMX;Index dropped.SQL> create index random_empno_idx on test_random(empno;Index created.SQL> analyze table test_random compute statistics for table for all indexes for al

25、l indexed columns;Table analyzed.SQL> select substr(segment_name,1,30 segment_name, bytes/1024/1024 "Size in MB"2 from user_segments3 where segment_name in ('TEST_RANDOM','RANDOM_EMPNO_IDX'SEGMENT_NAME Size in MB- -TEST_RANDOM 50RANDOM_EMPNO_IDX 18SQL> select index_na

26、me, clustering_factor from user_indexes;INDEX_NAME CLUSTERING_FACTOR- -RANDOM_EMPNO_IDX 999830此表顯示此索引的大小等于其在 TEST_NORMAL 表上的大小，但群集因子更加接近行數(shù)，這使得此索引對于范圍謂詞查詢無效（我們將在步驟 4 中看到這種情況）。此群集因子將不會影響等式謂詞查詢，因為行具有全異的值，且每個鍵的行數(shù)為 1。現(xiàn)在讓我們來運行具有等式謂詞和相同值集的查詢。SQL> select * from test_random where empno=&empno;Enter va

27、lue for empno: 1000old 1:select * from test_random where empno=&empnonew 1:select * from test_random where empno=1000Elapsed: 00:00:00.01Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=341 0 TABLE ACCESS (BY INDEX ROWID OF 'TEST_RANDOM' (Cost=4 Card=1 Bytes=342 1

28、INDEX (RANGE SCAN OF 'RANDOM_EMPNO_IDX' (NON-UNIQUE (Cost=3 Card=1Statistics-0 recursive calls0 db block gets5 consistent gets0 physical reads0 redo size515 bytes sent via SQL*Net to client499 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (di

29、sk1 rows processed同樣，結(jié)果幾乎與步驟 1A 和 1B 中的的結(jié)果一樣。數(shù)據(jù)分配不影響唯一列的一致性讀請求和物理讀取的數(shù)量。步驟 3A（在 TEST_NORMAL 上）在這一步中，我們將創(chuàng)建位圖索引（與步驟 1A 相似）。我們知道索引的大小和群集因子，它等于表中的行數(shù)。現(xiàn)在讓我們使用范圍謂詞運行某些查詢。SQL> select * from test_normal where empno between &range1 and &range2;Enter value for range1: 1Enter value for range2: 2300old

30、 1:select * from test_normal where empno between &range1 and &range2new 1:select * from test_normal where empno between 1 and 23002300 rows selected.Elapsed: 00:00:00.03Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=451 Card=2299 Bytes=781661 0 TABLE ACCESS (BY INDEX ROWID OF '

31、TEST_NORMAL' (Cost=451 Card=2299 Bytes=781662 1 BITMAP CONVERSION (TO ROWIDS3 2 BITMAP INDEX (RANGE SCAN OF 'NORMAL_EMPNO_BMX'Statistics-0 recursive calls0 db block gets331 consistent gets0 physical reads0 redo size111416 bytes sent via SQL*Net to client2182 bytes received via SQL*Net fr

32、om client155 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk2300 rows processed步驟 3B（在 TEST_NORMAL 上）在這一步中，我們將對具有 B-tree 索引的 TEST_NORMAL 表執(zhí)行查詢。SQL> select * from test_normal where empno between &range1 and &range2;Enter value for range1: 1Enter value for range2: 2300old 1:se

33、lect * from test_normal where empno between &range1 and &range2new 1:select * from test_normal where empno between 1 and 23002300 rows selected.Elapsed: 00:00:00.02Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=2299 Bytes=781661 0 TABLE ACCESS (BY INDEX ROWID OF 'TEST_N

34、ORMAL' (Cost=23 Card=2299 Bytes=781662 1 INDEX (RANGE SCAN OF 'NORMAL_EMPNO_IDX' (NON-UNIQUE (Cost=8 Card=2299Statistics-0 recursive calls0 db block gets329 consistent gets15 physical reads0 redo size111416 bytes sent via SQL*Net to client2182 bytes received via SQL*Net from client155 SQ

35、L*Net roundtrips to/from client0 sorts (memory0 sorts (disk2300 rows processed為不同的范圍集執(zhí)行這些查詢時，結(jié)果會顯示如下：位圖EMPNO（范圍）B-TREE一致讀數(shù)物理讀數(shù)一致讀數(shù)物理讀數(shù)33101-2300329028508-19802830346191850-4250344164273128888-31850424283712782900-85478367232157149984888-1000000213935您會發(fā)現(xiàn)，這一次兩個索引的一致讀數(shù)和物理讀數(shù)又幾乎一樣。最后一個范圍 (984888-1000000

36、 幾乎返回 15,000 行，這是針對上面給出的所有范圍所取得的行的最大數(shù)量。因此，我們請求了全表掃描（通過給出提示 /*+ full(test_normal */），一致性讀數(shù)和物理讀數(shù)的分別為 7,239 和 5,663。步驟 4A（在 TEST_RANDOM 上）在這一步中，我們將在具有位圖索引的 TEST_RANDOM 表上使用范圍謂詞運行查詢，并檢查一致性讀數(shù)和物理讀數(shù)。此處您將看到群集因子的影響。SQL>select * from test_random where empno between &range1 and &range2;Enter va

37、lue for range1: 1Enter value for range2: 2300old 1:select * from test_random where empno between &range1 and &range2new 1:select * from test_random where empno between 1 and 23002300 rows selected.Elapsed: 00:00:08.01Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=453 Card=2299 Byte

38、s=781661 0 TABLE ACCESS (BY INDEX ROWID OF 'TEST_RANDOM' (Cost=453 Card=2299 Bytes=781662 1 BITMAP CONVERSION (TO ROWIDS3 2 BITMAP INDEX (RANGE SCAN OF 'RANDOM_EMPNO_BMX'Statistics-0 recursive calls0 db block gets2463 consistent gets1200 physical reads0 redo size111416 bytes sent via

39、 SQL*Net to client2182 bytes received via SQL*Net from client155 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk2300 rows processed步驟 4B（在 TEST_RANDOM 上）在這一步中，我們將具有 B-tree 索引的 TEST_RANDOM 上執(zhí)行范圍謂詞查詢。記住，此索引的群集因子與表中的行數(shù)非常接近（且因而無效）。此處是優(yōu)化程序關(guān)于這一點的建議：SQL> select * from test_random where emp

40、no between &range1 and &range2;Enter value for range1: 1Enter value for range2: 2300old 1:select * from test_random where empno between &range1 and &range2new 1:select * from test_random where empno between 1 and 23002300 rows selected.Elapsed: 00:00:03.04Execution Plan-0 SELECT STAT

41、EMENT Optimizer=CHOOSE (Cost=613 Card=2299 Bytes=781661 0 TABLE ACCESS (FULL OF 'TEST_RANDOM' (Cost=613 Card=2299 Bytes=78166Statistics-0 recursive calls0 db block gets6415 consistent gets4910 physical reads0 redo size111416 bytes sent via SQL*Net to client2182 bytes received via SQL*Net fro

42、m client155 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk2300 rows processed此優(yōu)化程序選擇全表掃描，而不是使用索引，原因是群集因子：位圖EMPNO（范圍）B-TREE一致性讀數(shù)物理讀數(shù)一致性讀數(shù)物理讀數(shù)246312001-2300641549102114318-198063894910257211351850-4250641849093173162028888-31850645649092762135882900-854786431490972543329984888-100000072

43、544909僅對于最后的范圍 (984888-1000000，優(yōu)化程序選擇了位圖索引的全表掃描，然而，對于所有范圍，它選擇 B-tree 索引的全表掃描。群集因子是導(dǎo)致這一差異的原因：使用位圖索引生成執(zhí)行計劃時，該優(yōu)化程序不考慮群集因子的值，然而，對于 B-tree 索引，它會考慮群集因子的值。在這種情況下，位圖索引執(zhí)行會比 B-tree 索引更加有效。下面的步驟列舉關(guān)于這些索引的值得關(guān)注的更多情況。步驟 5A（在 TEST_NORMAL 上）在 TEST_NORMAL 表的 SAL 列上創(chuàng)建位圖索引。此列具有正常的基數(shù)。SQL> create bitmap index normal_s

44、al_bmx on test_normal(sal;Index created.SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;Table analyzed.現(xiàn)在讓我們來獲取索引的大小和群集因子。SQL>select substr(segment_name,1,30 segment_name, bytes/1024/1024 "Size in MB"2* from user_segments3* where se

45、gment_name in ('TEST_NORMAL','NORMAL_SAL_BMX'SEGMENT_NAME Size in MB- -TEST_NORMAL 50NORMAL_SAL_BMX 4SQL> select index_name, clustering_factor from user_indexes;INDEX_NAME CLUSTERING_FACTOR- -NORMAL_SAL_BMX 6001現(xiàn)在開始進(jìn)行查詢，首先使用等式謂詞運行它們：SQL> set autot traceSQL> select * from tes

46、t_normal where sal=&sal;Enter value for sal: 1869old 1:select * from test_normal where sal=&salnew 1:select * from test_normal where sal=1869164 rows selected.Elapsed: 00:00:00.08Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=168 Bytes=40321 0 TABLE ACCESS (BY INDEX ROWID O

47、F 'TEST_NORMAL' (Cost=39 Card=168 Bytes=40322 1 BITMAP CONVERSION (TO ROWIDS3 2 BITMAP INDEX (SINGLE VALUE OF 'NORMAL_SAL_BMX'Statistics-0 recursive calls0 db block gets165 consistent gets0 physical reads0 redo size8461 bytes sent via SQL*Net to client609 bytes received via SQL*Net f

48、rom client12 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk164 rows processed然后使用范圍謂詞：SQL> select * from test_normal where sal between &sal1 and &sal2;Enter value for sal1: 1500Enter value for sal2: 2000old 1:select * from test_normal where sal between &sal1 and &sa

49、l2new 1:select * from test_normal where sal between 1500 and 200083743 rows selected.Elapsed: 00:00:05.00Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes=20010241 0 TABLE ACCESS (FULL OF 'TEST_NORMAL' (Cost=601 Card=83376Bytes=2001024Statistics-0 recursive calls0

50、 db block gets11778 consistent gets5850 physical reads0 redo size4123553 bytes sent via SQL*Net to client61901 bytes received via SQL*Net from client5584 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk83743 rows processed現(xiàn)在丟棄位圖索引，并在 TEST_NORMAL 上創(chuàng)建一個 B-tree 索引。SQL> create index norm

51、al_sal_idx on test_normal(sal;Index created.SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;Table analyzed.查看索引和群集因子的大小。SQL> select substr(segment_name,1,30 segment_name, bytes/1024/1024 "Size in MB"2 from user_segments3 where segment_name in ('TEST_NORMAL','NORMAL_SAL_IDX'SEGMENT_NAME Size in MB- -TEST_NORMAL 50NORMAL_SAL_IDX 17SQL> select index_name, clustering_factor from user_indexes;INDEX_NAME CLUSTERING_FACTOR- -NORMAL_SAL_I