ORACLE根据查询条件值的变化自动调整执行计划
环境ORACLE9204 CURSOR_SHARING=EXACT
select t.termid
from w_mohis t
where t.receive between
to_date('2008-01-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2008-01-03 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and t.srvtype = 1
ORACLE选择了利用srvtype 的索引。
select t.termid
from w_mohis t
where t.receive between
to_date('2007-01-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2007-01-03 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and t.srvtype = 1
此时ORACLE选择了利用receive 的索引。
个中原因值得仔细研究
前者的执行计划：
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 1 | | |
|* 1 | TABLE ACCESS BY LOCAL INDEX ROWID| W_MOHIS | 1 | 87 | 1 | 12 | 12 |
|* 2 | INDEX RANGE SCAN | I_MO_SRVTYPE | 1 | | 1 | 12 | 12 |
------------------------------------------------------------------------------------------------------
后者的执行计划：
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 258 | 6966 | 345 | | |
|* 1 | TABLE ACCESS BY LOCAL INDEX ROWID| W_MOHIS | 258 | 6966 | 345 | 8 | 8 |
|* 2 | INDEX RANGE SCAN | I_MO_RECEIVE | 9970 | | 29 | 8 | 8 |
------------------------------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9844649/viewspace-580071/ ，如需转载，请注明出处，否则将追究法律责任。
