ORACLE的执行计划

ゞ浴缸里的玫瑰 2022-08-25 01:59 208阅读 0赞

**背景知识：**  
 为了更好的进行下面的内容我们必须了解一些概念性的术语：  
共享sql语句  
  
为了不重复解析相同的SQL语句(因为解析操作比较费资源，会导致性能下降)，在第一次解析之后，ORACLE将SQL语句及解析后得到的执行计划存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果该语句和之前的执行过的某一语句完全相同，并且之前执行的该语句与其执行计划仍然在内存中存在，则ORACLE就不需要再进行分析，直接得到该语句的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并大大节省了内存的使用。使用这个功能的关键是将执行过的语句尽可能放到内存中，所以这要求有大的共享池(通过设置shared buffer pool参数值)和尽可能的使用绑定变量的方法执行SQL语句。  
  
当你向ORACLE 提交一个SQL语句，ORACLE会首先在共享内存中查找是否有相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格,换行等)。  
  
下面是判断SQL语句是否与共享内存中某一SQL相同的步骤：  
1). 对所发出语句的文本串进行hashed。如果hash值与已在共享池中SQL语句的hash值相同，则进行第2步：  
2) 将所发出语句的文本串（包括大小写、空白和注释）与在第１步中识别的所有  
已存在的SQL语句相比较。  
例如：  
SELECT \* FROM emp WHERE empno = 1000;  
和下列每一个都不同  
SELECT \* from emp WHERE empno = 1000;  
SELECT \* FROM EMP WHERE empno = 1000;  
SELECT \* FROM emp WHERE empno = 2000;  
在上面的语句中列值都是直接SQL语句中的，今后我们将这类sql成为硬编码SQL  
或字面值SQL  
  
使用绑定变量的SQL语句中必须使用相同的名字的绑定变量(bind variables) ，  
例如：  
a. 该2个sql语句被认为相同  
select pin , name from people where pin = :blk1.pin;  
select pin , name from people where pin = :blk1.pin;  
b. 该2个sql语句被认为不相同  
select pin , name from people where pin = :blk1.ot\_ind;  
select pin , name from people where pin = :blk1.ov\_ind;  
今后我们将上面的这类语句称为绑定变量SQL。  
  
3). 将所发出语句中涉及的对象与第２步中识别的已存在语句所涉及对象相比较。  
例如:  
如用户user1与用户user2下都有EMP表，则  
用户user1发出的语句：SELECT \* FROM EMP; 与  
用户user2发出的语句：SELECT \* FROM EMP; 被认为是不相同的语句，  
因为两个语句中引用的EMP不是指同一个表。  
  
4). 在SQL语句中使用的捆绑变量的捆绑类型必须一致。  
  
如果语句与当前在共享池中的另一个语句是等同的话，Oracle并不对它进行语法分析。而直接执行该语句，提高了执行效率，因为语法分析比较耗费资源。  
  
注意的是，从oracle 8i开始，新引入了一个CURSOR\_SHARING参数，该参数的主要目的就是为了解决在编程过程中已大量使用的硬编码SQL问题。因为在实际开发中，很多程序人员为了提高开发速度，而采用类似下面的开发方法：  
str\_sql string;  
int\_empno int;  
int\_empno = 2000;  
str\_sql = ‘SELECT \* FROM emp WHERE empno = ‘ + int\_empno;  
…………  
int\_empno = 1000;  
str\_sql = ‘SELECT \* FROM emp WHERE empno = ‘ + int\_empno;  
上面的代码实际上使用了硬编码SQL，使我们不能使用共享SQL的功能，结果是数据库效率不高。但是从上面的2个语句来看，产生的硬编码SQL只是列值不同，其它部分都是相同的，如果仅仅因为列值不同而导致这2个语句不能共享是很可惜的，为了解决这个问题，引入了CURSOR\_SHARING参数，使这类问题也可以使用共享SQL，从而使这样的开发也可以利用共享SQL功能。听起来不错，ORACLE真为用户着想，使用户在不改变代码的情况下还可以利用共享SQL的功能。真的如此吗？天上不会无缘无故的掉一个馅饼的，ORACLE对该参数的使用做了说明，建议在经过实际测试后再改该参数的值(缺省情况下，该参数的值为EXACT，语句完全一致才使用共享SQL)。因为有可能该变该值后，你的硬编码SQL是可以使用共享SQL了，但数据库的性能反而会下降。 我在实际应用中已经遇到这种情况。所以建议编写需要稳定运行程序的开发人员最好还是一开始就使用绑定变量的SQL。  
  
