新手请教啊，求各位进来看看~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
我想用下面的sql统计出一年12个月体温正常的人数作为报表，但是查询速度要十秒，谁能帮我优化一下啊？谢谢~~
with m as
(select rownum s_date from dual connect by rownum between 1 and 12)
select m.s_date,
case
when t.num is null then
0
else
t.num
end
from m
left outer join (select count(*) num,
to_char(t.t_checktime, 'mm') as checktime
from temperature t,
students s,
eduorganization e ,
province p
where t.t_checktime >= to_date( '2013-01-01 00:00:00' ,'yyyy-mm-dd hh24:mi:ss')
and t.t_checktime < to_date( '2013-12-31 23:59:59' ,'yyyy-mm-dd hh24:mi:ss')
and t.temperature >= 36 and t.temperature <= 37.5
and t.stu_obid = s.id
and s.organization_obid = e.id
and e.provinceid = '19'
and p.id = 1
group by to_char(t.t_checktime, 'mm')) t
on t.checktime = m.s_date
order by m.s_date asc;
|* 2 | HASH JOIN OUTER | | 1 | 29 | 3381 (1)| 00:00:41 |
| 3 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | COUNT | | | | | |
|* 5 | CONNECT BY WITHOUT FILTERING| | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | VIEW | | 1 | 16 | 3378 (1)| 00:00:41 |
| 8 | HASH GROUP BY | | 1 | 141 | 3378 (1)| 00:00:41 |
|* 9 | HASH JOIN | | 1771 | 243K| 3377 (1)| 00:00:41 |
|* 10 | HASH JOIN | | 139 | 13900 | 7 (15)| 00:00:01 |
| 11 | NESTED LOOPS | | 35 | 1260 | 3 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | SYS_C0011082 | 1 | 2 | 0 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | EDUORGANIZATION | 35 | 1190 | 3 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | STUDENTS | 139 | 8896 | 3 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | TEMPERATURE | 1771 | 72611 | 3370 (1)| 00:00:41 |
----------------------------------------------------------------------------------------------------
报表 优化 sql
分享到：
[解决办法]
1、创建的是什么索引
to_char(t.t_checktime, 'mm')) 这个有创建函数索引吗？
t.temperature 是什么类型，如果是varchar2的话加上‘’号
e.provinceid = '19' 虽然是小表，但是你关联大表的话，还是建个索引看看效率有没有提示吧
2、这种优化需要根据你的执行计划去调式，看一下你现在的执行计划有没有改变，是否真正用到了创建的索引，可以先去掉order by看效率怎么样，完了之后再加上，看有多大的影响
