oracle的查詢最佳化

oracle的查詢最佳化[@more@]一、SQL 語句轉換
試探查詢轉換
1、檢視合併
例子：
簡單檢視合併
最佳化前：
create view test_view as
select ename,dname,sal
from emp e,dept d
where e.deptno = d.deptno;
select ename,dname from test_view where sal > 10000;
最佳化後：
select ename,dname
from emp e,dept d
where e.deptno = d.deptno
and e.sal > 10000;
複雜檢視合併
最佳化前：
create view avg_sal_view as
select deptno,avg(sal) avg_sal_dept
from emp
group by deptno;
select d.name,avg_sal_dept
from dept d,avg_sal_view a
where d.deptno = a.deptno
and d.loc = 'OAKLAND';
最佳化後：
select d.name,avg(e.sal)
from dept d,emp e
where d.deptno = e.deptno
and d.loc = 'OAKLAND'
group by d.rowid,d.name;
2、子查詢展平
Oracle有一些轉換能將不同型別的子查詢轉變為聯接、半聯接或反聯接。子查詢展平也是獲得良好查詢執行效能的基本最佳化辦法。
例子：
最佳化前：
select d.dname from dept d where d.deptno in (
select e.deptno from emp e where e.sal > 10000);
最佳化後：
select d.dname
from (select distinct deptno from emp where sal > 10000) e,
dept d
where d.deptno = e.deptno;
3、傳遞謂詞生成
在某些查詢中，由於表間的聯接關係，一個表中的謂詞可以轉化為另一個表中的謂詞。Oracle 會以這種方式演繹出新的謂詞，這類謂詞被稱為傳遞謂詞。
例子：
最佳化前：
select count(distinct o_orderkey)
from order,lineitem
where o_orderkey = l_orderkey
and o_orderdate = l_shipdate
and o_orderdate between '1-JAN-2002'
and '31-JAN-2002';
最佳化後：
select count(distinct o_orderkey)
from order,lineitem
where o_orderkey = l_orderkey
and o_orderdate = l_shipdate
and o_orderdate between '1-JAN-2002'
and '31-JAN-2002'
and l_shipdate between '1-JAN-2002'
and '31-JAN-2002';
4、消除通用子表達
如果同樣的子表達或計算在一個查詢中出現多次，Oracle 對每一行只評估一次該表示式。
例子：
最佳化前：
select * from emp,dept
where (emp.deptno = dept.deptno
and loc = 'DALLAS' and sal > 10000)
or (emp.deptno = dept.deptno
and loc = 'DALLAS' and job_title = 'VICE PRESIDENT');
最佳化後：
select * from emp,dept
where emp.deptno = dept.deptno
and loc = 'DALLAS'
and (sal > 10000 or job_title = 'VICE PRESIDENT');
5、謂詞下推和上移
複雜查詢往往包含多個檢視與子查詢，在這些檢視和子查詢中包含多個謂詞。Oracle 可將謂詞移入或移出檢視，以產生新的高效查詢。 謂詞下推和上移透過產生新的訪問路徑以提高執行效能，這在沒有增加新的謂詞前不可能做到。
例子：
最佳化前：
CREATE VIEW EMP_AGG AS
SELECT DEPTNO, AVG(SAL) AVG_SAL, FROM EMP GROUP BY DEPTNO;
SELECT DEPTNO, AVG_SAL FROM EMP_AGG WHERE DEPTNO = 10;
最佳化後：
SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10
GROUP BY DEPTNO;
6、用於"CUBE"查詢的分組修整
SQL CUBE 表示式是 SQL group-by 運算子的擴充套件，使得可以用單個 SQL 語句就可以檢索多個集合。對於包含帶有 CUBE 表示式的檢視的查詢，有時可以減少評估該查詢所需要的資料量。
例子：
最佳化前：
SELECT MONTH, REGION, DEPARTMENT
FROM (SELECT MONTH, REGION, DEPARTMENT,
SUM(SALES_AMOUNT) AS REVENUE
FROM SALES
GROUP BY CUBE (MONTH, REGION, DEPT))
WHERE MONTH = 'JAN-2001';
最佳化後：
SELECT MONTH, REGION, DEPARTMENT
FROM (SELECT MONTH, REGION, DEPARTMENT,
SUM(SALES_AMOUNT) AS REVENUE
FROM SALES WHERE MONTH = ‘JAN-2001’
GROUP BY MONTH, CUBE(REGION, DEPT))
WHERE MONTH = 'JAN-2001';
7、外聯接到內聯接的轉換在某些情況下，能夠確定查詢中的一個外聯接能產生與內聯接相同的結果。在這類情況下，最佳化程式會將外聯接轉變為內聯接。這種轉換讓 Oracle 能夠進一步合併檢視或選用新的聯接順序，查詢是一個外聯接時就做不到這一點。
基於開銷的查詢轉換
1、實體化檢視重寫
以實體化檢視的形式預先處理和儲存常用資料能夠大大加速查詢處理。Oracle 能對 SQL 查詢進行轉換，使查詢中對一個或多個表的引用被對一個實體化檢視的引用所取代。如果該實體化檢視小於原來要引用的表，或比原來要引用的表有更好的訪問路徑，則該轉換後的 SQL 語句比原查詢的執行速度會快得多。
Oracle 具有一系列強有力的實體化檢視重寫技術，允許每個實體化檢視能被用在儘可能多的查詢型別中。Oracle 實體化檢視的另一個顯著特點是與 Oracle 資料庫中的宣告式維整合在一起。Oracle 可以允許生成維的後設資料物件，描述維內的層次關係。該層次化後設資料用來支援更為複雜的實體化檢視查詢重寫。
注意，使用實體化檢視的轉換後的查詢並不總是比原查詢效率高。因為，就算實體化檢視比其基於的表小，但這些基礎表可以有更好的索引，從而能夠被更快地訪問。選擇最佳化執行計劃的唯一途徑是對使用和未使用實體化檢視的執行計劃進行計算並比較其開銷。Oracle 正是這樣做的，所以實體化檢視重寫是基於開銷的查詢轉換範例。
例子：
CREATE MATERIALIZED VIEW SALES_SUMMARY AS
SELECT SALES.CUST_ID, TIME.MONTH, SUM(SALES_AMOUNT) AMT
FROM SALES, TIME
WHERE SALES.TIME_ID = TIME.TIME_ID
GROUP BY SALES.CUST_ID, TIME.MONTH;
最佳化前：
SELECT CUSTOMER.CUST_NAME, TIME.MONTH, SUM(SALES.SALES_AMOUNT) FROM SALES, CUSTOMER, TIME
WHERE SALES.CUST_ID = CUST.CUST_ID
AND SALES.TIME_ID = TIME.TIME_ID
GROUP BY CUSTOMER.CUST_NAME, TIME.MONTH;
最佳化後：
SELECT CUSTOMER.CUST_NAME, SALES_SUMMARY.MONTH,
SALES_SUMMARY.AMT
FROM CUSTOMER, SALES_SUMMARY
WHERE CUSTOMER.CUST_ID = SALES_SUMMARY.CUST_ID;
2、OR 擴充套件
該技術把在 WHERE 子句中帶有 OR 的查詢轉換成一個包含多個不帶 OR 的查詢的 UNION ALL。當 OR 表示的是對不同表的限制條件時，這是大有好處的。
例子：
最佳化前：
SELECT * FROM SHIPMENT, PORT P1, PORT P2
WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID
AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID
AND (P1.PORT_NAME = 'OAKLAND' OR P2.PORT_NAME = 'OAKLAND') ;
最佳化後：
SELECT * FROM SHIPMENT, PORT P1, PORT P2
WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID
AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID
AND P1.PORT_NAME = 'OAKLAND'
UNION ALL
SELECT * FROM SHIPMENT, PORT P1, PORT P2
WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID
AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID
AND P2.PORT_NAME = 'OAKLAND'
AND P1.PORT_NAME <> 'OAKLAND';
注意，每個 UNION ALL 分支可以有不同的最佳化的聯接順序。在第一個分支裡，Oracle 可以利用對 P1 的限制，從該表開始聯接。而在第二個分支裡，Oracle 可以從 P2 開始。產生的計劃可能比原來的查詢快好幾個數量級，這取決於表索引和其資料。這種查詢轉換必須是基於開銷的，因為它並不保證每個查詢的效能都會得到提高。
3、星型轉換
星型方式是普遍用於資料中心或資料倉儲的建模策略。星型模式一般包含一個或多個非常大的表（叫做事實表），用來儲存交易資料，另外還包含大量較小的查詢表（叫做維表），以來存放描述性資料。
Oracle 支援一種用於評估星型模式查詢的技術（叫做“星型轉換”）。該技術透過進行轉換（向原有的 SQL 中新增新的子查詢）來提高星型查詢的效率。
這些新的子查詢允許利用點陣圖索引更有效地訪問事實表。
星型轉換是基於開銷的查詢轉換，根據最佳化程式的開銷估算決定是使用某個維的子查詢開銷較低還是查詢重寫比原有語句更好。
例子：
最佳化前：
SELECT STORE.STATE, SUM(SALES.AMOUNT)
FROM SALES, DAY, QUARTER, PRODUCT, STORE
WHERE SALES.DAY_ID = DAY.DAY_ID
AND DAY.QUARTER_ID =QUARTER.QUARTER_ID
AND SALES.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND SALES.STORE_ID = STORE.STORE_ID
AND PRODUCT.PRODUCT_CATEGORY = 'BEVERAGES'
AND QUARTER.QUARTER_NAME = '2001Q3'
GROUP BY STORE.STATE;
最佳化後：
SELECT STORE.STATE, SUM(SALES.AMOUNT)
FROM SALES, STORE
WHERE SALES.STORE_ID = STORE.STORE_ID
AND SALES.DAY_ID IN (SELECT DAY.DAY_ID
FROM DAY, QUARTER
WHERE DAY.QUARTER_ID = QUARTER.QUARTER_ID
AND QUARTER.QUARTER_NAME = '2001Q3')
AND SALES.PRODUCT_ID IN (SELECT PRODUCT.PRODUCT_ID
FROM PRODUCT
WHERE PRODUCT.PRODUCT_CATEGORY = 'BEVERAGES')
GROUP BY STORE.STATE;
4、外聯接檢視的謂詞下推
通常，當查詢包含一個聯接到其他表的檢視時，該檢視可以被合併，以實現更好的查詢最佳化。但是，如果一個檢視是用外聯接方式聯接的，則該檢視就不能被合併。在這種情況下，Oracle 有一個特別的謂詞下推操作，使聯接謂詞可以下推到該檢視中，該轉換使透過該檢視中的某個表的索引執行外聯接成為可能。這種轉換是基於開銷的，因為索引訪問可能並不是最有效的。