**Rowid的概念：**  
rowid是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。对每个表都有一个rowid的伪列，但是表中并不物理存储ROWID列的值。不过你可以像使用其它列那样使用它，但是不能删除改列，也不能对该列的值进行修改、插入。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变。  
  
**为什么使用ROWID**  
rowid对访问一个表中的给定的行提供了最快的访问方法，通过ROWID可以直接定位到相应的数据块上，然后将其读到内存。我们创建一个索引时，该索引不但存储索引列的值，而且也存储索引值所对应的行的ROWID，这样我们通过索引快速找到相应行的ROWID后，通过该ROWID，就可以迅速将数据查询出来。这也就是我们使用索引查询时，速度比较快的原因。  
  
在ORACLE8以前的版本中，ROWID由FILE 、BLOCK、ROW NUMBER构成。随着oracle8中对象概念的扩展，ROWID发生了变化，ROWID由OBJECT、FILE、BLOCK、ROW NUMBER构成。利用DBMS\_ROWID可以将rowid分解成上述的各部分，也可以将上述的各部分组成一个有效的rowid。  
  
**Recursive SQL概念**  
有时为了执行用户发出的一个sql语句，Oracle必须执行一些额外的语句，我们将这些额外的语句称之为'recursive calls'或'recursive SQL statements'。如当一个DDL语句发出后，ORACLE总是隐含的发出一些recursive SQL语句，来修改数据字典信息，以便用户可以成功的执行该DDL语句。当需要的数据字典信息没有在共享内存中时，经常会发生Recursive calls，这些Recursive calls会将数据字典信息从硬盘读入内存中。用户不比关心这些recursive SQL语句的执行情况，在需要的时候，ORACLE会自动的在内部执行这些语句。当然DML语句与SELECT都可能引起recursive SQL。简单的说，我们可以将触发器视为recursive SQL。  
  
**Row Source(行源)**  
用在查询中，由上一操作返回的符合条件的行的集合，即可以是表的全部行数据的集合；也可以是表的部分行数据的集合；也可以为对上2个row source进行连接操作(如join连接)后得到的行数据集合。  
  
**Predicate(谓词)**  
一个查询中的WHERE限制条件  
  
**Driving Table(驱动表)**  
该表又称为外层表(OUTER TABLE)。这个概念用于嵌套与HASH连接中。如果该row source返回较多的行数据，则对所有的后续操作有负面影响。注意此处虽然翻译为驱动表，但实际上翻译为驱动行源(driving row source)更为确切。一般说来，是应用查询的限制条件后，返回较少行源的表作为驱动表，所以如果一个大表在WHERE条件有有限制条件(如等值限制)，则该大表作为驱动表也是合适的，所以并不是只有较小的表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行源的表作为驱动表。在执行计划中，应该为靠上的那个row source，后面会给出具体说明。在我们后面的描述中，一般将该表称为连接操作的row source 1。  
  
**Probed Table(被探查表)**  
该表又称为内层表(INNER TABLE)。在我们从驱动表中得到具体一行的数据后，在该表中寻找符合连接条件的行。所以该表应当为大表(实际上应该为返回较大row source的表)且相应的列上应该有索引。在我们后面的描述中，一般将该表称为连接操作的row source 2。  
  
