天天看點
傳回

Oracle裡另外一些典型的執行計劃

1. AND-EQUAL(INDEX MERGE)

AND-EQUAL又稱為INDEX MERGE,顧名思義，INDEX MERGE就是指如果where條件裡出現了多個針對不同單列的等值條件，并且這些列上都有單鍵值的索引，則Oracle可能會以相應的單個等值條件去分别掃描這些索引；然後Oracle會合并這些掃描單個索引所得到的rowid集合，如果能從這些集合中找到相同的rowid，那麼這個rowid就是目标SQL最終執行結果所對應的rowid。最後，Oracle隻需要用這些rowid回表就能得到目标SQL的最終執行結果。

AND-EQUAL在執行計劃中對應的關鍵字就是“AND-EQUAL”，我們可以使用Hint來強制讓Oracle走AND-EQUAL。

看一個執行個體：

<code>zx@MYDB&gt;</code><code>create</code> <code>table</code> <code>emp_temp </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>scott.emp;</code>

<code>Table</code> <code>created.</code>

<code>zx@MYDB&gt;</code><code>create</code> <code>index</code> <code>idx_mgr </code><code>on</code> <code>emp_temp(mgr);</code>

<code>Index</code> <code>created.</code>

<code>zx@MYDB&gt;</code><code>create</code> <code>index</code> <code>idx_deptno </code><code>on</code> <code>emp_temp(deptno);</code>

<code>zx@MYDB&gt;</code><code>select</code> <code>/*+ and_equal(emp_temp idx_mgr idx_deptno) */ empno,job </code><code>from</code> <code>emp_temp </code><code>where</code> <code>mgr=7902 </code><code>and</code> <code>deptno=20;</code>

<code>     </code><code>EMPNO JOB</code>

<code>---------- ---------------------------</code>

<code>      </code><code>7369 CLERK</code>

<a href="https://s2.51cto.com/wyfs02/M01/8E/01/wKiom1iy0EzBk-5TAABFHiShYhs991.png" target="_blank"></a>

從上述顯示内容中可以看出，現在此SQL的執行計劃走的是對索引IDX_MGR和IDX_DEPTNO的AND-EQUAL。

2. INDEX JOIN

INDEX JOIN很容易引起誤解，因為它并不是指通常意義上針對多表的表連接配接。這裡INDEX JOIN指的是針對單表上的不同索引之間的連接配接。

還以上面的EMP_TEMP為例，已經在列MGR和DEPTNO上分别建立了兩個單鍵值的B*Tree索引，如果此時執行SQL語句“select mgr,deptno from emp_temp”，因為這裡要查詢的列MGR和DEPTNO均可來源于索引IDX_MGR和IDX_DEPTNO(不考慮NULL值)，不用回表，是以除了正常的執行方法之外，Oracle還可以采用如下方法：分别掃描索引IDX_MGR和IDX_DEPTNO，得到的結果集分别記為結果集1和結果集2，然後将結果集1和2做一個連接配接，連接配接條件就是“結果集1.rowid=結果集2.rowid”，這樣得到的最終連接配接結果(不用回表)就是上述SQL的執行結果。

很顯然，針對上述SQL的INDEX JOIN的執行效率是不如我們直接在列MGR和DEPTNO上建一個組合索引，然後直接掃描該組全索引的效率高。INDEX JOIN隻是為CBO提供了一種可選的執行路徑，大多數情況下，它隻是額外多出的一種選擇而已。

看一下例子：

<code>zx@MYDB&gt;</code><code>delete</code> <code>from</code> <code>emp_temp </code><code>where</code> <code>mgr </code><code>is</code> <code>null</code><code>;</code>

<code>1 row deleted.</code>

<code>zx@MYDB&gt;</code><code>commit</code><code>;</code>

<code>Commit</code> <code>complete.</code>

<code>zx@MYDB&gt;</code><code>alter</code> <code>table</code> <code>emp_temp </code><code>modify</code> <code>mgr </code><code>not</code> <code>null</code><code>;</code>

