ORACLE 一个比较复杂的查询语句,一直处于执行, 我要怎么优化才能让它正常执行出结果呢?

SmartFitMan 2018-12-25 09:57:49
SQL查询语句如下:


with cftb as
(
select
xmjbxx.werks,
t001w.name1,
xmjbxx.pspid,
xmjbxx.proj_post1,
xmjbxx.posid dt_wbsid,
xmjbxx.prps_post1,
prps.posid wbs_id,
prps.post1 wbs_name,
eban.banfn, -- 采购申请号
eban.bnfpo, -- 采购申请行项目号
eban.matnr, -- 物料编码
eban.txz01, -- 物料名称
nvl2(wlpzb.mm_id,1,0) sw_id_manger,
nvl2(wlpzb.mm_id,1,eban.menge) menge,
eban.meins, -- 计量单位
eban.preis, -- 需求单价
nvl2(wlpzb.mm_id,1,eban.menge)*eban.preis xqje, -- 需求金额
ekpo.ebeln, -- 采购订单号
ekpo.ebelp, -- 采购订单行项目号
ekpo.brtwr/ekpo.menge hsdj -- 订单含税单价
from view_erp_ebkn ebkn -- 采购订单与wbs关联关系
left join view_erp_prps prps on prps.pspnr = ebkn.ps_psp_pnr -- WBS(工作中断结构) 元素主数据
inner join t_sbwcetj_xmjbxx xmjbxx on substr(prps.posid,1,12) = xmjbxx.pspid -- 统计范围表
left join view_erp_t001w t001w on t001w.werks = xmjbxx.werks -- 工厂/分支机构
left join view_erp_eban eban on eban.banfn = ebkn.banfn and eban.bnfpo = ebkn.bnfpo -- 预购订单表 (匹配订单编号 和 项目编号)
left join odssjqc.t_wlpzb wlpzb on wlpzb.mm_id = eban.matnr -- 21736
left join ( select * from view_erp_ekpo where loekz = ' ' and pstyp <> 9 ) ekpo on ekpo.banfn = ebkn.banfn and ekpo.bnfpo = ebkn.bnfpo -- 订单表
where eban.loekz = ' ' and eban.frgkz = 'O'
connect by wlpzb.mm_id is not null and eban.banfn = prior eban.banfn and eban.bnfpo = prior eban.bnfpo and level <= eban.menge and prior dbms_random.value is not null
)
select
tt.*
,case when tt.shsl = '0' then '0' when tt.menge = tt.shsl then '1' else '2' end shbs
,case when tt.shsl = '0' then '0' when tt.menge = tt.shsl then '1' else '2' end azbs
,tt.shsl azsl -- 安装数量
,tt.shsj azsj -- 安装时间
from
(
select
sys_guid() xh,
yqctb.werks,
yqctb.name1,
yqctb.pspid,
yqctb.proj_post1, -- XH,WERKS,NAME1,PSPID,PROJ_POST1,
yqctb.dt_wbsid,
yqctb.prps_post1,
yqctb.wbs_id,
yqctb.wbs_name,
yqctb.banfn, -- 采购申请号 -- DT_WBSID,PRPS_POST1,WBS_ID,WBS_NAME,BANFN,
yqctb.bnfpo, -- 采购申请行项目号
yqctb.matnr, -- 物料编码
yqctb.txz01, -- 物料名称
yqctb.sw_id_manger,
yqctb.menge, -- BNFPO,MATNR,TXZ01,SW_ID_MANGER,MENGE,
yqctb.meins, -- 计量单位
nvl(yqctb.preis,0), -- 需求单价
nvl(yqctb.xqje,0), -- 需求金额
yqctb.ebeln, -- 采购订单号
yqctb.ebelp, -- 采购订单行项目号 -- MEINS,PREIS,XQJE,EBELN,EBELP,
nvl(yqctb.hsdj,0), -- 订单含税单价
yqctb.sw_id,
hwjj01.zfhtzdh, -- 物资交接单号
bama.pm_id, -- 设备编号
devi.erp_equitype, -- 设备类型 -- HSDJ,SW_ID,ZFHTZDH,PM_ID,ERP_EQUITYPE,
devi.erp_equiname, -- 设备类型名称
bama.pmc_id, -- 调度编号
bama.aa_id, -- 资产编号
nvl(anln.zsum,0) zzje, -- 转资金额
nvl2(thpr.banfn,1,nvl2(yqctb.ebeln,1,0)) z_tjcgbs, -- ERP_EQUINAME,PMC_ID,AA_ID,ZZJE,Z_TJCGBS,
nvl2(twprr.banfn,1,nvl2(yqctb.ebeln,1,0)) z_zbwcbs, -- 招标完成标识
nvl2(bmgt0022.zsw_id,1,0) fhbs,
nvl2(zyitem.zsw_id,1,0) pdbs, -- Z_ZBWCBS,SHBS,FHBS,AZBS,PDBS,
nvl2(bama.pmc_id, nvl2(bama.pm_id,1,0), 0) tybs,
'185_ODSWLMO1_DBJob' zname,
sysdate ztime -- TYBS,ZNAME,ZTIME
,kpfseg.bldat shsj -- 收货时间
,decode(yqctb.sw_id_manger,'1',nvl2(bmgt0021.zsw_id,1,0),nvl(msegc.cou,0)) shsl -- 到货数量
from (
select distinct cftb.*,tsip.sw_id from cftb
left join t_sw_id_po tsip on tsip.po = cftb.ebeln and tsip.po_item = cftb.ebelp -- 实物ID与采购订单对应表
where tsip.sw_id is not null
union all
select cftb.*,tsip.sw_id from cftb
left join t_sw_id_po tsip on tsip.po = cftb.ebeln and tsip.po_item = cftb.ebelp -- 实物ID与采购订单对应表
where tsip.sw_id is null
) yqctb
left join view_erp_zmmjyhwjj01 hwjj01 on hwjj01.ebeln = yqctb.ebeln and hwjj01.ebelp = yqctb.ebelp
left join odsbmgt.t_bama bama on bama.sw_id = yqctb.sw_id
left join view_erp_mara mara on mara.matnr = bama.mm_id
left join odsbmgt.t_matnr_devicetype devi on mara.matkl = devi.matkl
left join view_erp_zfi_fzzz_anln anln on anln.anln1 = bama.aa_id
left join (select distinct banfn,bnfpo from view_erp_zmmjy_th_pr) thpr on thpr.banfn = yqctb.banfn and thpr.bnfpo = yqctb.bnfpo
left join odsviewdata.view_erp_zmmjy_tw_prr twprr on twprr.banfn = yqctb.banfn and twprr.bnfpo = yqctb.bnfpo
left join (select distinct zsw_id from view_erp_zmm_bmgt_002 where bwart = '105') bmgt0021 on bmgt0021.zsw_id = yqctb.sw_id
left join (select distinct zsw_id from view_erp_zmm_bmgt_002 where bwart = '281') bmgt0022 on bmgt0022.zsw_id = yqctb.sw_id
left join (select distinct swid from bmgt_zjc_azb ) bzazb on bzazb.swid = yqctb.sw_id
left join view_erp_zfi26t_ysqc_item zyitem on zyitem.zsw_id = yqctb.sw_id
left join (
-- 取最早时间
select min(mkpf.bldat) bldat,mseg.ebeln,mseg.ebelp from view_erp_mkpf mkpf
left join view_erp_mseg mseg on mkpf.mblnr = mseg.mblnr
group by mseg.ebeln,mseg.ebelp
) kpfseg on kpfseg.ebeln = yqctb.ebeln and kpfseg.ebelp = yqctb.ebelp
left join ( select count(*) cou,ebeln,ebelp from view_erp_mseg where bwart in ('101','105') group by ebeln,ebelp ) msegc
on msegc.ebeln=yqctb.ebeln and msegc.ebelp = yqctb.ebelp
) tt
;
...全文
1173 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
AHUA1001 2019-05-09
  • 打赏
  • 举报
