ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 32761|回复: 10

这个SQL,该如何改写?

[复制链接]
论坛徽章:
304
奥迪
日期:2013-07-29 13:45:59红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
发表于 2017-2-27 17:41 | 显示全部楼层 |阅读模式
ORACLE 11203, LINUX5.8 RAC2 NODE

下面的SQL,由14个子查询组成,子查询之间用 UNION ALL 连接,我分析过,每个子查询的执行计划都很好,暂时没有优化的余地,但这些子查询,用上的表,以及查询约束条件
都很相似,所以,我想能否先用 WITH 语句,整理个公共的查询结果集出来,供各个子查询使用,再用此结果集,关联每个子查询间不同的部分(比如 每个子查询的 T2,T6 表都不一样),
这样,就可以避免每个子查询都走一遍重复的查询从操作,如:我先整理了个公共查询结果集,

with n_temp1 as
( select
  t1.fbranchfee branchfeeIn,t1.ftransitfee transitfeeIn,t1.fdeliveryfee deliveryfeeIn
  ,0 branchfeeOut,0 transitfeeOut,0 deliveryfeeOut
  ,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payInFee ,0 payOutFee
  ,t1.fwaybillid,t1.fjoinbillid,t1.FISRETRY
  ,t4.fname_l2 payInPlatform,null payOutPlatform,t1.FISJOIN,t1.fisbatch
  ,t5.cfpackNumber,t5.cfactualWeight,t5.cfvolume
  ,pdb.fname_l2 payInBigPlatform,null payOutBigPlatform
  ,nvl(( select sum(nvl(ROUND(bs.cfpayamount,2), 0)) from CT_OPE_WayBSCP bs where bs.fparentId = t5.fid group by fparentid ), 0) specialFeeIn
  ,0 specialFeeOut
  ,t5.CFDepOfCustomerID,t5.fid
  ,t5.fcreatetime
  from T_FIN_PlatformCountFee t1   
  left join  CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid
  left join  CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = t5.CFDepOfCustomerID
  left join  CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
  left join  CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
  left join  CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid
)

我的理解,这种思路应该有效果,但又担心,实际上,优化器并没有这种做:重复 每个子查询主语句中,相似的查询从操作,而是知道有重复部分,直接把结果集拿来用,
这样的话,我上述的思想就是多余的了,

