结论：避免对列进行运算，否则将用不到索引，除非使用函数索引。
请看一个开发人员中非常常见的写法：
where trunc(created)>=TO_DATE('2013-12-14', 'YYYY-MM-DD')
and trunc(created)<=TO_DATE('2013-12-15', 'YYYY-MM-DD')
这个写法会有什么问题呢？具体见试验过程如下：
*/
drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(created);
set autotrace traceonly
set linesize 1000
--以下写法大量的出现在开发人员的代码中，是一个非常常见的通病,由于对列进行了运算，所以用不到索引，如下：
select * from t where trunc(created)>=TO_DATE('2013-12-14', 'YYYY-MM-DD')
and trunc(created)<=TO_DATE('2013-12-15', 'YYYY-MM-DD');
执行计划
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 296 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 12 | 2484 | 296 (2)| 00:00:04 |
--------------------------------------------------------------------------
1 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"))>=TO_DATE(' 2013-12-14
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
TRUNC(INTERNAL_FUNCTION("CREATED"))<=TO_DATE(' 2013-12-15 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1049 consistent gets
0 physical reads
0 redo size
1390 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
---调整为如下等价语句后，就可以用到索引了。
select * from t where created>=TO_DATE('2013-12-14', 'YYYY-MM-DD')
and created
执行计划
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
2 - access("CREATED">=TO_DATE(' 2013-12-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "CREATED"
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1393 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