**组合索引(concatenated index)**  
由多个列构成的索引，如create index idx\_emp on emp(col1, col2, col3, ……)，则我们称idx\_emp索引为组合索引。在组合索引中有一个重要的概念：引导列(leading column)，在上面的例子中，col1列为引导列。当我们进行查询时可以使用”where col1 = ? ”，也可以使用”where col1 = ? and col2 = ?”，这样的限制条件都会使用索引，但是”where col2 = ? ”查询就不会使用该索引。所以限制条件中包含先导列时，该限制条件才会使用该组合索引。  
  
**可选择性(selectivity)：**  
比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。如果该列的”唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。  
  
  
有了这些背景知识后就开始介绍执行计划。为了执行语句，Oracle可能必须实现许多步骤。这些步骤中的每一步可能是从数据库中物理检索数据行，或者用某种方法准备数据行，供发出语句的用户使用。Oracle用来执行语句的这些步骤的组合被称之为执行计划。执行计划是SQL优化中最为复杂也是最为关键的部分，只有知道了ORACLE在内部到底是如何执行该SQL语句后，我们才能知道优化器选择的执行计划是否为最优的。执行计划对于DBA来说，就象财务报表对于财务人员一样重要。所以我们面临的问题主要是：如何得到执行计划；如何分析执行计划，从而找出影响性能的主要问题。下面先从分析树型执行计划开始介绍，然后介绍如何得到执行计划，再介绍如何分析执行计划。  
  
举例：  
这个例子显示关于下面SQL语句的执行计划。  
SELECT ename, job, sal, dname  
FROM emp, dept  
WHERE emp.deptno = derpt.deptno  
AND NOT EXISTS  
( SELECT \*  
FROM salgrade  
WHERE emp.sal BETWEEN losal AND hisal );  
此语句查询薪水不在任何建议薪水范围内的所有雇员的名字，工作，薪水和部门名。  
下图5-1显示了一个执行计划的图形表示：  
  
  
  