<code>Table</code> <code>altered.</code>

<code>zx@MYDB&gt;</code><code>alter</code> <code>table</code> <code>emp_temp </code><code>modify</code> <code>deptno </code><code>not</code> <code>null</code><code>;</code>

<code>zx@MYDB&gt;</code><code>select</code> <code>mgr,deptno </code><code>from</code> <code>emp_temp;</code>

<code>       </code><code>MGR     DEPTNO</code>

<code>---------- ----------</code>

<code>      </code><code>7839         10</code>

<code>......</code>

<code>      </code><code>7698         30</code>

<code>13 </code><code>rows</code> <code>selected.</code>

<a href="https://s4.51cto.com/wyfs02/M02/8D/FF/wKioL1iy0_XSNPUSAAA5h9Oju58248.png" target="_blank"></a>

從上述顯示内容可以看出，現在目标SQL的執行計劃走的是對索引IDX_MGR和IDX_DEPTNO的HASH JOIN。

3. VIEW

Oracle在處理包含視圖的SQL時，根據該視圖是否能做為視圖合并(View Merging)，其對應的執行計劃有如下兩種形式。

如果可以做視圖合并，則Oracle在執行該SQL時可以直接針對該視圖的基表，此時SQL的執行計劃中很可能不會出現關鍵字“VIEW”(不能完全依賴關鍵字“VIEW”的出現與否來判斷Oracle是否做了視圖合并，因為對于某些SQL而言，即使Oracle已經做了視圖合并但其所對應的執行計劃中可能還會顯示關鍵字“VIEW”)。

如果不能做視圖合并，則Oracle将把該視圖看作一個整體并獨立地執行它，此時SQL的執行計劃中将會出現關鍵字“VIEW”。

看一個執行個體，還是使用上面的EMP_TEMP表：

<code>zx@MYDB&gt;</code><code>create</code> <code>view</code> <code>emp_mgr_view </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>emp_temp </code><code>where</code> <code>job=</code><code>'MANAGER'</code><code>;</code>

<code>View</code> <code>created.</code>

<code>zx@MYDB&gt;</code><code>select</code> <code>empno,sal </code><code>from</code> <code>emp_mgr_view </code><code>where</code> <code>ename=</code><code>'CLARK'</code><code>;</code>

<code>     </code><code>EMPNO        SAL</code>

<code>      </code><code>7782       2450</code>

<a href="https://s3.51cto.com/wyfs02/M01/8D/FF/wKioL1iy1gjTBQf-AABG_0rQQxQ315.png" target="_blank"></a>

從上述顯示内容中可以看出，現在SQL的執行計劃走的是對表EMP_TEMP的全表掃描，并且全表掃描進的過濾查詢條件是filter(("ENAME"='CLARK' AND "JOB"='MANAGER')).顯然這裡Oracle做了視圖合并，直接查詢的視圖EMP_MGR_VIEW的基表EMP_TEMP，并且把針對視圖的where條件推到了視圖的内部，和原先建立視圖時的限制條件做了合并。

現在修改視圖EMP_MGR_VIEW的定義，其建立語句中加入ROWNUM關鍵字，這樣新建立的同名視圖EMP_MGR_VIEW将不能再做視圖合并：

<code>zx@MYDB&gt;</code><code>create</code> <code>or</code> <code>replace</code> <code>view</code> <code>emp_mgr_view </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>emp_temp </code><code>where</code> <code>job=</code><code>'MANAGER'</code> <code>and</code> <code>rownum&lt;10;</code>

<a href="https://s5.51cto.com/wyfs02/M00/8E/01/wKiom1iy2wLxcpdlAAAyGCBwWFM332.png" target="_blank"></a>

從上述顯示内容中可以看出，現在該SQL的執行計劃中包含了關鍵字“VIEW”，即表明這裡Oracle并沒有對視圖EMP_MGR_VIEW做視圖合并，視圖EMP_MGR_VIEW被Oracle當作一個整體來獨立執行。

