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
;
uj5u.com熱心網友回復：
我現在不知道從哪里下手去優化這個sql了，有哪位大神指教一下~uj5u.com熱心網友回復：
有人能回答一下嗎？感覺頭好大uj5u.com熱心網友回復：
可以先explain下看下執行計劃，看是否有全表掃或索引全掃的情況，然后再統計下用到表的所有資料量，再考慮走索引之類的。僅供參考uj5u.com熱心網友回復：
效率低一般只會查的慢，一直查不出來更可能是邏輯錯誤查出笛卡爾積了。首先要保證你的陳述句是正確的。把陳述句分段執行，找出最影響效率的部分。把這部分的執行計劃發出來。
uj5u.com熱心網友回復：
沒有執行計劃怎么優化uj5u.com熱心網友回復：
我想問下PLSQL里面執行的時候都有執行計劃的吧？？uj5u.com熱心網友回復：
按右鍵看下執行計劃，不可能沒有的。你這個SQL確實太復雜了，如果確實沒有，你可以看看每個子查詢的執行計劃。
uj5u.com熱心網友回復：
看下執行計劃讓查詢走索引，我看你這個查詢這么大可能怎么優化效率都不高，你不如用臨時表把中間結果存起來uj5u.com熱心網友回復：
建議從這幾個角度考慮：一、保留程式邏輯結構
1、查看執行計劃，看看是否有全表掃描和笛卡爾積的情況。
2、優化索引，例如執行表分析等手段
二、調整程式邏輯結構
這確實是一個非常復雜的SQL，關聯表太多了，不建議超過5個表，索引看看能不能把最小結果集的sql提出去先執行，再用結果集去執行其他SQL。
uj5u.com熱心網友回復：
這個問題解決了 和你說的方式一樣
uj5u.com熱心網友回復：
不動你的邏輯，也沒有你的表機構，簡單的改了一下，可能和你的預期不大一樣。看這個陳述句，有些基礎的規則和規范，需要加強。
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
;
uj5u.com熱心網友回復：
能不能考慮下吧with的結果集寫入臨時表呢，復雜到簡單轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/44742.html
標籤：開發
上一篇：[ORA-00600: internal error code, arguments: [qctbyt : bfc]