**执行计划的步骤**  
执行计划的每一步返回一组行，它们或者为下一步所使用，或者在最后一步时返回给发出SQL语句的用户或应用。由每一步返回的一组行叫做行源(row source）。图5-1树状图显示了从一步到另一步行数据的流动情况。每步的编号反映了在你观察执行计划时所示步骤的顺序（如何观察执行计划将被简短地说明）。一般来说这并不是每一步被执行的先后顺序。执行计划的每一步或者从数据库中检索行，或者接收来自一个或多个行源的行数据作为输入：  
由红色字框指出的步骤从数据库中的数据文件中物理检索数据。这种步骤被称之为存取路径，后面会详细介绍在Oracle可以使用的存取路径：  
l 第３步和第６步分别的从EMP表和SALGRADE表读所有的行。  
l 第５步在PK\_DEPTNO索引中查找由步骤３返回的每个DEPTNO值。它找出与DEPT表中相关联的那些行的ROWID。  
l 第４步从DEPT表中检索出ROWID为第５步返回的那些行。  
由黑色字框指出的步骤在行源上操作，如做2表之间的关联，排序，或过滤等操作，后面也会给出详细的介绍：  
l 第２步实现嵌套的循环操作(相当于C语句中的嵌套循环)，接收从第３步和第４步来的行源，把来自第３步源的每一行与它第４步中相应的行连接在一起，返回结果行到第１步。  
l 第１步完成一个过滤器操作。它接收来自第２步和第６步的行源，消除掉第２步中来的，在第６步有相应行的那些行，并将来自第２步的剩下的行返回给发出语句的用户或应用。  
  
**实现执行计划步骤的顺序**  
执行计划中的步骤不是按照它们编号的顺序来实现的：Oracle首先实现图5-1树结构图形里作为叶子出现的那些步骤(例如步骤3、5、6)。由每一步返回的行称为它下一步骤的行源。然后Oracle实现父步骤。  
  
举例来说，为了执行图5-1中的语句，Oracle以下列顺序实现这些步骤：  
l 首先，Oracle实现步骤３，并一行一行地将结果行返回给第２步。  
l 对第３步返回的每一行，Oracle实现这些步骤：  
\-- Oracle实现步骤５，并将结果ROWID返回给第４步。  
\-- Oracle实现步骤４，并将结果行返回给第２步。  
\-- Oracle实现步骤２，将接受来自第３步的一行和来自第４步的一行，并返回  
给第１步一行。  
\-- Oracle实现步骤６，如果有结果行的话，将它返回给第１步。  
\-- Oracle实现步骤１，如果从步骤６返回行，Oracle将来自第２步的行返回给  
发出SQL语句的用户。  
  
注意Oracle对由第３步返回的每一行实现步骤５，４，２，６一次。许多父步骤在它们能执行之前只需要来自它们子步骤的单一行。对这样的父步骤来说，只要从子步骤已返回单一行时立即实现父步骤（可能还有执行计划的其余部分）。如果该父步骤的父步骤同样可以通过单一行返回激活的话，那么它也同样被执行。所以，执行可以在树上串联上去，可能包含执行计划的余下部分。对于这样的操作，可以使用 **first\_rows**作为优化目标以便于实现快速响应用户的请求。  
对每个由子步骤依次检索出来的每一行，Oracle就实现父步骤及所有串联在一起的步骤一次。对由子步骤返回的每一行所触发的父步骤包括表存取，索引存取，嵌套的循环连接和过滤器。  
  
有些父步骤在它们被实现之前需要来自子步骤的所有行。对这样的父步骤，直到所有行从子步骤返回之前Oracle不能实现该父步骤。这样的父步骤包括排序，排序一合并的连接，组功能和总计。对于这样的操作，不能使用 **first\_rows**作为优化目标，而可以用 **all\_rows**作为优化目标，使该中类型的操作耗费的资源最少。  
  
有时语句执行时，并不是象上面说的那样一步一步有先有后的进行，而是可能并行运行，如在实际环境中，3、5、4步可能并行运行，以便取得更好的效率。从上面的树型图上，是很难看出各个操作执行的先后顺序，而通过ORACLE生成的另一种形式的执行计划，则可以很容易的看出哪个操作先执行，哪个后执行，这样的执行计划是我们真正需要的，后面会给出详细说明。现在先来看一些预备知识。  
  
**访问路径(方法) -- access path**  
优化器在形成执行计划时需要做的一个重要选择是如何从数据库查询出需要的数据。对于SQL语句存取的任何表中的任何行，可能存在许多存取路径(存取方法)，通过它们可以定位和查询出需要的数据。优化器选择其中自认为是最优化的路径。  
在物理层，oracle读取数据，一次读取的最小单位为数据库块(由多个连续的操作系统块组成)，一次读取的最大值由操作系统一次I/O的最大值与multiblock参数共同决定，所以即使只需要一行数据，也是将该行所在的数据库块读入内存。逻辑上，oracle用如下存取方法访问数据：  
  
**1) 全表扫描（Full Table Scans, FTS）**  
为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件。Oracle顺序地读取分配给表的每个数据块，直到读到表的最高水线处(high water mark, HWM，标识表的最后一个数据块)。一个多块读操作可以使一次I/O能读取多块数据块(db\_block\_multiblock\_read\_count参数设定)，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。由于HWM标识最后一块被读入的数据，而delete操作不影响HWM值，所以一个表的所有数据被delete后，其全表扫描的时间不会有改善，一般我们需要使用truncate命令来使HWM值归为0。幸运的是oracle 10G后，可以人工收缩HWM的值。  
  
由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的尾部，这样可以使其快速交换出内存，从而不使内存重要的数据被交换出内存。  
使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% -- 10%，或你想使用并行查询功能时。  
使用全表扫描的例子：  
~~~~~~~~~~~~~~~~~~~~~~~~  
SQL> explain plan for select \* from dual;  
Query Plan  
\-----------------------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=  
TABLE ACCESS FULL DUAL  
  
