ANBOB™

今天在执行计划中发现了它，BITMAP CONVERSION FROM/TO ROWIDS，往往根据名字会想到有位图索引，是么？

下面看我的sql

SQL> set linesize 150
SQL> l
  1  SELECT  distinct score_activity_content bb
  2    FROM icme_noproject_score sc
  3   WHERE     give_score_org_id =100014679
  4     AND score_activity_content LIKE '2%'
  5* and  source_flag=3 and SC.SCORE_ACTIVITY_DATE between todate('2009-1-1') and todate('2009-12-31')
SQL> /

执行计划
----------------------------------------------------------
Plan hash value: 3847087321

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                               |     2 |    86 |       | 21409   (1)| 00:04:17 |
|   1 |  HASH UNIQUE                      |                               |     2 |    86 |       | 21409   (1)| 00:04:17 |
|*  2 |   TABLE ACCESS BY INDEX ROWID     | ICME_NOPROJECT_SCORE          |     2 |    86 |       | 21408   (1)| 00:04:17 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |                               |       |       |       |         |     |
|   4 |     BITMAP AND                    |                               |       |       |       |         |     |
|   5 |      BITMAP CONVERSION FROM ROWIDS|                               |       |       |       |         |     |
|*  6 |       INDEX RANGE SCAN            | IDX_NOPROJECT_SCORE_GIVEORGID |   285K|       |       |   612   (1)| 00:00:08 |
|   7 |      BITMAP CONVERSION FROM ROWIDS|                               |       |       |       |         |     |
|   8 |       SORT ORDER BY               |                               |       |       |    18M|         |     |
|*  9 |        INDEX RANGE SCAN           | IDX_I_NOPROJECT_SCORE_SAC     |   285K|       |       |  2702   (1)| 00:00:33 |
---------------------------------------------------------------------------------------------------------------------------

SELECT index_name,
       index_type,
       blevel,
       leaf_blocks,
       distinct_keys,
       avg_data_blocks_per_key,
       num_rows
  FROM user_indexes
 WHERE index_name IN
          ('IDX_I_NOPROJECT_SCORE_SAC', 'IDX_NOPROJECT_SCORE_GIVEORGID');
          
 
INDEX_NAME                     INDEX_TYPE     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_DATA_BLOCKS_PER_KEY   NUM_ROWS
------------------------------ ---------- ---------- ----------- ------------- ----------------------- ----------
IDX_I_NOPROJECT_SCORE_SAC      NORMAL              3      137027       1734422                       5   24215952
IDX_NOPROJECT_SCORE_GIVEORGID  NORMAL              2       66593          9056                     336   24216139

SQL> set autot trace exp
SQL> SELECT  /*+ opt_param('_b_tree_bitmap_plans', 'false') */   distinct score_activity_content bb
  2    FROM icme_noproject_score sc
  3   WHERE     give_score_org_id =100014679
  4     AND score_activity_content LIKE '2%'
  5  and  source_flag=3 and SC.SCORE_ACTIVITY_DATE between todate('2009-1-1') and todate('2009-12-31')
  6  ;

执行计划
----------------------------------------------------------
Plan hash value: 723580020

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                               |     2 |    86 | 29455   (1)| 00:05:54 |
|   1 |  HASH UNIQUE                 |                               |     2 |    86 | 29455   (1)| 00:05:54 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| ICME_NOPROJECT_SCORE          |     2 |    86 | 29454   (1)| 00:05:54 |
|*  3 |    INDEX RANGE SCAN          | IDX_NOPROJECT_SCORE_GIVEORGID |   285K|       |   633   (1)| 00:00:08 |
--------------------------------------------------------------------------------------------------------------

BITMAP CONVERSION 与bitmap index没有关系，当在一个表中两个where条件查询，每个条件上都有单独的索引，而且索引的唯一值又非常少的情况下出现，Selectivity 是Cardinality是CBO的两个重新指标，cardinality是估计返回的条数，Selectivity是返记录估计点表的比率。

和BITMAP同样的道理也是当列的唯一值较少时比如性别BITMAP index是不错的方法，BITMAP CONVERSION FROM/TO ROWIDS是cbo自己做你在b*tree索引中基础上再做的bitmap，上面的sql就是根据条件一走btree索引把rowid转换为BITMAP,条件二也是走另一个btree索引把rowid转换为另一个BITMAP,然后两个BITMAP 再做and 合并，合并后的结果再转换为ROWID，表再根据rowid取会行记录过滤没有在索引上的其它条件。

这个特性也有一个开关_b_tree_bitmap_plans，在8i时默认是false,9i后就转为了true,所以如果想禁用可以在sql中加hint也可以alter session修改。

打赏

微信扫一扫，打赏作者吧～