位图索引与 B-tree 索引：选择与时间

位图索引与 B-tree 索引：选择与时间

了解每个索引的正确应用对性能会有很大影响。

一般认为，位图索引最适合于具有低相异值的列（如 GENDER、MARITAL_STATUS 和 RELATION）。然合，这种观点并不完全正确。实际上，对于那些并不经常由许多并行系统更新其数据的系统来说，总是建议采用位图索引。事实上，正如我此处所阐明的，在具有 100% 唯一值（主键的列候选键）的列上的位图索引与 B-tree 索引同样有效。

在本文中，我将提供一些示例和优化程序决策，它们对于低基数列和高基数列上的索引类型是通用的。这些示例将帮助 DBA 了解位图索引的使用实际上并不依赖于基数，而是依赖于应用程序。

比较索引

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

为演示这一点，我创建了两个表 TEST_NORMAL 和 TEST_RANDOM。我使用 PL/SQL 块在 TEST_NORMAL 表中插入一百万行，然后随机在 TEST_RANDOM 表中插入这些行：

Create table test_normal (empno number(10), ename varchar2(30), sal number(10));

Begin
For i in 1..1000000
Loop
Insert into test_normal
values(i, dbms_random.string('U',30), dbms_random.value(1000,7000));
If mod(i, 10000) = 0 then
Commit;
End if;
End loop;
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
----------
   1000000

Elapsed: 00:00:01.09

SQL> select count(distinct empno) "Distinct Values" from test_normal;

Distinct Values
---------------
        1000000

Elapsed: 00:00:06.09
SQL> select count(*) "Total Rows" from test_random;

Total Rows
----------
   1000000

Elapsed: 00:00:03.05
SQL> select count(distinct empno) "Distinct Values" from test_random;

Distinct Values
---------------
        1000000

Elapsed: 00:00:12.07

注意 TEST_NORMAL 是条理的，而 TEST_RANDOM 表是随机创建的，因此 TEST_RANDOM 表的数据是无条理的。在上面的表中，列 EMPNO 包含完全相异的值，是很好的一个候选主键。如果您将此列定为主键，则您将创建一个 B-tree 索引而非位图索引，因为 Oracle 不支持位图主键索引。
要分析这些索引的行为，我们将执行下列步骤：

在 TEST_NORMAL 上：
在 EMPNO 列上创建一个位图索引，并使用等式谓词执行某些查询。
在 EMPNO 列上创建一个 B-tree 索引，使用等式谓词执行某些查询，并比较由查询做出的逻辑和物理输入/输出，以取出不同值集的结果。
在 TEST_RANDOM 上：
与步骤 1A 相同。
与步骤 1B 相同。
在 TEST_NORMAL 上：
与步骤 1A 相同，除了查询是使用在一定范围的谓词执行之外。
与步骤 1B 相同，除了查询是使用一定范围的谓词执行之外。现在比较统计值。
在 TEST_RANDOM 上：
与步骤 3A 相同。
与步骤 3B 相同。
在 TEST_NORMAL 上：
在 SAL 列上创建一个位图索引，然后使用等式谓词执行某些查询，及使用范围谓词执行某些查询。
在 SAL 列上创建一个 B-tree 索引，然后使用等式谓词执行某些查询，和使用范围谓词（与步骤 5A 中相同的某些值集）执行某些查询。比较由查询完成的输入/输出以取出结果。
将 GENDER 列添加到全部两个表中，并使用三个可能值更新列：M 代表男，F 代表女，null 代表不适用。基于一定条件使用这些值对列进行更新。
在此列上创建一个位图索引，并使用等式谓词执行某些查询。
在 GENDER 列上创建一个 B-tree 索引，并使用等式谓词执行某些查询。与步骤 7 的结果比较。
步骤 1 到步骤 4 涉及一个高基数（100% 不同）列，步骤 5 涉及一个普通基数列，而步骤 7 和步骤 8 涉及一个低基数列。
步骤 1A（在 TEST_NORMAL 上）

