——理解适当使用每个索引对性能的影响
Bitmap 索引 vs. B-tree 索引：如何选择以及何时使用？——1-5
Bitmap 索引 vs. B-tree 索引：如何选择以及何时使用？——2-5
Bitmap 索引 vs. B-tree 索引：如何选择以及何时使用？——3-5
Bitmap 索引 vs. B-tree 索引：如何选择以及何时使用？——4-5
Bitmap 索引 vs. B-tree 索引：如何选择以及何时使用？——5-5
本文内容
- 步骤 5A（TEST_NORMAL 表 SAL 列创建 Bitmap 索引，执行等值和范围查询）
- 步骤 5B（TEST_NORMAL 表 SAL 列创建 B-tree 索引，执行等值和范围查询）
步骤 5A（在 TEST_NORMAL 表 SAL 列 Bitmap 索引）
在 TEST_NORMAL 表 SAL 列上创建 Bitmap 索引，该列具有常规基数（cardinality）值，查看索引大小和聚类系数。
SQL> create bitmap index normal_sal_bmx on test_normal(sal);
索引已创建。
SQL> analyze table test_normal compute statistics for table for all indexes for
all indexed columns;
表已分析。
SQL>
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
------------------------------------------------------------ ----------
NORMAL_SAL_BMX 4
TEST_NORMAL 50
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
RANDOM_EMPNO_IDX 999823
NORMAL_EMPNO_IDX 6210
NORMAL_SAL_BMX 6001
SQL>
现在在 SAL 列上先进行等值查询：
SQL> set autot traceonly
SQL> select * from test_normal where sal=&sal;
输入 sal 的值: 1869
原值 1: select * from test_normal where sal=&sal
新值 1: select * from test_normal where sal=1869
已选择188行。
执行计划
----------------------------------------------------------
Plan hash value: 257953309
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 167 | 6179 | 35(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_NORMAL | 167 | 6179 | 35(0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | NORMAL_SAL_BMX | | | | |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SAL"=1869)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
188 consistent gets
0 physical reads
0 redo size
11392 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
188 rows processed
SQL>
然后，执行范围谓词查询：
SQL> select * from test_normal where sal between &sal1 and &sal2;
输入 sal1 的值: 1500
输入 sal2 的值: 2000
原值 1: select * from test_normal where sal between &sal1 and &sal2
新值 1: select * from test_normal where sal between 1500 and 2000
已选择83613行。
执行计划
----------------------------------------------------------
Plan hash value: 512490529
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 83534 | 3018K| 1727 (2)| 00:00:21|
|* 1 | TABLE ACCESS FULL| TEST_NORMAL | 83534 | 3018K| 1727 (2)| 00:00:21|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"<=2000 AND "SAL">=1500)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
11777 consistent gets
0 physical reads
0 redo size
4462936 bytes sent via SQL*Net to client
61833 bytes received via SQL*Net from client
5576 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
83613 rows processed
SQL>
步骤 5B（在 TEST_NORMAL 表 SAL 列 B-tree 索引）
现在，删除 TEST_NORMAL 表 SAL 列的 Bitmap 索引，创建 B-tree 索引：
SQL> drop index normal_sal_bmx;
索引已删除。
SQL> create index normal_sal_idx on test_normal(sal);
索引已创建。
SQL> analyze table test_normal compute statistics for table for all indexes for
all indexed columns;
表已分析。
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
------------------------------------------------------------ ----------
NORMAL_SAL_IDX 17
TEST_NORMAL 50
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
RANDOM_EMPNO_IDX 999823
NORMAL_EMPNO_IDX 6210
NORMAL_SAL_IDX 986864
SQL>
可以看到，该列的 B-tree 索引比 Bitmap 索引大。聚类系数接近表中的行数。
现在先执行等值查询：
SQL> select * from test_normal where sal=&sal;
输入 sal 的值: 1869
原值 1: select * from test_normal where sal=&sal
新值 1: select * from test_normal where sal=1869
已选择188行。
执行计划
----------------------------------------------------------
Plan hash value: 4257100160
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 167 | 6179 | 168 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_NORMAL | 167 | 6179 | 168 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | NORMAL_SAL_IDX | 167 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SAL"=1869)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
202 consistent gets
0 physical reads
0 redo size
11392 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
188 rows processed
SQL>
然后，执行范围查询：
SQL> select * from test_normal where sal between &sal1 and &sal2;
输入 sal1 的值: 1500
输入 sal2 的值: 2000
原值 1: select * from test_normal where sal between &sal1 and &sal2
新值 1: select * from test_normal where sal between 1500 and 2000
已选择83613行。
执行计划
----------------------------------------------------------
Plan hash value: 512490529
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 83534 | 3018K| 1727 (2)| 00:00:21|
|* 1 | TABLE ACCESS FULL| TEST_NORMAL | 83534 | 3018K| 1727 (2)| 00:00:21|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"<=2000 AND "SAL">=1500)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
11777 consistent gets
0 physical reads
0 redo size
4462936 bytes sent via SQL*Net to client
61833 bytes received via SQL*Net from client
5576 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
83613 rows processed
SQL>
当输入不同值查询时，如下表所示，结果输出揭示 consistent gets 和 physical reads 的值一致。
表 5 TEST_NORMAL 表 SAL 列 Bitmap 和 B-tree 索引统计信息比较
| Bitmap | B-tree | |
| size | 4MB | 17MB |
| clustering factor | 6001 | 986864 |
| blocks |
表 6 TEST_NORMAL 表 SAL 列使用 Bitmap 和 B-tree 索引等值查询比较
| BITMAP | B-TREE | Rows Fetched | |||
| Consistent Reads | Physical Reads | SAL (Equality) | Consistent Reads | Physical Reads | |
| 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 |
表 7 TEST_NORMAL 表 SAL 列使用 Bitmap 和 B-tree 索引范围查询比较
| BITMAP | B-TREE | Rows Fetched | |||
| Consistent Reads | Physical Reads | SAL (Range) | Consistent Reads | Physical Reads | |
| 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 |
优化器为所有不同输入的范围谓词查询，选择了全表扫描，完全不使用索引。而对等值谓词，优化器使用索引，consistent gets 和 physical reads 的值一致。
因此，可以得出结论，对于一个正常基数的列，优化器对这两种类型索引的选择是一样的，而 IO 之间没有重大差异。
基数（Cardinality） - 使用 bitmap 索引对低基数的列最合适：也就是说，不重复列的数量比表中行数小。如果一个列不重复值的数量小于表中行数的 1%，或如果一个列中的值被重复超过 100 次，那么该列就可以创建 bitmap 索引。如果在 WHERE 子句中设计复杂条件，那么即使具有较低重复数量列，从而具有较高的基数，也可以创建 bitmap 索引。
例如，一个一百万行的表，某个列具有 10000 个不重复的值，可以在该列创建 bitmap 索引。该列的 bitmap 索引会比其 B-tree 索引的性能要好，特别是当这个列经常与其他列进行连接时。
B-tree 索引对高基数的数据具有很高的效率：也就是说，数据具有很多可能的值，例如，CUSTOMER_NAME 或 PHONE_NUMBER。在一些情况下，一个 B-tree 索引会比已索引的数据大。适当地使用，bitmap 索引会比相应的 B-tree 索引要小很多。
在 ad hoc 查询和类型的情形下，bitmap 索引可以显著提高查询性能。WHERE 子句中的 AND 和 OR 条件可以被快速解析，这是在把结果 bitmap 转换成 rowid 前，通过在 bitmap 索引上直接执行相应的 Booleam 操作。如果结果的行数较小，那么无需重新排序就能快速得到结果。