Oracle查詢轉換（五）子查詢展開

子查詢展開是指最佳化器將子查詢轉換為等價的表連線，從而提高查詢效率。如果子查詢前的where條件為：
  ● =、<、>、<=、>=、<>
  ● EXISTS、IN、ANY
  ● NOT EXISTS、NOT IN、ALL
則目標SQL就有可能會做子查詢展開。

 

來看一個簡單的例子：
SQL> select employee_id,last_name from employees where employee_id in (select employee_id from job_history where department_id=80);

Execution Plan
----------------------------------------------------------
Plan hash value: 2277246041

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |     2 |    40 |     4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                     |     2 |    40 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS                 |                     |     2 |    40 |     4  (25)| 00:00:01 |
|   3 |    SORT UNIQUE                 |                     |     2 |    16 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| JOB_HISTORY         |     2 |    16 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | JHIST_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN           | EMP_EMP_ID_PK       |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | EMPLOYEES           |     1 |    12 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

 

這裡的條件是IN（等價於EXISTS、=ANY），最佳化器是將子查詢中的job_history拿出來和外部的employees表做了巢狀迴圈連線。可以使用NO_UNNEST Hint不讓最佳化器做子查詢展開：

 

SQL> select employee_id,last_name from employees where employee_id in (select /*+ no_unnest */employee_id from job_history where department_id=80);

Execution Plan
----------------------------------------------------------
Plan hash value: 3468528558

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |    12 |   109   (0)| 00:00:01 |
|*  1 |  FILTER                      |                   |       |       |            |          |
|   2 |   VIEW                       | index$_join$_001  |   107 |  1284 |     2   (0)| 00:00:01 |
|*  3 |    HASH JOIN                 |                   |       |       |            |          |
|   4 |     INDEX FAST FULL SCAN     | EMP_EMP_ID_PK     |   107 |  1284 |     1   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN     | EMP_NAME_IX       |   107 |  1284 |     1   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID| JOB_HISTORY       |     1 |     8 |     2   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN          | JHIST_EMPLOYEE_IX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "JOB_HISTORY" "JOB_HISTORY" WHERE
              "EMPLOYEE_ID"=:B1 AND "DEPARTMENT_ID"=80))

 

這個時候，子查詢會在最後最後一步執行，且走filter執行計劃，相當於使用子查詢對主查詢的結果逐一過濾，這個效率是比較低的。

如果條件是NOT IN（等價於 NOT EXISTS、<>ALL），則會轉換成對應的反連線。

SQL> select employee_id,last_name from employees where employee_id not in (select employee_id from job_history where department_id=80);

106 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2462043498

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |   105 |  2100 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI              |                     |   105 |  2100 |     4   (0)| 00:00:01 |
|   2 |   VIEW                       | index$_join$_001    |   107 |  1284 |     2   (0)| 00:00:01 |
|*  3 |    HASH JOIN                 |                     |       |       |            |          |
|   4 |     INDEX FAST FULL SCAN     | EMP_EMP_ID_PK       |   107 |  1284 |     1   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN     | EMP_NAME_IX         |   107 |  1284 |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| JOB_HISTORY         |     2 |    16 |     2   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN          | JHIST_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------