Oracle 比较 B-tree 和 Bitmap 索引
本文内容
- 创建测试数据表
- 测试 B-tree 索引
- 测试 Bitmap 索引
- Bitmap 索引与分组
- 总结
本文演示数据列值的特点对索引类型的影响。若数据列的取值范围是可以穷举的，则 Bitmap 索引比 B-tree 索引更合适。
创建测试数据表
代码段一：
CREATE TABLE TESTIDX_A AS
SELECT *
FROM (SELECT ROWNUM as id, t.object_name, t.object_type FROM dba_objects t);
利用 dba_objects 系统表生成测试数据表 testidx_a，并以同样方式生成表 testidx_b 和 testidx_c。用下面语句查看一下数据量。
代码段二：
SQL> select count(*) from TESTIDX_A;
COUNT(*)
----------
69448
SQL>
以这样方式生成的测试数据，即便是新创建的数据库实例，也有将近 7 万行。
另外，本测试数据有个特点。object_type 列值的取值范围是可以枚举、穷举的，比如 FUNCTION、INDEX、PACKAGE 等，而 object_name 是不能穷举的。这个特点对创建并使用哪种索引类型很重要。
将按如下方式为三个表创建索引：
- testidx_a 表不创建任何索引。
- testidx_b 表分别为 object_name 和 object_type 列创建 B-tree 索引。
- testidx_c 表分别为 object_name 和 object_type 列创建 Bitmap 索引。
测试 B-tree 索引
首先，testidx_a 表，不创建任何索引。其次，为 testidx_b 表的 object_name 和 object_type 列分别建立 idx_tb_on 和 idx_tb_ot 的 B-tree 索引。Oracle 默认创建 B-tree 索引。
代码段三：
SQL> create index idx_tb_on on TESTIDX_B(object_name);
索引已创建。
SQL> create index idx_tb_ot on TESTIDX_B(object_type);
索引已创建。
SQL>
检索 testidx_a 和 testidx_b 表 object_name 字段值 'EMP' 表。如下所示，查找 testidx_a：
代码段四：
SQL> select * from TESTIDX_A where object_name='EMP';
执行计划
----------------------------------------------------------
Plan hash value: 1043052094
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 450 | 119 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TESTIDX_A | 5 | 450 | 119 (1)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='EMP')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
418 consistent gets
0 physical reads
0 redo size
565 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
注意：
- 执行计划中 Rows、Bytes、Cost 列。其中，Rows 和 Bytes 列表示选择多少行及其大小；Cost 是该 SQL 的代价，它仅仅是根据 CPU、IO 等代价计算出来一个值。
- 统计信息部分是可变的，也就是，初次与再次执行 SQL 时，consistent gets 值可能不同。但无论怎么样，执行计划的值不会变。
查找 testidx_b，如下所示：
代码段五：
SQL> select * from TESTIDX_B where object_name='EMP';
执行计划
----------------------------------------------------------
Plan hash value: 4045461513
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 90 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTIDX_B | 1 | 90 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_ON | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='EMP')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
569 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
说明：
- 执行计划 Operations 列，因为，testidx_a 表没有建立任何索引，所以代码段四进行了全表扫描；而代码段五，使用了索引 idx_tb_on。
- 这样，执行计划 Cost 列的值，差距很大。创建索引后，SQL 执行的明显快。
检索 testidx_a 和 testidx_b 表 object_type 列为 'TABLE' 的记录。检索 testidx_a 如下所示：
代码段六：
SQL> select * from TESTIDX_A
2 where object_type='TABLE'
3 order by object_name asc;
已选择2750行。
执行计划
----------------------------------------------------------
Plan hash value: 3733299015
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3623 | 318K| | 197 (2)| 00:00:03 |
| 1 | SORT ORDER BY | | 3623 | 318K| 728K| 197 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| TESTIDX_A | 3623 | 318K| | 119 (1)| 00:00:02 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
417 consistent gets
0 physical reads
0 redo size
104070 bytes sent via SQL*Net to client
2429 bytes received via SQL*Net from client
186 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2750 rows processed
SQL>
检索 testidx_b 表，如下所示：
代码段七：
SQL> select * from TESTIDX_B
2 where object_type='TABLE'
3 order by object_name asc;
已选择2751行。
执行计划
----------------------------------------------------------
Plan hash value: 1436749833
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3555 | 312K| | 155 (1)| 00:00:02 |
| 1 | SORT ORDER BY | | 3555 | 312K| 712K| 155 (1)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| TESTIDX_B | 3555 | 312K| | 80 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_TB_OT | 3555 | | | 10 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
178 consistent gets
8 physical reads
0 redo size
104086 bytes sent via SQL*Net to client
2429 bytes received via SQL*Net from client
186 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2751 rows processed
SQL>
测试 Bitmap 索引
接下来，为 testidx_c 表的 object_name 和 object_type 列都建立 Bitmap 索引。如下所示。
代码段八：
SQL> create bitmap index idx_tc_on on TESTIDX_C(object_name);
索引已创建。
SQL> create bitmap index idx_tc_ot on TESTIDX_C(object_type);
索引已创建。
SQL>
同代码段六和七执行同样的操作——查找 object_type 列为 TABLE 的记录，如下所示。
代码段九：
SQL> select * from TESTIDX_C
2 where object_type='TABLE'
3 order by object_name asc;
已选择2752行。
执行计划
----------------------------------------------------------
Plan hash value: 1820242233
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 219K| 78 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 2500 | 219K| 78 (2)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | TESTIDX_C | 2500 | 219K| 77 (0)| 00:00:01 |
| 3 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | IDX_TC_OT | | | | |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
103 recursive calls
0 db block gets
170 consistent gets
1 physical reads
0 redo size
104107 bytes sent via SQL*Net to client
2429 bytes received via SQL*Net from client
186 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2752 rows processed
说明：
- 代码段六，没有创建任何索引；代码段七，创建的是 B-tree 索引；代码段九，创建的是 Bitmap 索引。
- 当检索作用在 object_type 列时，Bitmap 索引比 B-tree 索引的效果更好。这是因为 object_type 的列决定的。该列的值是可以枚举，或是说穷举出来的。
因此，对某个列，它的值若有范围，可穷举，用 Bitmap 索引比较合适。否则，Bitmap 索引的效果，就不是很明显。如下所示，执行同代码段四和五同样的操作，查找 object_name 列为 EMP 的记录。
代码段十：
SQL> select * from testidx_c
2 where object_name='EMP';
执行计划
----------------------------------------------------------
Plan hash value: 2697327847
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 90 | 77 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TESTIDX_C | 1 | 90 | 77 (0)|00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | IDX_TC_ON | | | | |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_NAME"='EMP')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
76 consistent gets
1 physical reads
0 redo size
569 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
说明：看代码段四、五和十的执行计划，Cost 列的值，代码段十还是不如代码段五。因为，object_name 列的值不能穷举，不具有像 object_type 列那样的特点。所以，虽然 SQL 使用了 Bitmap 索引，但效果明显不如 B-tree 索引。
这样，我们会想到一个问题，是不是 Bitmap 索引对分组语句效果很好？
Bitmap 索引与分组
分别对 testidx_b 和 testidx_c 表的 object_type 字段进行分组。如下所示，分组 testidx_b：
代码段十一：
SQL> select t.object_type,count(*) from testidx_b t
2 group by t.object_type;
已选择41行。
执行计划
----------------------------------------------------------
Plan hash value: 141313140
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 79998 | 859K| 122 (4)| 00:00:02 |
| 1 | HASH GROUP BY | | 79998 | 859K| 122 (4)| 00:00:02 |
| 2 | TABLE ACCESS FULL| TESTIDX_B | 79998 | 859K| 119 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
492 consistent gets
0 physical reads
0 redo size
1449 bytes sent via SQL*Net to client
438 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
41 rows processed
分组 testidx_c，如下所示：
SQL> select t.object_type,count(*) from testidx_c t
2 group by t.object_type;
已选择41行。
执行计划
----------------------------------------------------------
Plan hash value: 3862313015
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72610 | 779K| 9 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 72610 | 779K| 9 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION COUNT| | 72610 | 779K| 9 (0)| 00:00:01 |
| 3 | BITMAP INDEX FULL SCAN| IDX_TC_OT | | | | |
--------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
78 consistent gets
3 physical reads
0 redo size
1449 bytes sent via SQL*Net to client
438 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
41 rows processed
说明：从执行计划的 Cost 列看，效果果然很明显。但是，当执行分组的同时，还有过滤条件，SQL 就不会使用 Bitmap 索引，除非将过滤条件中的列与分组列同时建立 Bitmap 索引。
总结
- testidx_a 表不创建任何索引。
- testidx_b 表分别为 object_name 和 object_type 列创建 B-tree 索引。
- testidx_c 表分别为 object_name 和 object_type 列创建 Bitmap 索引。
如图 1 所示，在三个表的 object_name 列执行相同的操作，查找值为 emp 的记录。B-tree 索引的效果更好。因为，当像 object_name 这样不能枚举其值的列，B-tree 索引对 SQL 的执行效率很高。不能枚举其值意思是 distinct 该列的值与 count 该列，相差无几。这反映在执行计划的基数列。另外，对 object_name 列创建 Bitmap 索引，效果比 B-tree 索引差很多。
图 1
如图 2 所示，在三个表的 object_type 列执行相同的操作，查找值为 TABLE 的记录。Bitmap 索引的效果更好。因为，当为 object_type 这样可能枚举其值的列，Bitmap 索引对 SQL 的执行效率很高。可能枚举其值意思是 distinct 该列的值与 count 该列，相差很多。
图 2
如图 3 所示，在 testidx_b 和 testidx_c 执行相同的操作。按 object_type 分组，Bitmap 索引对分组操作更好。
图 3
