Oracle SQL查询语句的优化.doc

科技论文Oracle SQL查询语句的优化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系统中的优化原理优化通常有两方面的内容，即逻辑优化和物理优化。逻辑优化包括：l 选择运算尽早执行l 投影与选择运算同时进行，以避免重复扫描文件l 公共子表达式预处理l 谓词的简化处理l 表达式的恒等变换等物理优化包括：l 选择有效的存取路径l 选择合适的操作序列l 提供较优的操作实现方法逻辑优化往往是一种等价变换，它的优化会对查询带来绝对好处，这部分优化与用户无关，完全由优化器负责，故我们在下面中不关心这部分优化。物理优化则往往是对动态情况的一种权衡。物理优化目前采用的方法有：启发式的、基于统计信息的。智能式的ORACLE的优化器是启发式的，它的核心是一些从经验中得到的准则，它的好处是系统代价小，但不利的方面是用户的负担太重。一、单表查询的优化ORACLE中实现单表查询的途径有三种：l 使用ROWID（ACCESS BY ROWID）l 索引扫描（INDEX SCAN）l 全表扫描（ACCESS FULL TABLE）单表查询优化的目的在于如何选择一个最佳存取路径，显然使用ROWID的查询效果最高，索引扫描次之，全表扫描效率最低。在一个单表查询中，若上述三种方法都可用，ORACLE显然要选择使用ROWID的方法，本文要介绍的是如何在多个索引中选择一个最佳的。在一个查询中，若有多个索引可用，ORACLE优化器做如下工作：1. 选择可利用的索引，即判别索引与谓词的相容性；2. 选择查询的驱动索引，若有唯一索引出现，则以此索引为入口，其他索引不用。若有多个非唯一索引，且查询谓词为相等谓词，则采用索引合并的算法。索引与谓词有如下的优化关系：(1) 唯一索引列常数(2) 非唯一索引列常数(3) 索引列常数出现(1)时，(3)中的索引不用；出现(2)时，(3)的索引不用。索引合并的处理方法只用在表中有多个索引上，而且最多合并索引数目不超过五个。当且仅当只有(3)情况下的索引时，优化器任选其一使用。比索引合并更有效的方法是建立组合索引，组合索引有两种使用方式：全部说明方式和部分说明方式。所谓全部说明方式就是组合索引列全部出现在查询中，部分说明方式就是组合索引列前面一部分出现在查询中。例如，在表EMP上建有组合索引：Create index I_emp$dno_sal_mgrOn emp(deptno,sal,mgr)以及单索引：I_emp$dno，I_emp$mgr，I_emp$sal。有如下查询：Select *From empWhere deptno=20and sal2000and mgr=7902；此时优化器选组合索引I_emp$dno_sal_mgr进行索引扫描，而不会用索引I_emp$dno等进行索引合并。这里组合索引的使用即是采用全部说明方式。再看如下的查询：Select *From empWhere deptno=20and sal2000；此时优化器同样使用索引I_emp$dno_sal_mgr，但这里是采用部分说明方式。但对查询：Select *From empWhere deptno=20and mgr=7902；优化器选用I_emp$dno,I_emp$mgr进行索引合并，而不能使用组合索引I_emp$dno_sal_mgr，因为deptno，mgr不是组合索引列deptno，sal，mgr的前部。搞清优化器是如何使用索引的，就能有针对性地建立索引，从而受到更好的效果。ORACLE的索引中是不存空值的，因此对空值的查询不能用索引。二、多表连接的优化处理多表连接的基础是两表连接，连接优化的主要工作有：1、有关连接方式的选择ORACLE中所采用的连接方式基本上是合并排序法，对有索引和无索引在处理上略有不同。若连接属性上都建有索引，则可利用索引已有的排序作合并连接。但在连接属性上没有索引时，则要首先对两表在连接属性上排序，对排序结果再作连接。如图一。图一2、有关连接次序的优化驱动表的选择 ORACLE在驱动表的选择上，主要依据的是一张从经验中得出的查询路径计分表（表二）。得分条件1ROWID=常数2唯一索引列=常数3全体唯一组合索引码=常数4全体聚簇码=同一簇中另一表中相对应聚簇码5全体聚簇码=常数6全体非唯一组合索引码=常数7非唯一索引=常数8全体非压缩组合索引=下界9全体压缩组合索引=下界10限定了非压缩索引的多数字段11限定了压缩索引的多数字段12唯一索引列BETWEEN低值AND高值或唯一索引列LIKE c%(限定范围)13非唯一索引列BETWEEN低值AND高值或唯一索引列LIKE c%(限定范围)14唯一索引列常数或唯一索引列常数或非唯一索引列utlxplan即可在该用户下创建plan_table表。 可以编一个如下的SQL脚本文件XPLAIN.SQL，用于按一定层次结构显示plan_table中的查询计划: Set verify off Select lpad( ,2*(level-1) |level|. |position| |operation| |options| |object_name| |object_type query_plan from plan_table Connect by prior id=parent_id and statement_id=&1 start with id=1 and statement_id=&1 / Set verify on 现举例说明： 例1： SQL EXPLAIN PLAN SET STATEMENT_ID=ED1 2 FOR SELECT * 3 FROM EMP,DEPT 4 WHERE EMP.DEPTNO=DEPT.DEPTNO; SQL XPLAIN ED1 QUERY_PAN - 1.1 NESTED LOOPS 2.1 TABLE ACCESS FULL EMP 2.2 TABLE ACCESS BY ROWID DEPT3.1 INDEX UNIQUE SCAN DEPT_PRIMARY_KEY UNIQUE 从这可以了解到ORACLE执行上述SELECT语句的基本步骤。1.1说明的是EMP，DEPT连接采用的嵌套循环方式，2.1与2.2是嵌套循环的外层与内层，2.1说明按全表扫描方式读取表EMP，2.2说明按ROWID读取表DEPT，3.1是对2.2的进一步说明，指明2.2中的ROWID是由3.1中唯一索引DEPT_PRIMARY_KEY得到的。 从执行计划中，我们可以得到如下信息： 连接的算法：嵌套、合并等； 连接的次序：哪个为驱动表，哪个为被驱动表； 扫描方式：全表扫描、索引扫描、ROWID。范例所用的表和索引 为了使用后面的范例，需要使用一些表，我们以电信综合管理系统（“九七”工程）中所使用的表为例。我们使用了其中的三张表：ACC_NBR、SO_TYPE、SO，它们的表结构如下： SQL DESC ACC_NBR 列名 可空值否 类型 - - - ACC_NUMBER NOT NULL VARCHAR2(15) EXCH_ID NUMBER(3) SWT_ID NUMBER(3) STS NOT NULL CHAR(1) STS_DATE NOT NULL DATE CLASS NOT NULL CHAR(1) NBR_TYPE NOT NULL CHAR(1) SQL DESC SO_TYPE 列名 可空值否 类型 - - - SO_TYPE_ID NOT NULL NUMBER(3) TYPE_NAME NOT NULL VARCHAR2(20) VISIBLE NOT NULL CHAR(1) REMARKS VARCHAR2(60) SQL DESC SO 列名 可空值否 类型 - - - SO_NBR NOT NULL VARCHAR2(13) DISCOUNT_TYPE_ID NUMBER(3) APPL_DATE NOT NULL DATE CONTACT_DETAIL VARCHAR2(60) CONTACT_NAME VARCHAR2(20) HALT NOT NULL CHAR(1) HALT_DATE NOT NULL DATE PRIORITY CHAR(1) REQ_DATE DATE REQ_START_DATE DATE SERV_ID NUMBER(8) SO_TYPE_ID NOT NULL NUMBER(3) STATE NOT NULL NUMBER(3) STATE_DATE NOT NULL DATE SO_PAY_METH CHAR(1) SERV_TYPE_ID NOT NULL NUMBER(3) AMORT NUMBER(2) REMARKS VARCHAR2(60) SO_RELATED VARCHAR2(13) BILL_TYPE_ID NUMBER(3) 在表ACC_NBR上有基于Acc_Number的唯一索引SYS_C00477, 在表SO_TYPE上有基于So_Type_ID的唯一索引XPKSO_TYPE， 在表SO上有基于So_Type_ID的非唯一索引IDX_SO_SO_TYPE。范例与分析 类型一致与不一致的查询。 例2：类型一致的查询 SQL EXPLAIN PLAN SET STATEMENT_ID=ED2 2 FOR SELECT * 3 FROM ACC_NBR 4 WHERE ACC_NUMBER=8820099; SQL XPLAIN ED2 QUERY_PAN - 1.1 TABLE ACCESS BY ROWID ACC_NBR2.1 INDEX UNIQUE SCAN SYS_C00477 UNIQUE 例3：类型不一致的查询： SQL EXPLAIN PLAN SET STATEMENT_ID=ED3 2 FOR SELECT * 3 FROM ACC_NBR 4 WHERE ACC_NUMBER=8820099; SQL XPLAIN ED3 QUERY_PAN -1.1 TABLE ACCESS FULL ACC_NBR 从例2可以看出在类型一致的情况下查询，ORACLE的优化器会使用唯一索引SYS_C00477，而例3中在类型不一致的情况下查询，ORACLE的优化器不会使用到该索引，而采用全表扫描的方式来执行。 不等条件的查询 例4：不等条件的查询 SQL EXPLAIN PLAN SET STATEMENT_ID=ED4 2 FOR SELECT * 3 FROM ACC_NBR 4 WHERE ACC_NUMBER!=8820099; SQL XPLAIN ED4 QUERY_PAN - 1.1 TABLE ACCESS FULL ACC_NBR例5：不等条件的查询（2） SQL EXPLAIN PLAN SET STATEMENT_ID=ED5 2 FOR SELECT * 3 FROM ACC_NBR 4 WHERE ACC_NUMBER8820099 OR ACC_NUMBER XPLAIN ED5 QUERY_PAN - 1.1 CONCATENATION 2.1 TABLE ACCESS BY ROWID ACC_NBR 3.1 INDEX RANGE SCAN SYS_C00477 UNIQUE 2.2 TABLE ACCESS BY ROWID ACC_NBR 3.1 INDEX RANGE SCAN SYS_C00477 UNIQUE 从例4与例5中可以看出，对于不等查询的两种等价的不同形式，ORACLE优化器的执行计划是不一样的，例4中采用的是全表扫描的方式，而例5则利用了唯一索引SYS_C00477。具体采用哪一种方式则要根据表中的记录情况来确定，若ACC_NBR表中ACC_NUMBER!=8820099的记录数占表的总记录数的比例很小时采用例5的方式使用索引则速度较快，若该比例值较大，则使用索引变得没有什么影响。希望能强制使用索引时，可以把查询条件改写成例5的形式。 IS NULL与IS NOT NULL的查询 例6：IS NULL的查询 SQL EXPLAIN PLAN SET STATEMENT_ID=ED6 2 FOR SELECT * 3 FROM ACC_NBR 4 WHERE ACC_NUMBER IS NULL; SQL XPLAIN ED6 QUERY_PAN -1.1 TABLE ACCESS FULL ACC_NBR ORACLE在其索引结构中不存空值，因此对IS NULL条件的查询采用的是全表扫描的方式，而不可能使用索引，且对这种条件的查询不存在改写形式。但是对IS NOT NULL的条件则可巧施变换。 例7：IS NOT NULL的查询 SQL EXPLAIN PLAN SET STATEMENT_ID=ED7 2 FOR SELECT * 3 FROM ACC_NBR 4 WHERE ACC_NUMBER IS NOT NULL; SQL XPLAIN ED7 QUERY_PAN - 1.1 TABLE ACCESS FULL ACC_NBR 例8：IS NOT NULL的查询（2） SQL EXPLAIN PLAN SET STATEMENT_ID=ED8 2 FOR SELECT * 3 FROM ACC_NBR 4 WHERE ACC_NUMBER ; SQL XPLAIN ED8 QUERY_PAN - 1.1 TABLE ACCESS BY ROWID ACC_NBR 2.1 INDEX RANGE SCAN SYS_C00477 UNIQUE 在例7中直接使用IS NOT NULL时，ORACLE的执行计划采用的是全表扫描，而采用例8的等价写法时ORACLE则使用上索引SYS_C00477，一般地对于IS NOT NULL的条件可以有如下的等价写法： 对字符型的列用 列名 （空格串）； 对数值型的列用 列名0。 连接查询 例9：显示所有的申请单号和相应的申请类型 SQL EXPLAIN PLAN SET STATEMENT_ID=ED9 2 FOR SELECT SO_TYPE.TYPE_NAME,SO.SO_NBR 3 FROM SO,SO_TYPE 4 WHERE SO_TYPE.SO_TYPE_ID=SO.SO_TYPE_ID; SQL XPLAIN ED9 QUERY_PAN - 1.1 NESTED LOOPS 2.1 TABLE ACCESS FULL SO_TYPE 2.2 TABLE ACCESS BY ROWID SO 3.1 INDEX RANGE SCAN IDX_SO_SO_TYPE NON-UNIQUE 例10：显示所有的申请单号和相应的申请类型(2) SQL EXPLAIN PLAN SET STATEMENT_ID=ED10 2 FOR SELECT SO_TYPE.TYPE_NAME,SO.SO_NBR 3 FROM SO_TYPE,SO 4 WHERE SO_TYPE.SO_TYPE_ID=SO.SO_TYPE_ID; SQL XPLAIN ED10 QUERY_PAN - 1.1 NESTED LOOPS 2.1 TABLE ACCESS FULL SO 2.2 TABLE ACCESS BY ROWID SO_TYPE 3.1 INDEX UNIQUE SCAN XPKSO_TYPE UNIQUE 对例9与例10中，由于要查询所有的申请单，必然有一张表是要进行全表扫描的，同时这两张表都可利用到索引，这样选择哪张表为驱动表就是一个重要的关键，对ORACLE来说，优化器选择FROM子句右端的表为驱动表，对用户来说，要根据两张表记录的多寡来确定以哪张表为驱动表，将记录少的表写在FROM子句的右端。表SO_TYPE的记录数远少于SO表，因而例9的效果好于例10。 例11：显示申请类型为1（新装）的申请单和申请类型 SQL EXPLAIN PLAN SET STATEMENT_ID=ED11 2 FOR SELECT SO_TYPE.TYPE_NAME,SO.SO_NBR 3 FROM SO_TYPE,SO 4 WHERE SO_TYPE.SO_TYPE_ID=SO.SO_TYPE_ID 5 AND SO.SO_TYPE_ID=1; SQL XPLAIN ED11 QUERY_PAN - 1.1 NESTED LOOPS 2.1 TABLE ACCESS BY ROWID SO 3.1 INDEX RANGE SCAN IDX_SO_SO_TYPE NON-UNIQUE 2.2 TABLE ACCESS BY ROWID SO_TYPE 3.1 INDEX UNIQUE SCAN XPKSO_TYPE UNIQUE 例12：显示申请类型为1（新装）的申请单和申请类型（2） SQL EXPLAIN PLAN SET STATEMENT_ID=ED12 2 FOR SELECT SO_TYPE.TYPE_NAME,SO.SO_NBR 3 FROM SO,SO_TYPE 4 WHERE SO_TYPE.SO_TYPE_ID=SO.SO_TYPE_ID 5 AND SO.SO_TYPE_ID=1; SQL XPLAIN ED12 QUERY_PAN - 1.1 NESTED LOOPS 2.1 TABLE ACCESS BY ROWID SO 3.1 INDEX RANGE SCAN IDX_SO_SO_TYPE NON-UNIQUE 2.2 TABLE ACCESS BY ROWID SO_TYPE 3.1 INDEX UNIQUE SCAN XPKSO_TYPE UNIQUE 例13：显示申请类型为1（新装）的申请单和申请类型（3） SQL EXPLAIN PLAN SET STATEMENT_ID=ED13 2 FOR SELECT SO_TYPE.TYPE_NAME,SO.SO_NBR 3 FROM SO,SO_TYPE 4 WHERE SO_TYPE.SO_TYPE_ID=SO.SO_TYPE_ID 5 AND SO.SO_TYPE_ID=1 6 AND SO_TYPE.SO_TYPE_ID=1; SQL XPLAIN ED13 QUERY_PAN - 1.1 NESTED LOOPS 2.1 TABLE ACCESS BY ROWID SO_TYPE 3.1 INDEX UNIQUE SCAN XPKSO_TYPE UNIQUE 2.2 TABLE ACCESS BY ROWID SO 3.1 INDEX RANGE SCAN IDX_SO_SO_TYPE NON-UNIQUE 例14：显示申请类型为1（新装）的申请单和申请类型（4） SQL EXPLAIN PLAN SET STATEMENT_ID=ED14 2 FOR SELECT SO_TYPE.TYPE_NAME,SO.SO_NBR 3 FROM SO_TYPE,SO 4 WHERE SO_TYPE.SO_TYPE_ID=SO.SO_TYPE_ID 5 AND SO.SO_TYPE_ID=1 6 AND SO_TYPE.SO_TYPE_ID=1; SQL XPLAIN ED14 QUERY_PAN - 1.1 NESTED LOOPS 2.1 TABLE ACCESS BY ROWID SO_TYPE 3.1 INDEX UNIQUE SCAN XPKSO_TYPE UNIQUE 2.2 TABLE ACCESS BY ROWID SO 3.1 INDEX RANGE SCAN IDX_SO_SO_TYPE NON-UNIQUE 例15：显示申请类型为1（新装）的申请单和申请类型（5） SQL EXPLAIN PLAN SET STATEM