你的位置:首页 > 数据库

[数据库]其他典型的执行计划


 

一、AND-EQUAL(INDEX MERGE)

谓词中多个列等值条件,并且这些列上都有单键值的索引,oracle会合并扫描单个索引的rowid集合。

SQL_ID 3zmhhz4cbg12f, child number 0-------------------------------------select /*+and_equal(a index_emp_DEPTNO IND_EMP_JOB)*/ * from scott.emp a where a.deptno=20 and a.job='SALESMAN' Plan hash value: 2438547776 ------------------------------------------------------------------------------------------------| Id | Operation          | Name       | Rows | Bytes | Cost (%CPU)| Time   |------------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT      |         |    |    |   3 (100)|     ||* 1 | TABLE ACCESS BY INDEX ROWID| EMP       |   1 |  38 |   3  (0)| 00:00:01 ||  2 |  AND-EQUAL         |         |    |    |      |     ||* 3 |  INDEX RANGE SCAN     | IND_EMP_JOB   |   4 |    |   1  (0)| 00:00:01 ||* 4 |  INDEX RANGE SCAN     | INDEX_EMP_DEPTNO |   5 |    |   1  (0)| 00:00:01 |------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):---------------------------------------------------  1 - filter(("A"."JOB"='SALESMAN' AND "A"."DEPTNO"=20))  3 - access("A"."JOB"='SALESMAN')  4 - access("A"."DEPTNO"=20) 

通过先访问IND_EMP_JOB、INDEX_EMP_DEPTNO这两个索引后,在过滤rowid相同的在filter(("A"."JOB"='SALESMAN' AND "A"."DEPTNO"=20)) ,访问表的数据

 


二、INDEX JOIN

index join是针对单表上的不同索引之间的连接

SQL_ID 7qdwg0qwn6tgm, child number 0-------------------------------------select /*+index_join(a index_emp_DEPTNO IND_EMP_JOB)*/ deptno,job from scott.emp a where a.deptno=20 and a.job='SALESMAN' Plan hash value: 2687837119 ---------------------------------------------------------------------------------------| Id | Operation     | Name       | Rows | Bytes | Cost (%CPU)| Time   |---------------------------------------------------------------------------------------|  0 | SELECT STATEMENT  |         |    |    |   3 (100)|     ||* 1 | VIEW       | index$_join$_001 |   1 |  11 |   3 (34)| 00:00:01 ||* 2 |  HASH JOIN    |         |    |    |      |     ||* 3 |  INDEX RANGE SCAN| IND_EMP_JOB   |   1 |  11 |   1  (0)| 00:00:01 ||* 4 |  INDEX RANGE SCAN| INDEX_EMP_DEPTNO |   1 |  11 |   1  (0)| 00:00:01 |--------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------  1 - filter(("A"."JOB"='SALESMAN' AND "A"."DEPTNO"=20))  2 - access(ROWID=ROWID)  3 - access("A"."JOB"='SALESMAN')  4 - access("A"."DEPTNO"=20) 

通过IND_EMP_JOB取出索引信息,通过INDEX_EMP_DEPTNO取出索引信息,这两个索引信息关联,rowid=rowid,在过滤条件filter(("A"."JOB"='SALESMAN' AND "A"."DEPTNO"=20)),取出信息

 


三、VIEW

Oracle处理包含SQL时,根据视图是否能够视图合并(VIEW Merging),对应的执行计划有两种。

视图合并

SQL语句有视图,在语句中会展开,在执行计划中很可能不会出现VIEW,但是又可能还是存在,查看视图合并的例子

 

create or replace view emp_view as select * from scott.emp where deptno=30select * from emp_view where job='SALESMAN'select * from table(dbms_xplan.display_cursor(null,null))SQL_ID dwtdzmud7wdqs, child number 0-------------------------------------select * from emp_view where job='SALESMAN' Plan hash value: 3919104597 ----------------------------------------------------------------------------------------------| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time   |----------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT      |        |    |    |   2 (100)|     ||* 1 | TABLE ACCESS BY INDEX ROWID| EMP      |   1 |  38 |   2  (0)| 00:00:01 ||* 2 |  INDEX RANGE SCAN     | IND_EMP_JENAME |   4 |    |   1  (0)| 00:00:01 |---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------  1 - filter("DEPTNO"=30)  2 - access("JOB"='SALESMAN') 

第一步走了索引 IND_EMP_JENAME  access("JOB"='SALESMAN'),第二部过滤filter("DEPTNO"=30) ,视图已经合并

不做视图合并

执行计划中出现关键字“VIEW”,定义视图中存在ROWNUM

create or replace view emp_view as select * from scott.emp where deptno=30 and rownum<10select * from emp_view where job='SALESMAN'select * from table(dbms_xplan.display_cursor(null,null))SQL_ID dwtdzmud7wdqs, child number 0-------------------------------------select * from emp_view where job='SALESMAN' Plan hash value: 2822310472 --------------------------------------------------------------------------------------------------| Id | Operation           | Name       | Rows | Bytes | Cost (%CPU)| Time   |--------------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT       |         |    |    |   2 (100)|     ||* 1 | VIEW             | EMP_VIEW     |   6 |  522 |   2  (0)| 00:00:01 ||* 2 |  COUNT STOPKEY        |         |    |    |      |     ||  3 |  TABLE ACCESS BY INDEX ROWID| EMP       |   6 |  228 |   2  (0)| 00:00:01 ||* 4 |   INDEX RANGE SCAN     | INDEX_EMP_DEPTNO |   6 |    |   1  (0)| 00:00:01 |-------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------  1 - filter("JOB"='SALESMAN')  2 - filter(ROWNUM<10)  4 - access("DEPTNO"=30)

执行计划中存在VIEW,视图为单独执行

 


四、FILTER

得到一个驱动结果集

根据一定的过滤条件从上述驱动结果集中滤除不满足条件的记录

结果集中剩下的记录就会返回给最终用户或者继续参与下一个执行步骤

select /*+gather_plan_statistics*/ * from scott.emp where deptno in (select /*+no_unnest*/ deptno from scott.dept)select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS +COST'))SQL_ID 4xu8ns03jbd69, child number 0-------------------------------------select /*+gather_plan_statistics*/ * from scott.emp where deptno in (select /*+no_unnest*/ deptno from scott.dept) Plan hash value: 1783302997 -----------------------------------------------------------------------------------------------------| Id | Operation     | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |  A-Time  | Buffers |-----------------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT  |     |   1 |    |   3 (100)|   11 |00:00:00.01 |    9 ||* 1 | FILTER      |     |   1 |    |      |   11 |00:00:00.01 |    9 ||  2 |  TABLE ACCESS FULL| EMP   |   1 |   14 |   3  (0)|   14 |00:00:00.01 |    6 ||* 3 |  INDEX UNIQUE SCAN| PK_DEPT |   3 |   1 |   0  (0)|   2 |00:00:00.01 |    3 |----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------  1 - filter( IS NOT NULL)  3 - access("DEPTNO"=:B1)FILTER访问跟nested loop不同,驱动表在访问被驱动表时,会对关联字段做DISTINCT,如EMP.DEPTNO做DISTINCT为3,实际运行的次数(START)为3次。不是实际行数14的次数。如果是NESTED LOOP就需要14次了以后是NESTED LOOP的例子对比select /*+gather_plan_statistics*/ * from scott.emp where deptno in (select deptno from scott.dept)select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS +COST'))SQL_ID bku72zf75w5rk, child number 0-------------------------------------select /*+gather_plan_statistics*/  * from scott.emp where deptno in (select deptno from scott.dept) Plan hash value: 3074306753 -----------------------------------------------------------------------------------------------------| Id | Operation     | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |  A-Time  | Buffers |-----------------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT  |     |   1 |    |   3 (100)|   11 |00:00:00.01 |   10 ||  1 | NESTED LOOPS   |     |   1 |   14 |   3  (0)|   11 |00:00:00.01 |   10 ||  2 |  TABLE ACCESS FULL| EMP   |   1 |   14 |   3  (0)|   14 |00:00:00.01 |    6 ||* 3 |  INDEX UNIQUE SCAN| PK_DEPT |   14 |   1 |   0  (0)|   11 |00:00:00.01 |    4 |----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------  3 - access("DEPTNO"="DEPTNO")可以清晰看到被驱动表,执行次数是14次

FILTER类型的执行计划实际上是一种改良的嵌套循环连接,他并不像嵌套循环连接那样,驱动结果中的有多少记录就得访问多少次被驱动表

 


五、SORT

  • SORT AGGREGATE
  • SORT UNIQUE
  • SORT JOIN
  • SORT GROUP BY
  • SORT ORDER BY
  • BUFFER SORT

执行计划中出现关键字“SORT”,也不一定意味着就需要排序,如SORT AGGREGATE和BUFFER SORT不一定需要排序

(一)、SORT AGGREGATE

sys@GULL> set autotrace tracesys@GULL> select sum(sal) from scott.emp where deptno=30  2 ;执行计划----------------------------------------------------------Plan hash value: 2829802371-------------------------------------------------------------------------------------------------| Id | Operation          | Name       | Rows | Bytes | Cost (%CPU)| Time   |-------------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT       |         |   1 |   7 |   2  (0)| 00:00:01 ||  1 | SORT AGGREGATE       |         |   1 |   7 |      |     ||  2 |  TABLE ACCESS BY INDEX ROWID| EMP       |   6 |  42 |   2  (0)| 00:00:01 ||* 3 |  INDEX RANGE SCAN     | INDEX_EMP_DEPTNO |   6 |    |   1  (0)| 00:00:01 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------  3 - access("DEPTNO"=30)统计信息----------------------------------------------------------     1 recursive calls     0 db block gets     2 consistent gets     0 physical reads     0 redo size    535 bytes sent via SQL*Net to client    519 bytes received via SQL*Net from client     2 SQL*Net roundtrips to/from client     0 sorts (memory)     0 sorts (disk)     1 rows processed

 

看到sorts(memory)、sorts(disk)为0,无任何排序,但是在执行计划中可以看到sort aggregate

(二)、SORT UNIQUE

sys@GULL> select distinct job from scott.emp where deptno=30 order by job;执行计划----------------------------------------------------------Plan hash value: 2884078981-------------------------------------------------------------------------------------------------| Id | Operation          | Name       | Rows | Bytes | Cost (%CPU)| Time   |-------------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT       |         |   4 |  44 |   4 (50)| 00:00:01 ||  1 | SORT UNIQUE         |         |   4 |  44 |   3 (34)| 00:00:01 ||  2 |  TABLE ACCESS BY INDEX ROWID| EMP       |   6 |  66 |   2  (0)| 00:00:01 ||* 3 |  INDEX RANGE SCAN     | INDEX_EMP_DEPTNO |   6 |    |   1  (0)| 00:00:01 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------  3 - access("DEPTNO"=30)统计信息----------------------------------------------------------     1 recursive calls     0 db block gets     2 consistent gets     0 physical reads     0 redo size    605 bytes sent via SQL*Net to client    519 bytes received via SQL*Net from client     2 SQL*Net roundtrips to/from client     1 sorts (memory)     0 sorts (disk)     3 rows processed

查看sorts(memory)有存在排序

(三)、SORT JOIN

