时间类型数据存储的效率问题

最近有客户问了一个与时间字段相关的SQL效率问题，数据库为Oracle,SQL语句如下：

select * from sometable where to_date(somedate,'YYYY-MM-DD:HH24:MI:SS')>to_date('2016-04-16','YYYY-MM_DD')

该SQL语句的效率如何。

从SQL语句中能看出来其somedate的类型为文本型的，由于谓词使用了to_date对该列进行了转换，因此该语句的执行计划一定是全表扫描，因此随着表中的数量的增大，其查询效率会越来越低。正确的做法应该将该字段的类型设置为date类型，并在其上建B-TREE索引。具体效率提高多少也要看查询语句，下面做几个测试看看两种情况下效率会相差多少。

1. 创建两张表，一张使用varchar2存储时间值，一张使用date存储，并分别用随机插入100w条从20160401到20160417这17天的数据，并对两个存储时间的字段建索引

SQL*Plus: Release 12.1.0.2.0 Production on 星期二 4月 19 14:04:58 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

上次成功登录时间: 星期二 4月  19 2016 09:41:46 +08:00

连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL> create table testdate(id number primary key, test date);

表已创建。
SQL> begin
  2  for i in 1..1000000 loop
  3  insert into testdate values(i,to_date(TRUNC(DBMS_RANDOM.VALUE(to_number(to_
char(to_date('20160401','yyyymmdd'),'J')),to_number(to_char(to_date('20160417','
yyyymmdd')+1,'J')))),'J')+DBMS_RANDOM.VALUE(1,3600)/3600);
  4  end loop;
  5  end;
  6  /

PL/SQL 过程已成功完成。

SQL> commit;

提交完成。
SQL> create index test_idx on testdate(test);

索引已创建。

SQL> create table testchar as select id,to_char(test,'YYYY-MM-DD:HH24:MI:SS') as
 test from testdate;

表已创建。

SQL> create index testchar_idx on testchar(test);

索引已创建。

测试内容：
分别测试查询两张表中时间大于20160417,20160412,20160403的id的记录数量

SQL> set autot traceonly
SQL>
SQL> select count(id) from testdate where test>to_date('20160417','YYYY-MM-DD');

已用时间:  00: 00: 00.03

执行计划
----------------------------------------------------------
Plan hash value: 190015244

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     9 |   160   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_IDX | 51476 |   452K|   160   (0)| 00:00:01 |
------------------------------------------------------------------------------

走索引区域扫描，时间为0.03s

SQL> select count(id) from testdate where test>to_date('20160412','YYYY-MM-DD');


已用时间:  00: 00: 00.09

执行计划
----------------------------------------------------------
Plan hash value: 3044144586

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     9 |   698   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| TESTDATE |   308K|  2712K|   698   (3)| 00:00:01 |
-------------------------------------------------------------------------------
全表扫描，时间为0.09s

SQL> select count(id) from testdate where test>to_date('20160403','YYYY-MM-DD');


已用时间:  00: 00: 00.09

执行计划
----------------------------------------------------------
Plan hash value: 3044144586

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     9 |   698   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| TESTDATE |   779K|  6849K|   698   (3)| 00:00:01 |
-------------------------------------------------------------------------------

全表扫描0.09s

*************************************************************************************

SQL> select count(id) from testchar where to_date(test,'YYYY-MM-DD:HH24:MI:SS')>
to_date('20160417','YYYY-MM-DD');

已用时间:  00: 00: 01.07

执行计划
----------------------------------------------------------
Plan hash value: 2794086230

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    25 |  1175   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |    25 |            |          |
|*  2 |   TABLE ACCESS FULL| TESTCHAR | 50000 |  1220K|  1175   (3)| 00:00:01 |
-------------------------------------------------------------------------------
全表扫描：1.07s

SQL> select count(id) from testchar where to_date(test,'YYYY-MM-DD:HH24:MI:SS')>
to_date('20160412','YYYY-MM-DD');

已用时间:  00: 00: 01.10

执行计划
----------------------------------------------------------
Plan hash value: 2794086230

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    25 |  1175   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |    25 |            |          |
|*  2 |   TABLE ACCESS FULL| TESTCHAR | 50000 |  1220K|  1175   (3)| 00:00:01 |
-------------------------------------------------------------------------------
全表扫描1.10s

SQL> select count(id) from testchar where to_date(test,'YYYY-MM-DD:HH24:MI:SS')>
to_date('20160403','YYYY-MM-DD');

已用时间:  00: 00: 01.13

执行计划
----------------------------------------------------------
Plan hash value: 2794086230

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    25 |  1175   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |    25 |            |          |
|*  2 |   TABLE ACCESS FULL| TESTCHAR | 50000 |  1220K|  1175   (3)| 00:00:01 |
-------------------------------------------------------------------------------
全表扫描1.13s

结论：

如果查询的数据相对于表的总记录数较小的情况下，testdate会选择索引，时间为0.03s,如果较大的情况，会选择全表扫描，时间为0.09s, 但是testchar不论什么情况都走全表扫描，而且每条记录都要使用to_date进行转换，因此其时间也比较固定为1.13s,比testdate走索引慢了38倍，比testdate走全表扫描慢了12倍。

这还只是单用户并且只有两个字段的情况下，如果多用户多字段时间相差的会更多。所以建议存储时间信息还是用原生的date类型，不要为了开发省事使用文本存储。