文档章节

Oracle语句优化的十九种方式

孟飞阳
 孟飞阳
发布于 2016/07/16 00:03
字数 3111
阅读 27
收藏 2
点赞 1
评论 0

Oracle语句优化的十九种方式

孟飞阳  2012-02-09

一. 选择合理的表名顺序

该方法只在基于规则的优化器中有效,基于代价时,ORACLE会根据表的物理大小,索引的状态,然后选用花费最低的执行路径。ORACLE的解析器按照从右到左的顺序处理FROM字句中的表名,因此FROM字句写在最后的表将被最先处理。

在FROM字句中的包含多个表的情况下,必须选择记录条数最少的表作为基础表(放在最后)。

如果有3个以上的表连接查询,那就需要选择交叉表(intersection table)作为基础表,交叉表是指哪个被其他表所引用的表。

例:

EMP表描述了LOCATION表和CATEGORY表的交集。

SELECT * FROM LOCATION L,CATEGORY C,EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN

将比下列SQL更有效率:

SELECT * FROM EMP E,LOCATION L,CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000

二. Where字句中的连接顺序

ORACLE采用自下而上的顺序解析where字句。

ORACLE总会在能使用索引的时候使用索引(除非表特别小),但如果有多个索引可供使用时,可能会因为顺序的不同导致效率的不同。考虑下面的SQL语句:

SELECT ……
FROM EMP_REC
WHERE EMAIL = ‘liu.yuzhou’
And DEPT_NO = ‘010201’

如果在(DEPT_NO,EMAIL)上有联合索引,且DEPT_NO上有索引,则使用联合索引;如果在DEPT_NO和EMAIL上都有索引,但EMAIL上是唯一索引,则使用EMAIL上的索引;如果在DEMP_NO和EMAIL都有索引,且都为非唯一性索引,则先根据EMAIL检索记录,再根据DEPT_NO检索记录,然后取他们的交集。

所以,我们在写SQL语句时,就应该考虑索引的使用情况。

如果无法使用索引,ORACLE会执行全表扫描,我们应将限制性强的条件放在后面。如我们知道一个部门大约有500人,而一个EMAIL大约对应5人,则我们应该将EMAIL=’liu.yuzhou’的条件放在后面。这样可以避免部分记录的二次比对。

三. 避免使用SELECT *

当你想在SELECT子句中列出所有的COLUMN时,使用’SELECT * ‘是一个方便的方法。不幸的是,这是一个低效的方法。实际上,ORACLE在解析的过程中,会将’* ‘依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。

四.删除重复记录

最高效的删除重复记录方法(因为使用了ROWID)

DELECT FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);

五.减少对表的查询

例1:

SELECT TAB_NAME FROM TABLES
WHERE TAB_NAME = (SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER = (SELECT DB_VER FROM TAB_COLUMNS
WHERE VERSION = 604)

可改编为:

SELECT TAB_NAME FROM TABLES
WHERE(TAB_NAME,DB_VER)  = (SELECT TAB_NAME,DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)

例2:

UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY)
FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;

可改编为:

UPDATE EMP
SET (EMP_CAT,SAL_RANGE)
= (SELECT MAX(CATEGORY),MAX(SAL_RANGE)
FROM EMP_CAEGORIES)
WHERE EMP_DEPT = 0020;

六.使用存储函数提高效率

七.使用表的别名

当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句出现在这个Column时,SQL解析器无法判断这个Column的归属。

八.用EXISTS替代IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行连接,在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率。

例:

SELECT * FROM EMP(基础表)
WHERE EMP_NO > 0
AND DEPTNO IN(SELECT DEPTNO FROM DEPT
WHERE LOC = ‘MELB’)

可改编为:

SELECT * FROM EMP(基础表)
WHERE EMP_NO > 0
AND EXISTS(SELECT ‘X’ FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’

九. 用NOT EXISTS替代NOT IN

在子查询中,NOT IN字句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN,我们可以把它改写成外连接(Out Joins)或NOT EXISTS。

例:

SELECT …… FROM EMP
WHERE DEPT_NO NOT IN(SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT = ‘A’);

改编方法一:高效

SELECT …… FROM EMP A,DEPT B
WHERE A.DEPT_NO = B.DEPT_NO(+)
AND B.DEPT_CAT<>’A’;

改编方法二:最高效

SELECT …… FROM EMP E
WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’);

