Oracle日期类型Date和timestamp需要注意的地方

阳光穿透心脏的1/2处 2022-07-15 00:19 204阅读 0赞

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)如果在左值上是肯定用不上索引的。**