程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 其他典型的執行計劃，典型執行計劃

其他典型的執行計劃，典型執行計劃

編輯：Oracle教程

其他典型的執行計劃，典型執行計劃

一、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=30

select * 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<10

select * 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 trace
sys@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 
union
select 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 all
select 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優化》