**2) 通过ROWID的表存取（Table Access by ROWID或rowid lookup）**  
行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。  
为了通过ROWID存取表，Oracle 首先要获取被选择行的ROWID，或者从语句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。  
这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。  
使用ROWID存取的方法：  
SQL> explain plan for select \* from dept where rowid = 'AAAAyGAADAAAAATAAF';  
Query Plan  
\------------------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=1  
TABLE ACCESS BY ROWID DEPT \[ANALYZED\]  
  
**3）索引扫描（Index Scan或index lookup）**  
我们先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找(index lookup)。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取一个数据库块。  
  
在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。索引扫描可以由2步组成：(1) 扫描索引得到对应的rowid值。 (2) 通过找到的rowid从表中读出具体的数据。每步都是单独的一次I/O，但是对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第1步的I/O经常是逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，这是一个机械操作，相对逻辑I/O来说，是极其费时间的。所以如果多大表进行索引扫描，取出的数据如果大于总量的5% -- 10%，使用索引扫描会效率下降很多。  
如下列所示：  
SQL> explain plan for select empno, ename from emp where empno=10;  
Query Plan  
\------------------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=1  
TABLE ACCESS BY ROWID EMP \[ANALYZED\]  
INDEX UNIQUE SCAN EMP\_I1  
注意TABLE ACCESS BY ROWID EMP部分，这表明这不是通过FTS存取路径访问数据，而是通过rowid lookup存取路径访问数据的。在此例中，所需要的rowid是由于在索引查找empno列的值得到的，这种方式是INDEX UNIQUE SCAN查找，后面给予介绍，EMP\_I1为使用的进行索引查找的索引名字。  
  
但是如果查询的数据能全在索引中找到，就可以避免进行第2步操作，避免了不必要的I/O，此时即使通过索引扫描取出的数据比较多，效率还是很高的，因为这只会在索引中读取。所以上面我在介绍基于规则的优化器时，使用了select count(id) from SWD\_BILLDETAIL where cn <'6'，而没有使用select count(cn) from SWD\_BILLDETAIL where cn <'6'。因为在实际情况中，只查询被索引列的值的情况极为少，所以，如果我在查询中使用count(cn)，则不具有代表性。  
  
SQL> explain plan for select empno from emp where empno=10; -- 只查询empno列值  
Query Plan  
\------------------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=1  
INDEX UNIQUE SCAN EMP\_I1  
  
进一步讲，如果sql语句中对索引列进行排序，因为索引已经预先排序好了，所以在执行计划中不需要再对索引列进行排序  
SQL> explain plan for select empno, ename from emp  
where empno > 7876 order by empno;  
Query Plan  
\--------------------------------------------------------------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=1  
TABLE ACCESS BY ROWID EMP \[ANALYZED\]  
INDEX RANGE SCAN EMP\_I1 \[ANALYZED\]  
从这个例子中可以看到：因为索引是已经排序了的，所以将按照索引的顺序查询出符合条件的行，因此避免了进一步排序操作。  
  
**根据索引的类型与where限制条件的不同，有4种类型的索引扫描：**  
索引唯一扫描(index unique scan)  
索引范围扫描(index range scan)  
索引全扫描(index full scan)  
索引快速扫描(index fast full scan)  
  
**(1) 索引唯一扫描(index unique scan)**  
通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中，如创建一个索引：create index idx\_test on emp(ename, deptno, loc)。则select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句可以使用该索引。如果该语句只返回一行，则存取方法称为索引唯一扫描。而select ename from emp where deptno = ‘DEV’语句则不会使用该索引，因为where子句种没有引导列。如果存在UNIQUE 或PRIMARY KEY 约束（它保证了语句只存取单行）的话，Oracle经常实现唯一性扫描。  
使用唯一性约束的例子：  
SQL> explain plan for  
select empno,ename from emp where empno=10;  
Query Plan  
\------------------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=1  
TABLE ACCESS BY ROWID EMP \[ANALYZED\]  
INDEX UNIQUE SCAN EMP\_I1  
  
