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

Question

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
   ;

Answer 1

我现在不知道从哪里下手去优化这个sql了，有哪位大神指教一下~

Answer 2

可以先explain下看下执行计划，看是否有全表扫或索引全扫的情况，然后再统计下用到表的所有数据量，再考虑走索引之类的。仅供参考

Answer 3

我想问下PLSQL里面执行的时候都有执行计划的吧？？

Answer 4

按右键看下执行计划，不可能没有的。
你这个SQL确实太复杂了，如果确实没有，你可以看看每个子查询的执行计划。

Answer 5

建议从这几个角度考虑：
一、保留程序逻辑结构
1、查看执行计划，看看是否有全表扫描和笛卡尔积的情况。
2、优化索引，例如执行表分析等手段二、调整程序逻辑结构
这确实是一个非常复杂的SQL，关联表太多了，不建议超过5个表，索引看看能不能把最小结果集的sql提出去先执行，再用结果集去执行其他SQL。

Answer 6

不动你的逻辑，也没有你的表机构，简单的改了一下，可能和你的预期不大一样。
看这个语句，有些基础的规则和规范，需要加强。
     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
   ;

Answer 7

能不能考虑下吧with的结果集写入临时表呢，复杂到简单