回复
不动你的逻辑,也没有你的表机构,简单的改了一下,可能和你的预期不大一样。
看这个语句,有些基础的规则和规范,需要加强。


WITH cftb AS
(
SELECT
xmjbxx.werks,
t001w.name1,
xmjbxx.pspid,
xmjbxx.proj_post1,
xmjbxx.posid dt_wbsid,
xmjbxx.prps_post1,
prps.posid wbs_id,
prps.post1 wbs_name,
eban.banfn, -- 采购申请号
eban.bnfpo, -- 采购申请行项目号
eban.matnr, -- 物料编码
eban.txz01, -- 物料名称
nvl2(wlpzb.mm_id,1,0) sw_id_manger,
nvl2(wlpzb.mm_id,1,eban.menge) menge,
eban.meins, -- 计量单位
eban.preis, -- 需求单价
nvl2(wlpzb.mm_id,1,eban.menge)*eban.preis xqje, -- 需求金额
ekpo.ebeln, -- 采购订单号
ekpo.ebelp, -- 采购订单行项目号
ekpo.brtwr/ekpo.menge hsdj -- 订单含税单价
FROM view_erp_ebkn ebkn -- 采购订单与wbs关联关系
LEFT JOIN view_erp_prps prps ON prps.pspnr = ebkn.ps_psp_pnr -- WBS(工作中断结构) 元素主数据
INNER JOIN t_sbwcetj_xmjbxx xmjbxx ON SUBSTR(prps.posid,1,12) = xmjbxx.pspid -- 统计范围表
LEFT JOIN view_erp_t001w t001w ON t001w.werks = xmjbxx.werks -- 工厂/分支机构
LEFT JOIN view_erp_eban eban ON eban.banfn = ebkn.banfn AND eban.bnfpo = ebkn.bnfpo -- 预购订单表 (匹配订单编号 和 项目编号)
LEFT JOIN odssjqc.t_wlpzb wlpzb ON wlpzb.mm_id = eban.matnr -- 21736
LEFT JOIN view_erp_ekpo ekpo ON ekpo.loekz = ' ' AND ekpo.pstyp <> 9 AND ekpo.banfn = ebkn.banfn AND ekpo.bnfpo = ebkn.bnfpo -- 订单表
WHERE eban.loekz = ' ' AND eban.frgkz = 'O'
connect BY wlpzb.mm_id IS NOT NULL AND eban.banfn = prior eban.banfn AND eban.bnfpo = prior eban.bnfpo AND LEVEL <= eban.menge AND prior dbms_random.value IS NOT NULL
)
SELECT
tt.*
,CASE WHEN tt.shsl = '0' THEN '0' WHEN tt.menge = tt.shsl THEN '1' ELSE '2' END shbs
,CASE WHEN tt.shsl = '0' THEN '0' WHEN tt.menge = tt.shsl THEN '1' ELSE '2' END azbs
,tt.shsl azsl -- 安装数量
,tt.shsj azsj -- 安装时间
FROM
(
SELECT
sys_guid() xh,
yqctb.werks,
yqctb.name1,
yqctb.pspid,
yqctb.proj_post1, -- XH,WERKS,NAME1,PSPID,PROJ_POST1,
yqctb.dt_wbsid,
yqctb.prps_post1,
yqctb.wbs_id,
yqctb.wbs_name,
yqctb.banfn, -- 采购申请号 -- DT_WBSID,PRPS_POST1,WBS_ID,WBS_NAME,BANFN,
yqctb.bnfpo, -- 采购申请行项目号
yqctb.matnr, -- 物料编码
yqctb.txz01, -- 物料名称
yqctb.sw_id_manger,
yqctb.menge, -- BNFPO,MATNR,TXZ01,SW_ID_MANGER,MENGE,
yqctb.meins, -- 计量单位
nvl(yqctb.preis,0), -- 需求单价
nvl(yqctb.xqje,0), -- 需求金额
yqctb.ebeln, -- 采购订单号
yqctb.ebelp, -- 采购订单行项目号 -- MEINS,PREIS,XQJE,EBELN,EBELP,
nvl(yqctb.hsdj,0), -- 订单含税单价
yqctb.sw_id,
hwjj01.zfhtzdh, -- 物资交接单号
bama.pm_id, -- 设备编号
devi.erp_equitype, -- 设备类型 -- HSDJ,SW_ID,ZFHTZDH,PM_ID,ERP_EQUITYPE,
devi.erp_equiname, -- 设备类型名称
bama.pmc_id, -- 调度编号
bama.aa_id, -- 资产编号
nvl(anln.zsum,0) zzje, -- 转资金额
nvl2(thpr.banfn,1,nvl2(yqctb.ebeln,1,0)) z_tjcgbs, -- ERP_EQUINAME,PMC_ID,AA_ID,ZZJE,Z_TJCGBS,
nvl2(twprr.banfn,1,nvl2(yqctb.ebeln,1,0)) z_zbwcbs, -- 招标完成标识
nvl2(yqctb.sw_id,1,0) fhbs,
nvl2(zyitem.zsw_id,1,0) pdbs, -- Z_ZBWCBS,SHBS,FHBS,AZBS,PDBS,
nvl2(bama.pmc_id, nvl2(bama.pm_id,1,0), 0) tybs,
'185_ODSWLMO1_DBJob' zname,
SYSDATE ztime -- TYBS,ZNAME,ZTIME
,kpfseg.bldat shsj -- 收货时间
,DECODE(yqctb.sw_id_manger,'1',nvl2(yqctb.sw_id,1,0),nvl(msegc.cou,0)) shsl -- 到货数量
FROM (
SELECT DISTINCT cftb.*,tsip.sw_id FROM cftb
LEFT JOIN t_sw_id_po tsip ON tsip.po = cftb.ebeln AND tsip.po_item = cftb.ebelp -- 实物ID与采购订单对应表
) yqctb
LEFT JOIN view_erp_zmmjyhwjj01 hwjj01 ON hwjj01.ebeln = yqctb.ebeln AND hwjj01.ebelp = yqctb.ebelp
LEFT JOIN odsbmgt.t_bama bama ON bama.sw_id = yqctb.sw_id
LEFT JOIN view_erp_mara mara ON mara.matnr = bama.mm_id
LEFT JOIN odsbmgt.t_matnr_devicetype devi ON mara.matkl = devi.matkl
LEFT JOIN view_erp_zfi_fzzz_anln anln ON anln.anln1 = bama.aa_id
LEFT JOIN (SELECT DISTINCT banfn,bnfpo FROM view_erp_zmmjy_th_pr) thpr ON thpr.banfn = yqctb.banfn AND thpr.bnfpo = yqctb.bnfpo
LEFT JOIN odsviewdata.view_erp_zmmjy_tw_prr twprr ON twprr.banfn = yqctb.banfn AND twprr.bnfpo = yqctb.bnfpo
LEFT JOIN view_erp_zfi26t_ysqc_item zyitem ON zyitem.zsw_id = yqctb.sw_id
LEFT JOIN (
-- 取最早时间
SELECT MIN(mkpf.bldat) bldat,mseg.ebeln,mseg.ebelp FROM view_erp_mkpf mkpf
LEFT JOIN view_erp_mseg mseg ON mkpf.mblnr = mseg.mblnr
GROUP BY mseg.ebeln,mseg.ebelp
) kpfseg ON kpfseg.ebeln = yqctb.ebeln AND kpfseg.ebelp = yqctb.ebelp
LEFT JOIN ( SELECT COUNT(*) cou,ebeln,ebelp FROM view_erp_mseg WHERE bwart IN ('101','105') GROUP BY ebeln,ebelp ) msegc
ON msegc.ebeln=yqctb.ebeln AND msegc.ebelp = yqctb.ebelp
) tt
;
Mricoo_周 2019-05-09
  • 打赏
  • 举报