sys@GULL> select /*+use_merge(a b)*/ * from scott.emp a,scott.dept b where a.deptno=b.deptno;已选择11行。执行计划----------------------------------------------------------Plan hash value: 844388907----------------------------------------------------------------------------------------| Id | Operation          | Name  | Rows | Bytes | Cost (%CPU)| Time   |----------------------------------------------------------------------------------------|  0 | SELECT STATEMENT       |     |  14 |  798 |   6 (17)| 00:00:01 ||  1 | MERGE JOIN         |     |  14 |  798 |   6 (17)| 00:00:01 ||  2 |  TABLE ACCESS BY INDEX ROWID| DEPT  |   3 |  57 |   2  (0)| 00:00:01 ||  3 |  INDEX FULL SCAN      | PK_DEPT |   3 |    |   1  (0)| 00:00:01 ||* 4 |  SORT JOIN         |     |  14 |  532 |   4 (25)| 00:00:01 ||  5 |  TABLE ACCESS FULL     | EMP   |  14 |  532 |   3  (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------  4 - access("A"."DEPTNO"="B"."DEPTNO")    filter("A"."DEPTNO"="B"."DEPTNO")统计信息----------------------------------------------------------     3 recursive calls     0 db block gets     16 consistent gets     1 physical reads     0 redo size    1730 bytes sent via SQL*Net to client    519 bytes received via SQL*Net from client     2 SQL*Net roundtrips to/from client     1 sorts (memory)     0 sorts (disk)     11 rows processed

1 sorts (memory)存在排序

 

(五)、SORT GROUP BY

sys@GULL> select job from scott.emp where deptno=30 group by job order by job;执行计划----------------------------------------------------------Plan hash value: 2097038129-------------------------------------------------------------------------------------------------| Id | Operation          | Name       | Rows | Bytes | Cost (%CPU)| Time   |-------------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT       |         |   4 |  44 |   3 (34)| 00:00:01 ||  1 | SORT GROUP BY        |         |   4 |  44 |   3 (34)| 00:00:01 ||  2 |  TABLE ACCESS BY INDEX ROWID| EMP       |   6 |  66 |   2  (0)| 00:00:01 ||* 3 |  INDEX RANGE SCAN     | INDEX_EMP_DEPTNO |   6 |    |   1  (0)| 00:00:01 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------  3 - access("DEPTNO"=30)统计信息----------------------------------------------------------     38 recursive calls     0 db block gets     51 consistent gets     0 physical reads     0 redo size    605 bytes sent via SQL*Net to client    519 bytes received via SQL*Net from client     2 SQL*Net roundtrips to/from client     7 sorts (memory)     0 sorts (disk)     3 rows processed

7 sorts (memory) 通过group by order by,当列为非NULL索引时,是不会排序的

 

(六)、SORT ORDER BY

sys@GULL> select job from scott.emp where deptno=30 order by job;已选择6行。执行计划----------------------------------------------------------Plan hash value: 4045776959-------------------------------------------------------------------------------------------------| Id | Operation          | Name       | Rows | Bytes | Cost (%CPU)| Time   |-------------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT       |         |   6 |  66 |   3 (34)| 00:00:01 ||  1 | SORT ORDER BY        |         |   6 |  66 |   3 (34)| 00:00:01 ||  2 |  TABLE ACCESS BY INDEX ROWID| EMP       |   6 |  66 |   2  (0)| 00:00:01 ||* 3 |  INDEX RANGE SCAN     | INDEX_EMP_DEPTNO |   6 |    |   1  (0)| 00:00:01 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------  3 - access("DEPTNO"=30)统计信息----------------------------------------------------------     1 recursive calls     0 db block gets     2 consistent gets     0 physical reads     0 redo size    620 bytes sent via SQL*Net to client    519 bytes received via SQL*Net from client     2 SQL*Net roundtrips to/from client     1 sorts (memory)     0 sorts (disk)     6 rows processed

1 sorts (memory) order by子句会产生排序,执行计划的体现sort order by

 

(七)、BUFFER SORT

sys@GULL> select * from scott.emp a,scott.dept b  2 ;已选择42行。执行计划----------------------------------------------------------Plan hash value: 2034389985-----------------------------------------------------------------------------| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time   |-----------------------------------------------------------------------------|  0 | SELECT STATEMENT   |   |  42 | 2394 |   9  (0)| 00:00:01 ||  1 | MERGE JOIN CARTESIAN|   |  42 | 2394 |   9  (0)| 00:00:01 ||  2 |  TABLE ACCESS FULL | DEPT |   3 |  57 |   3  (0)| 00:00:01 ||  3 |  BUFFER SORT    |   |  14 |  532 |   6  (0)| 00:00:01 ||  4 |  TABLE ACCESS FULL | EMP |  14 |  532 |   2  (0)| 00:00:01 |-----------------------------------------------------------------------------统计信息----------------------------------------------------------     1 recursive calls     0 db block gets     14 consistent gets     5 physical reads     0 redo size    3449 bytes sent via SQL*Net to client    541 bytes received via SQL*Net from client     4 SQL*Net roundtrips to/from client     1 sorts (memory)     0 sorts (disk)     42 rows processed

buffer sort表示ORACLE会用PGA把扫描结果load进去,这样的好处是省掉相对应的缓存在SGA的开销

buffer sort可能排序,可能也不会的。

还有一种方式查看是否存在排序,在执行计划中存在

Column Projection Information (identified by operation id):

   1 - (#keys=1) "JOB"[VARCHAR2,9]

#keys=1,大于1,说明排序数量为1,如果为0,没有排序

select distinct job from scott.emp where deptno=30 order by job; select * from table(dbms_xplan.display_cursor(null,null,'advanced'))SQL_ID 27vj2ut1x96m3, child number 0-------------------------------------select distinct job from scott.emp where deptno=30 order by job Plan hash value: 2884078981 -------------------------------------------------------------------------------------------------| Id | Operation          | Name       | Rows | Bytes | Cost (%CPU)| Time   |-------------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT       |         |    |    |   4 (100)|     ||  1 | SORT UNIQUE         |         |   4 |  44 |   3 (34)| 00:00:01 ||  2 |  TABLE ACCESS BY INDEX ROWID| EMP       |   6 |  66 |   2  (0)| 00:00:01 ||* 3 |  INDEX RANGE SCAN     | INDEX_EMP_DEPTNO |   6 |    |   1  (0)| 00:00:01 |------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------  1 - SEL$1  2 - SEL$1 / EMP@SEL$1  3 - SEL$1 / EMP@SEL$1 Outline Data-------------  /*+   BEGIN_OUTLINE_DATA   IGNORE_OPTIM_EMBEDDED_HINTS   OPTIMIZER_FEATURES_ENABLE('11.2.0.3')   DB_VERSION('11.2.0.3')   OPT_PARAM('optimizer_dynamic_sampling' 0)   ALL_ROWS   OUTLINE_LEAF(@"SEL$1")   INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))   END_OUTLINE_DATA */ Predicate Information (identified by operation id):---------------------------------------------------  3 - access("DEPTNO"=30) Column Projection Information (identified by operation id):-----------------------------------------------------------  1 - (#keys=1) "JOB"[VARCHAR2,9]  2 - "JOB"[VARCHAR2,9]  3 - "EMP".ROWID[ROWID,10] 

六、UNION/UNION ALL

UNION 是将两个结果集合并,去掉重复并排序。union 先做UNION ALL,在做SORT UNIQUE

select deptno from scott.emp unionselect deptno from scott.dept  select * from table(dbms_xplan.display_cursor(null,null))SQL_ID 9r3apuuwjtbgx, child number 0-------------------------------------select deptno from scott.emp  union select deptno from scott.dept Plan hash value: 3432554835 --------------------------------------------------------------------------------------| Id | Operation     | Name       | Rows | Bytes | Cost (%CPU)| Time   |--------------------------------------------------------------------------------------|  0 | SELECT STATEMENT |         |    |    |   4 (100)|     ||  1 | SORT UNIQUE   |         |  17 |  51 |   4 (75)| 00:00:01 ||  2 |  UNION-ALL    |         |    |    |      |     ||  3 |  INDEX FULL SCAN| INDEX_EMP_DEPTNO |  14 |  42 |   1  (0)| 00:00:01 ||  4 |  INDEX FULL SCAN| PK_DEPT     |   3 |   9 |   1  (0)| 00:00:01 |-------------------------------------------------------------------------------------- 

union all

就是两个结果合并,不做任何处理

select deptno from scott.emp union allselect deptno from scott.dept  select * from table(dbms_xplan.display_cursor(null,null))SQL_ID f42g872sqp9hd, child number 0-------------------------------------select deptno from scott.emp  union all select deptno from scott.dept Plan hash value: 3924871334 -------------------------------------------------------------------------------------| Id | Operation    | Name       | Rows | Bytes | Cost (%CPU)| Time   |-------------------------------------------------------------------------------------|  0 | SELECT STATEMENT |         |    |    |   2 (100)|     ||  1 | UNION-ALL    |         |    |    |      |     ||  2 |  INDEX FULL SCAN| INDEX_EMP_DEPTNO |  14 |  42 |   1  (0)| 00:00:01 ||  3 |  INDEX FULL SCAN| PK_DEPT     |   3 |   9 |   1  (0)| 00:00:01 |-------------------------------------------------------------------------------------

union all比union的性能好很多,尽量用union all

 


七、CONCAT

CONCAT就是 IN-LIST扩展(IN-LIST EXPANSION) 或OR扩展(OR EXPANSION),执行计划中对应CONCATENATION。

select * from scott.emp where job in ('SALESMAN','MANAGER')select * from table(dbms_xplan.display_cursor(null,null))SQL_ID 1sz0ywa9m6k1u, child number 0-------------------------------------select * from scott.emp where job in ('SALESMAN','MANAGER') Plan hash value: 3177582080 -----------------------------------------------------------------------------------------------| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time   |-----------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT       |        |    |    |   2 (100)|     ||  1 | INLIST ITERATOR       |        |    |    |      |     ||  2 |  TABLE ACCESS BY INDEX ROWID| EMP      |   7 |  266 |   2  (0)| 00:00:01 ||* 3 |  INDEX RANGE SCAN     | IND_EMP_JENAME |   7 |    |   1  (0)| 00:00:01 |----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------  3 - access(("JOB"='MANAGER' OR "JOB"='SALESMAN')) 

在未扩展之前,采用的是INLIST ITERATOR,可以指定hint(use_concate),事件设置

alter session set events '10142 trace name context forever'alter session set events '10157 trace name context forever' select /*+use_concat*/  * from scott.emp where job in ('SALESMAN','MANAGER')select * from table(dbms_xplan.display_cursor(null,null))SQL_ID 6u1d9uaruw10d, child number 0-------------------------------------select /*+use_concat*/  * from scott.emp where job in ('SALESMAN','MANAGER') Plan hash value: 1170295018 -----------------------------------------------------------------------------------------------| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time   |-----------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT       |        |    |    |   4 (100)|     ||  1 | CONCATENATION        |        |    |    |      |     ||  2 |  TABLE ACCESS BY INDEX ROWID| EMP      |   3 |  114 |   2  (0)| 00:00:01 ||* 3 |  INDEX RANGE SCAN     | IND_EMP_JENAME |   3 |    |   1  (0)| 00:00:01 ||  4 |  TABLE ACCESS BY INDEX ROWID| EMP      |   4 |  152 |   2  (0)| 00:00:01 ||* 5 |  INDEX RANGE SCAN     | IND_EMP_JENAME |   4 |    |   1  (0)| 00:00:01 |----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------  3 - access("JOB"='MANAGER')  5 - access("JOB"='SALESMAN') 

通常INLIST ITERATOR比CONCATENATION性能好。

 

内容来源:《基于oracle的SQL优化》