select
distinct cfreceivingName receivingName,CFEndStation station,CFReceivingAddressInfo receivingAddress,
round(internal.CFBatchWeight,2) weight,round(internal.CFBatchVolume,2) volume,internal.CFBatchNumber tnumber,t3.cfcompanyname customerName,
t2.fname_l2 customerBranch,cfisElecSupplier isSupplier,t1.fnumber wayNo,t1.CFToPayFee toPay
,CFStartStation startPlatform,t5.fname_l2 deliveryDep,t1.CFCollectionMoney withPay
,internal.payInFee,internal.payOutFee,t6.fname_l2 startDep,t7.fname_l2 endDep,t7.fid endDepid
,internal.payInPlatform,internal.payOutPlatform,internal.handoverNo,t1.CFSpecialMoney specialMoney,internal.id,internal.state
,to_char(t1.fcreatetime,'yyyy-MM-dd hh24:mi') createTime,internal.isJoin,internal.isBatch,t1.CFServiceType serviceType
,t10.cfprovincename province,t11.cfcityname city,t12.cfaddressareaname area
,internal.branchfeeIn,internal.transitfeeIn,internal.deliveryfeeIn
,internal.branchfeeOut,internal.transitfeeOut,internal.deliveryfeeOut
,internal.payInBigPlatform,internal.payOutBigPlatform,internal.specialFeeIn,internal.specialFeeOut
from (
select
distinct sum(payInFee) payInFee,max(payInPlatform) payInPlatform,fnumber handoverNo,fjoinbillid id
,sum(payOutFee) payOutFee,max(payOutPlatform) payOutPlatform,max(FISRETRY) state
,cfstartsationid,cfnextsationid,fwaybillid fwaybillid,max(FISJOIN) isJoin,max(fisbatch) isBatch
,max(CFBatchNumber) CFBatchNumber,max(CFBatchWeight) CFBatchWeight,max(CFBatchVolume) CFBatchVolume
,sum(round(branchfeeIn,2)) branchfeeIn,sum(round(transitfeeIn,2)) transitfeeIn,sum(round(deliveryfeeIn,2)) deliveryfeeIn
,sum(round(branchfeeOut,2)) branchfeeOut,sum(round(transitfeeOut,2)) transitfeeOut,sum(round(deliveryfeeOut,2)) deliveryfeeOut
,max(payInBigPlatform) payInBigPlatform,max(payOutBigPlatform) payOutBigPlatform,max(specialFeeIn) specialFeeIn,max(specialFeeOut) specialFeeOut
from (
  --1
  select
  t1.fbranchfee branchfeeIn,t1.ftransitfee transitfeeIn,t1.fdeliveryfee deliveryfeeIn
  ,0 branchfeeOut,0 transitfeeOut,0 deliveryfeeOut
  ,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payInFee ,0 payOutFee,t2.fcreatetime
  ,t2.cfstartsationid,t2.cfnextsationid,t1.fwaybillid,t2.fnumber,t1.fjoinbillid,t1.FISRETRY
  ,t4.fname_l2 payInPlatform,null payOutPlatform,t1.FISJOIN,t1.fisbatch,t6.CFBatchNumber,t6.CFBatchWeight,t6.CFBatchVolume
  ,pdb.fname_l2 payInBigPlatform,null payOutBigPlatform
  ,nvl(( select sum(nvl(ROUND(bs.cfpayamount,2), 0)) from CT_OPE_WayBSCP bs where bs.fparentId = t5.fid group by fparentid ), 0) specialFeeIn,0 specialFeeOut
  from T_FIN_PlatformCountFee t1
  inner join ct_ope_handoverroute t2 on t1.fjoinbillid = t2.fid
  left join  CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid
  left join  CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = t5.CFDepOfCustomerID
  left join  CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
  left join  CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
  left join  CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid
  left join  CT_OPE_HandOverRouteEntry t6 on t6.fparentid =  t2.fid and t6.cfwaybillid = t5.fid
  where 1 = 1
  and t1.FISRETRY in (0,1)
  /* and  exists (select * from CT_FIN_InternalPDE pde
               where pde.fparentid = (select cfplatformID from CT_FIN_InternalPDBE where fparentid = 'PKIAACt2IRIoO4Es')
               and pde.cfcostcenterunitid = t2.cfnextsationid) */
  and not exists (select * from CT_FIN_Joiningtrader trader where trader.CFCostCenterOrgID = t5.CFDepOfCustomerID)   
  and  t5.fcreatetime >= to_date('2017-01-04 00:00','yyyy-MM-dd hh24:mi')
  and  t5.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
  --alter system set events '10046 trace name context off'
  union all
  --3
  select  
  t1.fbranchfee branchfeeIn,t1.ftransitfee transitfeeIn,t1.fdeliveryfee deliveryfeeIn
  ,0 branchfeeOut,0 transitfeeOut,0 deliveryfeeOut
  ,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payInFee ,0 payOutFee,t2.fcreatetime
  ,t2.CFCreateDetID,t2.CFToOprateDepID,t1.fwaybillid,t2.fnumber,t1.fjoinbillid,t1.FISRETRY  
  ,t4.fname_l2 payInPlatform,null payOutPlatform,t1.FISJOIN,t1.fisbatch,t5.cfpackNumber,t5.cfactualWeight,t5.cfvolume
  ,pdb.fname_l2 payInBigPlatform,null payOutBigPlatform
  ,nvl(( select sum(nvl(ROUND(bs.cfpayamount,2), 0)) from CT_OPE_WayBSCP bs where bs.fparentId = t5.fid group by fparentid ), 0) specialFeeIn,0 specialFeeOut
  from T_FIN_PlatformCountFee t1  
  inner join CT_OPE_WayBillCooperation t2 on t1.fjoinbillid = t2.fid   
  left join CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid
  left join CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = t5.CFDepOfCustomerID
  left join CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
  left join CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
  left join CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid   
  where 1 = 1
  and t1.FISRETRY in (0,1)
  /* and  exists (select * from CT_FIN_InternalPDE pde
               where pde.fparentid = (select cfplatformID from CT_FIN_InternalPDBE where fparentid = 'PKIAACt2IRIoO4Es')
               and pde.cfcostcenterunitid = t2.CFToOprateDepID) */
  and not exists (select * from CT_FIN_Joiningtrader trader where trader.CFCostCenterOrgID = t5.CFDepOfCustomerID)   
  and  t5.fcreatetime >= to_date('2017-01-04 00:00','yyyy-MM-dd hh24:mi')
  and  t5.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
  union all
  --2
  select  
  t1.fbranchfee branchfeeIn,t1.ftransitfee transitfeeIn,t1.fdeliveryfee deliveryfeeIn
  ,0 branchfeeOut,0 transitfeeOut,0 deliveryfeeOut
  ,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payInFee ,0 payOutFee,t2.fcreatetime
  ,t2.CFStartDepID,t2.cfenddepid,t1.fwaybillid,t2.fnumber,t1.fjoinbillid,t1.FISRETRY  
  ,t4.fname_l2 payInPlatform,null payOutPlatform,t1.FISJOIN,t1.fisbatch,t6.CFHnumber,t6.CFHweight,t6.CFHvolum
  ,pdb.fname_l2 payInBigPlatform,null payOutBigPlatform
  ,nvl(( select sum(nvl(ROUND(bs.cfpayamount,2), 0)) from CT_OPE_WayBSCP bs where bs.fparentId = t5.fid group by fparentid ), 0) specialFeeIn,0 specialFeeOut
  from T_FIN_PlatformCountFee t1  
  inner join ct_ope_handover t2 on t1.fjoinbillid = t2.fid
  left join CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid
  left join  CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = t5.CFDepOfCustomerID
  left join  CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
  left join  CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
  left join  CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid
  left join  CT_OPE_HandOverEntry t6 on t6.fparentid =  t2.fid and t6.cfwaybillid = t5.fid
  where 1 = 1
  and t1.FISRETRY in (0,1)
  /*and  exists (select * from CT_FIN_InternalPDE pde
               where pde.fparentid = (select cfplatformID from CT_FIN_InternalPDBE where fparentid = 'PKIAACt2IRIoO4Es')
               and pde.cfcostcenterunitid = t2.cfenddepid) */
  and not exists (select * from CT_FIN_Joiningtrader trader where trader.CFCostCenterOrgID = t5.CFDepOfCustomerID)   
  and  t5.fcreatetime >= to_date('2017-01-04 00:00','yyyy-MM-dd hh24:mi')
  and  t5.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
  union all
  --10
  select  
  0 branchfeeIn,0 transitfeeIn,0 deliveryfeeIn
  ,t1.fbranchfee branchfeeOut,t1.ftransitfee transitfeeOut,t1.fdeliveryfee deliveryfeeOut
  ,0 payInFee,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payOutFee,t2.fcreatetime
  ,t2.CFCreateDetID,t2.CFToOprateDepID,t1.fwaybillid,t2.fnumber,t1.fjoinbillid,t1.FISRETRY  
  ,null payInPlatform,t4.fname_l2 payOutPlatform,t1.FISJOIN,t1.fisbatch,t5.cfpackNumber,t5.cfactualWeight,t5.cfvolume
  ,null payInBigPlatform,pdb.fname_l2 payOutBigPlatform
  ,0 specialFeeIn,nvl(( select sum(nvl(ROUND(bs.cfpayamount,2), 0)) from CT_OPE_WayBSCP bs where bs.fparentId = t5.fid group by fparentid ), 0) specialFeeOut
  from T_FIN_PlatformCountFee t1  
  inner join CT_OPE_WayBillCooperation t2 on t1.fjoinbillid = t2.fid
  left join CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid
  left join CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = t2.CFToOprateDepID
  left join CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
  left join CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
  left join CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid
  where 1 = 1
  and t1.FISRETRY in (0,1)
  /* and  exists (select * from CT_FIN_InternalPDE pde
               where pde.fparentid = (select cfplatformID from CT_FIN_InternalPDBE where fparentid = 'PKIAACt2IRIoO4Es')
               and pde.cfcostcenterunitid = t5.CFDepOfCustomerID)*/
  and not exists (select * from CT_FIN_Joiningtrader trader where trader.CFCostCenterOrgID = t5.CFDepOfCustomerID)
  and  t5.fcreatetime >= to_date('2017-02-04 00:00','yyyy-MM-dd hh24:mi')
  and  t5.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
  union all
  --8
  select
  0 branchfeeIn,0 transitfeeIn,0 deliveryfeeIn
  ,t1.fbranchfee branchfeeOut,t1.ftransitfee transitfeeOut,t1.fdeliveryfee deliveryfeeOut
  ,0 payInFee,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payOutFee,t2.fcreatetime
  ,t2.cfstartsationid,t2.cfnextsationid,t1.fwaybillid,t2.fnumber,t1.fjoinbillid,t1.FISRETRY
  ,null payInPlatform,t4.fname_l2 payOutPlatform,t1.FISJOIN,t1.fisbatch,t6.CFBatchNumber,t6.CFBatchWeight,t6.CFBatchVolume
  ,null payInBigPlatform,pdb.fname_l2 payOutBigPlatform
  ,0 specialFeeIn,nvl(( select sum(nvl(ROUND(bs.cfpayamount,2), 0)) from CT_OPE_WayBSCP bs where bs.fparentId = t5.fid group by fparentid ), 0) specialFeeOut
  from T_FIN_PlatformCountFee t1
  inner join ct_ope_handoverroute t2 on t1.fjoinbillid = t2.fid
  left join CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid
  left join CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = t2.cfnextsationid
  left join CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
  left join CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
  left join CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid   
  left join CT_OPE_HandOverRouteEntry t6 on t6.fparentid =  t2.fid and t6.cfwaybillid = t5.fid
  where 1 = 1
  and t1.FISRETRY in (0,1)
  /* and  exists (select * from CT_FIN_InternalPDE pde
               where pde.fparentid = (select cfplatformID from CT_FIN_InternalPDBE where fparentid = 'PKIAACt2IRIoO4Es')
               and pde.cfcostcenterunitid = t5.CFDepOfCustomerID)*/
  and not exists (select * from CT_FIN_Joiningtrader trader where trader.CFCostCenterOrgID = t5.CFDepOfCustomerID)   
  and  t5.fcreatetime >= to_date('2017-02-04 00:00','yyyy-MM-dd hh24:mi')
  and  t5.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
  union all
  --9
  select  
  0 branchfeeIn,0 transitfeeIn,0 deliveryfeeIn
  ,t1.fbranchfee branchfeeOut,t1.ftransitfee transitfeeOut,t1.fdeliveryfee deliveryfeeOut
  ,0 payInFee,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payOutFee,t2.fcreatetime
  ,t2.CFStartDepID,t2.cfenddepid,t1.fwaybillid,t2.fnumber,t1.fjoinbillid,t1.FISRETRY  
  ,null payInPlatform,t4.fname_l2 payOutPlatform,t1.FISJOIN,t1.fisbatch,t6.CFHnumber,t6.CFHweight,t6.CFHvolum
  ,null payInBigPlatform,pdb.fname_l2 payOutBigPlatform
  ,0 specialFeeIn,nvl(( select sum(nvl(ROUND(bs.cfpayamount,2), 0)) from CT_OPE_WayBSCP bs where bs.fparentId = t5.fid group by fparentid ), 0) specialFeeOut
  from T_FIN_PlatformCountFee t1  
  inner join ct_ope_handover t2 on t1.fjoinbillid = t2.fid
  left join CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid
  left join CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = t2.cfenddepid
  left join CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
  left join CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
  left join CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid
  left join CT_OPE_HandOverEntry t6 on t6.fparentid =  t2.fid and t6.cfwaybillid = t5.fid
  where 1 = 1
  and t1.FISRETRY in (0,1)
  /* and  exists (select * from CT_FIN_InternalPDE pde
               where pde.fparentid = (select cfplatformID from CT_FIN_InternalPDBE where fparentid = 'PKIAACt2IRIoO4Es')
               and pde.cfcostcenterunitid = t5.CFDepOfCustomerID) */
  and not exists (select * from CT_FIN_Joiningtrader trader where trader.CFCostCenterOrgID = t5.CFDepOfCustomerID)   
  and  t5.fcreatetime >= to_date('2017-02-04 00:00','yyyy-MM-dd hh24:mi')
  and  t5.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
  union all
  --4
  select
  t1.fbranchfee branchfeeIn,t1.ftransitfee transitfeeIn,t1.fdeliveryfee deliveryfeeIn
  ,0 branchfeeOut,0 transitfeeOut,0 deliveryfeeOut
  ,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payInFee ,0 payOutFee,t2.fcreatetime
  ,t2.cfstartsationid,t2.cfnextsationid,t1.fwaybillid,t2.fnumber,t1.fjoinbillid,t1.FISRETRY
  ,t4.fname_l2 payInPlatform,null payOutPlatform,t1.FISJOIN,t1.fisbatch,t6.CFBatchNumber,t6.CFBatchWeight,t6.CFBatchVolume
  ,pdb.fname_l2 payInBigPlatform,null payOutBigPlatform
  ,0 specialFeeIn,0 specialFeeOut
  from T_FIN_PlatformCountFee t1
  inner join ct_ope_handoverroute t2 on t1.fjoinbillid = t2.fid  
  left join CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid  
  left join (
            --5
        select * from (
        select
        Rank() over(PARTITION BY t1.fnumber ORDER BY CFSendTruckTime) rowno,
        t1.fid wayID,t3.CFStartSationID
        from CT_OPE_WayBill t1
        left join CT_OPE_HandOverRouteEntry t2 on t1.fid = t2.cfwaybillid
        left join ct_ope_handoverroute t3 on t2.fparentid = t3.fid
            left join CT_FIN_InternalPDE pde1 on pde1.CFCostCenterUnitID = t3.CFStartSationID
            left join CT_FIN_InternalPDE pde2 on pde2.CFCostCenterUnitID = t3.CFNextSationID
        where 1=1
            and t3.CFStartSationID in (select CFCostCenterUnitID from CT_FIN_InternalPDE)
            and t3.CFNextSationID in (select CFCostCenterUnitID from CT_FIN_InternalPDE)  
            and pde1.fparentid <> pde2.fparentid
            and t1.fcreatetime >= to_date('2017-01-04 00:00','yyyy-MM-dd hh24:mi')
            and t1.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
        )where rowno = 1
  ) startPlatform on t5.fid = startPlatform.wayID
  left join CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = startPlatform.CFStartSationID
  left join CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
  left join CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
  left join CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid
  left join CT_OPE_HandOverRouteEntry t6 on t6.fparentid =  t2.fid and t6.cfwaybillid = t5.fid
  where 1 = 1
  and t1.FISRETRY in (0,1)
  /* and  exists (select * from CT_FIN_InternalPDE pde
               where pde.fparentid = (select cfplatformID from CT_FIN_InternalPDBE where fparentid = 'PKIAACt2IRIoO4Es')
               and pde.cfcostcenterunitid = t2.cfnextsationid) */
  and exists (select * from CT_FIN_Joiningtrader trader where trader.CFCostCenterOrgID = t5.CFDepOfCustomerID)   
  and  t5.fcreatetime >= to_date('2017-01-04 00:00','yyyy-MM-dd hh24:mi')
  and  t5.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
  union all
  --6
  select  
  t1.fbranchfee branchfeeIn,t1.ftransitfee transitfeeIn,t1.fdeliveryfee deliveryfeeIn
  ,0 branchfeeOut,0 transitfeeOut,0 deliveryfeeOut
  ,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payInFee ,0 payOutFee,t2.fcreatetime
  ,t2.CFStartDepID,t2.cfenddepid,t1.fwaybillid,t2.fnumber,t1.fjoinbillid,t1.FISRETRY  
  ,t4.fname_l2 payInPlatform,null payOutPlatform,t1.FISJOIN,t1.fisbatch,t6.CFHnumber,t6.CFHweight,t6.CFHvolum
  ,pdb.fname_l2 payInBigPlatform,null payOutBigPlatform
  ,0 specialFeeIn,0 specialFeeOut
  from T_FIN_PlatformCountFee t1  
  inner join ct_ope_handover t2 on t1.fjoinbillid = t2.fid   
  left join  CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = t2.CFStartDepID
  left join  CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
  left join  CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
  left join  CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid
  left join  CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid
  left join  CT_OPE_HandOverEntry t6 on t6.fparentid =  t2.fid and t6.cfwaybillid = t5.fid
  where 1 = 1
  and t1.FISRETRY in (0,1)
  /* and  exists (select * from CT_FIN_InternalPDE pde
               where pde.fparentid = (select cfplatformID from CT_FIN_InternalPDBE where fparentid = 'PKIAACt2IRIoO4Es')
               and pde.cfcostcenterunitid = t2.cfenddepid) */
  and exists (select * from CT_FIN_Joiningtrader trader where trader.CFCostCenterOrgID = t5.CFDepOfCustomerID)   
  and  t5.fcreatetime >= to_date('2017-01-04 00:00','yyyy-MM-dd hh24:mi')
  and  t5.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
  union all
  --7
  select  
  t1.fbranchfee branchfeeIn,t1.ftransitfee transitfeeIn,t1.fdeliveryfee deliveryfeeIn
  ,0 branchfeeOut,0 transitfeeOut,0 deliveryfeeOut
  ,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payInFee ,0 payOutFee,t2.fcreatetime
  ,t2.CFCreateDetID,t2.CFToOprateDepID,t1.fwaybillid,t2.fnumber,t1.fjoinbillid,t1.FISRETRY  
  ,t4.fname_l2 payInPlatform,null payOutPlatform,t1.FISJOIN,t1.fisbatch,t5.cfpackNumber,t5.cfactualWeight,t5.cfvolume
  ,pdb.fname_l2 payInBigPlatform,null payOutBigPlatform
  ,0 specialFeeIn,0 specialFeeOut
  --from T_FIN_PlatformCountFee t1
  from T_FIN_PlatformCountFee_170226 t1
  inner join CT_OPE_WayBillCooperation t2 on t1.fjoinbillid = t2.fid   
  left join CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid
  left join CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = t2.CFCreateDetID
  left join CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
  left join CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
  left join CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid
  where 1 = 1
  and t1.FISRETRY in (0,1)
  /* and  exists (select * from CT_FIN_InternalPDE pde
               where pde.fparentid = (select cfplatformID from CT_FIN_InternalPDBE where fparentid = 'PKIAACt2IRIoO4Es')
               and pde.cfcostcenterunitid = t2.CFToOprateDepID) */
  and  exists (select * from CT_FIN_Joiningtrader trader where trader.CFCostCenterOrgID = t5.CFDepOfCustomerID)   
  and  t5.fcreatetime >= to_date('2017-01-04 00:00','yyyy-MM-dd hh24:mi')
  and  t5.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
  union all
  --11
  select
  0 branchfeeIn,0 transitfeeIn,0 deliveryfeeIn
  ,t1.fbranchfee branchfeeOut,t1.ftransitfee transitfeeOut,t1.fdeliveryfee deliveryfeeOut
  ,0 payInFee,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payOutFee,t2.fcreatetime
  ,t2.cfstartsationid,t2.cfnextsationid,t1.fwaybillid,t2.fnumber,t1.fjoinbillid,t1.FISRETRY
  ,null payInPlatform,t4.fname_l2 payOutPlatform,t1.FISJOIN,t1.fisbatch,t6.CFBatchNumber,t6.CFBatchWeight,t6.CFBatchVolume
  ,null payInBigPlatform,pdb.fname_l2 payOutBigPlatform
  ,0 specialFeeIn,0 specialFeeOut
  from T_FIN_PlatformCountFee t1
  inner join ct_ope_handoverroute t2 on t1.fjoinbillid = t2.fid
  left join CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid
  left join (
        --12
        select * from (
        select
        Rank() over(PARTITION BY t1.fnumber ORDER BY CFSendTruckTime) rowno,
        t1.fid wayID,t3.CFStartSationID
        from CT_OPE_WayBill t1
        left join CT_OPE_HandOverRouteEntry t2 on t1.fid = t2.cfwaybillid
        left join ct_ope_handoverroute t3 on t2.fparentid = t3.fid
            left join CT_FIN_InternalPDE pde1 on pde1.CFCostCenterUnitID = t3.CFStartSationID
            left join CT_FIN_InternalPDE pde2 on pde2.CFCostCenterUnitID = t3.CFNextSationID
        where 1=1
            and t3.CFStartSationID in (select CFCostCenterUnitID from CT_FIN_InternalPDE)
            and t3.CFNextSationID in (select CFCostCenterUnitID from CT_FIN_InternalPDE)  
            and pde1.fparentid <> pde2.fparentid
            and  t1.fcreatetime >= to_date('2017-02-04 00:00','yyyy-MM-dd hh24:mi')
            and  t1.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
        )where rowno = 1
  )startPlatform on t5.fid = startPlatform.wayID
  left join CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = t2.cfnextsationid
  left join CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
  left join CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
  left join CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid
  left join CT_OPE_HandOverRouteEntry t6 on t6.fparentid =  t2.fid and t6.cfwaybillid = t5.fid
  where 1 = 1
  and t1.FISRETRY in (0,1)
  /* and  exists (select * from CT_FIN_InternalPDE pde
               where pde.fparentid = (select cfplatformID from CT_FIN_InternalPDBE where fparentid = 'PKIAACt2IRIoO4Es')
               and pde.cfcostcenterunitid = startPlatform.CFStartSationID)*/
  and exists (select * from CT_FIN_Joiningtrader trader where trader.CFCostCenterOrgID = t5.CFDepOfCustomerID)   
  and  t5.fcreatetime >= to_date('2017-02-04 00:00','yyyy-MM-dd hh24:mi')
  and  t5.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
  union all
   --13
   select
   0 branchfeeIn,0 transitfeeIn,0 deliveryfeeIn
   ,t1.fbranchfee branchfeeOut,t1.ftransitfee transitfeeOut,t1.fdeliveryfee deliveryfeeOut
   ,0 payInFee,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payOutFee,t2.fcreatetime
   ,t2.CFStartDepID,t2.cfenddepid,t1.fwaybillid,t2.fnumber,t1.fjoinbillid,t1.FISRETRY  
   ,null payInPlatform,t4.fname_l2 payOutPlatform,t1.FISJOIN,t1.fisbatch,t6.CFHnumber,t6.CFHweight,t6.CFHvolum
   ,null payInBigPlatform,pdb.fname_l2 payOutBigPlatform
   ,0 specialFeeIn,0 specialFeeOut
   --from T_FIN_PlatformCountFee t1
   from T_FIN_PlatformCountFee_170226 t1
   inner join ct_ope_handover t2 on t1.fjoinbillid = t2.fid
   left join CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid
   left join CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = t2.cfenddepid
   left join CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
   left join CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
   left join CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid
   left join CT_OPE_HandOverEntry t6 on t6.fparentid =  t2.fid and t6.cfwaybillid = t5.fid
   where 1 = 1
   and t1.FISRETRY in (0,1)
   /* and  exists (select * from CT_FIN_InternalPDE pde
                where pde.fparentid = (select cfplatformID from CT_FIN_InternalPDBE where fparentid = 'PKIAACt2IRIoO4Es')
                and pde.cfcostcenterunitid = t2.CFStartDepID)*/
   and exists (select * from CT_FIN_Joiningtrader trader where trader.CFCostCenterOrgID = t5.CFDepOfCustomerID)   
   and  t5.fcreatetime >= to_date('2017-02-04 00:00','yyyy-MM-dd hh24:mi')
   and  t5.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
   union all
   --14
   select  
   0 branchfeeIn,0 transitfeeIn,0 deliveryfeeIn
   ,t1.fbranchfee branchfeeOut,t1.ftransitfee transitfeeOut,t1.fdeliveryfee deliveryfeeOut
   ,0 payInFee,(round(t1.fbranchfee,2) + round(ftransitfee,2) + round(t1.fdeliveryfee,2)) payOutFee,t2.fcreatetime
   ,t2.CFCreateDetID,t2.CFToOprateDepID,t1.fwaybillid,t2.fnumber,t1.fjoinbillid,t1.FISRETRY  
   ,null payInPlatform,t4.fname_l2 payOutPlatform,t1.FISJOIN,t1.fisbatch,t5.cfpackNumber,t5.cfactualWeight,t5.cfvolume
   ,null payInBigPlatform,pdb.fname_l2 payOutBigPlatform
   ,0 specialFeeIn,0 specialFeeOut
   --from T_FIN_PlatformCountFee t1
   from T_FIN_PlatformCountFee_170226 t1
   inner join CT_OPE_WayBillCooperation t2 on t1.fjoinbillid = t2.fid
   left join CT_OPE_WayBill t5 on t1.fwaybillid =  t5.fid
   left join CT_FIN_InternalPDE t3 on t3.cfcostcenterunitid = t2.CFToOprateDepID
   left join CT_FIN_InternalPlatformDivide t4 on t3.fparentid =  t4.fid
   left join CT_FIN_InternalPDBE pdbe on pdbe.cfplatformid = t4.fid
   left join CT_FIN_InternalPDB pdb on pdb.fid = pdbe.fparentid
   where 1 = 1
   and t1.FISRETRY in (0,1)
   /* and  exists (select * from CT_FIN_InternalPDE pde
                where pde.fparentid = (select cfplatformID from CT_FIN_InternalPDBE where fparentid = 'PKIAACt2IRIoO4Es')
                and pde.cfcostcenterunitid = t2.CFCreateDetID) */
   and exists (select * from CT_FIN_Joiningtrader trader where trader.CFCostCenterOrgID = t5.CFDepOfCustomerID)   
   and  t5.fcreatetime >= to_date('2017-02-04 00:00','yyyy-MM-dd hh24:mi')
   and  t5.fcreatetime <= to_date('2017-02-24 23:59','yyyy-MM-dd hh24:mi')
)
group by fnumber,fjoinbillid,cfstartsationid,cfnextsationid,fwaybillid
) internal
left join CT_OPE_WayBill t1 on internal.fwaybillid = t1.fid
left join T_ORG_CostCenter t2 on t1.cfdepOfCustomerid = t2.fid
left join CT_MAR_Customer t3 on t1.cfcustomerid = t3.fid
left join CT_OPE_SignBill t4 on t1.fid = t4.cfwaybillid
left join  T_ORG_CostCenter t5 on t4.CFActualDeliveryDe = t5.fid
left join  T_ORG_CostCenter t6 on internal.cfstartsationid = t6.fid
left join  T_ORG_CostCenter t7 on internal.cfnextsationid = t7.fid
left join  T_ORG_CostCenter t8 on t8.fid = t1.CFCreateDepID
left join CT_BAS_Province t10 on t1.CFEndProvinceID = t10.fid
left join CT_BAS_City t11 on t1.CFEndCityID = t11.fid
left join CT_BAS_AreaAddress t12 on t1.CFEndAreaAddressID = t12.fid
where t6.fid in (select CFCostCenterUnitID from CT_FIN_InternalPDE)
and t7.fid in (select CFCostCenterUnitID from CT_FIN_InternalPDE)
order by  t1.fnumber,internal.handoverNo
/



