Oracle不走索引hint,加hints不走索引（位图索引）

各位大神

从测试平台迁移到正式平台，处理一个存储过程变得异常缓慢，最终锁定是一个语句执行特别慢

DELETE FROM BILL_DETAIL_TMP T WHERE T.BILL_PLAT = :B3 AND T.BILL_TYPE = :B2 AND T.MIN_HCODE = :B1

对比执行计划，发现正式平台需要执行全表扫描，而测试上就直接通过位图索引IDX1就可以了

发现测试平台和正式平台表结构一样，索引也一样

重新分析了表和索引，没有效果，甚至重建了索引还是不走这个索引，还有这个不是B-Tree索引，不应该是有非空数据造成的，而且这个表是不会有空数据的

非常郁闷！！！

以下是测试和正式的执行计划对比，请诸位出手

【测试】

DELETE FROM BILL_DETAIL_TMP T WHERE T.BILL_PLAT = :B3 AND T.BILL_TYPE = :B2 AND T.MIN_HCODE = :B1

DELETE STATEMENT, GOAL = ALL_ROWS                        Cost=1        Cardinality=1        Bytes=132

DELETE        Object owner=FEE_BILL        Object name=BILL_DETAIL_TMP

TABLE ACCESS BY INDEX ROWID        Object owner=FEE_BILL        Object name=BILL_DETAIL_TMP        Cost=1        Cardinality=1        Bytes=132

BITMAP CONVERSION TO ROWIDS

BITMAP INDEX SINGLE VALUE        Object owner=FEE_BILL        Object name=IDX1_BILL_DETAIL_TMP

【测试】

DELETE  /*+INDEX(BILL_DETAIL_TMP IDX1_BILL_DETAIL_TMP)*/FROM BILL_DETAIL_TMP T WHERE T.BILL_PLAT = :B3 AND T.BILL_TYPE = :B2 AND T.MIN_HCODE = :B1

DELETE STATEMENT, GOAL = ALL_ROWS                        Cost=1        Cardinality=1        Bytes=132

DELETE        Object owner=FEE_BILL        Object name=BILL_DETAIL_TMP

TABLE ACCESS BY INDEX ROWID        Object owner=FEE_BILL        Object name=BILL_DETAIL_TMP        Cost=1        Cardinality=1        Bytes=132

BITMAP CONVERSION TO ROWIDS

BITMAP INDEX SINGLE VALUE        Object owner=FEE_BILL        Object name=IDX1_BILL_DETAIL_TMP

【正式】

DELETE FROM BILL_DETAIL_TMP T WHERE T.BILL_PLAT = :B3 AND T.BILL_TYPE = :B2 AND T.MIN_HCODE = :B1

DELETE STATEMENT, GOAL = ALL_ROWS                        Cost=1145        Cardinality=1        Bytes=68

DELETE        Object owner=FEE_BILL        Object name=BILL_DETAIL_TMP

TABLE ACCESS FULL        Object owner=FEE_BILL        Object name=BILL_DETAIL_TMP        Cost=1145        Cardinality=1        Bytes=68

【正式】

DELETE  /*+INDEX(BILL_DETAIL_TMP IDX1_BILL_DETAIL_TMP)*/FROM BILL_DETAIL_TMP T WHERE T.BILL_PLAT = :B3 AND T.BILL_TYPE = :B2 AND T.MIN_HCODE = :B1

DELETE STATEMENT, GOAL = ALL_ROWS                        Cost=1145        Cardinality=1        Bytes=68

DELETE        Object owner=FEE_BILL        Object name=BILL_DETAIL_TMP

TABLE ACCESS FULL        Object owner=FEE_BILL        Object name=BILL_DETAIL_TMP        Cost=1145        Cardinality=1        Bytes=68