4. FILTER

FILTER直譯過來就是過濾、篩選的意思，它是一種特殊的執行計劃，所對應的執行過程就是如下三步：

得到一個驅動結果集

根據一定的過濾條件從上述驅動結果集中濾除不滿足條件的記錄

結果集中剩下的記錄就會傳回給最終使用者或者繼續參與一下個執行步驟。

看一個執行個體，還是使用上面的視圖EMP_MGR_VIEW：

<code>zx@MYDB&gt;</code><code>select</code> <code>empno,ename </code><code>from</code> <code>emp </code><code>where</code> <code>empno </code><code>in</code> <code>(</code><code>select</code> <code>empno </code><code>from</code> <code>emp_mgr_view);</code>

<code>     </code><code>EMPNO ENAME</code>

<code>---------- ------------------------------</code>

<code>      </code><code>7566 JONES</code>

<code>      </code><code>7698 BLAKE</code>

<code>      </code><code>7782 CLARK</code>

<a href="https://s4.51cto.com/wyfs02/M02/8E/02/wKiom1iy7xjDABngAABIYpa3jVk703.png" target="_blank"></a>

從上述的顯示内容可以看出，現在該SQL的執行計劃走的是嵌套循環連接配接，并沒有出現我們希望的FILTER類型的執行計劃。這是因為Oracle在這裡做了子查詢展開(Subquery Unnexting)，即把子查詢和它外部的SQL做了合并，轉化成視圖VW_NOS_1和表EMP做連接配接。

這裡使用Hint禁掉子查詢展開後重新執行上述SQL:

<code>zx@MYDB&gt;</code><code>select</code> <code>empno,ename </code><code>from</code> <code>emp </code><code>where</code> <code>empno </code><code>in</code> <code>(</code><code>select</code> <code>/*+ NO_UNNEST */ empno </code><code>from</code> <code>emp_mgr_view);</code>

<a href="https://s4.51cto.com/wyfs02/M01/8E/02/wKiom1iy8CGAa58bAABCstMzNOo732.png" target="_blank"></a>

從上述顯示内容中可以看出，現在該SQL走的就是我們希望的FILTER類型執行計劃。

FILTER類型的執行計劃實際上是種改良的嵌套循環連接配接，它并不像嵌套循環連接配接那樣，驅動結果集中的有多少記錄就得通路多少次被驅動表。

用一個實驗驗證：

<code>zx@MYDB&gt;</code><code>select</code> <code>* </code><code>from</code> <code>t1;</code>

<code>      </code><code>COL1 COL2</code>

<code>---------- ----</code>

<code>         </code><code>1 A</code>

<code>         </code><code>2 B</code>

<code>         </code><code>3 B</code>

<code>zx@MYDB&gt;</code><code>select</code> <code>* </code><code>from</code> <code>t2;</code>

<code>COL2 COL3</code>

<code>---- ------</code>

<code>A    A2</code>

<code>B    B2</code>

<code>D    D2</code>

<code>zx@MYDB&gt;</code><code>select</code> <code>/*+ gather_plan_statistics */ * </code><code>from</code> <code>t1 </code><code>where</code> <code>col2 </code><code>in</code><code>(</code><code>select</code> <code>/*+ no_unnest */ col2 </code><code>from</code> <code>t2);</code>

<a href="https://s5.51cto.com/wyfs02/M01/8E/00/wKioL1iy8lyiTF1gAABc8rjXk4w569.png" target="_blank"></a>

注意到上述顯示内容中id=2的執行步驟所對應的列A-Rows的值為3，id=3的執行步驟所對應的列Starts的值為2，說明雖然全表掃描T1所得到的驅動結果集的數量為3，但走Filter類型的執行計劃時通路被驅動表T2的實際次數卻不是3，而是2.這是因為表T數量雖然是3，但其列COL2的distinct值的數量卻隻有2，是以在用過濾條件“where col2 in(select /*+ no_unnest */ col2 from t2)”去過濾表T1中的資料時，隻用通路兩次表T2就可以了。

