1/10用友股份-LE支持服务业务本部技术方案--《SQL优化案例》建立日期：2014-01-20修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-JS-2014-00032/10文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围发布人姓名所属部门发布时间内部员工伙伴客户日期作者所属部门邮件地址版本2014-01-20郜耿智技术支持部V1.0版本领域模块备注3/10目录SQL优化案例............................................................................................................4(一--原始SQL和原始执行计划：................................................................4)(二--查看and谓词条件的过滤结果：........................................................5)(三--查看完整子查询SQL和执行计划：....................................................5)(四--查看子查询单个IN语句<不包含or>SQL过滤结果：.......................6)(五--查看子查询单个IN语句<不包含or>SQL和执行计划：...................7)(六--查看子查询or谓词SQL过滤结果：...................................................8)(七--查看子查询or谓词SQL和优化后执行计划：...................................8)(八--查看调整后的SQL和执行计划：........................................................9)(九验证结果：.............................................................................................10)4/10SQL优化案例注：此解决方案涉及数据库操作或代码操作，请由计算机专业人士操作，并且操作前请做好备份工作！--原始SQL和原始执行计划：(一)explainplanforselectdistinctaccountcode,accountname,account,remcode,pk_banktype,arapprop,unitname,genebranprop,ownercorp,bd_bankaccbas.pk_bankaccbasfrombd_bankaccbaswhere(pk_bankaccbasin(selectbd_bankaccbas.pk_bankaccbasfrombd_bankaccbas,bd_bankaccauthwherebd_bankaccbas.pk_bankaccbas=bd_bankaccauth.pk_bankaccbasandbd_bankaccauth.pk_corp='2003'andisauthedusepower='Y'andmainaccountisnullandusepowerflag='Y')or(accclass=3andownercorp='2003')or(accclass=2andmainaccountisnotnullandpk_corp='2003'))andmainaccountisnulland((accstate=0oraccstate=2))orderbyaccountcode;select*fromtable(Dbms_Xplan.display)5/10--查看and谓词条件的过滤结果：(二)And过滤结果集过多，优化空间不大--查看完整子查询SQL和执行计划：(三)explainplanforselect*frombd_bankaccbaswhere(pk_bankaccbasin(selectbd_bankaccbas.pk_bankaccbasfrombd_bankaccbas,bd_bankaccauthwherebd_bankaccbas.pk_bankaccbas=bd_bankaccauth.pk_bankaccbasandbd_bankaccauth.pk_corp='2003'6/10andisauthedusepower='Y'andmainaccountisnullandusepowerflag='Y')or(accclass=3andownercorp='2003')or(accclass=2andmainaccountisnotnullandpk_corp='2003'));select*fromtable(Dbms_Xplan.display);--查看子查询单个IN语句<不包含or>SQL过滤结果：(四)7/10--创建第一个优化脚本createindexi_bd_bankaccauth_jszc01onbd_bankaccauth(pk_corp,isauthedusepower);--查看子查询单个IN语句<不包含or>SQL和执行计划：(五)explainplanforselect*frombd_bankaccbaswhere(pk_bankaccbasin(selectbd_bankaccbas.pk_bankaccbasfrombd_bankaccbas,bd_bankaccauthwherebd_bankaccbas.pk_bankaccbas=bd_bankaccauth.pk_bankaccbasandbd_bankaccauth.pk_corp='2003'andisauthedusepower='Y'andmainaccountisnullandusepowerflag='Y'));select*fromtable(Dbms_Xplan.display);8/10--查看子查询or谓词SQL过滤结果：(六)--创建第二、三个优化脚本createindexi_bd_bankaccbas_jszc04onbd_bankaccbas(ownercorp,accclass)nologging;createindexi_bd_bankaccbas_jszc05onbd_bankaccbas(pk_corp,accclass)nologging;--查看子查询or谓词SQL和优化后执行计划：(七)explainplanforselect*frombd_bankaccbaswhere(accclass=3andownercorp='2003')or(accclass=2andmainaccountisnotnullandpk_corp='2003');select*fromtable(dbms_xplan.display);9/10--查看调整后的SQL和执行计划：(八)explainplanforselectdistinctaccountcode,accountname,account,remcode,pk_banktype,arapprop,unitname,genebranprop,ownercorp,pk_bankaccbasfrom(selectdistinctaccountcode,accountname,account,remcode,pk_banktype,arapprop,unitname,genebranprop,ownercorp,bd_bankaccbas.pk_bankaccbasfrombd_bankaccbaswherepk_bankaccbasin(selectbd_bankaccbas.pk_bankaccbasfrombd_bankaccbas,bd_bankaccauthwherebd_bankaccbas.pk_bankaccbas=bd_bankaccauth.pk_bankaccbasandbd_bankaccauth.pk_corp='2003'andisauthedusepower='Y'andmainaccountisnullandusepowerflag='Y')andmainaccountisnulland((accstate=0oraccstate=2))unionselectdistinctaccountcode,accountname,account,remcode,pk_banktype,arapprop,unitname,genebranprop,ownercorp,10/10bd_bankaccbas.pk_bankaccbasfrombd_bankaccbaswhere(accclass=3andownercorp='2003')or(accclass=2andmainaccountisnotnullandpk_corp='2003')andmainaccountisnulland((accstate=0oraccstate=2)))orderbyaccountcode;select*fromtable(Dbms_Xplan.display);验证结果：(九)SQL语句从之前的30S左右，减少到0.1S
