在之前的文章里写了Oracle里常见的执行计划，可以参考文章：http://www.jb51.cc/article/p-ystgtzjb-xe.html，这篇文章里介绍的是其他的一些典型的执行计划。
1. AND-EQUAL(INDEX MERGE)
AND-EQUAL又称为INDEX MERGE,顾名思义，INDEX MERGE就是指如果where条件里出现了多个针对不同单列的等值条件，并且这些列上都有单键值的索引，则Oracle可能会以相应的单个等值条件去分别扫描这些索引；然后Oracle会合并这些扫描单个索引所得到的rowid集合，如果能从这些集合中找到相同的rowid，那么这个rowid就是目标sql最终执行结果所对应的rowid。最后，Oracle只需要用这些rowid回表就能得到目标sql的最终执行结果。
AND-EQUAL在执行计划中对应的关键字就是“AND-EQUAL”，我们可以使用Hint来强制让Oracle走AND-EQUAL。
看一个实例：
zx@MYDB>createtableemp_tempasselect*fromscott.emp; Tablecreated. zx@MYDB>createindexidx_mgronemp_temp(mgr); Indexcreated. zx@MYDB>createindexidx_deptnoonemp_temp(deptno); Indexcreated. zx@MYDB>select/*+and_equal(emp_tempidx_mgridx_deptno)*/empno,jobfromemp_tempwheremgr=7902anddeptno=20; EMPNOJOB ------------------------------------- 7369CLERK
从上述显示内容中可以看出，现在此sql的执行计划走的是对索引IDX_MGR和IDX_DEPTNO的AND-EQUAL。
2. INDEX JOIN
INDEX JOIN很容易引起误解，因为它并不是指通常意义上针对多表的表连接。这里INDEX JOIN指的是针对单表上的不同索引之间的连接。
还以上面的EMP_TEMP为例，已经在列MGR和DEPTNO上分别创建了两个单键值的B*Tree索引，如果此时执行sql语句“select mgr,deptno from emp_temp”，因为这里要查询的列MGR和DEPTNO均可来源于索引IDX_MGR和IDX_DEPTNO(不考虑NULL值)，不用回表，所以除了常规的执行方法之外，Oracle还可以采用如下方法：分别扫描索引IDX_MGR和IDX_DEPTNO，得到的结果集分别记为结果集1和结果集2，然后将结果集1和2做一个连接，连接条件就是“结果集1.rowid=结果集2.rowid”，这样得到的最终连接结果(不用回表)就是上述sql的执行结果。
很显然，针对上述sql的INDEX JOIN的执行效率是不如我们直接在列MGR和DEPTNO上建一个组合索引，然后直接扫描该组全索引的效率高。INDEX JOIN只是为CBO提供了一种可选的执行路径，大多数情况下，它只是额外多出的一种选择而已。
看一下例子：
zx@MYDB>deletefromemp_tempwheremgrisnull; 1rowdeleted. zx@MYDB>commit; Commitcomplete. zx@MYDB>altertableemp_tempmodifymgrnotnull; Tablealtered. zx@MYDB>altertableemp_tempmodifydeptnonotnull; Tablealtered. zx@MYDB>selectmgr,deptnofromemp_temp; MGRDEPTNO -------------------- 783910 ...... 769830 13rowsselected.
从上述显示内容可以看出，现在目标sql的执行计划走的是对索引IDX_MGR和IDX_DEPTNO的HASH JOIN。
3. VIEW
Oracle在处理包含视图的sql时，根据该视图是否能做为视图合并(View Merging)，其对应的执行计划有如下两种形式。
如果可以做视图合并，则Oracle在执行该sql时可以直接针对该视图的基表，此时sql的执行计划中很可能不会出现关键字“VIEW”(不能完全依赖关键字“VIEW”的出现与否来判断Oracle是否做了视图合并，因为对于某些sql而言，即使Oracle已经做了视图合并但其所对应的执行计划中可能还会显示关键字“VIEW”)。
如果不能做视图合并，则Oracle将把该视图看作一个整体并独立地执行它，此时sql的执行计划中将会出现关键字“VIEW”。
看一个实例，还是使用上面的EMP_TEMP表：
zx@MYDB>createviewemp_mgr_viewasselect*fromemp_tempwherejob='MANAGER'; Viewcreated. zx@MYDB>selectempno,salfromemp_mgr_viewwhereename='CLARK'; EMPNOSAL -------------------- 77822450
从上述显示内容中可以看出，现在sql的执行计划走的是对表EMP_TEMP的全表扫描，并且全表扫描进的过滤查询条件是filter(("ENAME"='CLARK' AND "JOB"='MANAGER')).显然这里Oracle做了视图合并，直接查询的视图EMP_MGR_VIEW的基表EMP_TEMP，并且把针对视图的where条件推到了视图的内部，和原先创建视图时的限制条件做了合并。
现在修改视图EMP_MGR_VIEW的定义，其创建语句中加入ROWNUM关键字，这样新创建的同名视图EMP_MGR_VIEW将不能再做视图合并：
zx@MYDB>createorreplaceviewemp_mgr_viewasselect*fromemp_tempwherejob='MANAGER'andrownum<10; Viewcreated. zx@MYDB>selectempno,salfromemp_mgr_viewwhereename='CLARK'; EMPNOSAL -------------------- 77822450
从上述显示内容中可以看出，现在该sql的执行计划中包含了关键字“VIEW”，即表明这里Oracle并没有对视图EMP_MGR_VIEW做视图合并，视图EMP_MGR_VIEW被Oracle当作一个整体来独立执行。
4. FILTER
FILTER直译过来就是过滤、筛选的意思，它是一种特殊的执行计划，所对应的执行过程就是如下三步：
得到一个驱动结果集
根据一定的过滤条件从上述驱动结果集中滤除不满足条件的记录
结果集中剩下的记录就会返回给最终用户或者继续参与一下个执行步骤。
看一个实例，还是使用上面的视图EMP_MGR_VIEW：
zx@MYDB>selectempno,enamefromempwhereempnoin(selectempnofromemp_mgr_view); EMPNOENAME ---------------------------------------- 7566JONES 7698BLAKE 7782CLARK
从上述的显示内容可以看出，现在该sql的执行计划走的是嵌套循环连接，并没有出现我们希望的FILTER类型的执行计划。这是因为Oracle在这里做了子查询展开(Subquery Unnexting)，即把子查询和它外部的sql做了合并，转化成视图VW_NOS_1和表EMP做连接。
zx@MYDB>selectempno,enamefromempwhereempnoin(select/*+NO_UNNEST*/empnofromemp_mgr_view); EMPNOENAME ---------------------------------------- 7566JONES 7698BLAKE 7782CLARK
从上述显示内容中可以看出，现在该sql走的就是我们希望的FILTER类型执行计划。
FILTER类型的执行计划实际上是种改良的嵌套循环连接，它并不像嵌套循环连接那样，驱动结果集中的有多少记录就得访问多少次被驱动表。
用一个实验验证：
zx@MYDB>select*fromt1; COL1COL2 -------------- 1A 2B 3B zx@MYDB>select*fromt2; COL2COL3 ---------- AA2 BB2 DD2 zx@MYDB>select/*+gather_plan_statistics*/*fromt1wherecol2in(select/*+no_unnest*/col2fromt2); COL1COL2 -------------- 1A 2B 3B
注意到上述显示内容中id=2的执行步骤所对应的列A-Rows的值为3，id=3的执行步骤所对应的列Starts的值为2，说明虽然全表扫描T1所得到的驱动结果集的数量为3，但走Filter类型的执行计划时访问被驱动表T2的实际次数却不是3，而是2.这是因为表T数量虽然是3，但其列COL2的distinct值的数量却只有2，所以在用过滤条件“where col2 in(select /*+ no_unnest */ col2 from t2)”去过滤表T1中的数据时，只用访问两次表T2就可以了。
5. SORT
SORT就是排序的意思，执行计划中的SORT通常会以组合的方式出现，这些组合方式包括但不限于如下这几种：
SORT AGGREGATE
SORT UNIQUE
SORT JOIN
SORT GROUP BY
SORT ORDER BY
BUFFER SORT
执行计划中即使出现了关键字“SORT”，也不一定意味着就需要排序，比如SORT AGGREGATE和BUFFER SORT就不一定需要排序。
看一个实例，还是使用上面的EMP_TEMP表：
zx@MYDB>setautotracetraceonly zx@MYDB>selectsum(sal)fromemp_tempwherejob='MANAGER';
从上述显示内容可以看出，现在sql的执行计划走的是SORT AGGREGATE，这里执行的sql只是求了一个sum值，很显然这里不需要排序的。统计信息中的sort(memroy)和sort(disk)的值均为0，也说明Oracle在执行此sql时并没有做任何排序操作，所以我们说SORT AGGREGATE并不一定需要排序，这其中的关键字“SORT”具有一定的迷惑性。
下面再做实例：
zx@MYDB>setautotraceoff zx@MYDB>selectdistinctenamefromemp_tempwherejob='MANAGER'orderbyename; ENAME ------------------------------ BLAKE CLARK JONES
上述sql的含义是既要排序又要去重，它对应的执行计划就会是SORT UNIQUE
zx@MYDB>select/*+use_merge(t1t2)*/t1.empno,t1.ename,t2.salfromscott.empt1,emp_tempt2wheret1.empno=t2.empno;
从上述显示内容中可以看出，现在该sql的执行计划走的是对EMP和EMP_TEMP的排序合并连接。SORT JOIN类型的执行计划通常会出现在排序合并连接中，它是排序合并连接所对应的执行计划第一步要做的事情。
再执行如下sql：
zx@MYDB>selectenamefromemp_tempwherejob='MANAGER'orderbyename; ENAME ------------------------------ BLAKE CLARK JONES
上述sql的含义是只需要单纯的排序，它对应的执行计划就会是SORT ORDER BY:
接着执行下面的sql:
selectenamefromemp_tempwherejob='MANAGER'groupbyenameorderbyename; ENAME ------------------------------ BLAKE CLARK JONES
上述sql的含义是既要排序又要分组，所以它对应的执行计划就会是SORT GROUP BY:
selectt1.empno,t2.enamefromscott.empt1,emp_tempt2;
从上述显示内容可以看出，现在该sql的执行计划走的是对表EMP_TEMP和表EMP上主键PK_EMP的笛卡儿连接，因为上述sql中没有指定连接条件。此处执行计划的步骤是首先全表扫描表EMP_TEMP，扫描结果记为结果集1；接着对表EMP上的主键PK_EMP做索引快速全扫描，并将扫描结果load进PGA中，然后对结果集1和结果集2做笛卡儿连接，最后笛卡儿连接的结果就是上述sql的最终执行结果。执行计划中关键字“BUFFER SORT”就是表示Oracle会借用PGA并把扫描结果load进去，这样做的好处是省掉了相应的缓存在SGA中所带来的种种额外开销(如持有、释放相关Latch等)。PGA常常用来做排序，这可能就是“BUFFER SORT”中关键字SORT的由来。
需要注意的是，BUFFER SORT不一定会排序，也可能会排序，也可能不会。
看一个sql是否排序，最直观的方法就是查看其统计信息中"sorts(memory)"和"sorts(disk)"的值，如果这两个指标的值大于0，则说明该sql在执行时经历过排序。但遗憾的是，这两个指标对BUFFER SORT而言是不准的，此时我们就需要借助目标sql真实执行计划中"Column Projection Information"部分"keys"的值来判断到底所对应的BUFFER SORT有没有排序。"#keys"的值就表示该执行步骤实际排序列的数量，如果"#keys"值大于0时，则表示该执行步骤确实排过序了。
看如下sql：
setautotracetraceonly zx@MYDB>selectt1.ename,t2.locfromscott.empt1,scott.deptt2; 56rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:2034389985 ----------------------------------------------------------------------------- |Id|Operation |Name|Rows|Bytes|Cost(%cpu)|Time| ----------------------------------------------------------------------------- |0|SELECTSTATEMENT| | 56| 784| 10(0)|00:00:01| |1|MERGEJOINCARTESIAN| | 56| 784| 10(0)|00:00:01| |2|TABLEACCESSFULL|DEPT| 4| 32| 3(0)|00:00:01| |3|BUFFERSORT | | 14| 84| 7(0)|00:00:01| |4|TABLEACCESSFULL|EMP| 14| 84| 2(0)|00:00:01| ----------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 315recursivecalls 0dbblockgets 70consistentgets 11physicalreads 0redosize 1831bytessentviasql*Nettoclient 557bytesreceivedviasql*Netfromclient 5sql*Netroundtripsto/fromclient 7sorts(memory) 0sorts(disk) 56rowsprocessed
注意到上述显示内容中“统计信息”部分的sorts(memory)的值为7，但由于该sql中出现了ID=3的执行步骤“BUFFER SORT”，所以这并不一定能说明该sql在执行时经历过排序。
我们来看一下执行坟墓中id=3的执行步骤“BUFFER SORT”所对应的“#keys”的值：
zx@MYDB>selectsql_id,sql_textfromv$sqlwheresql_text='selectt1.ename,scott.deptt2'; sql_ID sql_TEXT ------------------------------------------------------------------------------------------------------------------------ 3dmxcxk72fwr4 selectt1.ename,scott.deptt2 zx@MYDB>select*fromtable(dbms_xplan.display_cursor('3dmxcxk72fwr4','advanced'));
从上述显示内容中可以看出，Id=3的执行步骤“BUFFER SORT”所对应的“#keys”的值为0，说明该sql在执行“BUFFER SORT”时确实没有排序，排序的数量为0。
这就验证了我们之前提到的观点：统计信息中sorts(memory)和sorts(disk)的值对于BUFFER SORT而言是不准的，Oracle在执行BUFFER SORT时可能不需要排序。
6. UNION/UNION ALL
UNION/UNION ALL表示对两个结果集进行合并，如果它们出现在执行计划中也表示相同的含义。
UNION和UNION ALL的区别是：UNION ALL仅仅是简单地将两个结果集合并，并不做任何额外的处理；而UNION除了将两个结果集简单合并之外，还会对合并后的结果集做排序和去重，即UNION相当于先做UNION ALL，然后再对UNION ALL之后的结果集做SORT UNIQUE
看一个实例：
selectempno,enamefromscott.empunionallselectempno,enamefromemp_temp; EMPNOENAME ---------------------------------------- 7369SMITH ...... 7934MILLER 27rowsselected.
从上述显示内容中可以看出，现在该sql的执行计划走的是对表EMP和EMP_TEMP全表扫描后的结果集的UNION ALL,UNION ALL在执行计划中对应的关键字就是UNION-ALL。表EMP有13条记录，表EMP_TEMP有12条记录，UNION ALL合并后的结果集总是25。
把UNION ALL改为UNION：
zx@MYDB>selectempno,enamefromscott.empunionselectempno,enamefromemp_temp; EMPNOENAME ---------------------------------------- 7369SMITH ...... 7934MILLER 14rowsselected.
从上述显示内容可以看出，现在该sql的执行计划走的是对EMP和EMP_TEMP全表扫描的结果集的UNION，UNION在执行计划中对应的关键字就是"UNION-ALL"和"SORT UNIQUE",即表示UNION相当于在UNION ALL的基础上做排序和去重。表EMP_TEMP的数据全部来源于表EMP，所以这里UNION操作返回结果集的复数就是表EMP的行数14。
7. CONCAT
CONCAT就是IN-List扩展(IN-List Expansion)或OR扩展(OR Expansion)，IN-List扩展/OR扩展在执行计划中对应的关键字是“CONCATENATION”，使用Hint来强制让Oracle走IN-List扩展/OR扩展。
看一下实例：
zx@MYDB>selectempno,enamefromscott.empwhereempnoin(7654,7698,7782); EMPNOENAME ---------------------------------------- 7654MARTIN 7698BLAKE 7782CLARK
从上述显示内容可以看出，现在该sql的执行计划走的是对表EMP和主键索引PK_EMP的IN-List迭代。
使用Hint让Oracle强制走IN-List扩展
zx@MYDB>select/*+USE_CONCAT*/empno,7782); EMPNOENAME ---------------------------------------- 7654MARTIN 7698BLAKE 7782CLARK
从上面显示内容可以看出，Hint失效了，还是走IN-List迭代。使用如下两个事件在当前Session中将IN-List迭代禁掉，并将输入参数no_invalidate的值设为false后重新收集一下统计信息，以便后续再次执行上述sql时不会没用之前走IN-List迭代的执行计划：
zx@MYDB>altersessionsetevents'10142tracenamecontextforever'; Sessionaltered. zx@MYDB>altersessionsetevents'10157tracenamecontextforever'; Sessionaltered. zx@MYDB>execdbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',cascade=>true,method_opt=>'forallcolumnssize1',no_invalidate=>false); PL/sqlproceduresuccessfullycompleted. zx@MYDB>select/*+USE_CONCAT*/empno,7782); EMPNOENAME ---------------------------------------- 7654MARTIN 7698BLAKE 7782CLARK
从上述显示内容中可以看出，现在该sql的执行计划变成了我们想要的IN-List扩展，在执行计划中对应的关键字就是CONCATENATION。这里CONCATENATION的含义就相当于UNION ALL，即上述sql就相当于UNION ALL改写为如下的形式：
select empno,ename from emp where empno=7782
union all
select empno,ename from emp where empno=7698
union all
select empno,ename from emp where empno=7654
8. CONNECT BY
CONNECT BY是Oracle数据库中层次查询(Hierachical Queries)所对应的关键字，如果出现在执行中也是表示同样的含义。
看一下实例：
zx@MYDB>selectempno,ename,job,mgrfromscott.emp; EMPNOENAME JOB MGR ----------------------------------------------------------------------------- 7369SMITH CLERK 7902 7499ALLEN SALESMAN 7698 7521WARD SALESMAN 7698 7566JONES MANAGER 7839 7654MARTIN SALESMAN 7698 7698BLAKE MANAGER 7839 7782CLARK MANAGER 7839 7788SCOTT ANALYST 7566 7839KING PRESIDENT 7844TURNER SALESMAN 7698 7876ADAMS CLERK 7788 7900JAMES CLERK 7698 7902FORD ANALYST 7566 7934MILLER CLERK 7782
从上述内容可以看到KING是PRESIDENT，它所在记录的MGR的值为NULL，表示KING没有上级。
我们执行如下sql，从KING所在的记录开始，将所有人按照上下级关系分成显示出来：
zx@MYDB>selectempno,mgrfromempstartwithempno=7839connectbypriorempno=mgr; EMPNOENAME MGR -------------------------------------------------- 7839KING 7566JONES 7839 7788SCOTT 7566 7876ADAMS 7788 7902FORD 7566 7369SMITH 7902 7698BLAKE 7839 7499ALLEN 7698 7521WARD 7698 7654MARTIN 7698 7844TURNER 7698 7900JAMES 7698 7782CLARK 7839 7934MILLER 7782
查看执行计划：
从上述显示内容可以看出，现在该sql的执行计划走的就是CONNECT BY，在执行计划中我们也能看到CONNECT BY 关键字。
原文链接：https://www.f2er.com/oracle/210397.html