④内部公开请勿外传--1存货收发汇总报表，用户反馈，耗时15分钟。--2该报表的SQL语句由3段代码组成，用两个UNION合并起来。下面是存货收发汇总报表的一部分SQL，SELECTSTOCKDIM.FENTRYIDFDIMID,HSDIM.FENTRYIDFACCTGDIMID,CALD.FACCTSYSTEMIDFACCTGSYSTEMID,CALD.FFINORGIDFACCTGORGID,CALD.FACCTPOLICYIDFACCTPOLICYID,2020,1,STOCKDIM.FMATERIALIDFMATERIALBASEID,MAT.FNUMBERFMATERIALID,MAT_L.FNAMEFMATERIALNAME,MAT_L.FSPECIFICATIONFMODEL,LOT.FNUMBERFLOTNO,STOCKDIM.FAUXPROPIDFASSIPROPERTYID,MAT_T.FERPCLSIDFMATERPROPERTY,MAT_T.FCATEGORYIDFMATERTYPE,STOCKDIM.FBOMIDFBOMNO,STOCKDIM.FMTONOFPLANNO,N''FSEQUENCENO,STOCKDIM.FPROJECTNOFPROJECTNO,STOCKDIM.FCargoOwnerIdFOWNERID,STOCKDIM.FStockOrgIdFSTOCKORGID,STOCKDIM.FSTOCKSTATUSIDFSTOCKSTATUSID,STOCKDIM.FSTOCKIDFSTOCKID,STOCKDIM.FSTOCKLOCIDFSTOCKPLACEID,HSDIM.FACCTGRANGEIDFACCTGRANGEID,MAT_T.FBASEUNITIDFUNITID,HSDIM.FVALUATIONMETHODFVALUATIO,'HS_INOUTSTOCKDETAILRPT',0FROMT_HS_OUTACCTGAGINNERJOINV_HS_InivBalanceIVONAG.FID=IV.FIDINNERJOINT_HS_CALDIMENSIONSCALDON(AG.FDIMENSIONID=CALD.FDIMENSIONIDANDAG.FDIMENSIONID=1)INNERJOINT_HS_InivStockDimensionSTOCKDIMONIV.FDimeEntryId=STOCKDIM.FENTRYIDLEFTOUTERJOINT_BD_LOTMASTERLOTONSTOCKDIM.FLOT=LOT.FLOTIDINNERJOINT_HS_StockDimensionHSDIMONHSDIM.FENTRYID=IV.FACCTGDIMEENTRYIDINNERJOINt_bd_MaterialMATONSTOCKDIM.FMATERIALID=MAT.FMATERIALIDINNERJOINT_BD_MATERIALBASEMAT_TONMAT.FMATERIALID=MAT_T.FMATERIALIDLEFTOUTERJOINt_BD_StockSTOCKONSTOCK.FSTOCKID=STOCKDIM.FSTOCKIDLEFTOUTERJOINt_bd_Material_lMAT_LON(MAT.FMATERIALID=MAT_L.FMATERIALIDANDMAT_L.FLOCALEID=2052)WHEREIV.FENDINITKEY='0'ANDAG.FYEAR=2020ANDAG.FPERIOD=1ANDCAlD.FACCTSYSTEMID=1ANDCAlD.FFINORGID=100132ANDCAlD.FACCTPOLICYID=1ANDAG.FYEAR=2020AND(AG.FPERIOD>=1)AND(AG.FPERIOD<=9)AND(MAT.FNUMBER>='04150040178')AND(MAT.FNUMBER<='04150040178')/--3经分析，这是3段中耗时中耗时最久的一段，13分58秒。如下图：在SQLPLUS里执行，返回完查询结果后，显示耗时13分57.69秒。其中，红框部分显示，V_HS_INIVBALANCE的这个操作，耗时12分07秒。1/4④内部公开请勿外传--4再次分析，发现视图V_HS_INIVBALANCE是引起该语句耗时良久的根源，其底层SQL如下：SELECTINIV.FENTRYID,INIV.FID,DIM.FMATERIALID,DIM.FCARGOOWNERTYPE,DIM.FCARGOOWNERID,INIV.FDIMEENTRYID,DIM.FSTOCKORGID,INIV.FQTY,INIV.FAMOUNT,DIM.FSTOCKSTATUSID,INIV.FENDINITKEY,INIV.FYEARSUMQTY,INIV.FYEARSUMAMOUNT,INIV.FYEAROUTSUMQTY,INIV.FYEAROUTSUMAMOUNT,INIV.FACCTGDIMEENTRYIDFROMT_HS_INIVBALANCE_HINIVINNERJOINT_HS_INIVSTOCKDIMENSIONDIMONINIV.FDIMEENTRYID=DIM.FENTRYIDUNIONALLSELECTINIV.FENTRYID,INIV.FID,DIM.FMATERIALID,DIM.FCARGOOWNERTYPE,DIM.FCARGOOWNERID,INIV.FDIMEENTRYID,DIM.FSTOCKORGID,INIV.FQTY,INIV.FAMOUNT,DIM.FSTOCKSTATUSID,INIV.FENDINITKEY,INIV.FYEARSUMQTY,INIV.FYEARSUMAMOUNT,INIV.FYEAROUTSUMQTY,INIV.FYEAROUTSUMAMOUNT,INIV.FACCTGDIMEENTRYIDFROMT_HS_INIVBALANCEINIVINNERJOINT_HS_INIVSTOCKDIMENSIONDIMONINIV.FDIMEENTRYID=DIM.FENTRYID注意语句的红色部分，该视图由两段SQLUNIONALL组成，在第1段中，表T_HS_INIVBALANCE_HINIV和T_HS_INIVSTOCKDIMENSIONDIM通过此条件ONINIV.FDIMEENTRYID=DIM.FENTRYID发生关系。在第2段中，表T_HS_INIVBALANCEINIV和T_HS_INIVSTOCKDIMENSIONDIM通过此条件ONINIV.FDIMEENTRYID=DIM.FENTRYID发生关系。从表名上就可以看出，表：T_HS_INIVBALANCE_H和T_HS_INIVBALANCE是历史表和当前的关系，两者表结构经查询，一模一样。可以看出，维度表T_HS_INIVSTOCKDIMENSION被关联的两次，所以，优化的第一个思路，就是减少该表的关联次数，修改后的视图SQL，如下：2/4④内部公开请勿外传SELECTINIV.*,DIM.FMATERIALID,DIM.FCARGOOWNERTYPE,DIM.FCARGOOWNERID,DIM.FSTOCKORGID,DIM.FSTOCKSTATUSID(SELECTINIV.FENTRYID,INIV.FID,INIV.FDIMEENTRYID,INIV.FQTY,INIV.FAMOUNT,INIV.FENDINITKEY,INIV.FYEARSUMQTY,INIV.FYEARSUMAMOUNT,INIV.FYEAROUTSUMQTY,INIV.FYEAROUTSUMAMOUNT,INIV.FACCTGDIMEENTRYIDFROMT_HS_INIVBALANCE_HINIVUNIONALLSELECTINIV.FENTRYID,INIV.FID,INIV.FDIMEENTRYID,INIV.FQTY,INIV.FAMOUNT,INIV.FENDINITKEY,INIV.FYEARSUMQTY,INIV.FYEARSUMAMOUNT,INIV.FYEAROUTSUMQTY,INIV.FYEAROUTSUMAMOUNT,INIV.FACCTGDIMEENTRYIDFROMT_HS_INIVBALANCEINIV)INIVINNERJOINT_HS_INIVSTOCKDIMENSIONDIMONINIV.FDIMEENTRYID=DIM.FENTRYID如下尝试：先把当前表和历史表UNIONALL，得到结果集后，再和维度表关联，此时，语句耗时7分8秒，比之前的13分58秒，降低了410秒。--5从上面解析到的执行计划耗时可以看出，耗时主要消耗在VIEW的执行上；得到结果集后，再和外围条件发生关联，从ID=6这步骤来看，这个结果集和外围条件发生关系时，没有返回符合条件的记录（A-ROWS=0），因此，存在这种可能，不执行这个VIEW，而是把VIEW里的两表拆分开，单独和外围语句发生关联（此时原始语句将变成两个UNIONALL），这样，是不是效率更快？因为从VIEW的结果集和外围语句作HASHJOIN的操作看，此举只消耗（362-322）=40秒。改写后的语句变成这样，此前的VIEW里的两表，独自与外围表关联，得到的结果集，再UNIONALL，而VIEW里面之前的维度表，仍然这样体现关系：INNERJOINT_HS_INIVSTOCKDIMENSIONDIMONIV.FDIMEENTRYID=DIM.FENTRYID3/4④内部公开请勿外传测试结果如下：再省却VIEW的UNIONALL操作后，由于外围条件得到结果集很小，优化器选择了走索引来读取T_HS_INIVBALANCE和T_HS_INIVBALANCE_H表的数据，结果集数据很小，语句立马执行结束，耗时0.65秒。从上面的两次优化可以得出这样的结果：1读表操作，越少约好。2能不用视图，尽量不用，直接关联原始表，这样最便于优化器选择最佳的执行计划，也便于调优。用VIEW的写法，很容易把VIEW语句和外围的语句隔离开，无法便利地使用外围的条件过滤VIEW里的数据（无法把外围条件，顺利地推进到VIEW里来作过滤）。4/4