论坛徽章:
484
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
发表于 2017-2-27 23:54 | 显示全部楼层
你的改写实际效果如何呢?CBO应该没那么聪明,你的担心是多余的。

使用道具 举报

回复
论坛徽章:
0
发表于 2017-2-28 07:21 | 显示全部楼层
With 应该是有效的

来自苹果客户端来自客户端

使用道具 举报

回复
论坛徽章:
4
祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-01-04 08:33:082015年新春福章
日期:2015-03-04 14:51:122015年新春福章
日期:2015-03-06 11:57:31
发表于 2017-3-2 09:10 | 显示全部楼层
有效果。。我记得做过类似的操作,是有效的

使用道具 举报

回复
论坛徽章:
304
奥迪
日期:2013-07-29 13:45:59红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
 楼主| 发表于 2017-3-2 17:38 | 显示全部楼层
starfifi 发表于 2017-3-2 09:10
有效果。。我记得做过类似的操作,是有效的

我用了WITH改写前半段(日期范围为 1/4 -> 2/4,7个子查询),效果不明显,一致读减少了20万,但时间
只少了2秒,

后我改写SQL中的标量子查询,拉回到主语句中(select x from x group by x),结果,效率竟然比原语句的效率还差,多了十几秒,主要是多了2万个物理读,但我还没查看,是什么原因导致多了这2万物理读,

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
477
本田
日期:2014-01-05 16:51:44技术图书徽章
日期:2014-04-21 10:26:402014年世界杯参赛球队: 伊朗
日期:2014-05-23 10:41:312014年世界杯参赛球队: 比利时
日期:2014-06-17 12:09:43itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29
发表于 2017-3-2 18:44 | 显示全部楼层
ZALBB 发表于 2017-3-2 17:38
我用了WITH改写前半段(日期范围为 1/4 -> 2/4,7个子查询),效果不明显,一致读减少了20万,但时间
只 ...

