1. 查询条件合理排序
Oracle采用自下而上的顺序解析WHERE字据，从优化性能角度考虑，建议将那些可以过滤掉大量记录行的条件写在WHERE子句的末尾，而将表
之间的连接条件置于其他WHERE子句之前，即对易排查的条件先做判断处理，这样在过滤掉尽可能多的记录后再进行等值连接，可以提高检索效率。
例如：
|
1 2 3 4 5 |
SELECT empno, ename, job, sal, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno AND emp.deptno = 20; |
要比下述语句的查询效率高一些：
|
1 2 3 4 5 |
SELETE empno, ename, job, sal, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno = 20 AND emp.deptno = dept.deptno; |
2.连接中使用表别名
在进行连接查询时，建议在SQL语句中使用表的别名， 并把别名前缀与每个字段上。这样可以减少解析的时间，并可避免因字段名存在歧义（使用多个
表中出现的同名字段）而导致的语法错误。例如下述语句：
|
1 2 3 4 5 |
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。
例如：
|
1 |
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指定的过滤条件
例如：
|
1 2 3 4 5 |
SELECT deptno, avg (sal) FROM emp
WHERE deptno IN (10, 20)
GROUP BY deptno; |
效率会高于下面
|
1 2 3 4 5 |
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')，对大数据量查询时，执行函数也会对系统产生比较大的影响。
下面来看一个例子：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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的执行过程进行分析，发现几乎所有的时间都化在了
|
1 |
fetch v_cur into v_rptDate,v_jcdlb,v_jcdName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount,v_tongbiRuntime,v_tongbiBhg; |
（期间把过程转成程序执行，性能分析时发现，主要时间都是由dr.Read()占用，和对过程的分析一致。）
那问题应该是在fetch对应的查询上，把
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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秒多。
再把其中的子查询：
|
1 2 3 4 5 6 7 8 9 10 11 |
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秒。
这样速度就提高了很多，但少了去年同比的数据，那就分成两个查询！按这个思路下来，最后得到的过程如下（只保留主要部分）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
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秒，提高差不多三个数量级。
查看更多关于Oracle数据库中基本的查询优化与子查询优化讲解的详细内容...