Oracle 查詢轉換之子查詢展開

概念:子查詢展開（Subquery Unnesting）是優化器處理帶子查詢的目標sql的一種優化手段，它是指優化器不再將目標sql中子查詢當作一個獨立的處理單元來單獨執行，而是將該子查詢轉換為它自身和外部查詢之間等價的表連接。這種等價連接轉換要么是將子查詢展開（即將該子查詢中的表，視圖從子查詢中拿出來，然后和外部查詢中的表，視圖做表連接），要么是不拆開但是會把該子查詢轉換為一個內嵌視圖（Inline View）然后再和外部查詢中的表，視圖做表連接。Oracle 會確保子查詢展開所對應的等價連接轉換的正確性，即轉換后的sql和原sql在語義上一定是等價的。當然不是所有的子查詢都能做子查詢展開，有些子查詢是不能做這種等價表連接轉換的，這種情況下oracle就不會對其做子查詢展開，也就是說此時oracle還是會將該子查詢當作一個獨立的處理單元來單獨執行。另外，在oracle10g以后版本中，對于那種不拆開子查詢但是會把該子查詢轉換成一個內嵌視圖的子查詢展開，只有當經過子查詢展開后的等價改寫sql的成本值小于原sql的成本值時，oracle才會對原sql執行子查詢展開

    子查詢展開通常都會提高原sql的執行效率，因為如果原sql不做子查詢展開，那么通常情況下該子查詢就會在其執行計劃的最后一步才被執行，并且會走filter類型的執行計劃，這也就意味著對于外部查詢所在結果集的沒一條記錄，該子查詢就會被執行多少次，這種執行方式的執行效率通常情況不會太高，尤其在子查詢中包含兩個或兩個以上表連接時，此時做子查詢展開后的執行效率往往會比走filter類型的執行計劃高很多。

    Oracle 數據庫里子查詢前where條件如果是如下這些條件之一，那么這種類型的目標sql在滿足了一定條件后就可以做子查詢展開,

single-row,exists，not exists,in ,not in,any，all。

范例1：

SQL> set lines 200 pagesize 1000
in寫法：
SELECT t1.cust_last_name, t1.cust_id
  FROM customers t1
 WHERE t1.cust_id IN
  4         (SELECT t2.cust_id FROM sales t2 WHERE t2.amount_sold > 700)
  5  ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2448612695
----------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     | |  7059 |   158K| |  1583   (1)| 00:00:20 | | |
|*  1 |  HASH JOIN SEMI      | |  7059 |   158K|  1360K|  1583   (1)| 00:00:20 | | |
|   2 |   TABLE ACCESS FULL  | CUSTOMERS | 55500 |   704K| |   405   (1)| 00:00:05 | | |
|   3 |   PARTITION RANGE ALL| |   560K|  5469K| |   526   (2)| 00:00:07 |     1 |    28 |
|*  4 |    TABLE ACCESS FULL | SALES |   560K|  5469K| |   526   (2)| 00:00:07 |     1 |    28 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
any等價寫法：
SELECT t1.cust_last_name, t1.cust_id
  FROM customers t1
 WHERE t1.cust_id = ANY
  4   (SELECT t2.cust_id FROM sales t2 WHERE t2.amount_sold > 700)
  5  ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2448612695
----------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     | |  7059 |   158K| |  1583   (1)| 00:00:20 | | |
|*  1 |  HASH JOIN SEMI      | |  7059 |   158K|  1360K|  1583   (1)| 00:00:20 | | |
|   2 |   TABLE ACCESS FULL  | CUSTOMERS | 55500 |   704K| |   405   (1)| 00:00:05 | | |
|   3 |   PARTITION RANGE ALL| |   560K|  5469K| |   526   (2)| 00:00:07 |     1 |    28 |
|*  4 |    TABLE ACCESS FULL | SALES |   560K|  5469K| |   526   (2)| 00:00:07 |     1 |    28 |
----------------------------------------------------------------------------------------------------------
exists等價寫法：
SELECT t1.cust_last_name, t1.cust_id
  FROM customers t1
 WHERE t1.cust_id = ANY
  4   (SELECT t2.cust_id FROM sales t2 WHERE t2.amount_sold > 700)
  5  ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2448612695
----------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     | |  7059 |   158K| |  1583   (1)| 00:00:20 | | |
|*  1 |  HASH JOIN SEMI      | |  7059 |   158K|  1360K|  1583   (1)| 00:00:20 | | |
|   2 |   TABLE ACCESS FULL  | CUSTOMERS | 55500 |   704K| |   405   (1)| 00:00:05 | | |
|   3 |   PARTITION RANGE ALL| |   560K|  5469K| |   526   (2)| 00:00:07 |     1 |    28 |
|*  4 |    TABLE ACCESS FULL | SALES |   560K|  5469K| |   526   (2)| 00:00:07 |     1 |    28 |
----------------------------------------------------------------------------------------------------------

