<form id="tznrh"><form id="tznrh"><th id="tznrh"></th></form></form>

            我提出了“子操作按先后順序執行,遞歸向下”的解釋執行計劃的基本策略,但是這并不是一個完美的規則,即使遵循基本的“子操作按先后順序執行”,也很容易對執行過程中發生的事情產生錯誤判斷。


            在這部分中,我們將看到幾個例子,其中我們仍將在某些情況下使用“子操作按先后順序執行”,一個BUG的例子使規則看起來是錯誤的,還有一個示例不適用“子操作按先后順序執行”。實際上,有幾種不同的情況并不適用該規則,但是我們必須等到第6部分才能了解更多的情況。



            子查詢更新

            這篇文章中介紹的例子如下:更新,select列表中的標量子查詢和子查詢分解。為了涵蓋盡可能多的例子,我將提供一個帶有計劃的示例語句并給出一些注釋;我不會提供重新創建表和數據的完整代碼。
            第一個例子是帶有子查詢的更新:部分原因是DML計劃很少出現在執行計劃的文本中,另一部分原因是可以將它與我的第二個示例進行對比。示例語句:


            update t1 set
             n1 = (
                    select  max(mod100)
                    from        t2
                    where       t2.id = t1.id
                ),
                n2 = (
                    select  max(trunc100)
                    from        t3
                    where       t3.id = t1.id
                )
            where
                id between 101 and 200
            ;update t1 set



            這個語句有三個直觀的步驟。第一我們需要找到更新的行,對每一行,我們執行t2的子查詢,之后是t3的子查詢。所以執行計劃如下所示:


            --------------------------------------------------------------------------------------- | Id  | Operation                    |Name  | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | UPDATE STATEMENT             |      |   101 |  1212 |  610  (34)| 00:00:04 | |   1 |  UPDATE                      |T1    |       |      |           |         | |*  2 |   INDEX RANGE SCAN           | T1_I1 |  101 |  1212 |     2   (0)|00:00:01 | |   3 |   SORT AGGREGATE             |      |     1 |    7 |            |         | |   4 |    FIRST ROW                  |      |     1 |    7 |     2   (0)| 00:00:01| |*  5 |     INDEX RANGE SCAN (MIN/MAX)|T2_I1 |     1 |     7 |    2   (0)| 00:00:01 | |   6 |   SORT AGGREGATE             |      |     1 |    7 |            |         | |   7 |    FIRST ROW                 |      |     1 |    7 |     2   (0)| 00:00:01| |*  8 |     INDEX RANGE SCAN (MIN/MAX)|T3_I1 |     1 |     7 |    2   (0)| 00:00:01 | ---------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               2 - access("ID">=101 AND"ID"<=200) 5 - access("T2"."ID"=:B1) 8 - access("T3"."ID"=:B1)


            就像你看到的,這個計劃遵循了“子操作按先后順序執行”。第1行的更新操作有三個子操作:第2,3和6行。第一個子操作INDEX RANGE SCAN(索引范圍掃描)幫助我們找到需要更新的行的rowid,第二個子操作生成的子計劃(3-5行)表示子查詢更新列n1,第三個子操作生成的子計劃(6-8行)表示子查詢更新列n2。



            標量子查詢


            對于第二個例子,我將把這個語句轉換成一個查詢語句,該查詢顯示更新操作將如何更改數據。我所要做的就是從更新語句中獲取每個子查詢,并將其作為一個標量子查詢加入select列表中。在這個例子中,我們看到“子操作按先后順序執行”規則幾乎被顛倒過來:


            select     n1, n2,
                (
                    select  max(mod100)
                    from    t2
                    where   t2.id = t1.id
                ) new_n1,
                (
                    select  max(trunc100)
                    from    t3
                    where   t3.id = t1.id
                ) new_n2 from     t1 where     t1.id between 101 and 200 ; -------------------------------------------------------------------------------------- | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |       |   101 |  1212 |     4   (0)| 00:00:01 |
            |   1 |  SORT AGGREGATE              |       |     1 |     7 |            |          |
            |   2 |   FIRST ROW                  |       |     1 |     7 |     2   (0)| 00:00:01 |
            |*  3 |    INDEX RANGE SCAN (MIN/MAX)| T2_I1 |     1 |     7 |     2   (0)| 00:00:01 |
            |   4 |  SORT AGGREGATE              |       |     1 |     7 |            |          |
            |   5 |   FIRST ROW                  |       |     1 |     7 |     2   (0)| 00:00:01 |
            |*  6 |    INDEX RANGE SCAN (MIN/MAX)| T3_I1 |     1 |     7 |     2   (0)| 00:00:01 |
            |   7 |  TABLE ACCESS BY INDEX ROWID | T1    |   101 |  1212 |     4   (0)| 00:00:01 |
            |*  8 |   INDEX RANGE SCAN           | T1_I1 |   101 |       |     2   (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("T2"."ID"=:B1)
               6 - access("T3"."ID"=:B1)
               8 - access("T1"."ID">=101 AND "T1"."ID"<=200)


            這個計劃中可以看出,第0行的select語句有3個子查詢(1,4,7行),但是,當我們直觀感覺肯定會發生的跟實際發生的子操作的順序做比較,我們會看到,最后一個子操作代表了驅動查詢語句的起點。當你語句的select列表中有標量子查詢時,最后一個子操作會被當作第一個子操作進行調用,剩下的表示標量子查詢的子操作,會按照順序被調用。


            BUG演示

            編寫越來越復雜的SQL是不可避免的,只需要一點小改動就能命中與標量子查詢相關的顯示錯誤—如果我們想根據表t2的數據更新t1里的某些行,并根據表t3更新t1里的其他行,我們可能會使用decode()來實現。這里有一個查詢語句用來演示原理和相關bug:


            select     n1,
                decode(mod(n1,4),
                    0,  (
                        select  max(mod100)
                        from    t2
                        where   t2.id = t1.id
                        ),
                        (
                        select  max(trunc100)
                        from    t3
                        where   t3.id = t1.id
                        )
                ) from     t1 where     t1.id between 101 and 200 ; ----------------------------------------------------------------------------------------- | Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                |       |   101 |   808 |     4   (0)| 00:00:01 |
            |   1 |  SORT AGGREGATE                 |       |     1 |     7 |            |          |
            |   2 |   FIRST ROW                     |       |     1 |     7 |     2   (0)| 00:00:01 |
            |*  3 |    INDEX RANGE SCAN (MIN/MAX)   | T2_I1 |     1 |     7 |     2   (0)| 00:00:01 |
            |   4 |     SORT AGGREGATE              |       |     1 |     7 |            |          |
            |   5 |      FIRST ROW                  |       |     1 |     7 |     2   (0)| 00:00:01 |
            |*  6 |       INDEX RANGE SCAN (MIN/MAX)| T3_I1 |     1 |     7 |     2   (0)| 00:00:01 |
            |   7 |  TABLE ACCESS BY INDEX ROWID    | T1    |   101 |   808 |     4   (0)| 00:00:01 |
            |*  8 |   INDEX RANGE SCAN              | T1_I1 |   101 |       |     2   (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("T2"."ID"=:B1)
               6 - access("T3"."ID"=:B1)
               8 - access("T1"."ID">=101 AND "T1"."ID"<=200)


            這個查詢語句中,如果n1是4的倍數則返回t2中的值,否則返回t3的值。我們有理由相信這兩個子查詢在某些方面是“相同的”,但是當我們查看執行計劃時,情況就不一樣了。

            我們首先可以看到最后一個子操作是作為驅動這條查詢語句出現的,但是注意這個查詢語句只剩下另外一個子操作,如果我們運用基本規則,我們很顯然能看到,對t3的子查詢似乎是對t2子查詢的一個子操作。這告訴我們我們必須先執行4-6行生成rowsource,之后才能把結果集向上傳遞給3,2和1行。我們很容易就能寫出另外一個類似這種執行計劃的查詢語句,但是我要說的是,這個執行計劃的執行順序是錯誤的。(12c中也是相同的執行計劃,也同樣是錯誤的)我更早的部分中我解釋過,Oracle會對計劃中的每一行計算depth,我們可以從plan table(或動態性能是圖)中選擇該列生成執行計劃的縮進,但是有時候優化器會計算錯誤的depth值。上面的例子就是其中一種情況,我們需要使用自己的代碼(對parent_id和id列執行connectby查詢)來生成正確的執行計劃。我使用了比寫查詢代碼更簡單的方法來展示正確的執行計劃。用sql_tract跟蹤sql執行過程并用tkprod對trace文件格式化。這里是結果:


            Rows (1st)  Row Source Operation ----------  ---------------------------------------------------         25  SORT AGGREGATE (cr=11 pr=0 pw=0 time=126 us)
                    25   FIRST ROW  (cr=11 pr=0 pw=0 time=83 us cost=2 size=7 card=1)
                    25    INDEX RANGE SCAN (MIN/MAX) T2_I1 (cr=11 pr=0 pw=0 time=74 us cost=2 size=7 card=1)
                    75  SORT AGGREGATE (cr=11 pr=0 pw=0 time=241 us)
                    75   FIRST ROW  (cr=11 pr=0 pw=0 time=166 us cost=2 size=7 card=1)
                    75    INDEX RANGE SCAN (MIN/MAX) T3_I1 (cr=11 pr=0 pw=0 time=140 us cost=2 size=7 card=1)
                   100  TABLE ACCESS BY INDEX ROWID T1 (cr=13 pr=0 pw=0 time=82 us cost=4 size=808 card=101)
                   100   INDEX RANGE SCAN T1_I1 (cr=6 pr=0 pw=0 time=654 us cost=2 size=0 card=101)


            從這個輸出中可以看到,這兩個子查詢在select語句中是相等的,就像前面的select語句中一樣。trace文件中不包含depth信息;它的STAT行只包含id跟parent_id,所以tkprod必須獲取深度并給我們提供了正確的執行計劃。


            tkprof輸出中另一個有用的特性是,我們可以通過”Rows(1st)”列看到對t2的子查詢一共返回了25行,對t3的子查詢一共返回了75行?;仡欁畛醯牟樵?,我們期望(或希望)一個查詢運行25次,其他查詢運行75次,因此在這個例子中,我們有一些確證的證據。Note:實際上,在輸出中我們并沒有足夠的信息來了解實際情況—我們根據我們對數據以及查詢的理解直接跳到了結論:原則上所有的子查詢可能都執行了100次,分別返回了25%和75%的數據,我們實際上需要內部視圖v$sql_plan_statistics中”starts”的統計信息,但是如果我們通過dbms_xplan查詢這個視圖,它同樣會生成錯誤的執行計劃,這也是我們為什么不使用connectby查詢的原因,所以我們必須從這兩個地方(正確的統計信息和正確的執行路徑)去獲取正確的執行計劃。


            子查詢分解

            我提出12c會使用新的轉換將標量子查詢轉換為連接。讓我們回到前面的查詢—包含兩個簡單內聯標量子查詢的查詢,并在11g中模擬這個計劃。我們可以這么做:


            with sq2 as (
                select  /*+ materialize */         t2.id, max(t2.mod100)   new_n1
                from    t2
                where   t2.id between 101 and 200     group by t2.id
            ),
            sq3 as (
                select  /*+ materialize */         t3.id, max(t3.trunc100) new_n2
                from    t3
                where   t3.id between 101 and 200     group by t3.id
            ) select     t1.n1, t1.n2,
                sq2.new_n1,
                sq3.new_n2 from     t1, sq2, sq3 where     t1.id between 101 and 200 and    sq2.id(+) = t1.id and    sq3.id(+) = t1.id
            ; ----------------------------------------------------------------------------------------- | Id | Operation                      | Name     | Rows | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------- |  0 | SELECT STATEMENT               |          |  101 |  6464 |    12   (0)| 00:00:01 |
            |  1 |  TEMP TABLE TRANSFORMATION     |          |      |       |            |          |
            |  2 |   LOAD AS SELECT               | SYS_TEMP |      |       |            |          |
            |  3 |    SORT GROUP BY NOSORT        |          |  101 |   707 |     2   (0)| 00:00:01 |
            |* 4 |     INDEX RANGE SCAN           | T2_I1    |  101 |   707 |     2   (0)| 00:00:01 |
            |  5 |   LOAD AS SELECT               | SYS_TEMP |      |       |            |          |
            |  6 |    SORT GROUP BY NOSORT        |          |  101 |   707 |     2   (0)| 00:00:01 |
            |* 7 |     INDEX RANGE SCAN           | T3_I1    |  101 |   707 |     2   (0)| 00:00:01 |
            |* 8 |   HASH JOIN OUTER              |          |  101 |  6464 |     8   (0)| 00:00:01 |
            |* 9 |    HASH JOIN OUTER             |          |  101 |  3838 |     6   (0)| 00:00:01 |
            | 10 |     TABLE ACCESS BY INDEX ROWID| T1       |  101 |  1212 |     4   (0)| 00:00:01 |
            |*11 |      INDEX RANGE SCAN          | T1_I1    |  101 |       |     2   (0)| 00:00:01 |
            |*12 |     VIEW                       |          |  101 |  2626 |     2   (0)| 00:00:01 |
            | 13 |      TABLE ACCESS FULL         | SYS_TEMP |  101 |   707 |     2   (0)| 00:00:01 |
            |*14 |    VIEW                        |          |  101 |  2626 |     2   (0)| 00:00:01 |
            | 15 |     TABLE ACCESS FULL          | SYS_TEMP |  101 |   707 |     2   (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    4 - access("T2"."ID">=101 AND "T2"."ID"<=200) 7 - access("T3"."ID">=101 AND "T3"."ID"<=200) 8 - access("SQ3"."ID"(+)="T1"."ID") 9 - access("SQ2"."ID"(+)="T1"."ID") 11 - access("T1"."ID">=101 AND "T1"."ID"<=200) 12 - filter("SQ2"."ID"(+)>=101 AND "SQ2"."ID"(+)<=200) 14 - filter("SQ3"."ID"(+)>=101 AND "SQ3"."ID"(+)<=200)



            我在子查詢分解中使用hint/*+ materialize*/強制Oracle建立了兩張內部全局臨時表,表中包含t2和t3中我們所需要的結果,之后剩下的代碼就是將t1和兩個結果集做外連接。實際上我可以去掉hint,Oracle會將“分解的子查詢”進行內聯復制,從而產生一組類似與兩個聚合結果集進行outer hash join的結果集,并保存在會話內存的工作區中。我使用materialize選項只是為了顯示帶有物化子查詢的計劃。如果我們將執行計劃簡化下,那么我們會看到如下:


            -----------------------------------------------------------------------------------------
            | Id | Operation                     |Name     | Rows | Bytes | Cost (%CPU)| Time    |
            -----------------------------------------------------------------------------------------
            |  0 | SELECT STATEMENT              |         |  101 |  6464|    12   (0)| 00:00:01 |
            |  1 |  TEMP TABLE TRANSFORMATION    |         |     |       |           |         |
            |  2 |   LOAD AS SELECT              |SYS_TEMP |      |      |           |         |
            |  5 |   LOAD AS SELECT              |SYS_TEMP |      |      |           |         |
            |* 8 |   HASH JOIN OUTER             |         |  101 |  6464|     8   (0)| 00:00:01 |
            ----------------------------------------------------------------------------------------- 
            



            我想說這并不是我想看到的順序,我更想看到第8行(hash join)作為第0行(select statement)的一個子操作。我們可以看到計劃中三個主要的階段都是temptable transormation的子操作,我們先創建了兩張臨時表,之后做hash join。如果我們展開第2行,我們看到的是聚合表t2;如果我們展開第5行,我們看到的是聚合表t3;如果我們展開第8行,我們看到的是t1和兩個臨時表之間的hashjoin。


            -----------------------------------------------------------------------------------------
            | Id | Operation                      | Name     | Rows | Bytes | Cost (%CPU)| Time     |
            -----------------------------------------------------------------------------------------
            |* 8 |   HASH JOIN OUTER              |          |  101 |  6464 |     8   (0)| 00:00:01 |
            |* 9 |    HASH JOIN OUTER             |          |  101 |  3838 |     6   (0)| 00:00:01 |
            | 10 |     TABLE ACCESS BY INDEX ROWID| T1       |  101 |  1212 |     4   (0)| 00:00:01 |
            |*12 |     VIEW                       |          |  101 |  2626 |     2   (0)| 00:00:01 |
            |*14 |    VIEW                        |          |  101 |  2626 |     2   (0)| 00:00:01 |
            -----------------------------------------------------------------------------------------
            



            除了計劃中執行查詢被向右移動了一點外,在我們真正執行查詢前可能會生成很多數據,就像我們計劃中在select列表中的標量子查詢一樣,在子查詢中仍然遵循基本規則,可以簡單地通過折疊多余的文本進行分析,直到可以看到計劃中每個操作的第一個操作為止。


            結  論

            第5部分我們看了一些例子,基本都是關于一個大查詢中包含一些子查詢,來觀察”子操作按先后順序執行,遞歸向下”這個規則是怎么在復雜的查詢中輸出的。我們也檢查了我們已經找到的一個BUG,這也告訴我們不能盲目的遵循規則,必要時需要交叉引用查詢生成執行計劃的其他方式來獲得正確的計劃。我們同樣也看了一些標量子查詢的特殊例子,作為驅動查詢的操作往往都是最后一個子操作。在下一部分,我們將看到一些查詢案例,其中優化器執行的子查詢確實打破了“子操作按先后順序執行,遞歸向下“的規則,并使你完全錯誤的理解執行計劃。原文鏈接:https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-5-first-child-variations/原文作者:Jonathan Lewis




            | 譯者簡介

            林錦森·沃趣科技數據庫技術專家

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




            沃趣科技,讓客戶用上更好的數據庫技術!
            三分快3