在此步骤中，我们将在 TEST_NORMAL 表上创建一个位图索引，然后检查此索引的大小、其群集因子和表的大小。然后我们将使用等式谓词运行某些查询，并使用此位图索引注明这些查询的输入/输出。

SQL> create bitmap index normal_empno_bmx on test_normal(empno);

Index created.

Elapsed: 00:00:29.06
SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;


Table analyzed.

Elapsed: 00:00:19.01
SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2  from user_segments
3* where segment_name in ('TEST_NORMAL','NORMAL_EMPNO_BMX');

SEGMENT_NAME                               Size in MB
------------------------------------       ---------------
TEST_NORMAL                                50
NORMAL_EMPNO_BMX                           28

Elapsed: 00:00:02.00
SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME                             CLUSTERING_FACTOR
------------------------------         ---------------------------------
NORMAL_EMPNO_BMX                       1000000

Elapsed: 00:00:00.00

您会看到在前面表中索引的大小是 28 MB，而群集因子等于表中的行数。现在让我们使用等式谓词针对不同值集执行查询：
SQL> set autotrace only
SQL> select * from test_normal where empno=&empno;
Enter value for empno: 1000
old   1:select * from test_normal where empno=&empno

new   1:select * from test_normal where empno=1000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=4 Car
d=1 Bytes=34)
2    1     BITMAP CONVERSION (TO ROWIDS)
3    2       BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_EMPNO_BMX'

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

步骤 1B（在 TEST_NORMAL 上）
现在我们将丢弃此位图索引并在 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 columns;

Table analyzed.

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2  from user_segments
3  where segment_name in ('TEST_NORMAL','NORMAL_EMPNO_IDX');

SEGMENT_NAME                               Size in MB
----------------------------------         ---------------
TEST_NORMAL                                50
NORMAL_EMPNO_IDX                           18

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME                            CLUSTERING_FACTOR
----------------------------------    ----------------------------------
NORMAL_EMPNO_IDX                      6210

很清楚在此表中，B-tree 索引比 EMPNO 列上的位图索引更小。B-tree 索引的群集因子更加接近表中的块数；由于这一原因，B-tree 索引对于范围谓词查询效率更佳。
现在我们将使用我们的 B-tree 索引针对相同的值集运行相同的查询。

SQL> set autot trace
SQL> select * from test_normal where empno=&empno;
Enter value for empno: 1000
old   1:select * from test_normal where empno=&empno
new   1:select * from test_normal where empno=1000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=4 Car
d=1 Bytes=34)
2    1     INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_IDX' (NON-UNIQUE) (C
ost=3 Card=1)

Statistics
----------------------------------------------------------
29  recursive calls
0  db block gets
5  consistent gets
0  physical reads
0  redo size
515  bytes sent via SQL*Net to client
499  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

正如您可以看到的那样，针对不同的值集执行查询时，对于在完全唯一列上的位图和 B-tree 索引，一致性读请求和物理读取的数量是相同的。
位图 EMPNO B-TREE
一致性读数 物理读数 一致性读数 物理读数
5 0 1000 5 0
5 2 2398 5 2
5 2 8545 5 2
5 2 98008 5 2
5 2 85342 5 2
5 2 128444 5 2
5 2 858 5 2


步骤 2A（在 TEST_RANDOM 上）

现在我们将在 TEST_RANDOM 上执行相同的实验：

SQL> create bitmap index random_empno_bmx on test_random(empno);

Index created.

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_segments
3* where segment_name in ('TEST_RANDOM','RANDOM_EMPNO_BMX');

SEGMENT_NAME                               Size in MB
------------------------------------       ---------------
TEST_RANDOM                                50
RANDOM_EMPNO_BMX                           28

SQL> 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 empno: 1000
old   1:select * from test_random where empno=&empno

