ORACLE 查询中BUFFER SORT问题

s63403048 2013-07-30 04:51:42
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
...全文
142 点赞 收藏 2
写回复
2 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
s63403048 2013-07-31
人呢
回复
s63403048 2013-07-30

执行计划截图
回复
相关推荐
发帖
Oracle
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2013-07-30 04:51
社区公告
暂无公告