不展開，顯然不合理，sales表要執行很多次：
SELECT t1.cust_last_name, t1.cust_id
  FROM customers t1
 WHERE t1.cust_id IN (SELECT /*+ no_unnest */
                       t2.cust_id
                        FROM sales t2
                       WHERE t2.amount_sold > 700)

子查詢展開后，變成hash 半連接：

等價寫法：（如果cust_id是唯一鍵值）可以轉換為內連接：

SELECT t1.cust_last_name, t1.cust_id
  FROM customers t1, sales t2
 WHERE t1.cust_id= t2.cust_id
   AND t2.amount_sold > 700

如果是not in，則會轉換為hash 反連接：

SQL> set autot trace
SELECT t1.cust_last_name, t1.cust_id
  FROM customers t1
 WHERE t1.cust_id not in 
  4   (SELECT t2.cust_id FROM sales t2 WHERE t2.amount_sold > 700);
Execution Plan
----------------------------------------------------------
Plan hash value: 2850422635
----------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     | | 48441 |  1088K| |  1583   (1)| 00:00:20 | | |
|*  1 |  HASH JOIN ANTI      | | 48441 |  1088K|  1360K|  1583   (1)| 00:00:20 | | |
|   2 |   TABLE ACCESS FULL  | CUSTOMERS | 55500 |   704K| |   405   (1)| 00:00:05 | | |
|   3 |   PARTITION RANGE ALL| |   560K|  5469K| |   526   (2)| 00:00:07 |     1 |    28 |
|*  4 |    TABLE ACCESS FULL | SALES |   560K|  5469K| |   526   (2)| 00:00:07 |     1 |    28 |
----------------------------------------------------------------------------------------------------------

把子查詢轉換成內聯視圖：

SELECT t1.cust_last_name, t1.cust_id
  FROM customers t1
 WHERE t1.cust_id NOT IN
       (SELECT t2.cust_id
          FROM sales t2, products t3
         WHERE t2.prod_id = t3.prod_id and t2.amount_sold > 700)
Execution Plan
----------------------------------------------------------
Plan hash value: 1272298339
--------------------------------------------------------------------------------------------------------------
| Id  | Operation       | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |     | 48441 |1229K|     |1665   (1)| 00:00:20 |     |     |
|*  1 |  HASH JOIN ANTI        |     | 48441 |1229K|1360K|1665   (1)| 00:00:20 |     |     |
|   2 |   TABLE ACCESS FULL    | CUSTOMERS   | 55500 | 704K|     | 405   (1)| 00:00:05 |     |     |
|   3 |   VIEW       | VW_NSO_1    | 560K|7110K|     | 529   (2)| 00:00:07 |     |     |
|*  4 |    HASH JOIN       |     | 560K|9844K|     | 529   (2)| 00:00:07 |     |     |
|   5 |     INDEX FULL SCAN    | PRODUCTS_PK |  72 | 288 |     |   1   (0)| 00:00:01 |     |     |
|   6 |     PARTITION RANGE ALL|     | 560K|7657K|     | 526   (2)| 00:00:07 |   1 |  28 |
|*  7 |      TABLE ACCESS FULL | SALES     | 560K|7657K|     | 526   (2)| 00:00:07 |   1 |  28 |
--------------------------------------------------------------------------------------------------------------

這里oracle把子查詢轉換成內聯視圖 VM_NSO_1，然后再和外部查詢中的表customers做hash半連接。

等價：

SELECT t1.cust_last_name, t1.cust_id
  FROM customers t1,
       (SELECT t2.cust_id
          FROM sales t2, products t3
         WHERE t2.prod_id = t3.prod_id
           AND t2.amount_sold > 700) vm_nso_1
 WHERE t1.cust_id semi = vm_nso_1.cust_id

子查詢是否能夠做子查詢展開取決于如下兩個條件：

    子查詢展開所對應的等價改寫sql和原sql在語義上一定要完全等價的，如果改寫后的sql和原sql并不一定能保持語義上的完全等價，這種類型的子查詢就不能做子查詢展開。

    對于不能拆開的子查詢但是會把它轉換為一個內嵌視圖的子查詢展開，只有經過子查詢展開的等價改寫sql成本值小于原sql的成本值。oracle才會對目標sql執行子查詢展開。

對于子查詢展開的第一種情形（即將子查詢展開，把該子查詢中的表，視圖從子查詢中拿出來，然后和外部查詢中表，視圖做表連接），即使在oracle  10g以后的版本中，oracle也不會考慮子查詢展開的成本，即oracle此時會認為這種情形下子查詢展開的效率始終比不展開的效率高，這就意味著如果目標sql滿足子查詢展開的第一種情形。則oracle始終會做子查詢展開，而不管經過子查詢展開后的等價sql的成本值是否小于原sql的成本值。