**(2) 索引范围扫描(index range scan)**  
使用一个索引存取多行数据，同上面一样，如果索引是组合索引，如(1)所示，而且select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句返回多行数据，虽然该语句还是使用该组合索引进行查询，可此时的存取方法称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如>、<、<>、>=、<=、between)  
使用索引范围扫描的例子：  
SQL> explain plan for select empno,ename from emp  
where empno > 7876 order by empno;  
Query Plan  
\--------------------------------------------------------------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=1  
TABLE ACCESS BY ROWID EMP \[ANALYZED\]  
INDEX RANGE SCAN EMP\_I1 \[ANALYZED\]  
  
在非唯一索引上，谓词col = 5可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。  
使用index rang scan的3种情况：  
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)  
(b) 在组合索引上，只使用部分列进行查询，导致查询出多行  
(c) 对非唯一索引列上进行的任何查询。  
  
**(3) 索引全扫描(index full scan)**  
与全表扫描对应，也有相应的全索引扫描。在某些情况下，可能进行全索引扫描而不是范围扫描，需要注意的是全索引扫描只在CBO模式下才有效。CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时，才进行全索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。  
全索引扫描的例子：  
An Index full scan will not perform single block i/o's and so it may prove to be inefficient.  
  
e.g.  
Index BE\_IX is a concatenated index on big\_emp (empno, ename)  
  
SQL> explain plan for select empno, ename from big\_emp order by empno,ename;  
Query Plan  
\--------------------------------------------------------------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=26  
INDEX FULL SCAN BE\_IX \[ANALYZED\]  
  
**(4) 索引快速扫描(index fast full scan)**  
扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。  
  
索引快速扫描的例子：  
BE\_IX索引是一个多列索引：big\_emp (empno,ename)  
  
SQL> explain plan for select empno,ename from big\_emp;  
Query Plan  
\------------------------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=1  
INDEX FAST FULL SCAN BE\_IX \[ANALYZED\]  
  
只选择多列索引的第2列：  
SQL> explain plan for select ename from big\_emp;  
Query Plan  
\------------------------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=1  
INDEX FAST FULL SCAN BE\_IX \[ANALYZED\]  
  
  
**表之间的连接**  
Join是一种试图将两个表结合在一起的谓词，一次只能连接2个表，表连接也可以被称为表关联。在后面的叙述中，我们将会使用”row source”来代替”表”，因为使用row source更严谨一些，并且将参与连接的2个row source分别称为row source1和row source 2。Join过程的各个步骤经常是串行操作，即使相关的row source可以被并行访问，即可以并行的读取做join连接的两个row source的数据，但是在将表中符合限制条件的数据读入到内存形成row source后，join的其它步骤一般是串行的。有多种方法可以将2个表连接起来，当然每种方法都有自己的优缺点，每种连接类型只有在特定的条件下才会发挥出其最大优势。  
  
row source(表)之间的连接顺序对于查询的效率有非常大的影响。通过首先存取特定的表，即将该表作为驱动表，这样可以先应用某些限制条件，从而得到一个较小的row source，使连接的效率较高，这也就是我们常说的要先执行限制条件的原因。一般是在将表读入内存时，应用where子句中对该表的限制条件。  
根据2个row source的连接条件的中操作符的不同，可以将连接分为等值连接(如WHERE A.COL3 = B.COL4)、非等值连接(WHERE A.COL3 > B.COL4)、外连接(WHERE A.COL3 = B.COL4(+))。上面的各个连接的连接原理都基本一样，所以为了简单期间，下面以等值连接为例进行介绍。在后面的介绍中，都已：  
SELECT A.COL1, B.COL2  
FROM A, B  
WHERE A.COL3 = B.COL4;  
为例进行说明，假设A表为Row Soruce1，则其对应的连接操作关联列为COL 3；B表为Row Soruce2，则其对应的连接操作关联列为COL 4；  
  
**连接类型：**  
目前为止，无论连接操作符如何，典型的连接类型共有3种：  
排序 - - 合并连接(Sort Merge Join (SMJ) )  
嵌套循环(Nested Loops (NL) )  
哈希连接(Hash Join)  
  