new   1:select * from test_random where empno=1000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=4 Card=1 Bytes=34)
2    1     BITMAP CONVERSION (TO ROWIDS)
3    2       BITMAP INDEX (SINGLE VALUE) OF 'RANDOM_EMPNO_BMX'

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
5  consistent gets
0  physical reads
0  redo size
515  bytes sent via SQL*Net to client
499  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  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 all indexed columns;

Table analyzed.

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2  from user_segments
3  where segment_name in ('TEST_RANDOM','RANDOM_EMPNO_IDX');

SEGMENT_NAME                               Size in MB
----------------------------------         ---------------
TEST_RANDOM                                50
RANDOM_EMPNO_IDX                           18

SQL> select index_name, 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 value for empno: 1000
old   1:select * from test_random where empno=&empno

new   1:select * from test_random where empno=1000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=4 Card=1 Bytes=34)
2    1     INDEX (RANGE SCAN) OF 'RANDOM_EMPNO_IDX' (NON-UNIQUE) (Cost=3 Card=1)

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

同样，结果几乎与步骤 1A 和 1B 中的的结果一样。数据分配不影响唯一列的一致性读请求和物理读取的数量。

步骤 3A（在 TEST_NORMAL 上）

在这一步中，我们将创建位图索引（与步骤 1A 相似）。我们知道索引的大小和群集因子，它等于表中的行数。现在让我们使用范围谓词运行某些查询。

SQL> select * from test_normal where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old   1:select * from test_normal where empno between &range1 and &range2

new   1:select * from test_normal where empno between 1 and 2300

2300 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=451 Card=2299 Bytes=78166)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=451 Card=2299 Bytes=78166)
2    1     BITMAP CONVERSION (TO ROWIDS)
3    2       BITMAP INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_BMX'

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
331  consistent gets
0  physical reads
0  redo size
111416  bytes sent via SQL*Net to client
2182  bytes received via SQL*Net from client
155  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2300  rows processed

步骤 3B（在 TEST_NORMAL 上）
在这一步中，我们将对具有 B-tree 索引的 TEST_NORMAL 表执行查询。

SQL> select * from test_normal where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old   1:select * from test_normal where empno between &range1 and &range2

new   1:select * from test_normal where empno between 1 and 2300

2300 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=2299 Bytes=78166)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=23 Card=2299 Bytes=78166)
2    1     INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_IDX' (NON-UNIQUE) (Cost=8 Card=2299)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
329  consistent gets
15  physical reads
0  redo size
111416  bytes sent via SQL*Net to client
2182  bytes received via SQL*Net from client
155  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2300  rows processed

为不同的范围集执行这些查询时，结果会显示如下：
位图 EMPNO（范围） B-TREE
一致读数 物理读数 一致读数 物理读数
331 0 1-2300 329 0
285 0 8-1980 283 0
346 19 1850-4250 344 16
427 31 28888-31850 424 28
371 27 82900-85478 367 23
2157 149 984888-1000000 2139 35


您会发现，这一次两个索引的一致读数和物理读数又几乎一样。最后一个范围 (984888-1000000) 几乎返回 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 value for range1: 1
Enter value for range2: 2300
old   1:select * from test_random where empno between &range1 and &range2

new   1:select * from test_random where empno between 1 and 2300

2300 rows selected.

Elapsed: 00:00:08.01

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=453 Card=2299 Bytes=78166)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=453 Card=2299 Bytes=78166)
2    1     BITMAP CONVERSION (TO ROWIDS)
3    2       BITMAP INDEX (RANGE SCAN) OF 'RANDOM_EMPNO_BMX'

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
2463  consistent gets
1200  physical reads
0  redo size
111416  bytes sent via SQL*Net to client
2182  bytes received via SQL*Net from client
155  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2300  rows processed


步骤 4B（在 TEST_RANDOM 上）
在这一步中，我们将具有 B-tree 索引的 TEST_RANDOM 上执行范围谓词查询。记住，此索引的群集因子与表中的行数非常接近（且因而无效）。此处是优化程序关于这一点的建议：

SQL> select * from test_random where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old   1:select * from test_random where empno between &range1 and &range2