十.用表连接替换EXISTS

通常来说,采用表连接的方式比EXISTS更有效率

例:

SELECT ENAME
FROM EMP E
WHERE EXISTS(SELECT ‘X’
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’);

更高效的写法是:

SELECT ENAME
FROM DEPT D,EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND DEPT_CAT = ‘A’;

但是很多情况下我们无法将EXISTS改编为连接,如DEPT_NO不唯一。

十一.用EXISTS替换DISTINCT

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT字句中使用DISTINCT。一般可以考虑用EXIST替换。

EXISTS使用查询更为迅速,因为REBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。

例:找出有职员的部门

SELECT DISTINCT DEPT_NO,DEPT_NAME
FROM DEPT D,EMP E
WHERE D.DEPT_NO = E.DEPT_NO;

可改编为:

SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS(SELECT ‘X’
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);

十二.使用索引提高效率

索引是表的一个概念部门,用来提高检索数据的效率。通常,通过索引查询数据比全表扫描的要快。

同样在连结多个表时使用索引也可以提高效率。另一个使用索引的好处是,它提供了主键(primary Key)的唯一性验证。

使用索引时也必须注意到它的代价。索引需要空间存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会修改。这意味着每条记录的INSERT,DELECT,UPDATE将为此多付出4,5次的磁盘I/O。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。

ORACLE对索引有两种访问模式:

索引唯一扫描(INDEX UNIQUE SCAN)

索引范围查询(INDEX RANGE SCAN)

适用于两种情况:

1)  基于一个范围的检索

2)  基于非唯一性索引的检索

在前面已经讲了一些使用索引的情况,下面再补充几种:

当WHERE字句中有多个索引列,且包含非’=’号时,ORACLE会放弃使用非’=’号的索引:

例:

DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引

SELECT ENAME FROM EMP

WHERE DEPTNO > 20 AND EMP_CAT = ‘A’;

这里只有EMP_CAT索引被用到,然后所有的记录将逐条与DEPTNO条件进行比较,执行路径如下:

TABLE ACCESS BY ROWIDON EMP

INDEX RANGESCAN ON CAT_IDX

这是因为DEPTNO > 20的条件可能会检索出大量记录,而EMP_CAT = ‘A’可能只检索出少量记录。合并操作比如上面的方式划算。

当WHERE字句中有多个索引列,且都为非’=’号时,ORACLE将只使用一个索引:

例:

DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引。

SELECT ENAME FROM EMP

WHERE DEPTNO > 20 AND EMP_CAT > ‘A’;

这里,ORACLE只会用到其中一个索引(用哪个视优化模式,统计信息而定),执行路径如下(假使使用DEPTNO上的索引);

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON DEPT_IDX

 

十三.强制索引失效

如果两个或以上索引具有相同的等级,而我们只想使用其中的一个(通过它,检索出的记录数量少),我们可以使用下面的方法:

SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO = 10
AND EMP_TYPE = ‘A’;

如果我们只想用到EMPNO上的索引(相对另外两个条件记录数量较少,做合并不划算),则可改编为:

SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10   /*DEPTNO上的索引将不会使用*/

同样,如果我们想使用某个列上的索引,则不能对此列做运算,例:
 

SELECT ……
FROM DEPT
WHERE SAL * 12 > 25000;

不能使用SAL列上的索引,可改编为:

SELECT ……
FROM DEPT
WHERE SAL > 25000/12;

十四.用>=替代>

如果DEPTNO上有一个索引,则:

SELECT * FROM EMP
WHERE DEPTNO >= 4

比下面的语句更有效率:

SELECT * FROM EMP
WHERE DEPTNO > 3;

两者的区别在于,前者将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO = 3的记录并且想前扫描到第一个DEPT大于3的记录。

十五.用UNION替换OR

通常情况下,用UNION替换WHERE字句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意,以上规则只针对多个索引列有效,如果有列没有被索引,查询效率可能会因为没有选择OR而降低。

在下面的例子中,LOC_ID和REGION上都建有索引,则:

SELECT LOC_ID,LOC_DESC,REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT lOC_ID,LOC_DESC,REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”;

比下面的语句效率高:

SELECT LOC_ID,LOC_DESC,REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”;

十六.IN 和OR

下面两个查询等价:

SELECT ……
FROM LOCATION
WHERE LOC_ID = 10
OR LOC_ID = 20;

SELECT …… FROM LOCATION
WHERE LOC_IN  IN(10,20);

在9i后,ORACLE可以根据优化模式选择合并方式或直接IN检索方式。

十七.避免在索引列上使用IS NULL和IS NOT NULL

对于单列索引,如果列包含空值,索引中将不存在此记录。

对于复合索引,如果单个列都为空,索引中同样不存在此记录。如果至少有一个列不为空。则记录存在于索引中。

因为空值不存在于索引中,所以WHERE字句中对索引列进行空值比较将使ORACLE停用该索引。

下面的例子不使用索引:

SELECT ……
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;

可以改编如下(如果为字符,可根据情况写为>’a’);

SELECT ……
FROM DEPARTMENT
WHERE DEPT_CODE >= 0;

十八.用UNION-ALL替换UNION

当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序,并将重复记录过滤掉。

如果用UNION-ALL替代UNION,这样排序就不是必要的了,效率会因此得到提高。

需要注意的是,UNION-ALL将重复输出两个结果集合中相同记录。因此,还是要从业务需求分析使用UNION-ALL的可行性。

十九.其他需要注意的问题

‘!=’将不使用索引。索引只能告诉你什么存在于表中,而不能告诉你什么不存在于表中。

基于成本优化器(CBO,Cost-Based Optimizer)对索引的选择性进行判断来决定索引的使用是否能提高效率。

如果索引有很高的选择性,那就是说对于每个不重复的索引键值,只对应数量很少的记录。

比如,表中共有100条记录而其中有80个不重复的索引键值,这个索引的选择性就是80/100=0.8,选择性越高,通过索引键值检索出的记录就越少。

如果索引的选择性很低,检索数据就需要大量的索引范围查询操作和ROWID访问表的操作,也许会比全表扫描的效率更低。

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作,而其他的至少需要执行两次排序。

例如。一个UNION查询,其中每个查询都带有GROUP BY字句,GROUP BY会触发嵌入排序(NESTED SORT);这样,每个查询需要执行一次排序,然后在执行UNION时,有一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行。嵌入顺序的深度会大大影响查询的效率。

通常,带有UNION,MINUS,INTERSECT的SQL语句都可以用其他方式重写。

© 著作权归作者所有

共有 人打赏支持
孟飞阳
粉丝 202
博文 912
码字总数 535395
作品 5
朝阳
个人站长
Oracle SQL 性能优化技巧

1.选用适合的ORACLE优化器  ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZERMODE参数的各种声明,...

ghostwl
2014/12/19
0
0
Oracle数据库学习的福利来啦——最佳入门经验分享

适用人群:从事数据库技术实施或售后工程师,准备参加OCA,OCP认证考试 通过学习对Oracle数据库的相关基础知识进行梳理,最终共同提炼出必须最先掌握的那部分知识,无论你是数据库开发、管理、...

让往事随风
2016/04/13
34
0
Oracle性能优化之性能调整_超越OCP精通Oracle视频教程培训38

Oracle性能优化之性能调整超越OCP精通Oracle视频教程培训38 课程介绍 风哥Oracle视频教程<>的第8/10套:Oracle性能优化之性能调整。学习Oracle性能优化,性能调整必备知识讲解 ,操作系统优化调...

风哥Oracle
06/28
0
0
ORACLE SQL性能优化系列 (一) black_snai

ORACLE SQL性能优化系列 (一) 原创 2003年07月21日 18:34:00 标签: oracle / sql / 性能优化 / 优化 / 数据库 / statistics <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:......

rootliu
04/12
0
0
Oracle Table连接方式分析

表连接基本知识: 1、哪张表将驱动查询(即访问的第一张表)?按照指定的路径查询,何时将访问到没一张表?可选的驱动路径有哪些? 2、可能出现哪些Oracle连接?记住:在Oracle中,连接顺序、...

余二五
2017/11/16
0
0
使用智能优化器提高Oracle的性能极限

消耗在准备新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分。但是通过理解Oracle内部产生执行计划的机制,你能够控制Oracle花费在评估连接顺序的时间数量,并且能在大体上提高...