5. SORT

SORT就是排序的意思，執行計劃中的SORT通常會以組合的方式出現，這些組合方式包括但不限于如下這幾種：

SORT AGGREGATE

SORT UNIQUE

SORT JOIN

SORT GROUP BY 

SORT ORDER BY

BUFFER SORT

執行計劃中即使出現了關鍵字“SORT”，也不一定意味着就需要排序，比如SORT AGGREGATE和BUFFER SORT就不一定需要排序。

<code>zx@MYDB&gt;</code><code>set</code> <code>autotrace traceonly</code>

<code>zx@MYDB&gt;</code><code>select</code> <code>sum</code><code>(sal) </code><code>from</code> <code>emp_temp </code><code>where</code> <code>job=</code><code>'MANAGER'</code><code>;</code>

<a href="https://s5.51cto.com/wyfs02/M01/8E/03/wKiom1izjWDDKB1RAAAmjXX-OwI746.png" target="_blank"></a>

從上述顯示内容可以看出，現在SQL的執行計劃走的是SORT AGGREGATE，這裡執行的SQL隻是求了一個sum值，很顯然這裡不需要排序的。統計資訊中的sort(memroy)和sort(disk)的值均為0，也說明Oracle在執行此SQL時并沒有做任何排序操作，是以我們說SORT AGGREGATE并不一定需要排序，這其中的關鍵字“SORT”具有一定的迷惑性。

下面再做執行個體：

<code>zx@MYDB&gt;</code><code>set</code> <code>autotrace </code><code>off</code>

<code>zx@MYDB&gt;</code><code>select</code> <code>distinct</code> <code>ename </code><code>from</code> <code>emp_temp </code><code>where</code> <code>job=</code><code>'MANAGER'</code> <code>order</code> <code>by</code> <code>ename;</code>

<code>ENAME</code>

<code>------------------------------</code>

<code>BLAKE</code>

<code>CLARK</code>

<code>JONES</code>

上述SQL的含義是既要排序又要去重，它對應的執行計劃就會是SORT UNIQUE

<a href="https://s5.51cto.com/wyfs02/M02/8E/03/wKiom1izki7jcRn1AAAyqvifC8o863.png" target="_blank"></a>

<code>zx@MYDB&gt;</code><code>select</code> <code>/*+ use_merge(t1 t2) */t1.empno,t1.ename,t2.sal </code><code>from</code> <code>scott.emp t1,emp_temp t2 </code><code>where</code> <code>t1.empno=t2.empno;</code>

<a href="https://s1.51cto.com/wyfs02/M00/8E/04/wKiom1izkuui3hLqAABQZGYtayw080.png" target="_blank"></a>

從上述顯示内容中可以看出，現在該SQL的執行計劃走的是對EMP和EMP_TEMP的排序合并連接配接。SORT JOIN類型的執行計劃通常會出現在排序合并連接配接中，它是排序合并連接配接所對應的執行計劃第一步要做的事情。

再執行如下SQL：

<code>zx@MYDB&gt;</code><code>select</code> <code>ename </code><code>from</code> <code>emp_temp </code><code>where</code> <code>job=</code><code>'MANAGER'</code> <code>order</code> <code>by</code> <code>ename;</code>

上述SQL的含義是隻需要單純的排序，它對應的執行計劃就會是SORT ORDER BY:

<a href="https://s4.51cto.com/wyfs02/M02/8E/02/wKioL1izlDDgzisCAAAx8rJQYXg340.png" target="_blank"></a>

接着執行下面的SQL:

<code>select</code> <code>ename </code><code>from</code> <code>emp_temp </code><code>where</code> <code>job=</code><code>'MANAGER'</code> <code>group</code> <code>by</code> <code>ename </code><code>order</code> <code>by</code> <code>ename;</code>

