sql调优4：修改sql，去视图

 场景：
     新物资系统中发现有不少sql使用了视图，从语句上来看，可读性稍好些，但性能很差，经加适当索引后，语句的性能有改观，但仍然存在表扫描的情况。笔者尝试去掉视图后，发现执行计划改善，性能提高。

修改前的语句：

with temp as(
  select *
    from (
      select MATERIAL_ID, rownumber() over () as rn
        from MAT_MATERIAL
        where REQPLAN_ID=2119011
          and ABANDON_STATE=0 ) as tableTemp
    where tableTemp.rn between 1 and 15 )
  select mm.MAT_UNIQCODE, mm.MATERIAL_ID, mm.ANNEXFLAG , bc.CORP_NAME as 
      needCorp, pv.PKGNAME, pv.PROJECT_NAME, pv.ENGINEER_CODE, 
      pv.ENGINEER_NAME, pv.KYPFNUMBER, pv.ASSETATTR_NAME, pv.PRONATURE_NAME, 
      pv.PROJECTATTR_NAME, mmt.ZBGS_NAME, mmt.TYPE_NAME, mmt.SPECTYPE, 
      mpl.TECH_PARA, mmt.MEAUNIT_NAME, mmt.VOL_NAME, mm.REQ_AMOUNT, 
      mm.DELIVER_TIME, mm.DELIVER_TIME_FLAG, mm.DELIVER_ADDRESS, 
      mm.PROPH_MANUF, mm.REMARK, mm.BUDGET, mm.ANNEXFLAG, a.PLANSOURCE, 
      a.REQPLAN_CODE, a.WORKFLOW_STATE, su.user_name, mg.WORKFLOW_STATE as 
      gatherState, rownumber() over (order by mm.MATERIAL_ID) as rn
    from MAT_MATERIAL mm
      left join MAT_REQPLAN a
      on mm.REQPLAN_ID=a.REQPLAN_ID
      left join BAS_CORP as bc
      on a.CROP_CODE=bc.CORP_CODE
      left join  MATERIALINFO_VIEW mmt
      on mm.THESAURUS_ID=mmt.THESAURUS_ID
      left join MAT_PLANMATTECHPAR mpl
      on mm.MATERIAL_ID=mpl.MATERIAL_ID
      left join  PROJECTINFO_VIEW pv
      on mm.PROJECT_ID=pv.PROJECT_ID
      left join sys_user su
      on su.user_id=a.AUTHOR_ID
      left join MAT_GATHERPLAN mg
      on mm.GATHERPLAN_ID=mg.GATHERPLAN_ID
    where exists (
        select temp.MATERIAL_ID
          from temp
          where temp.MATERIAL_ID=mm.MATERIAL_ID )
  with ur

分析过程：
    运用questcentral的sql tuning查看其  cost为  5077.70.
主要消耗在两个表(MAT_THESAURUS、MAT_PROJECT)的表扫描上。而这两个表分别在视图(MATERIALINFO_VIEW、PROJECTINFO_VIEW)里调用。从连接条件上看索引已经加上了，但没有使用到。尝试去掉视图后可以使用索引。

优化后的sql:

with temp as(
  select *
    from (
      select MATERIAL_ID, rownumber() over () as rn
        from MAT_MATERIAL
        where REQPLAN_ID=2119011
          and ABANDON_STATE=0 ) as tableTemp
    where tableTemp.rn between 1 and 15 )
  select mm.MAT_UNIQCODE, mm.MATERIAL_ID, mm.ANNEXFLAG , bc.CORP_NAME as 
      needCorp, cc.PKGNAME,bb.PROJECT_NAME, bb.ENGINEER_CODE,bb.ENGINEER_NAME,
      bb.KYPFNUMBER, dd.ASSETATTR_NAME, ee.PRONATURE_NAME, ff.PROJECTATTR_NAME
      , e.NAME as ZBGS_NAME, b.TYPE_NAME, aa.SPECTYPE, mpl.TECH_PARA, d.NAME 
      as MEAUNIT_NAME, c.name as VOL_NAME, mm.REQ_AMOUNT, mm.DELIVER_TIME, 
      mm.DELIVER_TIME_FLAG, mm.DELIVER_ADDRESS, mm.PROPH_MANUF, mm.REMARK, 
      mm.BUDGET, mm.ANNEXFLAG, a.PLANSOURCE, a.REQPLAN_CODE, a.WORKFLOW_STATE,
      su.user_name, mg.WORKFLOW_STATE as gatherState, rownumber() over (order 
      by mm.MATERIAL_ID) as rn
    from MAT_MATERIAL mm
      left join MAT_REQPLAN a
      on mm.REQPLAN_ID=a.REQPLAN_ID
      left join BAS_CORP as bc
      on a.CROP_CODE=bc.CORP_CODE
      left join MAT_THESAURUS aa
      on mm.THESAURUS_ID=aa.THESAURUS_ID
      left join MAT_MATTYPE b
      on aa.MATTYPE_ID = b.MATTYPE_ID
      left join MAT_VOLLEV c
      on aa.VOLLEV_ID = c.VOLLEV_ID
      left join MAT_MEAUNIT d
      on aa.MEAUNIT_ID = d.MEAUNIT_ID
      left join MAT_ZBGS e
      on aa.ZBGS_ID=e.ZBGS_ID
      left join MAT_PLANMATTECHPAR mpl
      on mm.MATERIAL_ID=mpl.MATERIAL_ID
      left join MAT_PROJECT bb
      on mm.PROJECT_ID=bb.PROJECT_ID
      left join MAT_PROPKG cc
      on bb.PROPKG_ID = cc.PROPKG_ID
      left join MAT_ASSETATTR dd
      on bb.ASSETATTR_ID = dd.ASSETATTR_ID
      left join MAT_PRONATURE ee
      on bb.PRONATURE_ID =ee.PRONATURE_ID
      left join MAT_PROJECTATTR ff
      on ff.PROJECTATTR_ID=bb.PROJECTATTR_ID
      left join sys_user su
      on su.user_id=a.AUTHOR_ID
      left join MAT_GATHERPLAN mg
      on mm.GATHERPLAN_ID=mg.GATHERPLAN_ID
    where  mm.REQPLAN_ID=2119011  - 增加的条件,以告知优化器使用索引
      and exists (
        select temp.MATERIAL_ID
          from temp
          where temp.MATERIAL_ID=mm.MATERIAL_ID )
  with ur

优化后没有表扫描， cost为  261.31


需要说明的是，本案例只是说明有这种情况存在，并不是表明视图的性能差，不能用，而是这种情况下(查询条件里没有视图的关键字)没有用到索引，如果查询语句里有明确的视图关键字条件的话，视图还是会用到索引的。



-- The End --