一致性读降低20万,大概降低比例是多少?

使用道具 举报

回复
论坛徽章:
304
奥迪
日期:2013-07-29 13:45:59红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
 楼主| 发表于 2017-3-2 21:54 | 显示全部楼层
lastwinner 发表于 2017-3-2 18:44
一致性读降低20万,大概降低比例是多少?

100万多一点,降到82万左右,

但这只是部分语句,没改全这14个子查询, 最关键的是, 我无法确定改写是否等价,,,

使用道具 举报

回复
论坛徽章:
2
生肖徽章2007版:虎
日期:2008-01-02 17:35:53生肖徽章2007版:鼠
日期:2008-01-02 17:35:53
发表于 2017-3-12 10:40 | 显示全部楼层
减少 T_ORG_CostCenter  LEFT JOIN   和  CT_FIN_InternalPDE 关联看看  多表关联可以选择的执行路径是 N ! 次

使用道具 举报

回复
论坛徽章:
31
管理团队2006纪念徽章
日期:2006-04-16 22:44:452012新春纪念徽章
日期:2012-02-13 15:11:522012新春纪念徽章
日期:2012-02-13 15:11:522012新春纪念徽章
日期:2012-02-13 15:11:522012新春纪念徽章
日期:2012-02-13 15:11:52铁扇公主
日期:2012-02-21 15:02:402013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14
发表于 2017-3-13 11:34 | 显示全部楼层
本帖最后由 wing hong 于 2017-3-13 11:43 编辑