new   1:select * from test_random where empno between 1 and 2300

2300 rows selected.

Elapsed: 00:00:03.04

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=613 Card=2299 Bytes=78166)
1    0   TABLE ACCESS (FULL) OF 'TEST_RANDOM' (Cost=613 Card=2299 Bytes=78166)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
6415  consistent gets
4910  physical reads
0  redo size
111416  bytes sent via SQL*Net to client
2182  bytes received via SQL*Net from client
155  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2300  rows processed

此优化程序选择全表扫描，而不是使用索引，原因是群集因子：

位图 EMPNO（范围） B-TREE
一致性读数 物理读数 一致性读数 物理读数
2463 1200 1-2300 6415 4910
2114 31 8-1980 6389 4910
2572 1135 1850-4250 6418 4909
3173 1620 28888-31850 6456 4909
2762 1358 82900-85478 6431 4909
7254 3329 984888-1000000 7254 4909

仅对于最后的范围 (984888-1000000)，优化程序选择了位图索引的全表扫描，然而，对于所有范围，它选择 B-tree 索引的全表扫描。群集因子是导致这一差异的原因：使用位图索引生成执行计划时，该优化程序不考虑群集因子的值，然而，对于 B-tree 索引，它会考虑群集因子的值。在这种情况下，位图索引执行会比 B-tree 索引更加有效。

下面的步骤列举关于这些索引的值得关注的更多情况。

步骤 5A（在 TEST_NORMAL 上）

在 TEST_NORMAL 表的 SAL 列上创建位图索引。此列具有正常的基数。

SQL> create bitmap index normal_sal_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_segments
3* where segment_name in ('TEST_NORMAL','NORMAL_SAL_BMX');

SEGMENT_NAME                                Size in MB
------------------------------              --------------
TEST_NORMAL                                 50
NORMAL_SAL_BMX                              4

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME                             CLUSTERING_FACTOR
------------------------------         ----------------------------------
NORMAL_SAL_BMX                         6001

现在开始进行查询，首先使用等式谓词运行它们：
SQL> set autot trace
SQL> select * from test_normal where sal=&sal;
Enter value for sal: 1869
old   1:select * from test_normal where sal=&sal

new   1:select * from test_normal where sal=1869

164 rows selected.

Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=168 Bytes=4032)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=39 Card=168 Bytes=4032)
2    1     BITMAP CONVERSION (TO ROWIDS)
3    2       BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
165  consistent gets
0  physical reads
0  redo size
8461  bytes sent via SQL*Net to client
609  bytes received via SQL*Net from client
12  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
164  rows processed

然后使用范围谓词：
SQL> select * from test_normal where sal between &sal1 and &sal2;
Enter value for sal1: 1500
Enter value for sal2: 2000
old   1:select * from test_normal where sal between &sal1 and &sal2

new   1:select * from test_normal where sal between 1500 and 2000

83743 rows selected.

Elapsed: 00:00:05.00

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes
          =2001024)
1    0   TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=83376
Bytes=2001024)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
11778  consistent gets
5850  physical reads
0  redo size
4123553  bytes sent via SQL*Net to client
61901  bytes received via SQL*Net from client
5584  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
83743  rows processed

现在丢弃位图索引，并在 TEST_NORMAL 上创建一个 B-tree 索引。
SQL> create index normal_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_segments
3  where segment_name in ('TEST_NORMAL','NORMAL_SAL_IDX');

SEGMENT_NAME                         Size in MB
------------------------------       ---------------
TEST_NORMAL                          50
NORMAL_SAL_IDX                       17

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME                           CLUSTERING_FACTOR
------------------------------       ----------------------------------
NORMAL_SAL_IDX                       986778


在上面的表中，您可以看到此索引比相同列上的位图索引大。群集因子也接近此表中的行数。
下面要进行测试了，首先使用等式谓词：

SQL> set autot trace
SQL> select * from test_normal where sal=&sal;
Enter value for sal: 1869
old   1:select * from test_normal where sal=&sal

