Oracle日期类型Date和timestamp需要注意的地方
发布时间：2020-12-12 15:49:25 所属栏目：百科 来源：网络整理
导读：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
|
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)如果在左值上是肯定用不上索引的。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |