本文整理自网络,侵删。
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语句的执行顺序为:先WHERE子句, 在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()占用,和对过程的分析一致。)
相关阅读 >>
解决oracle11g导出数据报“ora-01455转换列溢出整数数据类型”的问题
更多相关阅读请进入《oracle》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。