ORACLE 查询中BUFFER SORT问题

[s63403048]

1楼SQL.
2楼执行计划


原始SQL

select y.branid,
               MAX(z.branname) branname,
               SUM(y.amt1) amt1,
               SUM(y.amt2) amt2,
               SUM(y.amt2 - y.amt1) zjamt,
               DECODE(SUM(y.amt1),
                      0,
                      0,
                      SUM(y.amt2 - y.amt1) / SUM(y.amt1) * 100) zjf
          FROM (SELECT c.branid, b.taxexpensemoney amt1, 0 amt2
                  FROM bud_charge_expense a,
                       bud_charge_expensedetail b,
                       (SELECT a.branid branid
                          FROM sys_bran a
                          START WITH a.branid in
                               (SELECT branid
                                  FROM bud_rpt_brangroupdtl
                                 WHERE 1 = 1
                                   and brangroupno = '1111')
                                    CONNECT BY PRIOR a.branid = a.pbranid) c
                 WHERE a.expenseid = b.expenseid
                   AND a.usestatus = 8
                   AND b.useorgid IN
                       (SELECT branid FROM sys_bran_user WHERE userid = 1)
                   AND b.useorgid = c.branid
                   AND a.expensedate BETWEEN
                       to_date('2012-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
                       to_date('2013-07-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
                UNION ALL
                SELECT c.branid, 0 amt1, b.taxexpensemoney amt2
                  FROM bud_charge_expense a,
                       bud_charge_expensedetail b,
                       (SELECT a.branid branid
                          FROM sys_bran a
                         START WITH a.branid in
                               (SELECT branid
                                  FROM bud_rpt_brangroupdtl
                                 WHERE 1 = 1
                                   and brangroupno = '1111')CONNECT BY PRIOR a.branid = a.pbranid) c
                 WHERE a.expenseid = b.expenseid
                   AND a.usestatus = 8
                   AND b.useorgid IN
                       (SELECT branid FROM sys_bran_user WHERE userid = 1)
                   AND b.useorgid = c.branid
                   AND a.expensedate BETWEEN
                       to_date('2013-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
                       to_date('2013-07-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) y,
               sys_bran z
         WHERE y.branid = z.branid
         GROUP BY y.branid

查询效率无问题
但会存在数据重复读取.

修改为以下SQL后.结果数据无问题.但会出现BUFFER SORT

select sum(c.amt1) amt1,
       sum(c.amt2) amt2,
       sum(c.amt2 - c.amt1) zjamt,
       case
         when (decode(SUM(c.amt1),
                      0,
                      1,
                      SUM((c.amt2 - c.amt1)) / sum(c.amt1)) * 100) = 0 then
          '0' || '%'
         else
          to_char(decode(sum(c.amt1),
                         0,
                         1,
                         (SUM((c.amt2 - c.amt1)) / sum(c.amt1)) * 100),
                  'fm999999990.00') || '%'
       end zjf
  from (select y.branid,
               MAX(z.branname) branname,
               SUM(y.amt1) amt1,
               SUM(y.amt2) amt2,
               SUM(y.amt2 - y.amt1) zjamt,
               DECODE(SUM(y.amt1),
                      0,
                      0,
                      SUM(y.amt2 - y.amt1) / SUM(y.amt1) * 100) zjf
          FROM (SELECT c.branid, b.taxexpensemoney amt1, 0 amt2
                  FROM bud_charge_expense a,
                       bud_charge_expensedetail b,
                       (SELECT a.branid branid
                          FROM sys_bran a
                         where a.branid in
                               (SELECT branid
                                  FROM bud_rpt_brangroupdtl
                                 WHERE 1 = 1
                                   and brangroupno = '1111')) c
                 WHERE a.expenseid = b.expenseid
                   AND a.usestatus = 8
                   AND b.useorgid IN
                       (SELECT branid FROM sys_bran_user WHERE userid = 1)
                   AND b.useorgid = c.branid
                   AND a.expensedate BETWEEN
                       to_date('2012-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
                       to_date('2013-07-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
                UNION ALL
                SELECT c.branid, 0 amt1, b.taxexpensemoney amt2
                  FROM bud_charge_expense a,
                       bud_charge_expensedetail b,
                       (SELECT a.branid branid
                          FROM sys_bran a
                         where a.branid in
                               (SELECT branid
                                  FROM bud_rpt_brangroupdtl
                                 WHERE 1 = 1
                                   and brangroupno = '1111')) c
                 WHERE a.expenseid = b.expenseid
                   AND a.usestatus = 8
                   AND b.useorgid IN
                       (SELECT branid FROM sys_bran_user WHERE userid = 1)
                   AND b.useorgid = c.branid
                   AND a.expensedate BETWEEN
                       to_date('2013-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
                       to_date('2013-07-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) y,
               sys_bran z
         WHERE y.branid = z.branid
         GROUP BY y.branid) c

[s63403048]

执行计划截图

• 打赏
• 举报

回复 赞

[s63403048]

人呢

• 打赏
• 举报

回复 赞