急，急。。优化pl-sql----执行很久时间

[四处转]

select DM.XF_TXDATE as 日期,
DM.XF_COLOR as 顏色,
DM.XF_STYLE as 款號,

sum(-DM.XF_QTY)+sum(vw.xf_qoh) as 收貨件數,
sum(-DM.XF_QTY) as 累計銷售件數,--銷售件數


-- sum(vw.xf_qoh) as 庫存件數
case
when XF_TXDATE between ( TRUNC ( SYSDATE - 7, 'DD')
- TO_CHAR (SYSDATE - 7, 'D')
+ 2)
and ( TRUNC ( SYSDATE - 7, 'DD')
- TO_CHAR ( SYSDATE - 7, 'D')
+ 8)
then
SUM (XF_QOH) ---上周
else
0
end
as qty,
case
when XF_TXDATE >=
TRUNC (NEXT_DAY( SYSDATE - 8, 1) + 1)
and XF_TXDATE <=
TRUNC (NEXT_DAY( SYSDATE - 8, 1) + 7) + 1 --本週
then
SUM (XF_QOH)
end
as col,
case
when XF_TXDATE between TRUNC (SYSDATE) - 14
and TRUNC (SYSDATE) - 8 --上上周
then
SUM (XF_QOH)
else
0
end
as shangshangzhou,
case
when XF_TXDATE between TRUNC (SYSDATE)-21
and TRUNC (SYSDATE)-15 --上上上周
then
SUM (XF_QOH)
else
0
end
-- decode((SUM(-DM.XF_QTY)+SUM(VW.XF_QOH)),0,1,sum(-DM.XF_QTY)/(SUM(-DM.XF_QTY)+SUM(VW.XF_QOH)))||'%' as 完成率
from XF_ITEMDM dm, BOS_QOH_VW vw
where -- dm.XF_TXDATE BETWEEN TO_DATe('2015-01-11','yyyy-MM-dd') and
-- to_date('2015-01-12','yyyy-MM-dd')
DM.XF_PLU = VW.XF_PLU
-- AND VW.XF_STORECODE=VW.XF_STORECODE
-- and STR.XF_GROUP2 in ('GZ','SZ')
and VW.XF_STORECODE <> '5AW500'
AND DM.XF_GROUP4 = 'SS15'
group by
DM.XF_COLOR,
DM.XF_STYLE,
dm.XF_TXDATE

[小灰狼W]

提供执行计划 如果涉及到了视图，提供视图定义

[四处转]

DROP VIEW POS_CN.BOS_QOH_VW; 视图POS_CN.BOS_QOH_VW /* Formatted on 2015/5/26 18:06:53 (QP5 v5.227.12220.39724) */ CREATE OR REPLACE FORCE VIEW POS_CN.BOS_QOH_VW ( XF_BALDATE, XF_STORECODE, XF_PLU, XF_STYLE, XF_COLOR, XF_SIZE, XF_ITEMLOTNUM, XF_QOH, XF_INVTQOH0, XF_INVTQOH1, XF_INVTQOH2, XF_INVTQOH3, XF_INVTQOH4, XF_INVTQOH5 ) AS SELECT qoh.xf_baldate, qoh.xf_storecode, qoh.xf_plu, qoh.xf_style, qoh.xf_color, qoh.xf_size, qoh.xf_itemlotnum, qoh.xf_qoh, qoh.xf_invtqoh0, qoh.xf_invtqoh1, qoh.xf_invtqoh2, qoh.xf_invtqoh3, qoh.xf_invtqoh4, qoh.xf_invtqoh5 FROM xf_qohbal qoh WHERE qoh.xf_baldate < TRUNC (SYSDATE) UNION ALL SELECT TRUNC (SYSDATE), iex.xf_storecode, iex.xf_plu, iex.xf_style, iex.xf_color, iex.xf_size, iex.xf_itemlotnum, iex.xf_qoh, iex.xf_invtqoh0, iex.xf_invtqoh1, iex.xf_invtqoh2, iex.xf_invtqoh3, iex.xf_invtqoh4, iex.xf_invtqoh5 FROM xf_itemext iex WHERE iex.xf_qoh <> 0;

[小灰狼W]

还有执行计划哈。。。

[四处转]

无配置执行计划 啊