上述SQL的含義是既要排序又要分組，是以它對應的執行計劃就會是SORT GROUP BY:

<a href="https://s1.51cto.com/wyfs02/M02/8E/04/wKiom1izlLjSEuI7AAA1mFOFEOk454.png" target="_blank"></a>

最後執行如下SQL：

<code>select</code> <code>t1.empno,t2.ename </code><code>from</code> <code>scott.emp t1,emp_temp t2;</code>

<a href="https://s3.51cto.com/wyfs02/M01/8E/04/wKiom1izlXiyXZooAAA_mOQNiBc965.png" target="_blank"></a>

從上述顯示内容可以看出，現在該SQL的執行計劃走的是對表EMP_TEMP和表EMP上主鍵PK_EMP的笛卡兒連接配接，因為上述SQL中沒有指定連接配接條件。此處執行計劃的步驟是首先全表掃描表EMP_TEMP，掃描結果記為結果集1；接着對表EMP上的主鍵PK_EMP做索引快速全掃描，并将掃描結果load進PGA中，然後對結果集1和結果集2做笛卡兒連接配接，最後笛卡兒連接配接的結果就是上述SQL的最終執行結果。執行計劃中關鍵字“BUFFER SORT”就是表示Oracle會借用PGA并把掃描結果load進去，這樣做的好處是省掉了相應的緩存在SGA中所帶來的種種額外開銷(如持有、釋放相關Latch等)。PGA常常用來做排序，這可能就是“BUFFER SORT”中關鍵字SORT的由來。

需要注意的是，BUFFER SORT不一定會排序，也可能會排序，也可能不會。

看一個SQL是否排序，最直覺的方法就是檢視其統計資訊中"sorts(memory)"和"sorts(disk)"的值，如果這兩個名額的值大于0，則說明該SQL在執行時經曆過排序。但遺憾的是，這兩個名額對BUFFER SORT而言是不準的，此時我們就需要借助目标SQL真實執行計劃中"Column Projection Information"部分"keys"的值來判斷到底所對應的BUFFER SORT有沒有排序。"#keys"的值就表示該執行步驟實際排序列的數量，如果"#keys"值大于0時，則表示該執行步驟确實排過序了。

看如下SQL：

<code>set</code> <code>autotrace traceonly</code>

<code>zx@MYDB&gt;</code><code>select</code> <code>t1.ename,t2.loc </code><code>from</code> <code>scott.emp t1,scott.dept t2;</code>

<code>56 </code><code>rows</code> <code>selected.</code>

<code>Execution Plan</code>

<code>----------------------------------------------------------</code>

<code>Plan hash value: 2034389985</code>

<code>-----------------------------------------------------------------------------</code>

<code>| Id  | Operation        | </code><code>Name</code> <code>| </code><code>Rows</code>  <code>| Bytes | Cost (%CPU)| </code><code>Time</code>     <code>|</code>

<code>|   0 | </code><code>SELECT</code> <code>STATEMENT     |       |    56 | 784 |   10   (0)| 00:00:01 |</code>

<code>|   1 |  MERGE </code><code>JOIN</code> <code>CARTESIAN|      |    56 | 784 |   10   (0)| 00:00:01 |</code>

<code>|   2 |   </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code>  <code>| DEPT |    4 |     32 |   3   (0)| 00:00:01 |</code>

<code>|   3 |   BUFFER SORT        |     |    14 |  84 |   7   (0)| 00:00:01 |</code>

<code>|   4 |    </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code> <code>| EMP  |  14 |  84 |   2   (0)| 00:00:01 |</code>

<code>Statistics</code>

<code>    </code><code>315  recursive calls</code>

<code>      </code><code>0  db block gets</code>

<code>     </code><code>70  consistent gets</code>

<code>     </code><code>11  physical reads</code>

<code>      </code><code>0  redo </code><code>size</code>

