執行計劃-6：推入子查詢

--------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |    19 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | MIN_MAX |     1 |    19 |     2 |
|*  2 |   INDEX UNIQUE SCAN            | MM_PK   |     1 |       |     1 |
|   3 |    SORT AGGREGATE              |         |     1 |    11 |       |
|*  4 |     TABLE ACCESS BY INDEX ROWID| MIN_MAX |     1 |    11 |     3 |
|*  5 |      INDEX RANGE SCAN          | MM_PK   |    10 |       |     2 |
--------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    19 |     5 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | MIN_MAX |     1 |    19 |     2 |
|*  2 |   INDEX UNIQUE SCAN           | MM_PK   |     1 |       |     1 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| MIN_MAX |     1 |    11 |     3 |
|*  4 |     INDEX RANGE SCAN          | MM_PK   |     2 |       |     2 |
-------------------------------------------------------------------------

實際上”第一個子操作先執行”對於第一個計劃來說是正確的，它會從下到上執行，但是對於第二個計劃來說是行不通的，由於優化器呈現的是“推入”過的篩選子查詢，所以計劃的形狀被扭曲了，也就是說，在儘可能早的時間執行。 很難從計劃的主體中看出這是否已經發生了，你真的需要檢查計劃的謂詞部分，甚至可以參考原始語句來理解發生了什麼。

這裡按順序展示了上面兩個執行計劃的謂詞部分：

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MM1"."ID_PARENT"=100 AND "MM1"."ID_CHILD"= (SELECT
              MAX("MM2"."ID_CHILD") FROM "MIN_MAX" "MM2" 
              WHERE "MM2"."ID_PARENT"=100 AND "STATUS"=1))
   4 - filter("STATUS"=1)
   5 - access("MM2"."ID_PARENT"=100)
 Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MM1"."ID_PARENT"=100 AND "MM1"."ID_CHILD"=1)
       filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM
              "MIN_MAX" "MM2" WHERE "MM2"."ID_PARENT"=100 AND "STATUS"=1 AND
              ("MM2"."ID_CHILD"=TRUNC(:B1) OR "MM2"."ID_CHILD"=TRUNC(:B2+1))))
   3 - filter("STATUS"=1)
   4 - access("MM2"."ID_PARENT"=100)
       filter("MM2"."ID_CHILD"=TRUNC(:B1) OR
              "MM2"."ID_CHILD"=TRUNC(:B2+1))

注意第2行出現的關鍵差異——在兩個執行計劃中的操作都是索引唯一掃描。 第一個計劃只報告一個訪問謂詞(包含一個子查詢)，而第二個計劃同時顯示一個訪問謂詞(它由針對索引列的簡單謂片語成)和一個篩選謂詞(它由一個子查詢組成)。

對於一個索引的操作，訪問謂詞告訴我們範圍掃描的起始和結束的值，過濾謂詞告訴我們需要怎麼針對每一個在範圍內的索引條目的進行驗證。看過了謂詞部分的不同之後我們可以解釋計劃：

First plan：

第5行我們對mm_pk索引進行範圍掃描，第4行訪問min_max表的某些行，在第3行聚合這些行並且找到id_child列的最大值，之後在第2行我們使用這個值對mm_pk進行索引唯一掃描，最後第1行中我們訪問表min_max並找到我們所需要的這一行。操作的順序為:5，4，3，2，1，0.

Second plan：

第2行我們使用提供的謂詞對mm_pk進行索引唯一掃描，之後在第4行中使用第一個索引掃描返回的值進行範圍掃描，再之後在第3行中訪問min_max表查詢是否有符合條件的行，如果有就在第1行返回最多一行的rowid。操作的順序為：2，4，3，1，0。

下面展示原始查詢語句可能更易於理解執行的過程。這些語句只是為了說明這小節表達的觀點，所以並不需要理解語句為什麼要這麼寫。

