Oracle之位图索引重复度低慎用

---测试位图索引重复度前准备工作
drop table t purge;
set autotrace off
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
update t set object_id=rownum;
commit;


--COUNT(*)在列重复度低时一般不会考虑使用位图索引


create bitmap index idx_bit_object_id on t(object_id);
create bitmap index idx_bit_status on t(status);
--注意，以下收集统计信息必须先执行。
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ; 


set linesize 1000
set autotrace traceonly 


/*同学们可能还记得前面count(*)性能大比拼位图索引一马当先的事，现在我们看看，不在status列建位图索引，
在object_id列建位图索引后，是啥情况
*/
--create bitmap index idx_bit_object_id on t(object_id);
select /*+index(t,idx_bit_object_id)*/  count(*) from t;
执行计划
--------------------------------------------------------------------------------------
| Id  | Operation                | Name              | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                   |     1 | 17245   (1)| 00:03:27 |
|   1 |  SORT AGGREGATE          |                   |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT|                   |  4688K| 17245   (1)| 00:03:27 |
|   3 |    BITMAP INDEX FULL SCAN| IDX_BIT_OBJECT_ID |       |            |          |
--------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      16837  consistent gets
          0  physical reads
          0  redo size
        426  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


--create bitmap index idx_bit_status on t(status);
select /*+index(t,index idx_bit_status)*/  count(*) from t;
执行计划
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |   105   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE               |                |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |                |  4688K|   105   (0)| 00:00:02 |
|   3 |    BITMAP INDEX FAST FULL SCAN| IDX_BIT_STATUS |       |            |          |
----------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        125  consistent gets
          0  physical reads
          0  redo size
        426  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


--原理分析：
set autotrace off 
select segment_name,blocks,bytes/1024/1024 "SIZE(M)"
from user_segments
where segment_name in( 'IDX_BIT_OBJECT_ID','IDX_BIT_STATUS');
 
SEGMENT_NAME           BLOCKS    SIZE(M)
----------------------------- ----------
IDX_BIT_OBJECT_ID       17408        136
IDX_BIT_STATUS            128          1