位图索引与B-tree 索引

1、DBA：性能与可用性位图索引与 B-tree 索引：选择与时间Vivek Sharma了解每个索引的正确应用对性能会有很大影响。一般认为，位图索引最适合于具有低相异值的列（如 GENDER、MARITAL_STATUS 和 RELATION）。然合，这种观点并不完全正确。实际上，对于那些并不经常由许多并行系统更新其数据的系统来说，总是建议采用位图索引。事实上，正如我此处所阐明的，在具有 100% 唯一值（主键的列候选键）的列上的位图索引与 B-tree 索引同样有效。在本文中，我将提供一些示例和优化程序决策，它们对于低基数列和高基数列上的索引类型是通用的。这些示例将帮助 DBA 了解位图索引的

2、使用实际上并不依赖于基数，而是依赖于应用程序。比较索引在唯一列上使用位图索引（ Oracle 不建议这种方法）有几个缺点，其中一个对充足空间的需求。然而，位图索引的大小依赖于列的基数，位图索引是在该列上创建,同时进行数据分配。因此，GENDER 列上的位图索引将小于相同列上的 B-tree 索引。相反，EMPNO（主键的候选键）上的位图索引将比此列上的 B-tree 索引大得多。但是因为访问决策支持系统 (DSS 的用户比访问交易处理 (OLTP 系统的用户要少，所以资源对于这些应用程序不是问题。为演示这一点，我创建了两个表 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 表是随机创建的，因此 TEST

6、_RANDOM 表的数据是无条理的。在上面的表中，列 EMPNO 包含完全相异的值，是很好的一个候选主键。如果您将此列定为主键，则您将创建一个 B-tree 索引而非位图索引，因为 Oracle 不支持位图主键索引。要分析这些索引的行为，我们将执行下列步骤：1. 在 TEST_NORMAL 上：A. 在 EMPNO 列上创建一个位图索引，并使用等式谓词执行某些查询。B. 在 EMPNO 列上创建一个 B-tree 索引，使用等式谓词执行某些查询，并比较由查询做出的逻辑和物理输入/输出，以取出不同值集的结果。2. 在 TEST_RANDOM 上：A. 与步骤 1A 相同。B. 与步骤 1B 相同

7、。3. 在 TEST_NORMAL 上：A. 与步骤 1A 相同，除了查询是使用在一定范围的谓词执行之外。B. 与步骤 1B 相同，除了查询是使用一定范围的谓词执行之外。现在比较统计值。4. 在 TEST_RANDOM 上：A. 与步骤 3A 相同。B. 与步骤 3B 相同。5. 在 TEST_NORMAL 上：A. 在 SAL 列上创建一个位图索引，然后使用等式谓词执行某些查询，及使用范围谓词执行某些查询。B. 在 SAL 列上创建一个 B-tree 索引，然后使用等式谓词执行某些查询，和使用范围谓词（与步骤 5A 中相同的某些值集）执行某些查询。比较由查询完成的输入/输出以取出结果。6.

8、将 GENDER 列添加到全部两个表中，并使用三个可能值更新列：M 代表男，F 代表女，null 代表不适用。基于一定条件使用这些值对列进行更新。7. 在此列上创建一个位图索引，并使用等式谓词执行某些查询。8. 在 GENDER 列上创建一个 B-tree 索引，并使用等式谓词执行某些查询。与步骤 7 的结果比较。步骤 1 到步骤 4 涉及一个高基数（100% 不同）列，步骤 5 涉及一个普通基数列，而步骤 7 和步骤 8 涉及一个低基数列。步骤 1A（在 TEST_NORMAL 上）在此步骤中，我们将在 TEST_NORMAL 表上创建一个位图索引，然后检查此索

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，而群集因子等于表中的行数。现在让我们使用等式谓词针对不同值集执行查询：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 上）现在

14、我们将丢弃此位图索引并在 EMPNO 列上创建一个 B-tree 索引。同以前一样，我们将检查索引的大小及其群集因子并针对相同的值集执行相同的查询，以比较输入/输出。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 索引的群集因子更加接近表中的块数；由于这一原因，B-tree 索引对于范围谓词查询效率更佳。现在我们将使用我们的 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正如您可以看到的那样，针对不同的值集执行查询时，对于在完全唯一列上的位图和 B-tree 索引，一致性读请求和物理读取的数量是相同的。位图EMPNOB-TREE一致性读数物理读数一致性读数物理读数50100050522398525285455252980085252853425252128444525285852步骤 2A（在 TEST_RANDOM 上）现在我们将在 TEST_RANDOM 上执行相同的实验：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再次，统计值（大小和群集因子）对于 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 上）现在，与步骤 1B 中一样，我们将丢弃该位图索引并在 EMPNO 列上创建一个 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 表上的大小，但群集因子更加接近行数，这使得此索引对于范围谓词查询无效（我们将在步骤 4 中看到这种情况）。此群集因子将不会影响等式谓词查询，因为行具有全异的值，且每个键的行数为 1。现在让我们来运行具有等式谓词和相同值集的查询。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同样，结果几乎与步骤 1A 和 1B 中的的结果一样。数据分配不影响唯一列的一致性读请求和物理读取的数量。步骤 3A（在 TEST_NORMAL 上）在这一步中，我们将创建位图索引（与步骤 1A 相似）。我们知道索引的大小和群集因子，它等于表中的行数。现在让我们使用范围谓词运行某些查询。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 表执行查询。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为不同的范围集执行这些查询时，结果会显示如下：位图EMPNO（范围）B-TREE一致读数物理读数一致读数物理读数33101-2300329028508-19802830346191850-4250344164273128888-31850424283712782900-85478367232157149984888-1000000213935您会发现，这一次两个索引的一致读数和物理读数又几乎一样。最后一个范围 (984888-1000000

36、 几乎返回 15,000 行，这是针对上面给出的所有范围所取得的行的最大数量。因此，我们请求了全表扫描（通过给出提示 /*+ full(test_normal */），一致性读数和物理读数的分别为 7,239 和 5,663。步骤 4A（在 TEST_RANDOM 上）在这一步中，我们将在具有位图索引的 TEST_RANDOM 表上使用范围谓词运行查询，并检查一致性读数和物理读数。此处您将看到群集因子的影响。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 上执行范围谓词查询。记住，此索引的群集因子与表中的行数非常接近（且因而无效）。此处是优化程序关于这一点的建议：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此优化程序选择全表扫描，而不是使用索引，原因是群集因子：位图EMPNO（范围）B-TREE一致性读数物理读数一致性读数物理读数246312001-2300641549102114318-198063894910257211351850-4250641849093173162028888-31850645649092762135882900-854786431490972543329984888-100000072

43、544909仅对于最后的范围 (984888-1000000，优化程序选择了位图索引的全表扫描，然而，对于所有范围，它选择 B-tree 索引的全表扫描。群集因子是导致这一差异的原因：使用位图索引生成执行计划时，该优化程序不考虑群集因子的值，然而，对于 B-tree 索引，它会考虑群集因子的值。在这种情况下，位图索引执行会比 B-tree 索引更加有效。下面的步骤列举关于这些索引的值得关注的更多情况。步骤 5A（在 TEST_NORMAL 上）在 TEST_NORMAL 表的 SAL 列上创建位图索引。此列具有正常的基数。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.现在让我们来获取索引的大小和群集因子。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现在开始进行查询，首先使用等式谓词运行它们：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现在丢弃位图索引，并在 TEST_NORMAL 上创建一个 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