new   1:select * from test_normal where sal=1869

164 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=169 Card=168 Bytes=4032)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=169 Card=168 Bytes=4032)
2    1     INDEX (RANGE SCAN) OF 'NORMAL_SAL_IDX' (NON-UNIQUE) (Cost=3 Card=168)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
177  consistent gets
0  physical reads
0  redo size
8461  bytes sent via SQL*Net to client
609  bytes received via SQL*Net from client
12  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
164  rows processed

然后，范围谓词：
SQL> select * from test_normal where sal between &sal1 and &sal2;
Enter value for sal1: 1500
Enter value for sal2: 2000
old   1:select * from test_normal where sal between &sal1 and &sal2

new   1:select * from test_normal where sal between 1500 and 2000

83743 rows selected.

Elapsed: 00:00:04.03

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes
          =2001024)
1    0   TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=83376
Bytes=2001024)


Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
11778  consistent gets
3891  physical reads
0  redo size
4123553  bytes sent via SQL*Net to client
61901  bytes received via SQL*Net from client
5584  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
83743  rows processed

针对不同的值集执行查询时，输出结果（如下表中所示）揭示一致性读数和物理读数是相同的。
位图 SAL（等式） B-TREE 取出的行数 
一致性读数 物理读数 一致性读数 物理读数
165 0 1869 177 164  
169 163 3548 181 167  
174 166 6500 187 172  
75 69 7000 81 73  
177 163 2500 190 175  


位图 SAL（范围） B-TREE 取出的行数 
一致性读数 物理读数 一致性读数 物理读数
11778  5850  1500-2000  11778  3891  83743 
11765  5468  2000-2500  11765  3879  83328 
11753  5471  2500-3000  11753  3884  83318 
17309  5472  3000-4000  17309  3892  166999 
39398  5454  4000-7000  39398  3973  500520 


对于范围谓词，优化程序选择为所有的不同值集进行全表扫描（它根本不使用索引）然而，对于等式谓词，该优化程序使用索引。和以前一样，一致性读数和物理读数是相同的。

因此，您可以得出结论：对于一般基数列，优化程序针对两种类型索引做出的决策都是相同的，且在输入/输出之间不存在显著的差异。

步骤 6（添加 GENDER 列）

在低基数列上执行测试之前，让我们将一个 GENDER 列添加到此表中，并使用 M、F 和 null 值更新它。

SQL> alter table test_normal add GENDER varchar2(1);

Table altered.

SQL> select GENDER, count(*) from test_normal group by GENDER;

S     COUNT(*)
-     ----------
F     333769
M     499921
      166310

3 rows selected.

此列上的位图索引的大小大约是 570 KB，如下表中所示：
SQL> create bitmap index normal_GENDER_bmx on test_normal(GENDER);

Index created.

Elapsed: 00:00:02.08

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2  from user_segments
3  where segment_name in ('TEST_NORMAL','NORMAL_GENDER_BMX');

SEGMENT_NAME                        Size in MB
------------------------------      ---------------
TEST_NORMAL                         50
NORMAL_GENDER_BMX                   .5625

2 rows selected.

相反，此列上的 B-tree 索引的大小为 13 MB，它比此列上的位图索引要大得多。
SQL> create index normal_GENDER_idx on test_normal(GENDER);

Index created.

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2  from user_segments
3  where segment_name in ('TEST_NORMAL','NORMAL_GENDER_IDX');

SEGMENT_NAME                       Size in MB
------------------------------     ---------------
TEST_NORMAL                        50
NORMAL_GENDER_IDX                  13

2 rows selected.

现在，如果我们使用等式谓词执行查询，则优化程序将不利用此索引，不管它是位图索引或 B-tree 索引。它将选择完全表扫描。
SQL> select * from test_normal where GENDER is null;

166310 rows selected.

Elapsed: 00:00:06.08

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=166310 Bytes=4157750)
1    0   TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=166310 Bytes=4157750)

SQL> select * from test_normal where GENDER='M';

