我想用下面的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;执行计划
----------------------------------------------------------
Plan hash value: 2247359995----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 29 | 3382 (1)| 00:00:41 || 1 | SORT ORDER BY | | 1 | 29 | 3382 (1)| 00:00:41 ||* 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
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;执行计划
----------------------------------------------------------
Plan hash value: 2247359995----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 29 | 3382 (1)| 00:00:41 || 1 | SORT ORDER BY | | 1 | 29 | 3382 (1)| 00:00:41 ||* 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
2、order by 是一定需要吗?
1.我在temperature表的check_time和temperature两项建了索引
2.我只是想按1到12排序
1.我在temperature表的check_time和temperature两项建了索引
2.我只是想按1到12排序看你的执行计划好像 索引没有用上啊?
对表有分析过吗或者使用hits 试试吧
1.我在temperature表的check_time和temperature两项建了索引
2.我只是想按1到12排序看你的执行计划好像 索引没有用上啊?
对表有分析过吗或者使用hits 试试吧
大哥,你觉得我应该在那些字段加索引好啊?我数据量最大就是那张temperature表,students表了我不知道这样算不算用了强制索引,运行很久都没反应,我是下面这样写的,2是check_time字段的索引,3是temperature字段的索引
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 /*+ INDEX(t,IDX_TEMPERATURE_2) INDEX(t,IDX_TEMPERATURE_3) */ 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;
to_char(t.t_checktime, 'mm')) 这个有创建函数索引吗?
t.temperature 是什么类型,如果是varchar2的话加上‘’号
e.provinceid = '19' 虽然是小表,但是你关联大表的话,还是建个索引看看效率有没有提示吧2、这种优化需要根据你的执行计划去调式,看一下你现在的执行计划有没有改变,是否真正用到了创建的索引,可以先去掉order by看效率怎么样,完了之后再加上,看有多大的影响