去掉对列的函数操作以及建立partition提高SQL执行效率

SQL很简单，查询一个表在日期区间内的记录条数。
原来是这么写的

SELECT COUNT(1) 
FROM TABLE_X A 
WHERE 1 = 1 
AND TO_CHAR(A.starttime, 'YYYYMMDDHH24MISS') >= '20010101121212'
AND TO_CHAR(A.endtime, 'YYYYMMDDHH24MISS') <= '20130101121212'

这张表1000万级数据，查询count用了15秒。

改成下面这样

SELECT COUNT(1) 
FROM TABLE_X A 
WHERE 1 = 1 
AND A.starttime >= TO_DATE('20010101121212', 'YYYYMMDDHH24MISS')
AND A.endtime  <= TO_DATE('20130101121212' , 'YYYYMMDDHH24MISS')

查询count只需要5秒了。


我们发现把左边字段的TO_CHAR函数去掉，SQL执行效率显著提高了。
为什么呢，以下是摘录自网络的一段：

任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。  
例：下列SQL条件语句中的列都建有恰当的索引，但30万行数据情况下执行速度却非常慢：  
select * from record where  substrb(CardNo,1,4)='5378'(13秒) 
select * from record where  amount/30< 1000（11秒） 
select * from record where  to_char(ActionTime,'yyyymmdd')='19991201'（10秒） 
由于where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的，因此它不得不进行表扫描，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表扫描，因此将SQL重写如下：
select * from record where CardNo like  '5378%'（< 1秒）（操作放在了右边）
select * from record where amount  < 1000*30（< 1秒）（操作放在了右边）
select * from record where ActionTime= to_date ('19991201' ,'yyyymmdd')（< 1秒）（操作放在了右边）
差别是很明显的！


另外我们表里已对endtime进行了partition,每2个小时段的数据分一个partition,这样可显著调高查询效率

  (
    PARTITION "PART_201201011400" VALUES LESS THAN (TO_DATE(' 2012-01-01 14:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
    PARTITION "PART_201201011600" VALUES LESS THAN (TO_DATE(' 2012-01-01 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
    PARTITION "PART_MAX" VALUES LESS THAN (MAXVALUE) 
  ) ;