太复杂了, 没法仔细看。

不过,

先 “整理个公共的查询结果集出来,供各个子查询使用,再用此结果集,关联每个子查询间不同的部分(比如 每个子查询的 T2,T6 表都不一样),”,

如果 这个 JOIN 的顺序不是最好的呢 ?
如果需要做其他转换呢 ?比如 JPPD , 比如 SUBQUERY push 呢?

更主要的原因是, subquery factoring (with as ) 好像在 不同的版本中 有不同的bug , 所以问题不少。

使用道具 举报

回复
论坛徽章:
31
管理团队2006纪念徽章
日期:2006-04-16 22:44:452012新春纪念徽章
日期:2012-02-13 15:11:522012新春纪念徽章
日期:2012-02-13 15:11:522012新春纪念徽章
日期:2012-02-13 15:11:522012新春纪念徽章
日期:2012-02-13 15:11:52铁扇公主
日期:2012-02-21 15:02:402013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14
发表于 2017-3-13 13:51 | 显示全部楼层
理论上, with as (inline 的情况)应该  和 不是 with as  的 写法 一样, 不过 ,好像特别是 旧 版本(10 ,11R1) , CBO 可能 对 with as 的 处理 有限制, 有些 转换 做不了。

所以 LZ 的 思路要 测试测试 看生成的计划有没有 不同。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

SACC2017购票7.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

活动链接>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表