回复
能不能考虑下吧with的结果集写入临时表呢,复杂到简单
SmartFitMan 2019-05-07
  • 打赏
  • 举报
回复
引用 8 楼 u010797391 的回复:
看下执行计划让查询走索引,我看你这个查询这么大可能怎么优化效率都不高,你不如用临时表把中间结果存起来
这个问题解决了 和你说的方式一样
xxfamly 2019-01-07
  • 打赏
  • 举报
回复
建议从这几个角度考虑:
一、保留程序逻辑结构
1、查看执行计划,看看是否有全表扫描和笛卡尔积的情况。
2、优化索引,例如执行表分析等手段

二、调整程序逻辑结构
这确实是一个非常复杂的SQL,关联表太多了,不建议超过5个表,索引看看能不能把最小结果集的sql提出去先执行,再用结果集去执行其他SQL。
u010797391 2019-01-02
  • 打赏
  • 举报
回复
看下执行计划让查询走索引,我看你这个查询这么大可能怎么优化效率都不高,你不如用临时表把中间结果存起来
andylei 2019-01-02
  • 打赏
  • 举报
回复
按右键看下执行计划,不可能没有的。
你这个SQL确实太复杂了,如果确实没有,你可以看看每个子查询的执行计划。
止水i 2018-12-30
  • 打赏
  • 举报
回复
引用 5 楼 yaiger 的回复:
没有执行计划怎么优化
我想问下PLSQL里面执行的时候都有执行计划的吧??
yaiger 2018-12-27
  • 打赏
  • 举报
回复
没有执行计划怎么优化
nayi_224 2018-12-26
  • 打赏
  • 举报
回复
效率低一般只会查的慢,一直查不出来更可能是逻辑错误查出笛卡尔积了。首先要保证你的语句是正确的。 把语句分段执行,找出最影响效率的部分。把这部分的执行计划发出来。
Mricoo_周 2018-12-26
  • 打赏
  • 举报
回复
可以先explain下看下执行计划,看是否有全表扫或索引全扫的情况,然后再统计下用到表的所有数据量,再考虑走索引之类的。仅供参考
SmartFitMan 2018-12-25
  • 打赏
  • 举报
回复
我现在不知道从哪里下手去优化这个sql了,有哪位大神指教一下~
SmartFitMan 2018-12-25
  • 打赏
  • 举报
回复
有人能回答一下吗?感觉头好大

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