B-tree索引与Bitmap索引的对比测试
昨天发现一条语句没有走索引，检查发现表没有建相应索引，先建立B-tree索引，测试发现是全表扫描，检查表数据发现此字段的值只有2个,删除原索引又建立bitmap索引，发现还是全表扫描，再次检查数据发现2个值基本各占一半，因此是否有索引都是全表扫描。随后又做了下面的测试加以验证，从dba_objects建立test_objects表，反复插入120万数据，大概130M，测试如下(oracle版本，10g、11g)：
--1.无索引情况：
SQL> select * from scott.test_objects t where t.owner='SYS';
已选择550872行。 执行计划
---------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 694K| 117M| 4636 (3)| 00:00:56 |
|* 1 | TABLE ACCESS FULL| TEST_OBJECTS | 694K| 117M| 4636 (3)| 00:00:56 |
----------------------------------------------------------------------------------
统计信息 7 recursive calls
0 db block gets
57601 consistent gets
0 physical reads
327660 redo size
26774068 bytes sent via SQL*Net to client
404349 bytes received via SQL*Net from client
36726 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
550872 rows processed
---2. 为owner字段建立bitmap 索引（nologging 下用时0.4秒多）：
SQL> select * from scott.test_objects t where t.owner='SYS';
已选择550872行。执行计划
-----------------------------------------------------------
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 694K| 117M| 2214 (1)| 00:00:27 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_OBJECTS | 694K| 117M| 2214 (1)| 00:00:27 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | IDX_OWNER | | | | |
---------------------------------------------------------------------------------------------
统计信息 13 recursive calls
0 db block gets
50196 consistent gets
48 physical reads
0 redo size
58672868 bytes sent via SQL*Net to client
404349 bytes received via SQL*Net from client
36726 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
550872 rows processed
--3. 为owner字段建立普通索引（nologging 下用时12秒多）：：
SQL> select * from scott.test_objects t where t.owner='SYS' order by created desc;
已选择550872行。执行计划
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 694K| 117M| | 31701 (1)| 00:06:21 |
| 1 | SORT ORDER BY | | 694K| 117M| 278M| 31701 (1)| 00:06:21 |
|* 2 | TABLE ACCESS FULL| TEST_OBJECTS | 694K| 117M| | 4636 (3)| 00:00:56 |
-------------------------------------------------------------------------------------------
统计信息
72 recursive calls
178 db block gets
16832 consistent gets
11444 physical reads
0 redo size
24811026 bytes sent via SQL*Net to client
404349 bytes received via SQL*Net from client
36726 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
550872 rows processed
---分析表后看执行结果：
SQL> analyze table scott.test_objects compute statistics for all indexes;
--普通索引：
SQL> select * from scott.test_objects t where t.owner='SYS' order by created desc;
已选择550872行。执行计划
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 539K| 47M| | 16247 (2)| 00:03:15 |
| 1 | SORT ORDER BY | | 539K| 47M| 129M| 16247 (2)| 00:03:15 |
|* 2 | TABLE ACCESS FULL| TEST_OBJECTS | 539K| 47M| | 4622 (2)| 00:00:56 |
-------------------------------------------------------------------------------------------
统计信息
64 recursive calls
71 db block gets
16736 consistent gets
11904 physical reads
0 redo size
24810760 bytes sent via SQL*Net to client
404349 bytes received via SQL*Net from client
36726 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
550872 rows processed
--bitmap索引：
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 539K| 47M| | 16247 (2)| 00:03:15 |
| 1 | SORT ORDER BY | | 539K| 47M| 129M| 16247 (2)| 00:03:15 |
|* 2 | TABLE ACCESS FULL| TEST_OBJECTS | 539K| 47M| | 4622 (2)| 00:00:56 |
-------------------------------------------------------------------------------------------
--改变sql语句后：
SQL> select t.* from scott.test_objects t where t.owner='ICDMAIN' order by timestamp desc;
已选择216行。执行计划
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 2 (50)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 93 | 2 (50)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | TEST_OBJECTS | 1 | 93 | 1 (0)| 00:00:01 |
| 3 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | IDX_OWNER | | | | |
----------------------------------------------------------------------------------------------
把索引多次删除并分析，情况基本一样，得出下面结果：
1. 第一次建立bitmap索引后，查询走了索引，后来的查询就再也没有走索引；
2. nologging状态下建bitmap用时非常小，而b-tree索引用时较大；
3. 在排序状态下，要占用原表2倍大的TempSpc表空间，此表数据130多M，占用tempspe为278M。再此查询时占用temp空间为129M，因为共享池中已经存在了。
4. 改变sql语句的查询条件后，走了索引（sys为550872行，icdmain为216行），也进一步验证了下面的原则：
索引范围扫描的总体原则是：
1. 对于原始排序的表, 仅读取少于表记录数40％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的40％的查询应该使用全表扫描。
2. 对于未排序的表, 仅读取少于表记录数7％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的7％的查询应该使用全表扫描。
本文出自 “srsunbing” 博客，请务必保留此出处http://srsunbing.blog.51cto.com/3221858/724473
相关新闻>>
- 发表评论
-
- 最新评论 更多>>
