基础：如何编写优化的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 Card=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’andcity=’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说明：它通过合并每个表中已排序的记录列表去联结数据表。

它主要是针对大型批处理操作，但是对事务处理可能是无效的。

当ORACLE实施联结而又不能使用索引时，就使用merge join。

例子：select from company ,sales where pany_id+0=pany_id+0 and sales.period_id=3 and sales.sales_total>1000注意：需要值得注意的是它是针对集操作。

对于它的执行计划，在执行完一个全表扫描之后还要进行一个sort join的排序操作，然后再进行merge join操作。

2.2.11.NESTED LOOPS说明：在进行多表的联结时，如果有一个联结的列被索引过的时候，那么nested loops将起作用。

例子：select from company ,sales where pany_id=pany_id and sales.period_id=3 and sales.sales_total>1000注意：●在使用nested loops的时候，查询的驱动表的选择是很重要的，关于这点，这下面会作专题讨论。

原则是如果是rule_based的情况下，驱动表将选择from 语句的最后一个，如果是cost_based的情况下，驱动表的选择间考虑表的大小和索引的选择性。

●在进行nested loops的操作时，首先作的是驱动表的全表扫描，然后才使用索引的扫描，然后才进行nested loops操作。

2.2.12.OUTER JOIN说明：它是nested loops、hash join、merge join操作的一个选项。

它使来自驱动表的行能够返回到调用查询，虽然在联结数据表里没有发现任何匹配的行。

例子：select from company ,sales where pany_id=pany_id(+) and sales.period_id=3 and sales.sales_total>10002.2.13.SEQUENCE说明：当通过nextval和currval来访问sequence时，使用sequence操作。

例子：select seq_tbi_tm.nextval from dual;2.2.14.SORT AGGREGATE说明：每当对数据集的组操作功能在sql语句中出现但是没有group by子句时，SORT AGGREGATE（聚集排序）就用来给结果排序和聚集。

这些组函数包括：max、min、count、sum、avg例子：select sum(sales_total) from sales;2.2.15.SORT GROUP BY说明：在数据集中实现分组功能例子：select zip,count(*)from company group by zip;2.2.16.SORT JOIN说明：把用于MERGE JOIN操作的一个记录集排序，同merge join是同步出现的。

例子：select from company ,sales where pany_id+0=pany_id+0 and sales.period_id=3 and sales.sales_total>10002.2.17.SORT ORDER BY说明：它用于给结果集排序，但是不去除重复记录。

例子：select name from company order by name;2.2.18.SORT UNIQUE说明：在由minus、intersection和union操作处理前，它用于给结果集排序并将重复记录去除。

例子：select company_id from company minus select company_id from compeitor; 2.2.19.TABLE ACCESS BY ROWID说明：基于所提供Rowid的操作，它从一个表返回一条记录。