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, --当年完成检测数
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, --完成检测数
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语句中我都建立的索引,但是不走索引。
执行计划如下所示:
怎么样才能让让效率提高啊。
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, --当年完成检测数
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, --完成检测数
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语句中我都建立的索引,但是不走索引。
执行计划如下所示:
怎么样才能让让效率提高啊。
解决方案 »
- shutdown immediate时,没有commit的事务是提交还是回滚?
- 如何一次导出所有表的索引
- 帮我解决个SQL语句~
- 表数据合并问题
- sql plus中scott.emp如何实现对表操作时用户的参数化,即如何变成类似 name.emp的语句
- 昨天还没问题,今天我的Oracle就出了个问题:数据库未打开,仅允许在固定表/视图中查询...
- oracle建立数据库的问题,请问如何处理hostdef不存在?
- 请教一个oracle9 编译难题(有分)
- oracle如何实现远程调用
- 求助,Oracle10 bin下所有应用程序打开,都提示已停止工作
- PL/SQL 求逆矩阵方法
- ORACLE显示一列问题,急啊!
sum(case
when t.flwcount = 1 then
1
else
0
end) as sumflw1,
改为:
sum (decode(t.flwcount,1,1,0))
比如union all 后面的那句加上/*+ INDEX(tab1.col1 tab2.col2 ...) */
测试表比较小吧,不能说明问题
用大表来比较下
用索引性能也不一定就比不用索引高
如果是case方式,数据库执行是这样:
if (t.flwcount = 1) {
1;
} else {0}
而使用decode时:
case 1 : 1;break;
default : 0;
如果就两个值比较,当然效率一样,如果多个值比较,decode明显比case when快
官方说明是
Oracle now supports simple and searched CASE statements. CASE statements are similar in purpose to the DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. They are commonly used when breaking categories into buckets like age (for example, 20-29, 30-39, and so on).http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/analysis.htm#DWHSG02012两者工作方式类似,只是作用范围和写法上不一样而已