Oracle sql执行计划解析

Oracle sql执行计划解析

https://blog.csdn.net/xybelieve1990/article/details/50562963

Oracle优化器

Oracle的优化器共有3种模式：RULE (基于规则)、COST(基于成本)、CHOOSE(基于选择)。

设置缺省的优化器的方法，是在启动参数文件中针对OPTIMIZER_ MODE参数的各种声明进行选择，如RULE、COST、CHOOSE、ALL_ ROWS、FIRST_ ROWS。当然也可以在SQL语句级别或是会话级别对其进行覆盖。

为了使用基于成本的优化器(CBO，Cost—Based Optimizer)，必须经常运行analyze命令，以增加数据库中的对象统计信息(object statistics)的准确性。

如果数据库的优化器模式设置为基于选择，那么实际的优化器模式将和是否运行过analyze命令有关。如果数据表已经被analyze过，优化器模式将自动切换成CBO，反之，数据库将采用RULE形式的优化器。在缺省情况下，Oracle采用CHOOSE优化器。为避免那些不必要的全表扫描，必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。

2 种类型的优化器：

基于规则的优化器

基于代价的优化器。

不同之处：取得代价的方法与衡量代价的大小不同。

基于规则的优化器 -- Rule Based (Heuristic) Optimization(简称RBO)

基于规则的优化器中采用启发式的方法(Heuristic Approach)或规则(Rules)来生成执行计划。例如，如果一个查询的where条件(where clause)包含一个谓词(predicate，其实就是一个判断条件，如”=”, “>”, ”<”等)，而且该谓词上引用的列上有有效索引，那么优化器将使用索引访问这个表，而不考虑其它因素，如表中数据的多少、表中数据的易变性、索引的可选择性等。此时数据库中没有关于表与索引数据的统计性描述，如表中有多上行，每行的可选择性等。优化器也不考虑实例参数，如multi block i/o、可用排序内存的大小等，所以优化器有时就选择了次优化的计划作为真正的执行计划，导致系统性能不高。如，对于 select * from emp where deptno = 10;如果是使用基于规则的优化器，而且deptno列上有有效的索引，则会通过deptno列上的索引来访问emp表。在绝大多数情况下，这是比较高效的，但是在一些特殊情况下，使用索引访问也有比较低效的时候，现举例说明： 1) emp表比较小，该表的数据只存放在几个数据块中。此时使用全表扫描比使用索引访问emp表反而要好。因为表比较小，极有可能数据全在内存中，所以此时做全表扫描是最快的。而如果使用索引扫描，需要先从索引中找到符合条件记录的rowid，然后再一一根据这些rowid从emp中将数据取出来，在这种条件下，效率就会比全表扫描的效率要差一些。2) emp表比较大时，而且deptno = 10条件能查询出表中大部分的数据如(50%)。如该表共有4000万行数据，共放在有500000个数据块中，每个数据块为8k，则该表共有约4G，则这么多的数据不可能全放在内存中，绝大多数需要放在硬盘上。此时如果该查询通过索引查询，则是你梦魇的开始。db_file_multiblock_read_count参数的值200。如果采用全表扫描，则需要500000/db_file_multiblock_read_count=500000/200=2500次I/O。但是如果采用索引扫描，假设deptno列上的索引都已经cache到内存中，所以可以将访问索引的开销忽略不计。因为要读出4000万x 50% = 2000万数据，假设在读这2000万数据时，有99.9%的命中率，则还是需要20000次I/O,比上面的全表扫描需要的2500次多多了，所以在这种情况下，用索引扫描反而性能会差很多。在这样的情况下，用全表扫描的时间是固定的，但是用索引扫描的时间会随着选出数据的增多使查询时间相应的延长。

基于代价的优化器 -- Cost Based Optimization(简称CBO)

Oracle把一个代价引擎(Cost Engine)集成到数据库内核中，用来估计每个执行计划需要的代价，该代价将每个执行计划所耗费的资源进行量化，从而CBO可以根据这个代价选择出最优的执行计划。查询耗费的资源可以被分成3个基本组成部分：I/O代价、CPU代价、network代价。

¨I/O代价是将数据从磁盘读入内存所需的代价。访问数据包括将数据文件中数据块的内容读入到SGA的数据高速缓存中，在一般情况下，该代价是处理一个查询所需要的最主要代价，所以我们在优化时，一个基本原则就是降低查询所产生的I/O总次数。

¨CPU代价是处理在内存中数据所需要的代价，如一旦数据被读入内存，则我们在识别出我们需要的数据后，在这些数据上执行排序(sort)或连接(join)操作，这需要耗费CPU资源。对于需要访问跨节点(即通常说的服务器)数据库上数据的查询来说，存在network代价，用来量化传输操作耗费的资源。查询远程表的查询或执行分布式连接的查询会在network代价方面花费比较大。

判断当前数据库使用何种优化器

由optimizer_mode初始化参数决定的。该参数可能的取值为：first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule。具体解释如下：

¨RULE为使用RBO优化器。

