——理解适当使用每个索引对性能的影响
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
本文内容
- 步骤 6（添加一个 GENDER 列）
步骤 6（添加一个 GENDER 列）
在一个低基数列执行测试前，向表添加一个 GENDER 列，并基于某个条件用 M、F、null 值来更新。
SQL> alter table test_normal add GENDER varchar2(1);
表已更改。
SQL>
用下面匿名过程更新 GENDER 列的值，之后看看列值如何分布：
DECLARE
v_empno test_normal.empno%type;
CURSOR c IS
SELECT empno FROM test_normal;
BEGIN
OPEN c;
LOOP
FETCH c
INTO v_empno;
exit when c%notfound;
IF dbms_random.random MOD 3 = 1 THEN
UPDATE test_normal set gender = 'M' WHERE empno = v_empno;
ELSIF dbms_random.random mod 3 = 2 THEN
UPDATE test_normal set gender = 'F' WHERE empno = v_empno;
ELSIF dbms_random.random mod 3 = 0 THEN
UPDATE test_normal set gender = null WHERE empno = v_empno;
END IF;
commit;
END LOOP;
CLOSE c;
END;
SQL> select GENDER, count(*) from test_normal group by GENDER;
GENDER COUNT(*)
---------- ----------
385961
M 320767
F 293272
SQL>
该列上的 Bitmap 索引大小约在 620KB 左右，如下所示：
SQL> create bitmap index normal_GENDER_bmx on test_normal(GENDER);
索引已创建。
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
------------------------------------------------------------ ----------
NORMAL_GENDER_BMX .625
TEST_NORMAL 50
SQL>
相比，该列上的 B-tree 索引为 9MB，比 Bitmap 索引大很多。
SQL> drop index normal_gender_bmx;
索引已删除。
SQL> create index normal_gender_idx on test_normal(gender);
索引已创建。
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
------------------------------------------------------------ ----------
NORMAL_GENDER_IDX 9
TEST_NORMAL 50
SQL>
现在，若执行一个等值谓词查询，那么优化器将不使用索引，Bitmap 索引或 B-tree 索引。而是执行一个全表扫描。
SQL> select * from test_normal where GENDER is null;
已选择385961行。
执行计划
----------------------------------------------------------
Plan hash value: 512490529
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 37M| 1725 (2)| 00:00:21|
|* 1 | TABLE ACCESS FULL| TEST_NORMAL | 1000K| 37M| 1725 (2)| 00:00:21|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("GENDER" IS NULL)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
31825 consistent gets
0 physical reads
0 redo size
22065645 bytes sent via SQL*Net to client
283550 bytes received via SQL*Net from client
25732 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
385961 rows processed
SQL>
SQL> select * from test_normal where GENDER='M';
已选择320767行。
执行计划
----------------------------------------------------------
Plan hash value: 654360527
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_NORMAL | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | NORMAL_GENDER_IDX | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GENDER"='M')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
49138 consistent gets
0 physical reads
0 redo size
19044637 bytes sent via SQL*Net to client
235744 bytes received via SQL*Net from client
21386 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
320767 rows processed
SQL>
SQL> select * from test_normal where GENDER='F';
已选择293272行。
执行计划
----------------------------------------------------------
Plan hash value: 654360527
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_NORMAL | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | NORMAL_GENDER_IDX | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GENDER"='F')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
45430 consistent gets
0 physical reads
0 redo size
17412131 bytes sent via SQL*Net to client
215581 bytes received via SQL*Net from client
19553 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
293272 rows processed
SQL>
表 8 Bitmap 索引在 TEST_NORMAL 表 EMPNO、SAL 和 GENDER 列的统计信息
| EMPNO | SAL | GENDER | |
| 基数程度 | 高 | 一般 | 低 |
| size | 28MB | 4MB | 625KB |
基数越低，Bitmap 索引越小。
表 9 B-tree 索引在 TEST_NORMAL 表 EMPNO、SAL 和 GENDER 列的统计信息
| EMPNO | SAL | GENDER | |
| 基数程度 | 高 | 一般 | 低 |
| size | 18MB | 17MB | 9MB |