Oracle数据库中基本的查询优化与子查询优化讲解
1.查询条件合理排序
Oracle采用自下而上的顺序解析WHERE字据，从优化性能角度考虑，建议将那些可以过滤掉大量记录行的条件写在WHERE子句的末尾，而将表
之间的连接条件置于其他WHERE子句之前，即对易排查的条件先做判断处理，这样在过滤掉尽可能多的记录后再进行等值连接，可以提高检索效率。
例如：
SELECTempno,ename,job,sal,dept.deptno,dname FROMemp,dept WHEREemp.deptno=dept.deptnoANDemp.deptno=20;
要比下述语句的查询效率高一些：
SELETEempno,ename,job,sal,dept.deptno,dname FROMemp,dept WHEREemp.deptno=20ANDemp.deptno=dept.deptno;
2.连接中使用表别名
在进行连接查询时，建议在SQL语句中使用表的别名，并把别名前缀与每个字段上。这样可以减少解析的时间，并可避免因字段名存在歧义（使用多个
表中出现的同名字段）而导致的语法错误。例如下述语句：
SELECTe.empno,e.ename,e.job,e.sal,e.deptno,d.name FROMempe,deptd WHEREe.deptno=d.deptnoANDe.deptno=20;
3.用EXISTS替换DISTINCT
在进行一对多关系的表间连接查询时，如果要剔除结果中的重复行，可以考虑使用EXISTS（结合子查询）替换DISTINCT。
例如：
SELECTdeptno,dnameFROMdeptdWHEREEXIST(SELECT'y'FROMempeWHEREe.deptno=d.deptno);
4.用WHERE替换HAVING
由于SELECT语句的执行顺序为：先ＷＨＥＲＥ子句,在GROUPBY子句，然后SELECT查询，再后HAVINT子句，最后是ORDERBY子句，因此在进行分组
查询时，如果过滤条件不涉及分组计算，则应该使用WHERE语句替换HAVING指定的过滤条件
例如：
SELECTdeptno,avg(sal)FROMemp WHEREdeptnoIN(10,20) GROUPBYdeptno;
效率会高于下面
SELECTdeptno,avg(sal)FROMemp WHEREdeptnoIN(10,20) GROUPBYdeptno;
当然，如果分组查询的过滤条件设计分组计算，就只能在HAVING子句中指定了。
5.子查询优化
1）在数据量大时，子查询的性能会受到比较大的影响；
2）在子查询中使用了函数to_char(add_months(to_date(tb.rptdate,'yyyymm'),12),'yyyymm')，对大数据量查询时，执行函数也会对系统产生比较大的影响。
下面来看一个例子：
createorreplaceprocedureRpt_Dyhgl_year( rptyearinvarchar2,--年yyyy --rpttypeinvarchar2,--报表类型，1年报，2季报 ref_cursoroutsys_refcursor--返回游标 ) As --变量声明 。。。。。 begin deletefromYWTJ_RPT_DYHGL_YEARwhereRPTYEAR=rptyearandRPTTYPE='1'; commit; --查询原始数据 v_sql:='selectrptdate,jcdlb,jcdlbName,sum(nvl(runtime,0))sumruntime,sum(nvl(buhegeTime,0))buhegeTime,count(jcdcount)jcdcount, sum(nvl(tongbiRuntime,0))tongbiRuntime,sum(nvl(tongbiBhg,0))tongbiBhgfrom( selectrptdate,runtime,r.chaoshangxian+r.chaoxiaxianbuhegeTime,r.objid,1jcdcount, (selectruntimefromywtj_rpt_dyhgl_monthtbwhereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'')=r.rptdateandtb.objid=r.objid)tongbiRuntime, (selectchaoshangxian+chaoxiaxianfromywtj_rpt_dyhgl_monthtbwhereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'')=r.rptdateandtb.objid=r.objid)tongbiBhg, (selectjcdlbfromjczl_dwjgdwhered.objid=r.objid)jcdlb, (selectjcdlbnamefromdic_jcdlbwherejcdlbid=(selectjcdlbfromjczl_dwjgwhereobjid=r.objid))jcdlbName fromywtj_rpt_dyhgl_monthrwhererptdate=:rptYear) groupbyrptdate,jcdlb,jcdlbName orderbyrptdate,jcdlb'; --插入新数据 v_sqlRptInsert:='InsertIntoYWTJ_RPT_DYHGL_YEAR(RPTYEAR,RPTDATE,RPTTYPE)values(:RPTYEAR,:RPTDATE,:RPTTYPE)'; v_sqlRptUpdate:=''; --计算1--12月的当月数据 forrptmonthin1..12loop executeimmediatev_sqlRptInsertusingrptyear,rptmonth,'1'; commit; openv_curforv_sqlusingrptyear||LPAD(rptmonth,2,'0'); loop fetchv_curintov_rptDate,v_jcdlb,v_jcdName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount,v_tongbiRuntime,v_tongbiBhg; exitwhenv_cur%notfound; --计算合格率 。。。。。。 --更新当月数据 。。。。。。 endloop; closev_cur; endloop; --更新累计数据 。。。。。。 commit; --计算综合合格率 openref_cursorforselect*fromYWTJ_RPT_DYHGL_YEARrwherer.rptyear=rptyearandr.rpttype=1orderbyr.rptdate; endRpt_Dyhgl_year;
功能运行正常，查询时间大约30-40秒。
然后，把报表部署到运行环境中后，验证功能时，发现2、3分钟报表没有出来！多次尝试，后来终于出来结果了：940多秒，15分钟多！这样用户肯定是不能接受的！使用pl/sqldev的性能分析对Rpt_Dyhgl_year的执行过程进行分析，发现几乎所有的时间都化在了
fetchv_curintov_rptDate,v_jcdlb,v_jcdName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount,v_tongbiRuntime,v_tongbiBhg;
（期间把过程转成程序执行，性能分析时发现，主要时间都是由dr.Read()占用，和对过程的分析一致。）
那问题应该是在fetch对应的查询上，把
v_sql:='selectrptdate,jcdlb,jcdlbName,sum(nvl(runtime,0))sumruntime,sum(nvl(buhegeTime,0))buhegeTime,count(jcdcount)jcdcount, sum(nvl(tongbiRuntime,0))tongbiRuntime,sum(nvl(tongbiBhg,0))tongbiBhgfrom( selectrptdate,runtime,r.chaoshangxian+r.chaoxiaxianbuhegeTime,r.objid,1jcdcount, (selectruntimefromywtj_rpt_dyhgl_monthtbwhereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'')=r.rptdateandtb.objid=r.objid)tongbiRuntime, (selectchaoshangxian+chaoxiaxianfromywtj_rpt_dyhgl_monthtbwhereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'')=r.rptdateandtb.objid=r.objid)tongbiBhg, (selectjcdlbfromjczl_dwjgdwhered.objid=r.objid)jcdlb, (selectjcdlbnamefromdic_jcdlbwherejcdlbid=(selectjcdlbfromjczl_dwjgwhereobjid=r.objid))jcdlbName fromywtj_rpt_dyhgl_monthrwhererptdate=:rptYear) groupbyrptdate,jcdlb,jcdlbName orderbyrptdate,jcdlb';--①
单独拿出来执行，大约5.7秒多。
再把其中的子查询：
selectrptdate,runtime,r.chaoshangxian+r.chaoxiaxianbuhegeTime,r.objid,1jcdcount, (selectruntimefromywtj_rpt_dyhgl_monthtbwhereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'')=r.rptdateandtb.objid=r.objid)tongbiRuntime, (selectchaoshangxian+chaoxiaxianfromywtj_rpt_dyhgl_monthtbwhereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'')=r.rptdateandtb.objid=r.objid)tongbiBhg, (selectjcdlbfromjczl_dwjgdwhered.objid=r.objid)jcdlb, (selectjcdlbnamefromdic_jcdlbwherejcdlbid=(selectjcdlbfromjczl_dwjgwhereobjid=r.objid))jcdlbName fromywtj_rpt_dyhgl_monthrwhererptdate=:rptYear;--②
单独拿出来执行，速度很快，但返回的数据量很大。如果再对其循环分析，估计性能也好不到哪去，先行放弃这种尝试。
把查询②中的tongbiRuntime和tongbiBhg两个字段子查询去掉，执行速度更快，同样再把①中的tongbiRuntime和tongbiBhg两个字段子查询去掉，执行速度提高很多，时间约0.157秒。
这样速度就提高了很多，但少了去年同比的数据，那就分成两个查询！按这个思路下来，最后得到的过程如下（只保留主要部分）：
createorreplaceprocedureRpt_Dyhgl_year( --电压合格率-年 v_rptyearinvarchar2,--年yyyy --rpttypeinvarchar2,--报表类型，1年报，2季报 ref_cursoroutsys_refcursor--返回游标 ) as --变量声明 ....... begin deletefromYWTJ_RPT_DYHGL_YEARwhereRPTYEAR=v_rptyearandRPTTYPE='1'; commit; --查询原始数据 v_sql:='selectrptdate,jcdlb,jcdlbName,sum(nvl(runtime,0))sumruntime,sum(nvl(buhegeTime,0))buhegeTime,count(jcdcount)jcdcount from(selectrptdate,runtime,r.chaoshangxian+r.chaoxiaxianbuhegeTime,r.objid,1jcdcount, (selectjcdlbfromjczl_dwjgdwhered.objid=r.objid)jcdlb, (selectjcdlbnamefromdic_jcdlbwherejcdlbid=(selectjcdlbfromjczl_dwjgwhereobjid=r.objid))jcdlbName fromywtj_rpt_dyhgl_monthrwhererptdate=:rptYear) groupbyrptdate,jcdlb,jcdlbName orderbyrptdate,jcdlb'; --插入新数据 v_sqlRptInsert:='InsertIntoYWTJ_RPT_DYHGL_YEAR(RPTYEAR,RPTDATE,RPTTYPE)values(:RPTYEAR,:RPTDATE,:RPTTYPE)'; v_sqlRptUpdate:=''; --计算1--12月的当月数据 forrptmonthin1..12loop --打开游标开始计算合格率 openv_curforv_sqlusingv_rptyear||LPAD(rptmonth,2,'0'); loop fetchv_curintov_rptDate,v_jcdlb,v_jcdLbName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount; exitwhenv_cur%notfound; --计算合格率 ifv_sumRuntime<>0then v_hgl:=(v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime*100; endif; --更新当月数据 ........ endloop; closev_cur; --打开游标开始计算同比合格率 openv_curforv_sqlusing(v_rptyear-1)||LPAD(rptmonth,2,'0'); loop fetchv_curintov_rptDate,v_jcdlb,v_jcdLbName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount; exitwhenv_cur%notfound; --计算合格率 ifv_sumRuntime<>0then v_hgl:=(v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime*100; endif; --更新同比数据 ......... endloop; closev_cur; endloop; --更新累计数据 ......... --计算综合合格率 ........ openref_cursorforselect*fromYWTJ_RPT_DYHGL_YEARrwherer.rptyear=v_rptyearandr.rpttype=1orderbyr.rptdate; endRpt_Dyhgl_year;
经验证，执行速度大约在一秒多，也就是说性能从原来的900多秒提高到1秒，提高差不多三个数量级。