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

发布时间：2020-09-05 15:11:25 所属栏目：Oracle 来源：互联网

导读：这篇文章主要介绍了Oracle数据库中基本的查询优化与子查询优化讲解,举了实例来分析子查询对性能的影响,需要的朋友可以参考下

1. 查询条件合理排序

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

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

例如：

FROM emp,dept

WHERE emp.deptno = dept.deptno AND emp.deptno = 20;

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

FROM emp,dept

WHERE emp.deptno = 20 AND emp.deptno = dept.deptno;

2.连接中使用表别名

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

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

FROM emp e,dept d

WHERE e.deptno = d.deptno AND e.deptno = 20;

3.用EXISTS替换DISTINCT

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

例如：

4.用WHERE替换HAVING

由于SELECT语句的执行顺序为：先ＷＨＥＲＥ子句,在GROUP BY 子句，然后SELECT查询，再后HAVINT子句，最后是ORDER BY子句，因此在进行分组

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

例如：

WHERE deptno IN (10,20)

GROUP BY deptno;

效率会高于下面

GROUP BY deptno;

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

5.子查询优化

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

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

下面来看一个例子：

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

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

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

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

select rptdate,(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,jcdlbName

order by rptdate,jcdlb '; --①

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

from ywtj_rpt_dyhgl_month r where rptdate =:rptYear; --②

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

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

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

-- 计算1--12月的当月数据
for rptmonth in 1..12 loop
-- 打开游标开始计算合格率
open v_cur for v_sql using v_rptyear||LPAD(rptmonth,v_jcdLbName,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,'0');
loop
fetch v_cur into v_rptDate,v_jcdCount;
exit when v_cur%notfound;
--计算合格率
if v_sumRuntime <> 0 then
v_hgl := (v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime100;
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秒，提高差不多三个数量级。

（编辑：4S站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!