谁能帮我优化一下这条oracle 的sql语句？

[xieyunchao]

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语句中我都建立的索引，但是不走索引。
执行计划如下所示：


怎么样才能让让效率提高啊。

[道光2008]

形如：
sum(case
when t.flwcount = 1 then
1
else
0
end) as sumflw1,
改为：
sum (decode(t.flwcount,1,1,0))

[alice鑫鑫]

我看看~~

[小灰狼W]

[Quote=引用 1 楼 warison2008 的回复:]
形如：
sum(case
            when t.flwcount = 1 then
              1
            else
              0
          end) as sumflw1,
改为：
sum (decode(t.flwcount,1,1,0))
[/Quote]
能简化代码，但不能优化效率

[小灰狼W]

试试Hint
比如union all 后面的那句加上/*+ INDEX(tab1.col1 tab2.col2 ...) */
测试表比较小吧，不能说明问题
用大表来比较下
用索引性能也不一定就比不用索引高

[道光2008]

[Quote=引用 3 楼 wildwave 的回复:]
引用 1 楼 warison2008 的回复:
形如：
sum(case
             when t.flwcount = 1 then
              1
             else
              0
           end) as sumflw1,
改为：
sum (decode(t.flwcount,1,1,0))

能简化代码，但不能优化效率
[/Quote]
这么肯定？
如果是case方式，数据库执行是这样：
if (t.flwcount = 1) {
1;
} else {0}
而使用decode时：
case 1 : 1；break；
default : 0；
如果就两个值比较，当然效率一样，如果多个值比较，decode明显比case when快

[小灰狼W]

[Quote=引用 5 楼 warison2008 的回复:]
引用 3 楼 wildwave 的回复:
引用 1 楼 warison2008 的回复:
形如：
sum(case
             when t.flwcount = 1 then
              1
             else
              0
           end) as sumflw1,
改为：
sum (decode(t.flwcount,1,1,0))

能简化代码，但不能优化效率

这么肯定？
如果是case方式，数据库执行是这样：
if (t.flwcount = 1) {
1;
} else {0}
而使用decode时：
case 1 : 1；break；
default :  0；
如果就两个值比较，当然效率一样，如果多个值比较，decode明显比case  when快
[/Quote]
?你从哪里看的
官方说明是
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

两者工作方式类似，只是作用范围和写法上不一样而已

[sxq129601]

应该都差不多的decode只是ORACLE得语法，只为了简单方便

[crazylaa]

case when 是标准SQL？