【sql調優之執行計劃】in相關的operation

當where條件中有某欄位的in條件，且in後的值為具體值而不是子查詢，並且在該欄位上有索引可以使用時，oracle優化器可能會使用in-lisy iterators操作，而不是concatrnation或者union all。而in可以用多個or來代替，所使用的執行計劃相同，例子：

SQL> select /*+ index(a) */

2 a.deptno

3 from scott.emp a

4 where a.empno in (7339, 9000);

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 2355049923

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |

| 1 | INLIST ITERATOR | | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 7 | 2 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("A"."EMPNO"=7339 OR "A"."EMPNO"=9000)

或者：

SQL> select /*+ index(a) */

2 a.deptno

3 from scott.emp a

4 where a.empno = 7339

5 or a.empno = 9000;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 2355049923

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |

| 1 | INLIST ITERATOR | | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 7 | 2 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("A"."EMPNO"=7339 OR "A"."EMPNO"=9000)

但是如果不是用empno上的索引，則有不同：

SQL> select /*+ full(a)*/

2 a.deptno

3 from scott.emp a

4 where a.empno in (7339, 9000);

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 1 | 7 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("A"."EMPNO"=7339 OR "A"."EMPNO"=9000)

在scott.emp.ename上建立索引，修改一下查詢，使得where clause中的兩個條件欄位上都有索引可以使用，看看執行計劃的改變：

SQL> create index scott.ind_emp_ename on scott.emp(ename);

Index created.

SQL> select

2 a.deptno

3 from scott.emp a

4 where a.empno = 7369

5 or a.ename = 'asd'

6 ;

DEPTNO

----------

20

Execution Plan

----------------------------------------------------------

Plan hash value: 2971452327

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 26 | 3 (0)| 00:00:01 |

| 1 | CONCATENATION | | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IND_EMP_ENAME | 1 | | 1 (0)| 00:00:01 |

|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 1 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("A"."ENAME"='asd')

4 - filter(LNNVL("A"."ENAME"='asd'))

5 - access("A"."EMPNO"=7369)

這裡使用的是CONCATENATION操作（串接）。

可以使用/*+NO_EXPAND */的hint來禁用串接操作，例如：

select /*+NO_EXPAND */

a.deptno

from scott.emp a

where a.empno = 7369

or a.ename = 'asd'

再來看看in子查詢的執行計劃

SQL> select /*+ */* from scott.emp a

2 where a.deptno in

3 (select deptno from scott.dept where rownum < 2 );

…

Execution Plan

----------------------------------------------------------

Plan hash value: 650699563

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 | 204 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 4 | 204 | 4 (0)| 00:00:01 |

| 2 | VIEW | VW_NSO_1 | 1 | 13 | 1 (0)| 00:00:01 |

|* 3 | COUNT STOPKEY | | | | | |

| 4 | INDEX FULL SCAN| PK_DEPT | 4 | 12 | 1 (0)| 00:00:01 |

|* 5 | TABLE ACCESS FULL| EMP | 4 | 152 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter(ROWNUM<2)

5 - filter("A"."DEPTNO" IS NOT NULL AND "A"."DEPTNO"="$nso_col_1")

SQL> select /*+ */* from scott.emp a

2 where a.deptno in

3 (select deptno from scott.dept where rownum < 3 );

…

Execution Plan

----------------------------------------------------------

Plan hash value: 1313905718

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 9 | 459 | 5 (20)| 00:00:01 |

|* 1 | HASH JOIN SEMI | | 9 | 459 | 5 (20)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 |

| 3 | VIEW | VW_NSO_1 | 2 | 26 | 1 (0)| 00:00:01 |

|* 4 | COUNT STOPKEY | | | | | |

| 5 | INDEX FULL SCAN| PK_DEPT | 4 | 12 | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("A"."DEPTNO"="$nso_col_1")

2 - filter("A"."DEPTNO" IS NOT NULL)

4 - filter(ROWNUM<3)

SQL> select /*+ */* from scott.emp a

2 where a.deptno in

3 (select deptno from scott.dept);

….

Execution Plan

----------------------------------------------------------

Plan hash value: 3074306753

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 13 | 533 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 13 | 533 | 3 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("A"."DEPTNO" IS NOT NULL)

3 - access("A"."DEPTNO"="DEPTNO")

可以看出，cbo下in的執行計劃與in內的資料量，欄位上的索引，是否空值等方面都有關係，從謂詞資訊可以看出，不同的操作，訪問路徑和應用條件的順序有所不同。

再看一個常見的子查詢問題：

SQL> select /*+ */* from scott.emp a

2 where a.empno in

3 (select empno from scott.dept where rownum < 2 );

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 812.61 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1609.6 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1259.6 500 30

7566 JONES MANAGER 7839 02-APR-81 2009.6 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1259.6 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2859.6 30

7782 CLARK MANAGER 7839 09-JUN-81 2459.6 10

7788 SCOTT ANALYST 7566 19-APR-87 3009.6 20

7839 KING PRESIDENT 17-NOV-81 5009.6 10

7844 TURNER SALESMAN 7698 08-SEP-81 1509.6 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1109.6 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7900 JAMES CLERK 7698 03-DEC-81 959.6 30

7902 FORD ANALYST 7566 03-DEC-81 3009.6 20

7934 update CLERK 7782 23-JAN-82 1309.6

14 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3458227086

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 38 | 10 (0)| 00:00:01 |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |

|* 3 | FILTER | | | | | |

|* 4 | COUNT STOPKEY | | | | | |

| 5 | INDEX FULL SCAN| PK_DEPT | 1 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( EXISTS (???)

3 - filter(:B1=:B2)

4 - filter(ROWNUM<2)

子查詢中的表並不存在empno欄位，然而這是可以執行的，從謂詞資訊中可以看到有一個exists的operation，這並不正確，但確實影響到了外層的查詢，如果修改成rownum<1，那麼這個查詢將不返回行，需要注意。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-671418/，如需轉載，請註明出處，否則將追究法律責任。

【sql調優之執行計劃】in相關的operation

相關文章