Oracle查詢最佳化
1>選擇最有效率的表名順序： 把記錄少的表放在from子句的最後面一個表. 2.如果有3個以上的表串連查詢, 那就需要選擇交叉表(intersection table)作為基礎資料表, 交叉表是指那個被其他表所引用的表. 原因：ORACLE的解析器按照從右至左的順序處理FROM子句中的表名,因此FROM子句中寫在最後的表(基礎資料表 driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎資料表.當ORACLE處理多個表時, 會運用排序及合并的方式串連它們.首先,掃描 第一個表(FROM子句中最後的那個表)並對記錄進行排序,然後掃描第二個表(FROM子句中最後第二個表),最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并 2> WHERE子句中的串連順序 ： ORACLE採用自右向左的順序解析WHERE子句,, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
3.>SELECT子句中避免使用 ‘ * ‘ ORACLE在解析的過程中, 需要通過查詢資料字典將'*' 依次轉換成所有的列名. 4.> 使用表的別名(Alias) 當在SQL語句中串連多個表時, 請使用表的別名並把別名首碼於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.
(Column歧義指的是由於SQL中不同的表具有相同的Column名,當SQL語句中出現這個Column時,SQL解析器無法判斷這個Column的歸屬)
5.>減少訪問資料庫的次數: 當執行每條SQL語句時, ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變數 , 讀資料區塊等等. 由此可見, 減少訪問資料庫的次數 , 就能實際上減少ORACLE的工作量 6.>(可能的話)用TRUNCATE替代DELETE. 當刪除表中的記錄時,在通常情況下, 復原段(rollback segments ) 用來存放可以被恢複的資訊. 如果你沒有COMMIT事務,ORACLE會將資料恢複到執行刪除命令之前的狀況. 而當運用TRUNCATE時, 復原段不再存放任何可被恢複的資訊.當命令運行後,資料不能被恢複.因此很少的資源被調用,執行時間也會很短. (TRUNCATE只在刪除全表裡的記錄時適用,TRUNCATE是DDL不是DML) 7.> (可能的話)使用COMMIT 只要有可能,在程式中盡量多使用COMMIT, 這樣程式的效能得到提高,需求也會因為COMMIT所釋放的資源而減少:
COMMIT所釋放的資源:
a. 復原段上用於恢複資料的資訊.
b. 被程式語句獲得的鎖 c. redo log buffer 中的空間 d. ORACLE為管理上述3種資源中的內部花費 8.>(可能的話)用Where子句替換HAVING子句 盡量少使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷. 9.>(某些情況下)可以用EXISTS替代IN . NOT EXISTS替代NOT IN 效能比較: 1.Select * from t1 where x in ( select y from t2) 2.select * from t1 where exists ( select 1 from t2 where t2. y = t1.x ) 當t1記錄比較少,t2比較大時適合用exists(exists大部分情況會利用到index),當子查詢記錄集很小時用in比較合適. 原因分析： 1.Select * from T1 where x in ( select y from T2 )
執行的過程相當於:
select * from t1, ( select distinct y from t2 ) t3 where t1.x = t3.y; 2. select * from t1 where exists ( select 1 from t2 where t2.y = t1.x )
執行的過程相當於:
for x in ( select * from t1 ) loop
if ( exists ( select 1 from t2 where t2.y = t1.x )
then
OUTPUT THE RECORD
end if
end loop
這樣表 T1 要被完全掃描一遍 . 所以可以得出結論:當t1記錄比較少,t2比較大時適合用exists(exists大部分情況會利用 到index),當子查詢記錄集很小時用in比較合適. 10.>用表串連替換EXISTS
改進第9打最佳化規則的例子. 11.>用EXISTS替換DISTINCT EXISTS 使查詢更為迅速,因為RDBMS核心模組在子查詢的條件一旦滿足後立刻返回結果. DISTINCT會先進行排序,然後會根據排序後的順序去除相同的行. 12.>使用顯式的遊標(CURSOR) 使用隱式的遊標,將會執行兩次操作. 第一次檢索記錄, 第二次檢查TOO MANY ROWS 這個exception . 而顯式遊標不執行第二次操作. 11-例： 1.低效:SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO ; 2.高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT * FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); 13.用索引提高效率 通常,通過索引查詢資料比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE最佳化器將使用索引.
除了那些LONG或LONG RAW資料類型, 你可以索引幾乎所有的列. 在大型表中使用索引特別有效.
雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來 儲存,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁碟I/O . 因為索引需要額外的儲存空間和處理,那些不必要的索引反而會使查詢反應時間變慢. 週期性重構索引是有必要的. ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
索引最佳化規則： 1. like件中不要以萬用字元(WILDCARD)開始,否則索引將不被採用. 例:SELECT LODGING FROM LODGING WHERE MANAGER LIKE ‘％HANMAN'; 2.避免在索引列上使用計算或改變索引列的類型或使用‘!=’及 <> 例: SELECT … FROM DEPT WHERE SAL * 12 > 25000; SELECT … FROM EMP WHERE EMP_TYPE=to_char(123);
select …. Where ACCOUNT_NAME||ACCOUNT_TYPE='AMEXA'; select …where empno!=8888 ; 3.避免在索引列上使用NOT . 4.用>=替代> . 高效: SELECT * FROM EMP WHERE DEPTNO >=4
低效: SELECT * FROM EMP WHERE DEPTNO >3
兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄. 5.用UNION替換OR (適用於索引列) 通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低.
在下面的例子中, LOC_ID 和REGION上都建有索引.
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE” 低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
如果你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面 注意：WHERE KEY1 = 10 （返回最少記錄）
OR KEY2 = 20 （返回最多記錄）
ORACLE 內部將以上轉換為
WHERE KEY1 = 10 AND（（NOT KEY1 = 10） AND KEY2 = 20） 6、避免在索引列上使用IS NULL和IS NOT NULL 避免在索引中使用任何可以為空白的列，ORACLE將無法使用該索引 ．對於單列索引，如果列包含空值，索引中將不存在此記錄. 對於複合索引，如果每個列都為空白，索引中同樣不存在此記錄. 如果至少有一個列不為空白，則記錄存在於索引中．
(建議：可以給null值的欄位設定一個預設值))
7. 如果索引是建立在多個列上,索引時段需要放在where 條件的第一個條件(Oracle8i之前),Oracle8i之後允許跳躍式索引. 8. 可能的話)用UNION-ALL 替換UNION. UNION-ALL就是做簡單的合并，不會進行排序， UNION先做簡單的合并，然後做進行排序，最後去除重複的記錄。 9.避免使用耗費資源的操作
帶有DISTINCT,UNION ,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎.執行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序.例如,一個UNION查詢,其中每個查詢都帶有GROUP BY子句, GROUP BY會觸發嵌入排序(NESTED SORT) ; 這樣, 每個查詢需要執行一次排序, 然後在執行UNION時, 又一個唯一排序(SORT UNIQUE)操作被執行而且它只能在前面的嵌入排序結束後才能開始執行. 嵌入的排序的深度會大大影響查詢的效率.通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫.
轉自：http://blog.csdn.net/whereusejava/article/details/7733422
