Oracle数据库中基本的查询优化与子查询优化讲解

1. 查询条件合理排序

oracle采用自下而上的顺序解析where字据，从优化性能角度考虑，建议将那些可以过滤掉大量记录行的条件写在where子句的末尾，而将表

之间的连接条件置于其他where子句之前，即对易排查的条件先做判断处理，这样在过滤掉尽可能多的记录后再进行等值连接，可以提高检索效率。

例如：

select empno, ename, job, sal, dept.deptno, dname 

from emp, dept 

where emp.deptno = dept.deptno and emp.deptno = 20;

要比下述语句的查询效率高一些：

selete empno, ename, job, sal, dept.deptno, dname

from emp, dept

where emp.deptno = 20 and emp.deptno = dept.deptno;

2.连接中使用表别名

在进行连接查询时，建议在sql语句中使用表的别名， 并把别名前缀与每个字段上。这样可以减少解析的时间，并可避免因字段名存在歧义（使用多个

表中出现的同名字段）而导致的语法错误。例如下述语句：

select e.empno, e.ename, e.job, e.sal, e.deptno, d.name

from emp e, dept d

where e.deptno = d.deptno and e.deptno = 20;

3.用exists替换distinct

在进行一对多关系的表间连接查询时，如果要剔除结果中的重复行，可以考虑使用exists（结合子查询）替换distinct。

例如：

select deptno, dname from dept d where exist (select 'y' from emp e where e.deptno = d.deptno);

4.用where替换having

由于select语句的执行顺序为：先ｗｈｅｒｅ子句, 在group by 子句， 然后select查询， 再后havint子句，最后是order by子句，因此在进行分组

查询时，如果过滤条件不涉及分组计算，则应该使用where语句替换having指定的过滤条件

例如：

select deptno, avg(sal) from emp

where deptno in (10, 20)

group by deptno;

效率会高于下面

select deptno, avg(sal) from emp

where deptno in (10, 20)

group by deptno;

当然， 如果分组查询的过滤条件设计分组计算，就只能在having子句中指定了。

5.子查询优化

1） 在数据量大时，子查询的性能会受到比较大的影响；

2） 在子查询中使用了函数to_char(add_months(to_date(tb.rptdate,'yyyymm'),12),'yyyymm')，对大数据量查询时，执行函数也会对系统产生比较大的影响。

下面来看一个例子：

createor replace procedure rpt_dyhgl_year( 
 rptyear      in varchar2, --年yyyy 
 --rpttype      in varchar2,  --报表类型，1年报，2季报 
 ref_cursor      out sys_refcursor --返回游标 
) 
as 
 -- 变量声明 
 。。。。。 
