在Oracle优化中所用到的语句有哪些

我们今天是要和大家一起讨论的是Oracle优化的查询，我前两天在相关网站看见的资料，觉得挺好，就拿出来供大家分享。如果你对Oracle优化的查询，心存好奇的话，以下的文章将会揭开它的神秘面纱。

假设LARGE_TABLE是一个较大的表，且username列上没有索引，则运行下面的语句：

SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST';   
Query Plan   
SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)   
TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED]

在这个例子中，TABLE ACCESS FULL LARGE_TABLE是***个操作，意思是在LARGE_TABLE表上做全表扫描。当这个操作完成之后，产生的row source中的数据被送往下一步骤进行处理，在此例中，SELECT STATEMENT操作是这个查询语句的***一步。

Optimizer=CHOOSE 指明这个查询的optimizer_mode，即optimizer_mode初始化参数指定的值，它并不是指语句执行时真的使用了该优化器。决定该语句使用何种优化器的唯一方法是看后面的cost部分。例如，如果给出的是下面的形式，则表明使用的是CBO优化器，此处的cost表示Oracle优化器认为该执行计划的代价:

SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)

然而假如执行计划中给出的是类似下面的信息，则表明是使用RBO优化器，因为cost部分的值为空，或者压根就没有cost部分。

SELECT STATEMENT Optimizer=CHOOSE Cost=   
SELECT STATEMENT Optimizer=CHOOSE

这样我们从Optimizer后面的信息中可以得出执行该语句时到底用了什么样的Oracle优化器。特别的，如果Optimizer=ALL_ROWS| FIRST_ROWS| FIRST_ROWS_n，则使用的是CBO优化器;如果Optimizer=RULE，则使用的是RBO优化器。

cost属性的值是一个在Oracle内部用来比较各个执行计划所耗费的代价的值，从而使优化器可以选择***的执行计划。不同语句的cost值不具有可比性，只能对同一个语句的不同执行计划的cost值进行比较。

[:Q65001] 表明该部分查询是以并行方式运行的。里面的数据表示这个操作是由并行查询的一个slave进程处理的，以便该操作可以区别于串行执行的操作。

[ANALYZED] 表明操作中引用的对象被分析过了，在数据字典中有该对象的统计信息可以供CBO使用。

例2：

假定A、B、C都是不是小表，且在A表上一个组合索引：A(a.col1,a.col2) ，注意a.col1列为索引的引导列。考虑下面的查询：

select A.col4   
from A , B , C   
where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5   
Execution Plan   
SELECT STATEMENT Optimizer=CHOOSE   
MERGE JOIN   
SORT (JOIN)   
NESTED LOOPS   
TABLE ACCESS (FULL) OF 'B'   
TABLE ACCESS (BY INDEX ROWID) OF 'A'   
INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)   
SORT (JOIN)   
TABLE ACCESS (FULL) OF 'C'   
Statistics   
0 recursive calls   
8 db block gets   
6 consistent gets   
0 physical reads   
0 redo size   
551 　bytes sent via SQL*Net to client   
430 　bytes received via SQL*Net from client   
2 SQL*Net roundtrips to/from client   
2 sorts (memory)   
0 sorts (disk)   
6 rows processed

在表做连接时，只能2个表先做连接，然后将连接后的结果作为一个row source，与剩下的表做连接，在上面的例子中，连接顺序为B与A先连接，然后再与C连接：
B 　<—>　 A 　<—>　 C
col3=10 　col3=5

如果没有执行计划，分析一下，上面的3个表应该拿哪一个作为***个驱动表？从SQL语句看来，只有B表与C表上有限制条件，所以***个驱动表应该为这2个表中的一个，到底是哪一个呢？

B表有谓词B.col3 = 10，这样在对B表做全表扫描的时候就将where子句中的限制条件(B.col3 = 10)用上，从而得到一个较小的row source, 所以B表应该作为***个驱动表。而且这样的话，如果再与A表做关联，可以有效利用A表的索引(因为A表的col1列为leading column)。

当然上面的查询中C表上也有谓词(C.col3 = 5)，有人可能认为C表作为***个驱动表也能获得较好的性能。让我们再来分析一下：如果C表作为***个驱动表，则能保证驱动表生成很小的row source，但是看看连接条件A.col2 = C.col2，此时就没有机会利用A表的索引，因为A表的col2列不为leading column，这样nested loop的效率很差，从而导致查询的效率很差。所以对于NL连接选择正确的驱动表很重要。

因此上面查询比较好的连接顺序为(B – – > A) – – > C。如果数据库是基于代价的Oracle优化器，它会利用计算出的代价来决定合适的驱动表与合适的连接顺序。一般来说，CBO都会选择正确的连接顺序，如果CBO选择了比较差的连接顺序，我们还可以使用Oracle提供的hints来让CBO采用正确的连接顺序。如下所示：

select /*+ ordered */ A.col4   
from B,A,C   
where B.col3 = 10   
and A.col1 = B.col1   
and A.col2 = C.col2   
and C.col3 = 5

既然选择正确的驱动表这么重要，那么让我们来看一下执行计划，到底各个表之间是如何关联的，从而得到执行计划中哪个表应该为驱动表：在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处。

判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中得来得，则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作先执行。具体解释如下：

得到去除妨碍判断的索引扫描后的执行计划：

Execution Plan  
SELECT STATEMENT Optimizer=CHOOSE   
MERGE JOIN   
SORT (JOIN)   
NESTED LOOPS   
TABLE ACCESS (FULL) OF 'B'   
TABLE ACCESS (BY INDEX ROWID) OF 'A'   
SORT (JOIN)   
TABLE ACCESS (FULL) OF 'C'

看执行计划的第3列，即字母部分，每列值的左面有空格作为缩进字符。在该列值左边的空格越多，说明该列值的缩进越多，该列值也越靠右。如上面的执行计划所示：***列值为6的行的缩进最多，即该行最靠右;***列值为4、5的行的缩进一样，其靠右的程度也一样，但是***列值为4的行比***列值为5的行靠上;谈论上下关系时，只对连续的、缩进一致的行有效。

从这个图中我们可以看到，对于NESTED LOOPS部分，最右、最上的操作是TABLE ACCESS (FULL) OF ‘B’，所以这一操作先执行，所以该操作对应的B表为***个驱动表(外部表)，自然，A表就为内部表了。

从图中还可以看出，B与A表做嵌套循环后生成了新的row source ，对该row source进行来排序后，与C表对应的排序了的row source(应用了C.col3 = 5限制条件)进行MSJ连接操作。所以从上面可以得出如下事实：B表先与A表做嵌套循环，然后将生成的row source与C表做排序—合并连接。

上述的相关内容就是对Oracle优化查询的描述，希望会给你带来一些帮助在此方面。

文章出自:http://www.programbbs.com/doc/5047.htm

【编辑推荐】