¨CHOOSE则是根据实际情况，如果数据字典中包含被引用的表的统计数据，即引用的对象已经被分析，则就使用CBO优化器，否则为RBO优化器。

¨ALL_ROWS为CBO优化器使用的第一种具体的优化方法，是以数据的吞吐量为主要目标，以便可以使用最少的资源完成语句。

¨FIRST_ROWS为优化器使用的第二种具体的优化方法，是以数据的响应时间为主要目标，以便快速查询出开始的几行数据。

¨FIRST_ROWS_[1 | 10 | 100 | 1000] 为优化器使用的第三种具体的优化方法，让优化器选择一个能够把响应时间减到最小的查询执行计划，以迅速产生查询结果的前 n 行。该参数为ORACLE 9I新引入的。

¨查看命令：show parameter OPTIMIZER_MODE

优化定义

什么是优化：

优化是选择最有效的执行计划来执行SQL语句的过程，这是在处理任何数据的语句（SELECT,INSERT,UPDATE或DELETE）中的一个重要步骤。对Oracle来说，执行这样的语句有许多不同的方法，譬如说，将随着以什么顺序访问哪些表或索引的不同而不同。所使用的执行计划可以决定语句能执行得有多快。Oracle中称之为优化器（Optimizer）的组件用来选择这种它认为最有效的执行计划。

相关概念解释

     共享SQL语句

为了不重复解析相同的SQL语句(因为解析操作比较费资源，会导致性能下降)，在第一次解析之后，ORACLE将SQL语句及解析后得到的执行计划存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果该语句和之前的执行过的某一语句完全相同，并且之前执行的该语句与其执行计划仍然在内存中存在，则ORACLE就不需要再进行分析，直接得到该语句的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并大大节省了内存的使用。使用这个功能的关键是将执行过的语句尽可能放到内存中，所以这要求有大的共享池(通过设置shared buffer pool参数值)和尽可能的使用绑定变量的方法执行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。

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访问数据的存取方法

     访问路径(方法) -- access path

优化器在形成执行计划时需要做的一个重要选择是如何从数据库查询出需要的数据。对于SQL语句存取的任何表中的任何行，可能存在许多存取路径(存取方法)，通过它们可以定位和查询出需要的数据。优化器选择其中自认为是最优化的路径。在物理层，oracle读取数据，一次读取的最小单位为数据库块(由多个连续的操作系统块组成)，一次读取的最大值由操作系统一次I/O的最大值与multiblock参数共同决定，所以即使只需要一行数据，也是将该行所在的数据库块读入内存。逻辑上，oracle用如下存取方法访问数据：

¨1 全表扫描（Full Table Scans, FTS）

¨2 通过ROWID的表存取（Table Access by ROWID或rowid lookup）

¨3 索引扫描（Index Scan或index lookup）

全表扫描

为实现全表扫描，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%，或你想使用并行查询功能时。

n使用全表扫描的例子：

¨SQL> explain plan for select * from dual;

¨Query Plan

¨-----------------------------------------

¨SELECT STATEMENT [CHOOSE] Cost=

¨TABLE ACCESS FULL DUAL

通过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]

索引扫描（Index Scan或index lookup）

通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据。一个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为使用的进行索引查找的索引名字。 根据索引的类型与where限制条件的不同，有4种类型的索引扫描：

１、索引唯一扫描(index unique scan)

２、索引范围扫描(index range scan)

３、索引全扫描(index full scan)

４、索引快速扫描(index fast full scan)

