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语句的执行顺序为:先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()占用,和对过程的分析一致。)
那问题应该是在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秒,提高差不多三个数量级。
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 Oracle数据库中基本的查询优化与子查询优化讲解
相关文章
- Oracle数据库管理:细节处理目录权限(oracle目录权限)
- 解析深入浅出:Oracle数据库表案例分析(oracle数据库表案例)
- 解决 Oracle 修改函数问题(oracle修改函数)
- Oracle数据库白名单管理实践(oracle白名单)
- 精通 Oracle 数据库对象(oracle数据库对象)
- 数据库Oracle数据库创建:从入门到实践(oracle怎么创建)
- 掌握Oracle数据库大小:超简单查询方法(oracle查询库大小)
- 管理Oracle数据库:触发器类型分析(oracle触发器类型)
- 掌握Oracle触发器的不同类型(oracle触发器类型)
- 学习Oracle:创建表的基本语句(oracle创建表语句)
- Oracle角色与权限管理实践(oracle角色和权限)
- 使用Oracle客户端的编码技巧:如何优化数据库管理(oracle客户端编码)
- Oracle数据库如何修改行?一些简单的步骤介绍(oracle修改行)
- 锁定Oracle数据库中表的正确方式(锁定oracle表)
- 如何使用Oracle数据库打开复杂的资料(oracle数据库怎么打开)
- Oracle数据库分析报告(oracle分析报告)
- 禁用Oracle数据库的直方图功能(oracle关闭直方图)
- Oracle数据库实现关联外表(oracle关联外表)
- Oracle数据库关归档步骤详解(oracle关归档步骤)
- Oracle数据库实现全字段替换技巧(oracle全部字段替换)
- 精准查找C语言查询Oracle数据库中的表名(c oracle查询表名)
- 数据Oracle如何保留最初的记录(oracle 保留最早)
- 组件Oracle 数据库 GC 组件更高级别的资源管理(oracle中的gc)
- 挖掘Oracle中成本数据的查询策略(oracle中查询成本)
- Oracle中究竟有多少种修改字断的方法(oracle 修改字断)
- Oracle数据库不同版本介绍(oracle不同版本介绍)
- Oracle杯大赛增添新颜色,释放激情(oracle cup数量)