<code>       </code><code>1831  bytes sent via SQL*Net </code><code>to</code> <code>client</code>

<code>    </code><code>557  bytes received via SQL*Net </code><code>from</code> <code>client</code>

<code>      </code><code>5  SQL*Net roundtrips </code><code>to</code><code>/</code><code>from</code> <code>client</code>

<code>      </code><code>7  sorts (memory)</code>

<code>      </code><code>0  sorts (disk)</code>

<code>     </code><code>56  </code><code>rows</code> <code>processed</code>

注意到上述顯示内容中“統計資訊”部分的sorts(memory)的值為7，但由于該SQL中出現了ID=3的執行步驟“BUFFER SORT”，是以這并不一定能說明該SQL在執行時經曆過排序。

我們來看一下執行墳墓中id=3的執行步驟“BUFFER SORT”所對應的“#keys”的值：

<code>zx@MYDB&gt;</code><code>select</code> <code>sql_id,sql_text </code><code>from</code> <code>v$sql </code><code>where</code> <code>sql_text = </code><code>'select t1.ename,t2.loc from scott.emp t1,scott.dept t2'</code><code>;</code>

<code>SQL_ID           SQL_TEXT</code>

<code>-------------------- ----------------------------------------------------------------------------------------------------</code>

<code>3dmxcxk72fwr4        </code><code>select</code> <code>t1.ename,t2.loc </code><code>from</code> <code>scott.emp t1,scott.dept t2</code>

<code>zx@MYDB&gt;</code><code>select</code> <code>* </code><code>from</code> <code>table</code><code>(dbms_xplan.display_cursor(</code><code>'3dmxcxk72fwr4'</code><code>,0,</code><code>'advanced'</code><code>));</code>

<a href="https://s2.51cto.com/wyfs02/M01/8E/04/wKiom1iznuLjb68NAACwOg85OLc670.png" target="_blank"></a>

從上述顯示内容中可以看出，Id=3的執行步驟“BUFFER SORT”所對應的“#keys”的值為0，說明該SQL在執行“BUFFER SORT”時确實沒有排序，排序的數量為0。

這就驗證了我們之前提到的觀點：統計資訊中sorts(memory)和sorts(disk)的值對于BUFFER SORT而言是不準的，Oracle在執行BUFFER SORT時可能不需要排序。

6. UNION/UNION ALL

UNION/UNION ALL表示對兩個結果集進行合并，如果它們出現在執行計劃中也表示相同的含義。

UNION和UNION ALL的差別是：UNION ALL僅僅是簡單地将兩個結果集合并，并不做任何額外的處理；而UNION除了将兩個結果集簡單合并之外，還會對合并後的結果集做排序和去重，即UNION相當于先做UNION ALL，然後再對UNION ALL之後的結果集做SORT UNIQUE

<code>select</code> <code>empno,ename </code><code>from</code> <code>scott.emp </code><code>union</code> <code>all</code> <code>select</code> <code>empno,ename </code><code>from</code> <code>emp_temp;</code>

<code>      </code><code>7369 SMITH</code>

<code>      </code><code>7934 MILLER</code>

<code>27 </code><code>rows</code> <code>selected.</code>

<a href="https://s1.51cto.com/wyfs02/M00/8E/04/wKiom1izoiHSrKv1AAAk6JPGLJI756.png" target="_blank"></a>

從上述顯示内容中可以看出，現在該SQL的執行計劃走的是對表EMP和EMP_TEMP全表掃描後的結果集的UNION ALL,UNION ALL在執行計劃中對應的關鍵字就是UNION-ALL。表EMP有13條記錄，表EMP_TEMP有12條記錄，UNION ALL合并後的結果集總是25。

把UNION ALL改為UNION：

<code>zx@MYDB&gt;</code><code>select</code> <code>empno,ename </code><code>from</code> <code>scott.emp </code><code>union</code> <code>select</code> <code>empno,ename </code><code>from</code> <code>emp_temp;</code>