**排序 - - 合并连接(Sort Merge Join, SMJ)**  
内部连接过程：  
1) 首先生成row source1需要的数据，然后对这些数据按照连接操作关联列(如A.col3)进行排序。  
2) 随后生成row source2需要的数据，然后对这些数据按照与sort source1对应的连接操作关联列(如B.col4)进行排序。  
3) 最后两边已排序的行被放在一起执行合并操作，即将2个row source按照连接条件连接起来  
下面是连接步骤的图形表示：  
MERGE  
/ /  
SORT SORT  
| |  
Row Source 1 Row Source 2  
  
如果row source已经在连接关联列上被排序，则该连接操作就不需要再进行sort操作，这样可以大大提高这种连接操作的连接速度，因为排序是个极其费资源的操作，特别是对于较大的表。 预先排序的row source包括已经被索引的列(如a.col3或b.col4上有索引)或row source已经在前面的步骤中被排序了。尽管合并两个row source的过程是串行的，但是可以并行访问这两个row source(如并行读入数据，并行排序).  
  
SMJ连接的例子：  
SQL> explain plan for  
select /\*+ ordered \*/ e.deptno, d.deptno  
from emp e, dept d  
where e.deptno = d.deptno  
order by e.deptno, d.deptno;  
  
Query Plan  
\-------------------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=17  
MERGE JOIN  
SORT JOIN  
TABLE ACCESS FULL EMP \[ANALYZED\]  
SORT JOIN  
TABLE ACCESS FULL DEPT \[ANALYZED\]  
  
排序是一个费时、费资源的操作，特别对于大表。基于这个原因，SMJ经常不是一个特别有效的连接方法，但是如果2个row source都已经预先排序，则这种连接方法的效率也是蛮高的。  
  
**嵌套循环(Nested Loops, NL)**  
这个连接方法有驱动表(外部表)的概念。其实，该连接过程就是一个2层嵌套循环，所以外层循环的次数越少越好，这也就是我们为什么将小表或返回较小row source的表作为驱动表(用于外层循环)的理论依据。但是这个理论只是一般指导原则，因为遵循这个理论并不能总保证使语句产生的I/O次数最少。有时不遵守这个理论依据，反而会获得更好的效率。如果使用这种方法，决定使用哪个表作为驱动表很重要。有时如果驱动表选择不正确，将会导致语句的性能很差、很差。  
  
内部连接过程：  
Row source1的Row 1 -------------- -- Probe -> Row source 2  
Row source1的Row 2 -------------- -- Probe -> Row source 2  
Row source1的Row 3 -------------- -- Probe -> Row source 2  
…….  
Row source1的Row n -------------- -- Probe -> Row source 2  
从内部连接过程来看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此时保持row source1尽可能的小与高效的访问row source2(一般通过索引实现)是影响这个连接效率的关键问题。这只是理论指导原则，目的是使整个连接操作产生最少的物理I/O次数，而且如果遵守这个原则，一般也会使总的物理I/O数最少。但是如果不遵从这个指导原则，反而能用更少的物理I/O实现连接操作，那尽管违反指导原则吧！因为最少的物理I/O次数才是我们应该遵从的真正的指导原则，在后面的具体案例分析中就给出这样的例子。  
  
在上面的连接过程中，我们称Row source1为驱动表或外部表。Row Source2被称为被探查表或内部表。  
  
在NESTED LOOPS连接中，Oracle读取row source1中的每一行，然后在row sourc2中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理row source1中的下一行。这个过程一直继续，直到row source1中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一，这种类型的连接可以用在需要快速响应的语句中，以响应速度为主要目标。  
  
如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。  
  
如果不使用并行操作，最好的驱动表是那些应用了where 限制条件后，可以返回较少行数据的的表，所以大表也可能称为驱动表，关键看限制条件。对于并行查询，我们经常选择大表作为驱动表，因为大表可以充分利用并行功能。当然，有时对查询使用并行操作并不一定会比查询不使用并行操作效率高，因为最后可能每个表只有很少的行符合限制条件，而且还要看你的硬件配置是否可以支持并行(如是否有多个CPU，多个硬盘控制器)，所以要具体问题具体对待。  
  
