group by 效率的问题

Question

注释那句放出来 好像就全表扫描了
select task_code,component_code,operatoruser_code,sum(statistical_data) as data1   
from 
(
select task_code,component_code,operatoruser_code,statistical_data
from T_assessdata where task_code>' ' 
--and operatortime >='20110301000000' and operatortime<='20110930235959'
group by task_code,component_code,operatoruser_code,statistical_data
)a
group by task_code,component_code,operatoruser_code120W条数据 日期条件去掉 0.71秒  加上 4.3秒

Answer 1

operatortime >='20110301000000' 
operatortime 什么格式的？怎么做字符串比较？太耗性能了，换个其它类型的，速度就上去了

Answer 2

operatortime 索引是有的operatortime 也的确是字符串类型的,我看过一个大型数据库设计 它里面的时间也用的字符串类型顺便问下时间用 字符串类型 和 日期类型 各有什么好处呢?

Answer 3

Plan hash value: 1362304809
 
-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |     3 |   135 |  4704   (1)| 00:00:57 |
|   1 |  HASH GROUP BY       |              |     3 |   135 |  4704   (1)| 00:00:57 |
|   2 |   VIEW               | VM_NWVW_0    |     5 |   225 |  4704   (1)| 00:00:57 |
|   3 |    HASH GROUP BY     |              |     5 |   305 |  4704   (1)| 00:00:57 |
|*  4 |     TABLE ACCESS FULL| T_ASSESSDATA | 10268 |   611K|  4703   (1)| 00:00:57 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("OPERATORTIME">='20110901000000' AND 
              "OPERATORTIME"<='20110923235959' AND "TASK_CODE">' ')