关于隐含参数_b_tree_bitmap_plans

最近碰到这样一个SQL：

SELECT DISTINCT T_GOODS_DECL.DECL_NO,T_GOODS_DECL.DECL_REG_NO,
T_GOODS_DECL.DECL_PERSON_CODE,T_GOODS_DECL.DECL_DATE,
T_GOODS_DECL.CONSIGNOR_CNAME,T_GOODS_DECL.CONSIGNEE_CNAME, T_GOODS_DECL.PROCESS_STATUS,T_GOODS_DECL.DECL_GET_NO,
T_GOODS_DECL.DEST_CODE,T_GOODS_DECL.DECL_TYPE_CODE,
T_GOODS_DECL.INSP_ORG_CODE,
T_GOODS_DECL.INSP_ORG_CODE||T_GOODS_DECL.INSP_DEPT_1,
T_GOODS_DECL.DEST_ORG_CODE
FROM T_GOODS_DECL,T_DECL_GOODS
WHERE ( T_GOODS_DECL.DECL_NO = T_DECL_GOODS.DECL_NO )
AND t_goods_decl.decl_date <= to_date( '2007.09.25','YYYY.MM.DD')
and t_goods_decl.decl_reg_no = '3803000061'
and decl_type_code like '2_'
and org_code like'380300'

[@more@]

执行计划是：

--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 4171074745 ----| | | 98 |
|SORT UNIQUE | | 1 | 139 | 98 |
| TABLE ACCESS BY INDEX ROWID |T_GOODS_DECL | 1 | 123 | 83 |
| NESTED LOOPS | | 2 | 278 | 83 |
| INDEX FAST FULL SCAN |IDX_DECL_NO | 398K| 6M| 4 |
| BITMAP CONVERSION TO ROWIDS | | | | |
| BITMAP AND | | | | |
| BITMAP CONVERSION FROM ROWI| | | | |
| INDEX RANGE SCAN |PK_T_GOODS_DECL | 1M| | |
| BITMAP CONVERSION FROM ROWI| | | | |
| INDEX RANGE SCAN |X_GOODS_DECL_REG_NO | 1M| | |
--------------------------------------------------------------------------------

效率非常低，其中

PK_T_GOODS_DECL是T_GOODS_DECL上的主键(decl_no)
IDX_DECL_NO是T_DECL_GOODS(decl_no)上的索引
X_GOODS_DECL_REG_NO是T_GOODS_DECL(decl_reg_no)上的索引，选择性还不错

按个人的想法，访问T_GOODS_DECL只需要走X_GOODS_DECL_REG_NO就可以了，果然加提示/*+NO_INDEX(T_GOODS_DECL PK_T_GOODS_DECL)*/后性能大为改观，然而应用是不能修改的，经同事指点，原来需要将隐含参数_b_tree_bitmap_plans设置为false.

from metalink:
* fact: Oracle Server - Enterprise Edition 9.2
* fact: Oracle Server - Enterprise Edition 9.0.1
* fact: Oracle Server - Enterprise Edition 7.3.4
* fact: Oracle Server - Enterprise Edition 8.1.7
* symptom: Execution plan operation shows bitmap conversion from rowids
* symptom: No bitmap indexes
* symptom: Execution plan shows BITMAP CONVERSION
* cause: In 7.3.4 and in 8.1.7 default value of _b_tree_bitmap_plans is FALSE whereas as of 9.0.1 (and 9.2) the default value is TRUE When _b_tree_bitmap_plans set to true (advice not to change the default setting yourself) the optimizer is allowed to produce bitmap plans for normal b*tree indexes even if no bitmap indexes set.



fix:

This is intended behaviour if _b_tree_bitmap_plans set to true
If this is unwanted leave _b_tree_bitmap_plans at default value (false)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/85922/viewspace-976999/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/85922/viewspace-976999/