Oracle中，常用的日期类型有Date和timestamp,当表定义好之后，写sql需要注意什么问题呢？来做一个实现：
drop table test purge;
drop table test1 purge;create table test
(
id number,
create_time date
);
create table test1
(
id number,
create_time timestamp
);
insert into test select level,sysdate-(level*0.001) from dual connect by level <10000;
commit;
insert into test1 select level,sysdate-(level*0.001) from dual connect by level <10000;
commit;
create index ind_t_create_time on test(create_time);
create index ind_t1_create_time on test1(create_time);
exec dbms_stats.gather_table_stats(user,'test',cascade => true);
exec dbms_stats.gather_table_stats(user,'test1',cascade => true);
sql> set autotrace traceonly
sql> select * from test where
create_time >= to_date('2016-10-26 10:03:46','yyyy-MM-dd HH24:mi:ss');
已选择48行。
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 1822039520
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 588 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 49 | 588 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_CREATE_TIME | 49 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CREATE_TIME">=TO_DATE(' 2016-10-26 10:03:46','syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1452 bytes sent via sql*Net to client
505 bytes received via sql*Net from client
5 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
48 rows processed
sql> select * from test where
create_time >= to_timestamp('2016-10-26 10:03:46','yyyy-MM-dd HH24:mi:ss');
已选择48行。
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 6000 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 500 | 6000 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("CREATE_TIME")>=TIMESTAMP' 2016-10-26
10:03:46.000000000')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
1316 bytes sent via sql*Net to client
505 bytes received via sql*Net from client
5 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
48 rows processed
sql> select * from test1 where
create_time >= to_date('2016-10-26 10:03:46','yyyy-MM-dd HH24:mi:ss');
已选择48行。
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3941734091
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 735 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 49 | 735 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T1_CREATE_TIME | 49 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CREATE_TIME">=TIMESTAMP' 2016-10-26 10:03:46')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1452 bytes sent via sql*Net to client
505 bytes received via sql*Net from client
5 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
48 rows processed
sql> select * from test1 where
create_time >= to_timestamp('2016-10-26 10:03:46','yyyy-MM-dd HH24:mi:ss');
已选择48行。
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3941734091
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 735 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 49 | 735 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T1_CREATE_TIME | 49 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CREATE_TIME">=TIMESTAMP' 2016-10-26 10:03:46.000000000')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1452 bytes sent via sql*Net to client
505 bytes received via sql*Net from client
5 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
48 rows processed
总结：如果字段类型是date，SQL查询是条件右值如果是to_date则可以用到索引，如果是to_timestamp是用不到索引的；如果字段类型是timestamp，SQL查询是条件右值如果是to_date或to_timestamp都是可以用到索引的。注意的是to_date(create_time)如果在左值上是肯定用不上索引的。 原文链接：https://www.f2er.com/oracle/212094.html
