Oracle
ORACLE 查询中BUFFER SORT问题
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区1楼SQL.
2楼执行计划
原始SQL
查询效率无问题
但会存在数据重复读取.
修改为以下SQL后.结果数据无问题.但会出现BUFFER SORT
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
...全文
