在日期字段上查询的效率问题

背景说明：SO表的APPL_DATE字段上具有normal索引，现在要根据该字段按天归类，形成当天的日报。

困难说明：so表是一个存储海量数据的巨表，完成业务统计要求与提升统计效率之间形成了一对矛盾，如下是先后尝试采用的三个方案，最终我们选择了第3号方案。

1.使用trunc(sysdate)

 

view plain copy to clipboard print ?

1. SELECT SO.PROD_ID PROD_ID,  
2.        SO.CHG_SERV_SPEC_ID SERV_ID,  
3.        SO.SO_STS SERV_STS,  
4.        SO.STS ORDER_STS,  
5.        COUNT(1) STATI_QUAITITY,  
6.        SYSDATE STATI_DATE  
7.   FROM SO  
8.  WHERE 1 = 1  
9.    AND TRUNC(SO.APPL_DATE) = TRUNC(SYSDATE)  
10.    AND SO.STS = 'A'  
11.  GROUP BY SO.PROD_ID, SO.CHG_SERV_SPEC_ID, SO.SO_STS, SO.STS  
12.  ORDER BY SO.PROD_ID, SO.CHG_SERV_SPEC_ID, COUNT(1) DESC  

 

 

sql效率：

使用方式INDEX FULL SCAN扫描

cost值=63211，cardinality=15774，实际执行时间200s左右

应用分析：

通过使用trunc函数截断得到当天的所有定单，完美的实现了统计要求，但是这种查询耗费的成本是最高的，耗时更是令人无法忍受，这种方案必定不能通过。

2.使用=sysdate

 

view plain copy to clipboard print ?

1. SELECT SO.PROD_ID PROD_ID,  
2.        SO.CHG_SERV_SPEC_ID SERV_ID,  
3.        SO.SO_STS SERV_STS,  
4.        SO.STS ORDER_STS,  
5.        COUNT(1) STATI_QUAITITY,  
6.        SYSDATE STATI_DATE  
7.   FROM SO  
8.  WHERE 1 = 1  
9.    AND SO.APPL_DATE =SYSDATE  
10.    AND SO.STS = 'A'  
11.  GROUP BY SO.PROD_ID, SO.CHG_SERV_SPEC_ID, SO.SO_STS, SO.STS  
12.  ORDER BY SO.PROD_ID, SO.CHG_SERV_SPEC_ID, COUNT(1) DESC  

 

 

sql效率：

 

使用方式INDEX RANGE SCAN扫描

cost值=11，cardinality=5，实际执行时间0.1s以下

应用分析：

直接和sysdate做等值连接，使用INDEX RANGE SCAN方式扫描，极大的提升了sql查询效率；

但是这种查询只可以获得当前时刻的定单条目，而不能获得统计要求的当天时间范围内的定单条目，因此是完全违背业务要求的，不合格的。

 

3.使用between sysdate-1 and sysdate

 

view plain copy to clipboard print ?

1. SELECT SO.PROD_ID PROD_ID,  
2.        SO.CHG_SERV_SPEC_ID SERV_ID,  
3.        SO.SO_STS SERV_STS,  
4.        SO.STS ORDER_STS,  
5.        COUNT(1) STATI_QUAITITY,  
6.        SYSDATE STATI_DATE  
7.   FROM SO  
8.  WHERE 1 = 1  
9.    AND so.appl_date BETWEEN SYSDATE-1 AND SYSDATE  
10.    AND SO.STS = 'A'  
11.  GROUP BY SO.PROD_ID, SO.CHG_SERV_SPEC_ID, SO.SO_STS, SO.STS  
12.  ORDER BY SO.PROD_ID, SO.CHG_SERV_SPEC_ID, COUNT(1) DESC  

 

 

sql效率：

使用方式INDEX RANGE SCAN扫描

cost值=266，cardinality=15481，实际执行时间5s以下

应用分析：

    这里使用BETWEEN SYSDATE-1 AND SYSDATE获取定单条目，使用了INDEX RANGE SCAN扫描记录，sql效率介于1号、2号方案之间，5s以下的执行时间也完全在可忍受的范围之内；

    同时，在时间上去了一个相对的一天，并不是完全的从早上0点到晚上24点，实际操作中会有一些偏差，但这是十分微小的。

    因此，这是一个折中的方案。