<code>14 </code><code>rows</code> <code>selected.</code>

<a href="https://s4.51cto.com/wyfs02/M02/8E/02/wKioL1izpNDg7Sn7AAA5TR7Hb7A524.png" target="_blank"></a>

從上述顯示内容可以看出，現在該SQL的執行計劃走的是對EMP和EMP_TEMP全表掃描的結果集的UNION，UNION在執行計劃中對應的關鍵字就是"UNION-ALL"和"SORT UNIQUE",即表示UNION相當于在UNION ALL的基礎上做排序和去重。表EMP_TEMP的資料全部來源于表EMP，是以這裡UNION操作傳回結果集的複數就是表EMP的行數14。

7. CONCAT

CONCAT就是IN-List擴充(IN-List Expansion)或OR擴充(OR Expansion)，IN-List擴充/OR擴充在執行計劃中對應的關鍵字是“CONCATENATION”，使用Hint來強制讓Oracle走IN-List擴充/OR擴充。

看一下執行個體：

<code>zx@MYDB&gt;</code><code>select</code> <code>empno,ename </code><code>from</code> <code>scott.emp </code><code>where</code> <code>empno </code><code>in</code> <code>(7654,7698,7782);</code>

<code>      </code><code>7654 MARTIN</code>

<a href="https://s3.51cto.com/wyfs02/M01/8E/04/wKiom1izqcOihg7gAAA6xz8NIwA279.png" target="_blank"></a>

從上述顯示内容可以看出，現在該SQL的執行計劃走的是對表EMP和主鍵索引PK_EMP的IN-List疊代。

使用Hint讓Oracle強制走IN-List擴充

<code>zx@MYDB&gt;</code><code>select</code> <code>/*+ USE_CONCAT */empno,ename </code><code>from</code> <code>scott.emp </code><code>where</code> <code>empno </code><code>in</code> <code>(7654,7698,7782);</code>

<a href="https://s3.51cto.com/wyfs02/M01/8E/02/wKioL1izqp-QgGdJAABAMN3yVRg087.png" target="_blank"></a>

從上面顯示内容可以看出，Hint失效了，還是走IN-List疊代。使用如下兩個事件在目前Session中将IN-List疊代禁掉，并将輸入參數no_invalidate的值設為false後重新收集一下統計資訊，以便後續再次執行上述SQL時不會沒用之前走IN-List疊代的執行計劃：

<code>zx@MYDB&gt;</code><code>alter</code> <code>session </code><code>set</code> <code>events </code><code>'10142 trace name context forever'</code><code>;</code>

<code>Session altered.</code>

<code>zx@MYDB&gt;</code><code>alter</code> <code>session </code><code>set</code> <code>events </code><code>'10157 trace name context forever'</code><code>;</code>

<code>zx@MYDB&gt;</code><code>exec</code> <code>dbms_stats.gather_table_stats(ownname=&gt;</code><code>'SCOTT'</code><code>,tabname=&gt;</code><code>'EMP'</code><code>,</code><code>cascade</code><code>=&gt;</code><code>true</code><code>,method_opt=&gt;</code><code>'for all columns size 1'</code><code>,no_invalidate=&gt;</code><code>false</code><code>);</code>

<code>PL/SQL </code><code>procedure</code> <code>successfully completed.</code>

<code>zx@MYDB&gt;</code><code>select</code> <code>/*+ USE_CONCAT */ empno,ename </code><code>from</code> <code>scott.emp </code><code>where</code> <code>empno </code><code>in</code> <code>(7654,7698,7782);</code>

<a href="https://s5.51cto.com/wyfs02/M00/8E/05/wKiom1izr82QrVp8AABjRKRhNxs455.png" target="_blank"></a>

從上述顯示内容中可以看出，現在該SQL的執行計劃變成了我們想要的IN-List擴充，在執行計劃中對應的關鍵字就是CONCATENATION。這裡CONCATENATION的含義就相當于UNION ALL，即上述SQL就相當于UNION ALL改寫為如下的形式：

