生产中有一个报表查询是下面这样的,客户反映速度太慢,让调优一下,由于表数据比较大,而且逻辑复杂了些,着实让我头疼。
select to_char(t.day_id, 'day'),
t.day_id,
(select 100.00 * decode(sum(t1.col1), null, 0, sum(t1.col2)) /
decode(sum(t1.col3), null, -1, sum(t1.col2))
from tablea t1
where t1.day_id = t.day_id
and t1.r = (select max(tt.r)
from tablea tt)
and t1.clo4=H
and t1.col5 = 'A') a1,
(select 100.00 * decode(sum(t2.col1), null, 0, sum(t2.col2)) /
decode(sum(t2.col3), null, -1, sum(t2.col2))
from tablea t2
where t2.day_id = t.day_id
and t2.r = (select max(tt.r)
from tablea tt)
and t2.d = H) a2,
(select 100.00 * decode(sum(t3.col1), null, 0, sum(t3.col2)) /
decode(sum(t3.col3), null, -1, sum(t3.col2))
from tablea t3
where t3.day_id = t.day_id
and t3.r = (select max(tt.r)
from tablea tt)
and t3.a= H
and t3.b= 'A') a3,
(select 100.00 * decode(sum(t4.col1), null, 0, sum(t4.col2)) /
decode(sum(t4.col3), null, -1, sum(t4.col2))
from tablea t4
where t4.day_id = t.day_id
and t4.r= (select max(tt.r)
from tablea tt)
and t4.a = H) a4
from tablea t
where t.day_id between to_date('20080501', 'yyyymmdd') and
to_date('20080530', 'yyyymmdd')
and t.r = (select max(tt.r)
from tablea tt)
group by t.day_id
order by t.DAY_ID
其实仔细分析下,and t.r = (select max(tt.r) from tablea tt) 这个子查询tablea 表的数据量很大,而且还用不上索引。所以决定从这入手
每一个的查询中都用这个作为条件,也就是说其实完全可以把符合(select max(tt.r) from tablea tt)这个条件的数据单独拿出来做一个查询用的表,这样生产应用的查询基本是在千分之一的数据量下查询,而且查询语句也更简单了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11244507/viewspace-438932/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11244507/viewspace-438932/