select
small_vc
 from min_max mm1
 where mm1.id_parent = 100
 and mm1.id_child = (
 select max(mm2.id_child)
 from   min_max mm2
 where  mm2.id_parent = 100
 and    status = 1
)
;
 select
 small_vc
 from min_max mm1
 where mm1.id_parent = 100
 and mm1.id_child = 1 
 and exists (
 select /*+ no_unnest push_subq */
                       null
 from   min_max mm2
 where  mm2.id_parent = 100
 and    (
           mm2.id_child = trunc(mm1.id_child) 
        or mm2.id_child = trunc(mm1.id_child + 1) 
       ) 
 and    status = 1 
) 
;

有了前面的兩個查詢語句，就更容易看到第一個可以通過先執行子查詢然後使用結果來驅動主查詢;第二個查詢必須先執行主查詢，然後每個步驟都需要停止並檢測子查詢的存在性。

你會注意到我在第二個查詢優化器使用hint，阻止優化器做任何轉換。我使用了no_unnest塊來阻止子查詢展開，然後我告訴優化器儘可能早的執行子查詢;後一種hint影響了執行計劃的形狀(即操作的順序)。如果我把hint改為no_push_subq(阻止子查詢推入)，計劃就會變為：

------------------------------------------------------------------------
 | Id  | Operation                    | Name    | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |         |     1 |    19 |     5 |
 |*  1 |  FILTER                      |         |       |       |       |
 |   2 |   TABLE ACCESS BY INDEX ROWID| MIN_MAX |     1 |    19 |     2 |
 |*  3 |    INDEX UNIQUE SCAN         | MM_PK   |     1 |       |     1 |
 |*  4 |   TABLE ACCESS BY INDEX ROWID| MIN_MAX |     1 |    11 |     3 |
 |*  5 |    INDEX RANGE SCAN          | MM_PK   |     2 |       |     2 |
------------------------------------------------------------------------
 Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ NO_PUSH_SUBQ NO_UNNEST */ 0 FROM
          "MIN_MAX" "MM2" WHERE "MM2"."ID_PARENT"=100 AND "STATUS"=1 AND
          ("MM2"."ID_CHILD"=TRUNC(:B1) OR "MM2"."ID_CHILD"=TRUNC(:B2+1))))
   3 - access("MM1"."ID_PARENT"=100 AND "MM1"."ID_CHILD"=1)
   4 - filter("STATUS"=1)
   5 - access("MM2"."ID_PARENT"=100)
       filter("MM2"."ID_CHILD"=TRUNC(:B1) OR
           "MM2"."ID_CHILD"=TRUNC(:B2+1))

有了這個更改，“第一個子操作先執行”規則又可以工作了:第1行的過濾操作呼叫第2行來返回行，然後呼叫第4行來決定是否保留該行。我們可以通過檢查謂詞部分來確認這種解釋，在該部分中，我們可以看到子查詢的文字現在作為過濾器謂詞出現在第1行。

通過使用' outline '選項作為對dbms_xplan呼叫的格式化引數，您可以在檢查已推入的子查詢方面獲得一些幫助，因為這將允許您檢視大綱/SQL計劃基線中是否有與計劃相關的push_subq()提示。

這個例子向您展示了推入子查詢的潛在好處——當我們阻塞推入時，Oracle直到它訪問了表並獲得了我們不需要的行之後才執行子查詢;另一方面，當我們推入子查詢時，Oracle會在索引項可用時立即執行它，從而避免了冗餘表訪問。

Conclusion

在所有這些計劃與“第一個子操作先執行”不匹配的例子中，我們已經看到了推入的子查詢——在某些情況下，我不得不在SQL中加入一個顯式的hint來建立我想要演示的計劃型別。當你試圖為包含多個子查詢的語句解釋執行計劃時，請注意推入的子查詢可能會導致“正常”形狀的失真。你可以仔細通過謂詞部分檢查,能否看到一些子查詢文字作為一個過濾謂詞出現。

原文連結:

https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-6-pushed-subqueries/

原文作者:

Jonathan Lewis

| 譯者簡介

林錦森·沃趣科技資料庫技術專家

沃趣科技資料庫工程師，多年從事Oracle資料庫，較豐富的故障處理、效能調優、資料遷移及備份恢復經驗。

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