晨曦之光
2012/03/09
0
0
Oracle原厂老兵:从负面案例看Hint的最佳使用方式

作者介绍 罗敏,从事Oracle技术研究、开发和服务工作20余年,在Oracle中国公司的10多年,分别在顾问咨询部、技术服务部担任资深技术顾问。曾参与国内银行、电信、政府等多个行业大型IT系统的...

罗敏
2016/10/12
0
0
谈一个技术的问题:oracle中sql语句的优化

谈一个技术的问题:oracle中sql语句的优化 作为应用软件开发人员,不能不用到oracle数据库.但是大多数人只了解其中的皮毛,今天就发现公司内部大多老员工都还不了解oracle语句的执行顺序是从后往...

优惠券发放
04/28
0
0
Oracle执行计划不走索引的原因总结

在Oracle数据库操作中,为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢?如何解决呢?本文我们主要就介绍这部分内容,接下来就让我们一起来了解一下。 不走索引...

xiaoxin
2016/04/22
212
0
Oracle动态SQL和静态SQL比较

1.静态SQLSQL与动态SQL Oracle编译PL/SQL程序块分为两个种:其一为前期联编(early binding),即SQL语句在程序编译期间就已经确定,大多数的编译情况属于这种类型;另外一种是后期联编(l...

余二五
2017/11/09
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

NNS域名系统之域名竞拍

0x00 前言 其实在官方文档中已经对域名竞拍的过程有详细的描述,感兴趣的可以移步http://doc.neons.name/zh_CN/latest/nns_protocol.html#id30 此处查阅。 我这里主要对轻钱包开发中会用到的...

暖冰
今天
0
0
32.filter表案例 nat表应用 (iptables)

10.15 iptables filter表案例 10.16/10.17/10.18 iptables nat表应用 10.15 iptables filter表案例: ~1. 写一个具体的iptables小案例,需求是把80端口、22端口、21 端口放行。但是,22端口我...

王鑫linux
今天
0
0
shell中的函数&shell中的数组&告警系统需求分析

20.16/20.17 shell中的函数 20.18 shell中的数组 20.19 告警系统需求分析

影夜Linux
今天
0
0
Linux网络基础、Linux防火墙

Linux网络基础 ip addr 命令 :查看网口信息 ifconfig命令:查看网口信息,要比ip addr更明了一些 centos 7默认没安装ifconfig命令,可以使用yum install -y net-tools命令来安装。 ifconfig...

李超小牛子
今天
1
0
[机器学习]回归--Decision Tree Regression

CART决策树又称分类回归树,当数据集的因变量为连续性数值时,该树算法就是一个回归树,可以用叶节点观察的均值作为预测值;当数据集的因变量为离散型数值时,该树算法就是一个分类树,可以很...

wangxuwei
昨天
1
0
Redis做分布式无锁CAS的问题

因为Redis本身是单线程的,具备原子性,所以可以用来做分布式无锁的操作,但会有一点小问题。 public interface OrderService { public String getOrderNo();} public class OrderRe...

算法之名
昨天
10
0
143. Reorder List - LeetCode

Question 143. Reorder List Solution 题目大意:给一个链表,将这个列表分成前后两部分,后半部分反转,再将这两分链表的节点交替连接成一个新的链表 思路 :先将链表分成前后两部分,将后部...

yysue
昨天
1
0
数据结构与算法1

第一个代码,描述一个被称为BankAccount的类,该类模拟了银行中的账户操作。程序建立了一个开户金额,显示金额,存款,取款并显示余额。 主要的知识点联系为类的含义,构造函数,公有和私有。...

沉迷于编程的小菜菜
昨天
1
0
从为什么别的队伍总比你的快说起

在机场候检排队的时候,大多数情况下,别的队伍都要比自己所在的队伍快,并常常懊悔当初怎么没去那个队。 其实,最快的队伍只能有一个,而排队之前并不知道那个队快。所以,如果有六个队伍你...

我是菜鸟我骄傲
昨天
1
0
分布式事务常见的解决方案

随着互联网的发展,越来越多的多服务相互之间的调用,这时候就产生了一个问题,在单项目情况下很容易实现的事务控制(通过数据库的acid控制),变得不那么容易。 这时候就产生了多种方案: ...

小海bug
昨天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部