基础：如何编写优化的sql语句

一.SQL的优化器执行分析

在ORACLE RDBMS SERVER软件的内部，对于SQL语句的执行有一个优化器（OPTIMIZER）对SQL语句的执行进行优化。在我们使用后面介绍的工具对SQL的执行路径进行查看的时候，系统显示出来的是由优化器给出的执行路径的解释方案，如果对优化器的解释方案不了解的话，就无法针对出现的问题进行SQL语句的调整。现把ORACLE8提供的优化器的执行解释方案公布如下。

这部分内容的详细解释可以参照oracle的文档。这里给出的是快速参考。实际上，这些操作的含义往往名字上就可以表现出来，不用查手册。

1.如何看SQL解释方案

Execution Plan

----------------------------------------------------------

1SELECT STATEMENT Optimizer=CHOOSE (Cost=94 Card=1)

2SORT (AGGREGATE)

3COUNT (STOPKEY)

4INDEX (FULL SCAN) OF 'PK_TBI_TM' (UNIQUE) (Cost=94 Car

d=27164)

（图1）

图1为ORACLE对语句“select count(*) from tbi_tm where rownum<10”给出的一个执行的解释方案，那我们该如何看这个方案呢？我们在看这个执行方案的时候要遵循一个原则“由里到外，由高到低”，同时“由里到外”不能违反“由高到低”的原则。因此上述的语句的执行步骤顺序为：4、3、2；假设存在一个步骤5的话，而且它的层次同3一致，那么，上述语句的执行步骤顺序就会改为：4、3、5、2。在图1中还给出了执行了何种操作（例如：INDEX（FULL SCAN），COUNT (STOPKEY)等，具体的说明在下面进行解释），同时在最后还给出了执

行的代价（COST）。

2.SQL解释方案介绍

2.1. 操作说明

系统中的全部操作可分为行操作或集（SET）操作。二者之间的比较可以对比如下：

对于行和集操作的分类如下(暂时列到ORACLE 8)：

2.2. 具体操作解释

在上述的操作中有许多我们平时很少用到，因此就不一一介绍了，只介绍日

常常用的一些操作：

2.2.1.约定

2.2.2.AND-EQUAL

说明：用来合并由索引返回的值的排序列表。AND-EQUAL用于非唯一索引的合并和唯一索引的范围扫描。

例子：select name,city,state from company where city=’Roanoke’ and state=’V A’2.2.3.CONCATENATION

说明：用来执行结果集的union all操作

例子：select name,city,state from company where state=’TX’and city in (‘Houston’,’Austin’,’Dallas’);

该例子最终可以该写成：select name ,city,state from company where (state=’TX’and city=’Houston’) or (state=’TX’and city=’Austin’) or (state=’TX’and

city=’Dallas’);

备注：有时候在语句比较复杂时，Oracle可能不会使用CONCATENATION操作，而是使用部分索引的范围扫描（Range Scan）。如果要强制使用CONCATENATION操作，就把语句写成最终的样子。

2.2.4.COUNT

说明：当使用伪列（Pseudo-column），并且指定RowNum的最大值时，执行Count （计数）。Count从它的子操作接收行并且增大RowNum的计数。

例子：select name,state,rownum from company where city>’Roanoke’

2.2.5.COUNT STOPKEY

说明：当使用限定计数的RowNum伪列时，执行COUNT STOPKEY。它从先前的操作接收行并且增加计数，如果计数达到了临界值，就产生一个“No More Rows”条件，结束查询并把结果返回给用户。

例子：select name,city,state,rownum from company where city>’Roanoke’and rownum<10;

2.2.6.FILETER

说明：使用在当没有索引可以用来帮助评估时，FILTER执行一个where子句条件。当FILTER在一个解释方案里显示时，通常显示的是索引丢失或者存在的索引不能用的结果。

2.2.7.FOR UPDATE

说明：为所有能从select语句回复的行级别（row level）上加锁。

例子：select name from company where city>’Roanoke’ for update of name;

说明：它是行操作和集操作的混合。它在内存中创建其中一个表的位图，然后利用哈希（HASH）功能在第二个表里定位联结行，通过HASH JOIN（哈希联结）将表联结起来。

例子：select from company,sales where pany_id=pany_id and sales.period_id=3 and sales.sales_total>1000;

注意：在这个例子中，sales表作为联结表被读到内存中，然后同company表中的记录进行逐行比较。能够使用到hash join的条件为在联结的表中，一张表比其他的联结表小的多，并且这些记录能够全部被读到内存中去，那么系统就会使用hash join而不是使用nested loops来进行连接。有时即使为联结提供了一个索引，hash join也许比nested loops联结跟可取。

注：并不一定要求能够全部读到内存中，有些情况下，Oracle也可能把一部分hash块放到临时空间中。

2.2.9.INDEX RANGE SCAN

说明：它从索引里选择一定范围的值，索引既可以是唯一的也可以是复合的。当遇到下列条件时，使用它：

●使用范围操作（如>or<）

●使用between子句

●使用有通配符的查找字符串（如：’b%’）

●只使用复合索引的一部分

例子：select name,city,state from company where city>’Roanoke’

注意：它的效率与两个因素有关：选择范围内的关键值的数目以及索引的条件，当关键数目越多，查找时间越长；分段越多，查找时间越长。

INDEX UNIQUE SCAN

说明：它从唯一索引中选择，是从已知字段里选择一行的最有效的方法。

例子：select name,city,state from company where company_id=12345