499921 rows selected.

Elapsed: 00:00:16.07

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=499921 Bytes=12498025)
1    0   TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=499921Bytes=12498025)

SQL>select * from test_normal where GENDER='F'
/

333769 rows selected.

Elapsed: 00:00:12.02

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=333769 Byte
s=8344225)
1    0   TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=333769
Bytes=8344225)

结论
现在我们已经了解优化程序是如何对这些技术作出反应的，接下来让我们分析一种情况，它将清楚地演示位图索引和 B-tree 索引各自的最佳应用。

在 GENDER 列已有位图索引，在 SAL 列上创建另一个位图索引，然后执行某些查询。将针对这些带 B-tree 索引的列重新执行这些查询。

从 TEST_NORMAL 表中，您需要得到月工资为下列任何值的所有男性雇员的数量：

1000
1500
2000
2500
3000
3500
4000
4500

执行：
SQL>select * from test_normal
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';

这是一个典型的数据仓库查询，当然，您应该永远都不会在 OLTP 系统执行该查询。下面是在两个列上都有位图索引时得到的结果：
SQL>select * from test_normal
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';

1453 rows selected.

Elapsed: 00:00:02.03

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=198 Card=754 Bytes=18850)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=198 Card=754 Bytes=18850)
2    1     BITMAP CONVERSION (TO ROWIDS)
3    2       BITMAP AND
4    3         BITMAP OR
5    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
6    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
7    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
8    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
9    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
10    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
11    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
12    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
13    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
14    3         BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_GENDER_BMX'

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
1353  consistent gets
920  physical reads
0  redo size
75604  bytes sent via SQL*Net to client
1555  bytes received via SQL*Net from client
98  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1453  rows processed

为 B-tree 索引时：
SQL>select * from test_normal
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';

1453 rows selected.

Elapsed: 00:00:03.01

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=754 Bytes=18850)
1    0   TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=754 Bytes=18850)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
6333  consistent gets
4412  physical reads
0  redo size
75604  bytes sent via SQL*Net to client
1555  bytes received via SQL*Net from client
98  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1453  rows processed

正如您此处可以看到的那样，使用 B-tree 索引，优化程序选择了全表扫描，然而在位图索引情形中，它使用索引来响应该查询。您可以根据取出结果所需的输入/输出数量来判断性能高低。
总之，不管基数如何，位图索引最适合于 DSS，原因如下：

使用位图索引，优化程序可以有效地响应包括 AND、OR 或 XOR 的查询。（Oracle 支持 B-tree 到位图的动态转换，但它可能效率很低。）

使用位图，在搜索空值或对其计数时，优化程序可以响应查询。位图索引也可以索引空值（与 B-tree 索引不同）。

最重要的是，DSS 系统中的位图索引支持即席查询，然而，B-tree 索引不支持即席查询。更为特殊的是，如果您有一个具有 50 列的表，且用户经常查询它们中的 10 个（查询全部 10 个或有时查询其中一个），则创建 B-tree 索引将非常困难。如果在所有这些列上创建 10 个位图索引，则所有查询都可以由这些索引来响应，而不管针对全部 10 列的查询，或其中 4 或 6 列的查询，或者是单个列上的查询。AND_EQUAL 提示为 B-tree 索引提供此功能，但一个查询仅能针对少于五个索引。使用位图索引将不会有这一限制。
但 B-tree 索引更适合于 OLTP 应用程序，在该应用程序中用户的查询是相对固定的（并在生产部署之前进行过很好的调整），这与即席查询相反，这些相对固定的查询出现频率更低，且在非高峰工作时间执行。因为数据在 OLTP 应用程序中经常更新，并已从 OLTP 应用程序中删除，所以在这些情形下，位图索引会导致严重的死锁问题。
本文提供的数据能让你清楚的了解各种情况。这两个索引都具有一个相似的目的：尽快地返回结果。但您选择使用哪个索引应该完全取决于应用程序的类型，而不是取决于基数的级别。