Oracle CBO幾種基本的查詢轉換詳解
在執(zhí)行計劃的開發(fā)過程中，轉換和選擇有這個不同的任務；實際上，在一個查詢進行完語法和權限檢查后，首先發(fā)生通稱為“查詢轉換”的步驟，這里會進行一系列查詢塊的轉換，然后才是“優(yōu)選”（優(yōu)化器為了決定最終的執(zhí)行計劃而為不同的計劃計算成本從而選擇最終的執(zhí)行計劃）。
我們知道查詢塊是以SELECT關鍵字區(qū)分的，查詢的書寫方式?jīng)Q定了查詢塊之間的關系，各個查詢塊通常都是嵌在另一個查詢塊中或者以某種方式與其相聯(lián)結；例如：
select * from employees where department_id in (select department_id from departments)
就是嵌套的查詢塊，不過它們的目的都是去探索如果改變查詢寫法會不會提供更好的查詢計劃。
這種查詢轉換的步驟對于執(zhí)行用戶可以說是完全透明的，要知道轉換器可能會在不改變查詢結果集的情況下完全改寫你的SQL語句結構，因此我們有必要重新評估自己的查詢語句的心理預期，盡管這種轉換通常來說都是好事，為了獲得更好更高效的執(zhí)行計劃。
我們現(xiàn)在來討論一下幾種基本的轉換：
1.視圖合并
2.子查詢解嵌套
3.謂語前推
4.物化視圖查詢重寫
一、視圖合并
這種方式比較容易理解，它會將內(nèi)嵌的視圖展開成一個獨立處理的查詢塊，或者將其與查詢剩余部分合并成一個總的執(zhí)行計劃，轉換后的語句基本上不包含視圖了。
視圖合并通常發(fā)生在當外部查詢塊的謂語包括：
1，能夠在另一個查詢塊的索引中使用的列
2，能夠在另一個查詢塊的分區(qū)截斷中所使用的列
3，在一個聯(lián)結視圖能夠限制返回行數(shù)的條件
在這種查詢器的轉換下，視圖并不總會有自己的子查詢計劃，它會被預先分析并通常情況下與查詢的其他部分合并以獲得性能的提升，如下例。
SQL> set autotrace traceonly explain
-- 進行視圖合并
SQL> select * from EMPLOYEES a,
2 (select DEPARTMENT_ID from EMPLOYEES) b_view
3 where a.DEPARTMENT_ID = b_view.DEPARTMENT_ID(+)
4 and a.SALARY > 3000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1634680537
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3161 | 222K| 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER| | 3161 | 222K| 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMPLOYEES | 103 | 7107 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 31 | 93 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."SALARY">3000)
3 - access("A"."DEPARTMENT_ID"="DEPARTMENT_ID"(+))
-- 使用NO_MERGE防止視圖被重寫
SQL> select * from EMPLOYEES a,
2 (select /*+ NO_MERGE */DEPARTMENT_ID from EMPLOYEES) b_view
3 where a.DEPARTMENT_ID = b_view.DEPARTMENT_ID(+)
4 and a.SALARY > 3000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1526679670
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3161 | 253K| 7 (15)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 3161 | 253K| 7 (15)| 00:00:01 |
| 2 | VIEW | | 107 | 1391 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 103 | 7107 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPARTMENT_ID"="B_VIEW"."DEPARTMENT_ID"(+))
4 - filter("A"."SALARY">3000)
出于某些情況，視圖合并會被禁止或限制，如果在一個查詢塊中使用了分析函數(shù)，聚合函數(shù)，，集合運算（如union，intersect，minux），order by子句，以及rownum中的任何一種，這種情況都會發(fā)生；盡管如此，我們?nèi)匀豢梢允褂?*+ MERGE(v) */提示來強制使用視圖合并，不過前提一定要保證返回的結果集是一致的?。?！如下例：
SQL> set autotrace on
-- 使用聚合函數(shù)avg導致視圖合并失效
SQL> SELECT e1.last_name, e1.salary, v.avg_salary
2 FROM hr.employees e1,
3 (SELECT department_id, avg(salary) avg_salary
4 FROM hr.employees e2
5 GROUP BY department_id) v
6 WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;
Execution Plan
----------------------------------------------------------
Plan hash value: 2695105989
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 697 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 17 | 697 | 8 (25)| 00:00:01 |
| 2 | VIEW | | 11 | 286 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")
filter("E1"."SALARY">"V"."AVG_SALARY")
--使用/*+ MERGE(v) */強制進行視圖合并
SQL> SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary
2 FROM hr.employees e1,
3 (SELECT department_id, avg(salary) avg_salary
4 FROM hr.employees e2
5 GROUP BY department_id) v
6 WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;
Execution Plan
----------------------------------------------------------
Plan hash value: 3553954154
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 165 | 5610 | 8 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 165 | 5610 | 8 (25)| 00:00:01 |
|* 3 | HASH JOIN | | 3296 | 109K| 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 2889 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
二、子查詢解嵌套
最典型的就是子查詢轉變?yōu)楸磉B接了，它和視圖合并的主要區(qū)別就在于它的子查詢位于where子句，由轉換器進行解嵌套的檢測。
下面便是一個子查詢==>表連接的例子：
SQL> select employee_id, last_name, salary, department_id
2 from hr.employees
3 where department_id in
4 (select department_id
5 from hr.departments where location_id > 1700);
Execution Plan
----------------------------------------------------------
Plan hash value: 432925905
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 884 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 34 | 884 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 4 | 28 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 4 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 10 | 190 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("LOCATION_ID">1700)
5 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
-- 使用/*+ NO_UNNEST */強制為子查詢單獨生成執(zhí)行計劃
SQL> select employee_id, last_name, salary, department_id
2 from hr.employees
3 where department_id in
4 (select /*+ NO_UNNEST */department_id
5 from hr.departments where location_id > 1700);
Execution Plan
----------------------------------------------------------
Plan hash value: 4233807898
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 190 | 14 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2033 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 7 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "HR"."DEPARTMENTS"
"DEPARTMENTS" WHERE "DEPARTMENT_ID"=:B1 AND "LOCATION_ID">1700))
3 - filter("LOCATION_ID">1700)
4 - access("DEPARTMENT_ID"=:B1)
可以看到?jīng)]有執(zhí)行子查詢解嵌套的查詢只使用了FILTER來進行兩張表的匹配，謂語信息第一步的查詢也沒有絲毫的改動，這便意味著對于EMPLOYEES表中返回的107行的每一行，都需要執(zhí)行一次子查詢。雖然在oracle中存在子查詢緩存的優(yōu)化，我們無法判斷這兩種計劃的優(yōu)劣，不過相比NESTED LOOPS，F(xiàn)ILTER運算的劣勢是很明顯的。
如果包含相關子查詢，解嵌套過程一般會將相關子查詢轉換成一個非嵌套視圖，然后與主查詢中的表x相聯(lián)結，如：
SQL> select outer.employee_id, outer.last_name, outer.salary, outer.department_id
2 from hr.employees outer
3 where outer.salary >
4 (select avg(inner.salary)
5 from hr.employees inner
6 where inner.department_id = outer.department_id);
Execution Plan
----------------------------------------------------------
Plan hash value: 2167610409
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 765 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 17 | 765 | 8 (25)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 11 | 286 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2033 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_1"="OUTER"."DEPARTMENT_ID")
filter("OUTER"."SALARY">"AVG(INNER.SALARY)")
上面的查詢是將子查詢轉換成視圖在與主查詢進行hash join，轉換后的查詢其實像這樣：
SQL> select outer.employee_id, outer.last_name, outer.salary, outer.department_id
2 from hr.employees outer,
3 (select department_id,avg(salary) avg_sal from hr.employees group by department_id) inner
4 where inner.department_id = outer.department_id and outer.salary > inner.avg_sal;
其實這兩個語句的執(zhí)行計劃也是一致
三、謂語前推
將謂詞從內(nèi)部查詢塊推進到一個不可合并的查詢塊中，這樣可以使得謂詞條件更早的被選擇，更早的過濾掉不需要的數(shù)據(jù)行，提高效率，同樣可以使用這種方式允許某些索引的使用。
-- 謂語前推示例
SQL> set autotrace traceonly explain
SQL> SELECT e1.last_name, e1.salary, v.avg_salary
2 FROM hr.employees e1,
3 (SELECT department_id, avg(salary) avg_salary
4 FROM hr.employees e2
5 GROUP BY department_id) v
6 WHERE e1.department_id = v.department_id
7 AND e1.salary > v.avg_salary
8 AND e1.department_id = 60;
Execution Plan
----------------------------------------------------------
Plan hash value: 3521487559
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 41 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 4 | HASH GROUP BY | | 1 | 7 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 35 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 5 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 5 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 15 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("DEPARTMENT_ID"=60)
7 - access("E1"."DEPARTMENT_ID"=60)
8 - filter("E1"."SALARY">"V"."AVG_SALARY")
-- 不進行謂語前推
SQL> SELECT e1.last_name, e1.salary, v.avg_salary
2 FROM hr.employees e1,
3 (SELECT department_id, avg(salary) avg_salary
4 FROM hr.employees e2
5 WHERE rownum > 1 -- rownum等于同時使用了no_merge和no_push_pred提示，這會同時禁用視圖合并和謂語前推
6 GROUP BY department_id) v
7 WHERE e1.department_id = v.department_id
8 AND e1.salary > v.avg_salary
9 AND e1.department_id = 60;
Execution Plan
----------------------------------------------------------
Plan hash value: 3834222907
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 123 | 7 (29)| 00:00:01 |
|* 1 | HASH JOIN | | 3 | 123 | 7 (29)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 75 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 5 | | 1 (0)| 00:00:01 |
|* 4 | VIEW | | 11 | 286 | 4 (25)| 00:00:01 |
| 5 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 6 | COUNT | | | | | |
|* 7 | FILTER | | | | | |
| 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")
filter("E1"."SALARY">"V"."AVG_SALARY")
3 - access("E1"."DEPARTMENT_ID"=60)
4 - filter("V"."DEPARTMENT_ID"=60)
7 - filter(ROWNUM>1)
比較上面的兩個查詢可以看到，在第一個查詢中，DEPARTMENT_ID=60謂詞被推進到視圖v中執(zhí)行了，這樣就使得內(nèi)部視圖查詢只需要獲得部門號為60的平均薪水就可以了；而在第二個查詢中則需要計算每個部門的平均薪水，然后在與外部查詢聯(lián)結的時候使用DEPARTMENT_ID=60條件過濾，相對而言這里為了等待應用謂詞條件，查詢做了更多的工作。
四、使用物化視圖進行查詢重寫
當為物化視圖開啟查詢重寫功能時，CBO優(yōu)化器會評估相應查詢對基表與物化視圖的訪問成本，如果優(yōu)化器認為該查詢結果從物化視圖中獲得會更高效，那么就會其自動選擇為物化視圖來執(zhí)行，否則則對基表生成查詢計劃。
還是來看栗子：
SQL> set autotrace traceonly explain
SQL> select DEPARTMENT_ID,count(EMPLOYEE_ID) from EMPLOYEES group by DEPARTMENT_ID;
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 33 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 33 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-- 創(chuàng)建物化視圖日志
SQL> create materialized view log on EMPLOYEES with sequence,
2 rowid (EMPLOYEE_ID,DEPARTMENT_ID) including new values;
Materialized view log created.
-- 創(chuàng)建物化視圖，并指定查詢重寫功能
SQL> create materialized view mv_t
2 build immediate refresh fast on commit
3 enable query rewrite as
4 select DEPARTMENT_ID,count(EMPLOYEE_ID) from EMPLOYEES group by DEPARTMENT_ID;
Materialized view created.
SQL> select DEPARTMENT_ID,count(EMPLOYEE_ID) from EMPLOYEES group by DEPARTMENT_ID;
Execution Plan
----------------------------------------------------------
Plan hash value: 1712400360
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 312 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_T | 12 | 312 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
可以看到在第二個查詢中，雖然是指定的查詢EMPLOYEES表，但是優(yōu)化器自動選擇了物化視圖的執(zhí)行路徑，因為它判斷出物化視圖已經(jīng)記載當前查詢需要的結果集數(shù)據(jù)了，直接訪問物化視圖會獲得更高的效率。
值得注意的是，這里的物化視圖查詢重寫是自動發(fā)生的，同樣也可以使用/*+ rewrite(mv_t) */提示的方式強制發(fā)生查詢重寫。
總結：
盡管優(yōu)化器在用戶透明的情況下改寫了我們的查詢結構，不過通常情況下這都是基于CBO優(yōu)化模式下其判斷較為高效的選擇，這也是我們所期望的，同時為我們提供了一種學習方法，即在寫SQL語句的過程中時刻考慮優(yōu)化器的作用。
相關文章
Oracle SQL Developer腳本輸出中文顯示亂碼的解決方法
我們在測試Oracle Select AI（自然語言查詢數(shù)據(jù)庫）時,發(fā)現(xiàn)Run Statement中文顯示正常,而Run Script中文顯示亂碼,所以本文給大家介紹了Oracle SQL Developer腳本輸出中文顯示亂碼的解決方法,需要的朋友可以參考下2024-05-05
最近小編在群里討論sql優(yōu)化的問題，今天小編給大家?guī)砹薕racle復合索引與空值的索引使用問題小結，需要的朋友參考下吧2018-02-02
Oracle創(chuàng)建Database Link的兩種方式詳解
Oracle數(shù)據(jù)庫如何創(chuàng)建Database Link呢？本文我們主要就介紹一下這部分內(nèi)容，Oracle數(shù)據(jù)庫創(chuàng)建Database Link有兩種方式，一種是通過菜單，一種是通過SQL2016-02-02
這篇文章主要介紹了Oracle模擬事務提交、表鎖，處理表鎖問題,通過可視化工具navicat執(zhí)行插入語句，發(fā)現(xiàn)雖然我們設置了不自動提交，這里還是插入成功了，這里給大家介紹下手動提交事務流程，需要的朋友可以參考下2022-09-09
Orcle的package中訪問其它Schema的表報錯ORA-00942解決方法
package要訪問其它Schema的表，但編譯報錯ORA-00942，經(jīng)過搜索了一下找到了解決方法下面與大家分享下2013-05-05
Zabbix監(jiān)控Oracle歸檔日志空間的全過程
本文將介紹Zabbix監(jiān)控Oracle歸檔日志空間的全過程,Zabbix是一個開源的監(jiān)控系統(tǒng),它可以監(jiān)控各種不同類型的服務器和服務,如果您想要監(jiān)控Oracle數(shù)據(jù)庫,文中是一些簡單的步驟,需要的朋友可以參考下2024-04-04
最近在啟動實例時遇到了一些錯誤，通過查找的相關的資料終于解決，決定分享出來給有需要的朋友們，所以下面這篇文章主要給大家介紹了在Oracle實例啟動時報錯：ORA-32004的解決方法，需要的朋友可以參考借鑒，下面來一起看看吧。2017-10-10
Oracle密碼過期如何取消密碼180天限制及密碼180天過期，賬號鎖住的問題
這篇文章主要介紹了Oracle密碼過期如何取消密碼180天限制及密碼180天過期，賬號鎖住的問題,需要的朋友可以參考下2018-12-12
Oracle除去數(shù)據(jù)中的換行符以免讀取出現(xiàn)問題
將整條數(shù)據(jù)取出，并用特殊符號分割，如果數(shù)據(jù)出現(xiàn)換行的情況，那么讀取時就有問題，這時就可以采用下面的方法來去除2014-07-07
Oracle中的DECODE函數(shù)是一種條件表達式函數(shù),本文主要介紹了Oracle特有的DECODE函數(shù)的使用,具有一定的參考價值,感興趣的可以了解一下2024-08-08