NL连接的例子：  
SQL> explain plan for  
select a.dname,b.sql  
from dept a,emp b  
where a.deptno = b.deptno;  
  
Query Plan  
\-------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=5  
NESTED LOOPS  
TABLE ACCESS FULL DEPT \[ANALYZED\]  
TABLE ACCESS FULL EMP \[ANALYZED\]  
  
**哈希连接(Hash Join, HJ)**  
这种连接是在oracle 7.3以后引入的，从理论上来说比NL与SMJ更高效，而且只用在CBO优化器中。  
较小的row source被用来构建hash table与bitmap，第2个row source被用来被hansed，并与第一个row source生成的hash table进行匹配，以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法，来检查在hash table中是否有匹配的行。特别的，当hash table比较大而不能全部容纳在内存中时，这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念，被构建为hash table与bitmap的表为驱动表，当被构建的hash table与bitmap能被容纳在内存中时，这种连接方式的效率极高。  
  
HASH连接的例子：  
SQL> explain plan for  
select /\*+ use\_hash(emp) \*/ empno  
from emp, dept  
where emp.deptno = dept.deptno;  
  
Query Plan  
\----------------------------  
SELECT STATEMENT \[ **CHOOSE**\] Cost=3  
HASH JOIN  
TABLE ACCESS FULL DEPT  
TABLE ACCESS FULL EMP  
  
要使哈希连接有效，需要设置HASH\_JOIN\_ENABLED=TRUE，缺省情况下该参数为TRUE，另外，不要忘了还要设置hash\_area\_size参数，以使哈希连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还要低。  
  
**总结一下，在哪种情况下用哪种连接方法比较好：**  
**排序 - - 合并连接(Sort Merge Join, SMJ)：**  
a) 对于非等值连接，这种连接方式的效率是比较高的。  
b) 如果在关联的列上都有索引，效果更好。  
c) 对于将2个较大的row source做连接，该连接方法比NL连接要好一些。  
d) 但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O。  
**嵌套循环(Nested Loops, NL)：**  
a) 如果driving row source(外部表)比较小，并且在inner row source(内部表)上  
有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。  
b) NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经  
连接的行，而不必等待所有的连接操作处理完才返回数据，  
这可以实现快速的响应时间。  
**哈希连接(Hash Join, HJ)：**  
a) 这种方法是在oracle7后来引入的，使用了比较先进的连接理论，  
一般来说，其效率应该好于其它2种连接，但是这种连接只能用在  
CBO优化器中，而且需要设置合适的hash\_area\_size参数，  
才能取得较好的性能。  
b) 在2个较大的row source之间连接时会取得相对较好的效率，在一个  
row source较小时则能取得更好的效率。  
c) 只能用于等值连接中  
  
**笛卡儿乘积(Cartesian Product)**   
当两个row source做连接，但是它们之间没有关联条件时，就会在两个row source中做笛卡儿乘积，这通常由编写代码疏漏造成(即程序员忘了写关联条件)。笛卡尔乘积是一个表的每一行依次与另一个表中的所有行匹配。在特殊情况下我们可以使用笛卡儿乘积，如在星形连接中，除此之外，我们要尽量使用笛卡儿乘积，否则，自己想结果是什么吧！  
  
注意在下面的语句中，在2个表之间没有连接。  
SQL> explain plan for  
select emp.deptno,dept,deptno  
from emp,dept  
  
Query Plan  
\------------------------------  
SLECT STATEMENT \[ **CHOOSE**\] Cost=5  
MERGE JOIN CARTESIAN  
TABLE ACCESS FULL DEPT  
SORT JOIN  
TABLE ACCESS FULL EMP  
  
CARTESIAN关键字指出了在2个表之间做笛卡尔乘积。假如表emp有n行，dept表有m行，笛卡尔乘积的结果就是得到n \* m行结果。