begin 
  delete from ywtj_rpt_dyhgl_year where rptyear = rptyear and rpttype = '1'; 
  commit; 
 --查询原始数据 
 v_sql := 'select rptdate, jcdlb, jcdlbname, sum(nvl(runtime,0)) sumruntime,sum(nvl(buhegetime,0)) buhegetime, count(jcdcount) jcdcount, 
    sum(nvl(tongbiruntime,0)) tongbiruntime, sum(nvl(tongbibhg,0))tongbibhg from ( 
  select rptdate, runtime,r.chaoshangxian+r.chaoxiaxian buhegetime, r.objid, 1 jcdcount, 
    (select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiruntime, 
    (select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbibhg, 
    (select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb, 
    (select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbname 
    from ywtj_rpt_dyhgl_month r where rptdate = :rptyear) 
  group by rptdate, jcdlb,  jcdlbname  
  order by rptdate ,jcdlb '; 
  -- 插入新数据 
  v_sqlrptinsert := 'insert intoywtj_rpt_dyhgl_year (rptyear,rptdate,rpttype) values(:rptyear,:rptdate,:rpttype)'; 
  v_sqlrptupdate := ''; 
  -- 计算1--12月的当月数据 
  for rptmonth in 1..12 loop 
   execute immediate v_sqlrptinsertusing rptyear, rptmonth, '1'; 
   commit; 
   open v_cur for v_sql using rptyear ||lpad(rptmonth, 2, '0'); 
   loop 
     fetch v_cur intov_rptdate,v_jcdlb,v_jcdname,v_sumruntime,v_sumbuhegetime,v_jcdcount,v_tongbiruntime,v_tongbibhg; 
     exit when v_cur%notfound; 
     --计算合格率 
     。。。。。。 
     -- 更新当月数据 
     。。。。。。 
    end loop; 
    close v_cur; 
  end loop; 
  -- 更新累计数据 
  。。。。。。 
  commit; 
  -- 计算综合合格率 
  open ref_cursor for select * fromywtj_rpt_dyhgl_year r where r.rptyear = rptyear and r.rpttype = 1 order byr.rptdate; 
end rpt_dyhgl_year; 

功能运行正常，查询时间大约30-40秒。

然后，把报表部署到运行环境中后，验证功能时，发现2、3分钟报表没有出来！多次尝试，后来终于出来结果了：940多秒，15分钟多！这样用户肯定是不能接受的！使用pl/sql dev的性能分析对rpt_dyhgl_year的执行过程进行分析，发现几乎所有的时间都化在了

fetch v_cur into v_rptdate,v_jcdlb,v_jcdname,v_sumruntime,v_sumbuhegetime,v_jcdcount,v_tongbiruntime,v_tongbibhg;

（期间把过程转成程序执行，性能分析时发现，主要时间都是由dr.read()占用，和对过程的分析一致。）

那问题应该是在fetch对应的查询上，把

v_sql :='select rptdate, jcdlb, jcdlbname,sum(nvl(runtime,0)) sumruntime, sum(nvl(buhegetime,0)) buhegetime,count(jcdcount) jcdcount, 
 
    sum(nvl(tongbiruntime,0)) tongbiruntime, sum(nvl(tongbibhg,0))tongbibhg from ( 
 
  select rptdate, runtime,r.chaoshangxian+r.chaoxiaxian buhegetime, r.objid, 1 jcdcount, 
 
    (select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiruntime, 
 
    (select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbibhg, 
 
    (select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb, 
 
    (select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbname 
 
    from ywtj_rpt_dyhgl_month r where rptdate = :rptyear) 
 
  group by rptdate, jcdlb,  jcdlbname  
 
  order by rptdate ,jcdlb ';      --① 

单独拿出来执行，大约5.7秒多。
再把其中的子查询：

selectrptdate, runtime, r.chaoshangxian+r.chaoxiaxian buhegetime, r.objid, 1jcdcount, 
 
    (select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiruntime, 
 
    (select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbibhg, 
 
    (select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb, 
 
    (select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbname 
 
    from ywtj_rpt_dyhgl_month r where rptdate =:rptyear;  --② 

单独拿出来执行，速度很快，但返回的数据量很大。如果再对其循环分析，估计性能也好不到哪去，先行放弃这种尝试。

把查询②中的tongbiruntime和tongbibhg两个字段子查询去掉，执行速度更快，同样再把①中的tongbiruntime和tongbibhg两个字段子查询去掉，执行速度提高很多，时间约0.157秒。

这样速度就提高了很多，但少了去年同比的数据，那就分成两个查询！按这个思路下来，最后得到的过程如下（只保留主要部分）：

createor replace procedure rpt_dyhgl_year( 
--电压合格率-年 
 v_rptyear      in varchar2, --年yyyy 
 --rpttype      in varchar2,  --报表类型，1年报，2季报 
 ref_cursor      out sys_refcursor --返回游标 
) 
as 
 -- 变量声明 
 ....... 
begin 
  delete from ywtj_rpt_dyhgl_year where rptyear = v_rptyear and rpttype = '1'; 
  commit; 
 --查询原始数据 
 v_sql := 'select rptdate, jcdlb, jcdlbname, sum(nvl(runtime,0)) sumruntime,sum(nvl(buhegetime,0)) buhegetime, count(jcdcount) jcdcount 
    from ( select rptdate, runtime,r.chaoshangxian+r.chaoxiaxian buhegetime, r.objid, 1 jcdcount, 
    (select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb, 
    (select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbname 
    from ywtj_rpt_dyhgl_month r where rptdate = :rptyear) 
  group by rptdate, jcdlb,  jcdlbname 
  order by rptdate ,jcdlb '; 
  -- 插入新数据 
  v_sqlrptinsert := 'insert intoywtj_rpt_dyhgl_year (rptyear,rptdate,rpttype) values(:rptyear,:rptdate,:rpttype)'; 
  v_sqlrptupdate := ''; 
 
  -- 计算1--12月的当月数据 
  for rptmonth in 1..12 loop 
   -- 打开游标开始计算合格率 
   open v_cur for v_sql using v_rptyear||lpad(rptmonth, 2, '0'); 
   loop 
     fetch v_cur intov_rptdate,v_jcdlb,v_jcdlbname,v_sumruntime,v_sumbuhegetime,v_jcdcount; 
     exit when v_cur%notfound; 
     --计算合格率 
     if v_sumruntime <> 0 then 
      v_hgl := (v_sumruntime-v_sumbuhegetime)/v_sumruntime *100; 
     end if; 
     -- 更新当月数据 
     ........ 
    end loop; 
    close v_cur; 
   -- 打开游标开始计算同比合格率 
   open v_cur for v_sql using (v_rptyear-1)|| lpad(rptmonth, 2, '0'); 
   loop 
     fetch v_cur into v_rptdate,v_jcdlb,v_jcdlbname,v_sumruntime,v_sumbuhegetime,v_jcdcount; 
     exit when v_cur%notfound; 
     --计算合格率 
     if v_sumruntime <> 0 then 
      v_hgl := (v_sumruntime-v_sumbuhegetime)/v_sumruntime*100; 
     end if; 
     -- 更新同比数据 
     ......... 
    end loop; 
    close v_cur; 
  end loop; 
 
  -- 更新累计数据 
  .........  
  -- 计算综合合格率 
  ........ 
 
  open ref_cursor for select * fromywtj_rpt_dyhgl_year r where r.rptyear = v_rptyear and r.rpttype = 1 order byr.rptdate; 
end rpt_dyhgl_year;

 

经验证，执行速度大约在一秒多，也就是说性能从原来的900多秒提高到1秒，提高差不多三个数量级。