索引唯一扫描(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

索引范围扫描(index range scan)

使用索引存取多行数据，如果索引是组合索引，如索引唯一扫描所示，且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种情况：

¨在唯一索引列上使用了range操作符(> < <> >= <= between)

¨在组合索引上，只使用部分列进行查询，导致查询出多行

索引全扫描(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]

索引快速扫描(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=1INDEX FAST FULL SCAN BE_IX [ANALYZED]

表连接

   Join是一种试图将两个表结合在一起的谓词，一次只能连接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；

连接类型：

1. 排序 - - 合并连接 (Sort Merge Join (SMJ) )

2. 嵌套循环 (Nested Loops (NL) )

3. 哈希连接 (Hash Join)

4.笛卡儿乘积(Cartesian Product)

排序 - - 合并连接(Sort Merge Join (SMJ) )

内部连接过程：

1. 首先生成row source1需要的数据，然后对这些数据按照连接操作关联列(如A.col3)进行排序。

2. 随后生成row source2需要的数据，然后对这些数据按照与sortsource1对应的连接操作关联列(如B.col4)进行排序。

3. 最后两边已排序的行被放在一起执行合并操作，即将2个rowsource按照连接条件连接起来。

如果row source已经在连接关联列上被排序，则该连接操作就不需要再进行sort操作，这样可以大大提高这种连接操作的连接速度，因为排序是个极其费资源的操作， 特别是对于较大的表。 预先排序的row source包括已经被索引的列(如a.col3或b.col4上有索引)或row source已经在前面的步骤中被排序了。尽管合并两个row source的过程是串行的，但是可以并行访问这两个row source(如并行读入数据，并行排序).排序是一个费时、费资源的操作，特别对于大表。基于这个原因，SMJ经常不是一个特别有效的连接方法，但是如果2个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]

嵌套循环(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)

理论上来说比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_JOIN_ENABLED=TRUE，缺省情况下该参数为TRUE，另外，不要忘了还要设置hash_area_size参数，以使哈希连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还要低。

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

笛卡儿乘积(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 EMPCARTESIAN关键字指出了在2个表之间做笛卡尔乘积。假如表emp有n行，dept表有m行，笛卡尔乘积的结果就是得到n * m行结果。

在哪种情况下用哪种连接方法比较好：

1.排序 - - 合并连接(Sort Merge Join, SMJ)：

ü 对于非等值连接，这种连接方式的效率是比较高的。

ü 如果在关联的列上都有索引，效果更好。

ü 对将2个较大的row source做连接，该连接方法比NL连接要好些。

ü 但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O。

2.嵌套循环(Nested Loops, NL)：

ü 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。

NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经 连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。

3.哈希连接(Hash Join, HJ)：

ü 此方法是在oracle7后来引入的，使用了比较先进的连接理论，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数，才能 取得较好的性能。

ü 在2个较大的row source之间连接时会取得相对较好的效率，在一个 row source较小时则能取得更好的效率。

ü 只能用于等值连接中。

如何产生执行计划

   要为一个语句产生执行计划，有三种方法，我们这里只介绍最简单的方法：在plsql中新建Explain Plan Window，然后将要产生执行计划的sql放到该窗口中执行，或者在普通的SQL Window中选择sql语句，按F5也可以产生执行计划窗口。

需要注意的是，以上方法并不会真正执行sql，只是产生执行计划。

分析执行计划

   通过如下示例进行分析演示：

例1：

假设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表示优化器认为该执行计划的代价:

SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14) 假如执行计划中给出的是类似下面的信息，则表明是使用RBO优化器，因为cost部分的值为空，或者压根就没有cost部分。

¨ SELECT STATEMENT Optimizer=CHOOSE Cost=

¨ SELECT STATEMENT Optimizer=CHOOSE

¨ 这样我们从Optimizer后面的信息中可以得出执行该语句时到底用了什么样的优化器。特别的，如果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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 MERGE JOIN

2 1 SORT (JOIN)

3 2 NESTED LOOPS

4 3 TABLE ACCESS (FULL) OF 'B'

5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'

6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)

7 1 SORT (JOIN)

8 7 TABLE ACCESS (FULL) OF 'C'

Statistics

0 recursive calls

8 db block gets

6 consistent gets

0 physical reads

34

0 redo size

551 bytes sent via SQLNet to client

430 bytes received via SQLNet 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。如果数据库是基于代价的优化器，它会利用计算出的代价来决定合适的驱动表与合适的连接顺序。一般来说，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

n通过执行计划如何判断驱动表：

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

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

¨Execution Plan

¨-----------------------------------------------------------------------------------------------

¨0 SELECT STATEMENT Optimizer=CHOOSE

¨1 0 MERGE JOIN

¨2 1 SORT (JOIN)

¨3 2 NESTED LOOPS

¨4 3 TABLE ACCESS (FULL) OF 'B'

¨5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'

¨7 1 SORT (JOIN)

¨8 7 TABLE ACCESS (FULL) OF 'C'

通过执行计划如何判断驱动表：

谈论上下关系时，只对连续的、缩进一致的行有效。

从这个图中我们可以看到，对于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表做排序—合并连接。

通过分析上面的执行计划，我们不能说C表一定在B、A表之后才被读取，事实上，B表有可能与C表同时被读入内存，因为将表中的数据读入内存的操作可能为并行的。事实上许多操作可能为交叉进行的，因为ORACLE读取数据时，如果就是需要一行数据也是将该行所在的整个数据块读入内存，而且还有可能为多块读。

看执行计划时，我们的关键不是看哪个操作先执行，哪个操作后执行，而是关键看表之间连接的顺序(如得知哪个为驱动表，这需要从操作的顺序进行判断)、使用了何种类型的关联及具体的存取路径(如判断是否利用了索引) 在从执行计划中判断出哪个表为驱动表后，根据我们的知识判断该表作为驱动表(就像上面判断ABC表那样)是否合适，如果不合适，对SQL语句进行更改，使优化器可以选择正确的驱动表。

干预执行计划

     使用hints提示：

基于代价的优化器在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从而使语句高效的运行。例如: 对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则我们可以指示优化器使用全表扫描。在ORACLE中，是通过为语句添加hints(提示)来实现干预优化器优化的目的。

hints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。可以用hints来实现：

¨1) 使用的优化器的类型

¨2) 基于代价优化器的优化目标，是all_rows还是first_rows。

¨3) 表的访问路径，是全表扫描、索引扫描，还是直接利用rowid。

¨4) 表之间的连接类型

¨5) 表之间的连接顺序

¨6) 语句的并行程度

关于hints的介绍到此为止，大家有兴趣自己去了解。