sql语句如下:
select zonecode as zonecode,
nvl(t.chargesrc, '-1') as chargesrc ,
nvl(t.infectapp, '-1') as infectapp,
sum(t.counts) as counts,
sum(case
when t.flwcount = 1 then
1
else
0
end) as sumflw1, --完成一次随访
sum(case
when t.flwcount = 2 then
1
else
0
end) as sumflw2, --完成两次随访
sum(case
when t.flwcount = 2 then
1
else
0
end) as sumflw3, --完成三次随访
sum(case
when t.flwcount = 2 then
1
else
0
end) as sumflw4, --完成四次随访
sum(case
when t.cd4count = 1 then
1
else
0
end) as cd41, --完成一次cd4检测
sum(case
when t.cd4count = 2 then
1
else
0
end) as cd42, --完成两次cd4检测
sum(case
when t.virunlncount = 1 then
1
else
0
end) as viruln1, --完成一次病毒载量
sum(t.virunlnno) as noviruln ,--病毒载量被抑制
sum(t.initvirunlncount) as avirulnsum
from (select a.card_id as card_id,
a.zonecode as zonecode,
a.chargesrc as chargesrc,
a.infectapp as infectapp,
sum(case
when a.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') then
1
else
0
end) as counts,
0 as flwcount, -- 当年完成随访次数
sum(case
when a.cd4 is not null and
a.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') then
1
else
0
end) as cd4count, --当年完成cd4检测数
sum(case
when a.viruln is not null and
a.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') then
1
else
0
end) as virunlncount, --当年完成病毒载量
0 as virunlnno,
sum(case
when a.viruln is not null and
a.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') then
1
else
0
end) as initvirunlncount
from aidszh_sgra_child_info a
where not exists (select ''
from aidszh_sgra_child_flw m
where a.card_id = m.card_id)
group by a.card_id, a.zonecode, a.chargesrc, a.infectapp
union all
select b.card_id,
b.zonecode as zonecode,
b.chargesrc as chargesrc,
b.infectapp as infectapp,
sum(case
when b.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '4' and ( d.end_cause <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) then
1
else
0
end) as counts,
sum(case
when c.card_id is not null and
b.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '4' and ( d.end_cause <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) then
1
else
0
end) as flwcount, --当年完成随访次数
sum(case
when c.cd4 is not null and
b.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '4' and ( d.end_cause <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) then
1
else
0
end) as cd4count, --完成cd4检测数
sum(case
when c.viruln is not null and
b.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '4' and ( d.end_cause <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) then
1
else
0
end) as virunlncount,
sum(case
when b.viruln is not null and b.viruln <> '0' and
c.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') and
b.dt_treatbegin >= to_date('2009-01-01', 'yyyy-mm-dd') and
c.viruln = '0' and
(c.dt_flworinterp - b.dt_treatbegin >= 180) and
(c.dt_flworinterp - b.dt_treatbegin <= 360)
and c.is_flw = '1' and c.clinic_treatment != '4' and ( d.end_cause <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) then
1
else
0
end) as virunlnno, --病毒载量被抑制
sum(case
when b.viruln is not null and
b.dt_treatbegin >= to_date('2009-01-01', 'yyyy-mm-dd') and
c.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) then
1
else
0
end) as initvirunlncount
from aidszh_sgra_child_info b, aidszh_sgra_child_flw c,aidszh_sgra_child_newstatus d
where b.card_id = c.card_id and d.card_id=b.card_id
group by b.card_id, b.zonecode, b.chargesrc, b.infectapp) t
group by zonecode,
nvl(t.chargesrc, '-1'),
nvl(t.infectapp, '-1')
其中,where语句条件列中都有索引。但是他没有走。下面看一下sql执行计划:执行效率太慢,怎么才能走的快些呢??
select zonecode as zonecode,
nvl(t.chargesrc, '-1') as chargesrc ,
nvl(t.infectapp, '-1') as infectapp,
sum(t.counts) as counts,
sum(case
when t.flwcount = 1 then
1
else
0
end) as sumflw1, --完成一次随访
sum(case
when t.flwcount = 2 then
1
else
0
end) as sumflw2, --完成两次随访
sum(case
when t.flwcount = 2 then
1
else
0
end) as sumflw3, --完成三次随访
sum(case
when t.flwcount = 2 then
1
else
0
end) as sumflw4, --完成四次随访
sum(case
when t.cd4count = 1 then
1
else
0
end) as cd41, --完成一次cd4检测
sum(case
when t.cd4count = 2 then
1
else
0
end) as cd42, --完成两次cd4检测
sum(case
when t.virunlncount = 1 then
1
else
0
end) as viruln1, --完成一次病毒载量
sum(t.virunlnno) as noviruln ,--病毒载量被抑制
sum(t.initvirunlncount) as avirulnsum
from (select a.card_id as card_id,
a.zonecode as zonecode,
a.chargesrc as chargesrc,
a.infectapp as infectapp,
sum(case
when a.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') then
1
else
0
end) as counts,
0 as flwcount, -- 当年完成随访次数
sum(case
when a.cd4 is not null and
a.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') then
1
else
0
end) as cd4count, --当年完成cd4检测数
sum(case
when a.viruln is not null and
a.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') then
1
else
0
end) as virunlncount, --当年完成病毒载量
0 as virunlnno,
sum(case
when a.viruln is not null and
a.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') then
1
else
0
end) as initvirunlncount
from aidszh_sgra_child_info a
where not exists (select ''
from aidszh_sgra_child_flw m
where a.card_id = m.card_id)
group by a.card_id, a.zonecode, a.chargesrc, a.infectapp
union all
select b.card_id,
b.zonecode as zonecode,
b.chargesrc as chargesrc,
b.infectapp as infectapp,
sum(case
when b.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '4' and ( d.end_cause <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) then
1
else
0
end) as counts,
sum(case
when c.card_id is not null and
b.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '4' and ( d.end_cause <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) then
1
else
0
end) as flwcount, --当年完成随访次数
sum(case
when c.cd4 is not null and
b.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '4' and ( d.end_cause <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) then
1
else
0
end) as cd4count, --完成cd4检测数
sum(case
when c.viruln is not null and
b.dt_treatbegin <= to_date('2009-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '4' and ( d.end_cause <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) then
1
else
0
end) as virunlncount,
sum(case
when b.viruln is not null and b.viruln <> '0' and
c.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') and
b.dt_treatbegin >= to_date('2009-01-01', 'yyyy-mm-dd') and
c.viruln = '0' and
(c.dt_flworinterp - b.dt_treatbegin >= 180) and
(c.dt_flworinterp - b.dt_treatbegin <= 360)
and c.is_flw = '1' and c.clinic_treatment != '4' and ( d.end_cause <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) then
1
else
0
end) as virunlnno, --病毒载量被抑制
sum(case
when b.viruln is not null and
b.dt_treatbegin >= to_date('2009-01-01', 'yyyy-mm-dd') and
c.tm_create <= to_date('2009-11-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) then
1
else
0
end) as initvirunlncount
from aidszh_sgra_child_info b, aidszh_sgra_child_flw c,aidszh_sgra_child_newstatus d
where b.card_id = c.card_id and d.card_id=b.card_id
group by b.card_id, b.zonecode, b.chargesrc, b.infectapp) t
group by zonecode,
nvl(t.chargesrc, '-1'),
nvl(t.infectapp, '-1')
其中,where语句条件列中都有索引。但是他没有走。下面看一下sql执行计划:执行效率太慢,怎么才能走的快些呢??
不走索引有很多原因,
1. 索引限制,不过楼主的SQL 没有看出来。
2. 在表上做下统计在看看:
analyze table tablename compute statistics for all indexes;
3. optimizer_index_cost_adj 参数设置过大.该参数影响优化器选择索引还是全表扫描的倾向,建议将其设为40. 楼主可以在相应字段上用hint 强制走索引看看.比如:select /*+ index(table_name,index_name) */ a.card_id as card_id,
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
where语句条件列中都有索引,case语句里的呢?它们也是要计算的。
优化时建议在sqlplus用trace看看统计,不要光看执行计划。
set autotrace on
执行你的语句,看看统计结果。