溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
在之前的文章里寫了Oracle里常見的執行計劃，可以參考文章：http://hbxztc.blog.51cto.com/1587495/1901416，這篇文章里介紹的是其他的一些典型的執行計劃。
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。
看一個實例：
zx@MYDB>create table emp_temp as select * from scott.emp; Table created. zx@MYDB>create index idx_mgr on emp_temp(mgr); Index created. zx@MYDB>create index idx_deptno on emp_temp(deptno); Index created. zx@MYDB>select /*+ and_equal(emp_temp idx_mgr idx_deptno) */ empno,job from emp_temp where mgr=7902 and deptno=20; EMPNO JOB ---------- --------------------------- 7369 CLERK
從上述顯示內容中可以看出，現在此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提供了一種可選的執行路徑，大多數情況下，它只是額外多出的一種選擇而已。
看一下例子：
zx@MYDB>delete from emp_temp where mgr is null; 1 row deleted. zx@MYDB>commit; Commit complete. zx@MYDB>alter table emp_temp modify mgr not null; Table altered. zx@MYDB>alter table emp_temp modify deptno not null; Table altered. zx@MYDB>select mgr,deptno from emp_temp; MGR DEPTNO ---------- ---------- 7839 10 ...... 7698 30 13 rows selected.
從上述顯示內容可以看出，現在目標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表：
zx@MYDB>create view emp_mgr_view as select * from emp_temp where job='MANAGER'; View created. zx@MYDB>select empno,sal from emp_mgr_view where ename='CLARK'; EMPNO SAL ---------- ---------- 7782 2450
從上述顯示內容中可以看出，現在SQL的執行計劃走的是對表EMP_TEMP的全表掃描，并且全表掃描進的過濾查詢條件是filter(("ENAME"='CLARK' AND "JOB"='MANAGER')).顯然這里Oracle做了視圖合并，直接查詢的視圖EMP_MGR_VIEW的基表EMP_TEMP，并且把針對視圖的where條件推到了視圖的內部，和原先創建視圖時的限制條件做了合并。
現在修改視圖EMP_MGR_VIEW的定義，其創建語句中加入ROWNUM關鍵字，這樣新創建的同名視圖EMP_MGR_VIEW將不能再做視圖合并：
zx@MYDB>create or replace view emp_mgr_view as select * from emp_temp where job='MANAGER' and rownum<10; View created. zx@MYDB>select empno,sal from emp_mgr_view where ename='CLARK'; EMPNO SAL ---------- ---------- 7782 2450
從上述顯示內容中可以看出，現在該SQL的執行計劃中包含了關鍵字“VIEW”，即表明這里Oracle并沒有對視圖EMP_MGR_VIEW做視圖合并，視圖EMP_MGR_VIEW被Oracle當作一個整體來獨立執行。
4. FILTER
FILTER直譯過來就是過濾、篩選的意思，它是一種特殊的執行計劃，所對應的執行過程就是如下三步：
得到一個驅動結果集
根據一定的過濾條件從上述驅動結果集中濾除不滿足條件的記錄
結果集中剩下的記錄就會返回給最終用戶或者繼續參與一下個執行步驟。
看一個實例，還是使用上面的視圖EMP_MGR_VIEW：
zx@MYDB>select empno,ename from emp where empno in (select empno from emp_mgr_view); EMPNO ENAME ---------- ------------------------------ 7566 JONES 7698 BLAKE 7782 CLARK
從上述的顯示內容可以看出，現在該SQL的執行計劃走的是嵌套循環連接，并沒有出現我們希望的FILTER類型的執行計劃。這是因為Oracle在這里做了子查詢展開(Subquery Unnexting)，即把子查詢和它外部的SQL做了合并，轉化成視圖VW_NOS_1和表EMP做連接。
這里使用Hint禁掉子查詢展開后重新執行上述SQL:
zx@MYDB>select empno,ename from emp where empno in (select /*+ NO_UNNEST */ empno from emp_mgr_view); EMPNO ENAME ---------- ------------------------------ 7566 JONES 7698 BLAKE 7782 CLARK
從上述顯示內容中可以看出，現在該SQL走的就是我們希望的FILTER類型執行計劃。
FILTER類型的執行計劃實際上是種改良的嵌套循環連接，它并不像嵌套循環連接那樣，驅動結果集中的有多少記錄就得訪問多少次被驅動表。
用一個實驗驗證：
zx@MYDB>select * from t1; COL1 COL2 ---------- ---- 1 A 2 B 3 B zx@MYDB>select * from t2; COL2 COL3 ---- ------ A A2 B B2 D D2 zx@MYDB>select /*+ gather_plan_statistics */ * from t1 where col2 in(select /*+ no_unnest */ col2 from t2); COL1 COL2 ---------- ---- 1 A 2 B 3 B
注意到上述顯示內容中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就不一定需要排序。
看一個實例，還是使用上面的EMP_TEMP表：
zx@MYDB>set autotrace traceonly zx@MYDB>select sum(sal) from emp_temp where job='MANAGER';
從上述顯示內容可以看出，現在SQL的執行計劃走的是SORT AGGREGATE，這里執行的SQL只是求了一個sum值，很顯然這里不需要排序的。統計信息中的sort(memroy)和sort(disk)的值均為0，也說明Oracle在執行此SQL時并沒有做任何排序操作，所以我們說SORT AGGREGATE并不一定需要排序，這其中的關鍵字“SORT”具有一定的迷惑性。
下面再做實例：
zx@MYDB>set autotrace off zx@MYDB>select distinct ename from emp_temp where job='MANAGER' order by ename; ENAME ------------------------------ BLAKE CLARK JONES
上述SQL的含義是既要排序又要去重，它對應的執行計劃就會是SORT UNIQUE
zx@MYDB>select /*+ use_merge(t1 t2) */t1.empno,t1.ename,t2.sal from scott.emp t1,emp_temp t2 where t1.empno=t2.empno;
從上述顯示內容中可以看出，現在該SQL的執行計劃走的是對EMP和EMP_TEMP的排序合并連接。SORT JOIN類型的執行計劃通常會出現在排序合并連接中，它是排序合并連接所對應的執行計劃第一步要做的事情。
再執行如下SQL：
zx@MYDB>select ename from emp_temp where job='MANAGER' order by ename; ENAME ------------------------------ BLAKE CLARK JONES
上述SQL的含義是只需要單純的排序，它對應的執行計劃就會是SORT ORDER BY:
接著執行下面的SQL:
select ename from emp_temp where job='MANAGER' group by ename order by ename; ENAME ------------------------------ BLAKE CLARK JONES
上述SQL的含義是既要排序又要分組，所以它對應的執行計劃就會是SORT GROUP BY:
最后執行如下SQL：
select t1.empno,t2.ename from scott.emp t1,emp_temp t2;
從上述顯示內容可以看出，現在該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：
set autotrace traceonly zx@MYDB>select t1.ename,t2.loc from scott.emp t1,scott.dept t2; 56 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2034389985 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 56 | 784 | 10 (0)| 00:00:01 | | 1 | MERGE JOIN CARTESIAN| | 56 | 784 | 10 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 32 | 3 (0)| 00:00:01 | | 3 | BUFFER SORT | | 14 | 84 | 7 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 14 | 84 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 315 recursive calls 0 db block gets 70 consistent gets 11 physical reads 0 redo size 1831 bytes sent via SQL*Net to client 557 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 56 rows processed
注意到上述顯示內容中“統計信息”部分的sorts(memory)的值為7，但由于該SQL中出現了ID=3的執行步驟“BUFFER SORT”，所以這并不一定能說明該SQL在執行時經歷過排序。
我們來看一下執行墳墓中id=3的執行步驟“BUFFER SORT”所對應的“#keys”的值：
zx@MYDB>select sql_id,sql_text from v$sql where sql_text = 'select t1.ename,t2.loc from scott.emp t1,scott.dept t2';
SQL_ID SQL_TEXT
-------------------- ----------------------------------------------------------------------------------------------------
3dmxcxk72fwr4 select t1.ename,t2.loc from scott.emp t1,scott.dept t2
zx@MYDB>select * from table(dbms_xplan.display_cursor('3dmxcxk72fwr4',0,'advanced'));
從上述顯示內容中可以看出，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
看一個實例：
select empno,ename from scott.emp union all select empno,ename from emp_temp; EMPNO ENAME ---------- ------------------------------ 7369 SMITH ...... 7934 MILLER 27 rows selected.
從上述顯示內容中可以看出，現在該SQL的執行計劃走的是對表EMP和EMP_TEMP全表掃描后的結果集的UNION ALL,UNION ALL在執行計劃中對應的關鍵字就是UNION-ALL。表EMP有13條記錄，表EMP_TEMP有12條記錄，UNION ALL合并后的結果集總是25。
把UNION ALL改為UNION：
zx@MYDB>select empno,ename from scott.emp union select empno,ename from emp_temp; EMPNO ENAME ---------- ------------------------------ 7369 SMITH ...... 7934 MILLER 14 rows selected.
從上述顯示內容可以看出，現在該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擴展。
看一下實例：
zx@MYDB>select empno,ename from scott.emp where empno in (7654,7698,7782); EMPNO ENAME ---------- ------------------------------ 7654 MARTIN 7698 BLAKE 7782 CLARK
從上述顯示內容可以看出，現在該SQL的執行計劃走的是對表EMP和主鍵索引PK_EMP的IN-List迭代。
使用Hint讓Oracle強制走IN-List擴展
zx@MYDB>select /*+ USE_CONCAT */empno,ename from scott.emp where empno in (7654,7698,7782); EMPNO ENAME ---------- ------------------------------ 7654 MARTIN 7698 BLAKE 7782 CLARK
從上面顯示內容可以看出，Hint失效了，還是走IN-List迭代。使用如下兩個事件在當前Session中將IN-List迭代禁掉，并將輸入參數no_invalidate的值設為false后重新收集一下統計信息，以便后續再次執行上述SQL時不會沒用之前走IN-List迭代的執行計劃：
zx@MYDB>alter session set events '10142 trace name context forever'; Session altered. zx@MYDB>alter session set events '10157 trace name context forever'; Session altered. zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false); PL/SQL procedure successfully completed. zx@MYDB>select /*+ USE_CONCAT */ empno,ename from scott.emp where empno in (7654,7698,7782); EMPNO ENAME ---------- ------------------------------ 7654 MARTIN 7698 BLAKE 7782 CLARK
從上述顯示內容中可以看出，現在該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
union all
select empno,ename from emp where empno=7654
8. CONNECT BY
CONNECT BY是Oracle數據庫中層次查詢(Hierachical Queries)所對應的關鍵字，如果出現在執行中也是表示同樣的含義。
看一下實例：
zx@MYDB>select empno,ename,job,mgr from scott.emp; EMPNO ENAME JOB MGR ---------- ------------------------------ --------------------------- ---------- 7369 SMITH CLERK 7902 7499 ALLEN SALESMAN 7698 7521 WARD SALESMAN 7698 7566 JONES MANAGER 7839 7654 MARTIN SALESMAN 7698 7698 BLAKE MANAGER 7839 7782 CLARK MANAGER 7839 7788 SCOTT ANALYST 7566 7839 KING PRESIDENT 7844 TURNER SALESMAN 7698 7876 ADAMS CLERK 7788 7900 JAMES CLERK 7698 7902 FORD ANALYST 7566 7934 MILLER CLERK 7782
從上述內容可以看到KING是PRESIDENT，它所在記錄的MGR的值為NULL，表示KING沒有上級。
我們執行如下SQL，從KING所在的記錄開始，將所有人按照上下級關系分成顯示出來：
zx@MYDB>select empno,ename,mgr from emp start with empno=7839 connect by prior empno=mgr; EMPNO ENAME MGR ---------- ------------------------------ ---------- 7839 KING 7566 JONES 7839 7788 SCOTT 7566 7876 ADAMS 7788 7902 FORD 7566 7369 SMITH 7902 7698 BLAKE 7839 7499 ALLEN 7698 7521 WARD 7698 7654 MARTIN 7698 7844 TURNER 7698 7900 JAMES 7698 7782 CLARK 7839 7934 MILLER 7782
查看執行計劃：
從上述顯示內容可以看出，現在該SQL的執行計劃走的就是CONNECT BY，在執行計劃中我們也能看到CONNECT BY 關鍵字。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
