Oracle SQL查询语句的优化.docVIP

科技论文 Oracle SQL查询语句的优化 企业信息化部 Page PAGE 2 of NUMPAGES 14 Oracle SQL查询语句的优化 作为关系数据库管理系统，要解决的一个重要问题就是如何提高查询的效率，即所谓的查询优化。为什么会出现这个问题呢？我们知道SQL语言是一种非过程化的语言，即它只要用户指出“做什么”，而不需指出“如何做”，这样对用户来说确实方便了，但系统的负担就重了。系统要完成“如何做”，就必然有个选择、比较、权衡的过程，即如何选择最佳的存取途径和实现算法。比如有如下的SQL语句： Select ename,dname from dept,emp; 这是一个简单的连接操作，假设其中表DEPT中有5条记录，表EMP中有14条纪录，若采用嵌套循环的方法实现连接，则有两种选择：一是以DEPT为连接外表，以EMP为内表；另一是以EMP为连接外表，DEPT为内表。两种情况下的循环次数分别为：5+5*14=75，14+14*5=84，显然前一种方案较好。 查询优化对提高查询效率是至关重要的，在任何一个商品化的RDBMS中，都必须有一个专门负责查询语句优化的程序，称为优化器。优化器的好坏直接影响到系统的性能。 优化器做的越完善，它的优化选择就越准确，它的优化效果就越好，对用户来说就越轻松，但系统的优化开销就越大；反之，优化器做的越简单，它的优化效果越差，用户相对来说就要多考虑一些，但系统的优化开销就小。为此商品化RDBMS的优化器，在尽量提高优化效果的同时，还要考虑不能过多地增加系统开销，否则适得其反。相对来说，ORACLE系统中的优化器做的比较简单，因此这就要求用户要有较强的优化意识。程序员要想获得较优的查询性能，就必须对表的大小、索引的选择率以及更新和存取操作的频度等统计信息了如指掌。 下面我们先介绍一下ORACLE系统的优化原理，然后在此基础上介绍一些优化经验。 ORACLE系统中的优化原理 优化通常有两方面的内容，即逻辑优化和物理优化。 逻辑优化包括： 选择运算尽早执行 投影与选择运算同时进行，以避免重复扫描文件 公共子表达式预处理 谓词的简化处理 表达式的恒等变换等 物理优化包括： 选择有效的存取路径 选择合适的操作序列 提供较优的操作实现方法 逻辑优化往往是一种等价变换，它的优化会对查询带来绝对好处，这部分优化与用户无关，完全由优化器负责，故我们在下面中不关心这部分优化。 物理优化则往往是对动态情况的一种权衡。物理优化目前采用的方法有：启发式的、基于统计信息的。智能式的ORACLE的优化器是启发式的，它的核心是一些从经验中得到的准则，它的好处是系统代价小，但不利的方面是用户的负担太重。 一、单表查询的优化 ORACLE中实现单表查询的途径有三种： 使用ROWID（ACCESS BY ROWID） 索引扫描（INDEX SCAN） 全表扫描（ACCESS FULL TABLE） 单表查询优化的目的在于如何选择一个最佳存取路径，显然使用ROWID的查询效果最高，索引扫描次之，全表扫描效率最低。在一个单表查询中，若上述三种方法都可用，ORACLE显然要选择使用ROWID的方法，本文要介绍的是如何在多个索引中选择一个最佳的。 在一个查询中，若有多个索引可用，ORACLE优化器做如下工作： 选择可利用的索引，即判别索引与谓词的相容性； 选择查询的驱动索引，若有唯一索引出现，则以此索引为入口，其他索引不用。若有多个非唯一索引，且查询谓词为相等谓词，则采用索引合并的算法。 索引与谓词有如下的优化关系： 唯一索引列＝常数 非唯一索引列＝常数 索引列常数 出现(1)时，(3)中的索引不用；出现(2)时，(3)的索引不用。 索引合并的处理方法只用在表中有多个索引上，而且最多合并索引数目不超过五个。当且仅当只有(3)情况下的索引时，优化器任选其一使用。 比索引合并更有效的方法是建立组合索引，组合索引有两种使用方式：全部说明方式和部分说明方式。所谓全部说明方式就是组合索引列全部出现在查询中，部分说明方式就是组合索引列前面一部分出现在查询中。 例如，在表EMP上建有组合索引： Create index I_emp$dno_sal_mgr On emp(deptno,sal,mgr) 以及单索引： I_emp$dno，I_emp$mgr，I_emp$sal。 有如下查询： Select * From emp Where deptno=20 and sal2000 and mgr=7902； 此时优化器选组合索引I_emp$dno_sal_mgr进行索引扫描，而不会用索引I_emp$dno…等进行索引合并。这里组合索引的使用即是采用全部说明方式。再看如下的查询： Select *