select empno,ename from emp where empno=7782

union all

select empno,ename from emp where empno=7698

select empno,ename from emp where empno=7654

8. CONNECT BY

CONNECT BY是Oracle資料庫中層次查詢(Hierachical Queries)所對應的關鍵字，如果出現在執行中也是表示同樣的含義。

<code>zx@MYDB&gt;</code><code>select</code> <code>empno,ename,job,mgr </code><code>from</code> <code>scott.emp;</code>

<code>     </code><code>EMPNO ENAME            JOB                  MGR</code>

<code>---------- ------------------------------ --------------------------- ----------</code>

<code>      </code><code>7369 SMITH           CLERK              7902</code>

<code>      </code><code>7499 ALLEN           SALESMAN                7698</code>

<code>      </code><code>7521 WARD               SALESMAN                7698</code>

<code>      </code><code>7566 JONES           MANAGER             7839</code>

<code>      </code><code>7654 MARTIN              SALESMAN                7698</code>

<code>      </code><code>7698 BLAKE           MANAGER             7839</code>

<code>      </code><code>7782 CLARK           MANAGER             7839</code>

<code>      </code><code>7788 SCOTT           ANALYST             7566</code>

<code>      </code><code>7839 KING               PRESIDENT</code>

<code>      </code><code>7844 TURNER              SALESMAN                7698</code>

<code>      </code><code>7876 ADAMS           CLERK              7788</code>

<code>      </code><code>7900 JAMES           CLERK              7698</code>

<code>      </code><code>7902 FORD               ANALYST             7566</code>

<code>      </code><code>7934 MILLER              CLERK              7782</code>

從上述内容可以看到KING是PRESIDENT，它所在記錄的MGR的值為NULL，表示KING沒有上級。

我們執行如下SQL，從KING所在的記錄開始，将所有人按照上下級關系分成顯示出來：

<code>zx@MYDB&gt;</code><code>select</code> <code>empno,ename,mgr </code><code>from</code> <code>emp start </code><code>with</code> <code>empno=7839 </code><code>connect</code> <code>by</code> <code>prior</code> <code>empno=mgr;</code>

<code>     </code><code>EMPNO ENAME               MGR</code>

<code>---------- ------------------------------ ----------</code>

<code>      </code><code>7839 KING</code>

<code>      </code><code>7566 JONES             7839</code>

<code>      </code><code>7788 SCOTT             7566</code>

<code>      </code><code>7876 ADAMS             7788</code>

<code>      </code><code>7902 FORD                 7566</code>

<code>      </code><code>7369 SMITH             7902</code>

<code>      </code><code>7698 BLAKE             7839</code>

<code>      </code><code>7499 ALLEN             7698</code>

<code>      </code><code>7521 WARD                 7698</code>

<code>      </code><code>7654 MARTIN                7698</code>

<code>      </code><code>7844 TURNER                7698</code>

<code>      </code><code>7900 JAMES             7698</code>

<code>      </code><code>7782 CLARK             7839</code>

<code>      </code><code>7934 MILLER                7782</code>

檢視執行計劃：

<a href="https://s1.51cto.com/wyfs02/M00/8E/02/wKioL1izqTrxayy-AAA3IciMX48509.png" target="_blank"></a>

從上述顯示内容可以看出，現在該SQL的執行計劃走的就是CONNECT BY，在執行計劃中我們也能看到CONNECT BY 關鍵字。

     本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1901606，如需轉載請自行聯系原作者

sql Oracle 關系型資料庫 索引 Oracle執行計劃 oracle生成執行計劃 oracle鎖定執行計劃 oracle檢視執行計劃 oracle分析執行計劃
上一篇: Java Collection - 疊代器 Iterator 怎麼使用？有什麼特點？
下一篇: 3D Slicer 開發者必曉ABC

繼續閱讀