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

最近有客户问了一个与时间字段相关的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语句中能看出来其precdate的类型为文本型的，由于谓词使用了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类型，不要为了开发省事使用文本存储。

文章来源：http://blog.csdn.net/liufeng1980423/article/details/51190392