OracleSQL性能優化技巧大總結

　　（） 選擇最有效率的表名順序(只在基於規則的優化器中有效)
    ORACLE的解析器按照從右到左的順序處理FROM子句中的表名FROM子句中寫在最後的表(基礎表 driving table)將被最先處理在FROM子句中包含多個表的情況下你必須選擇記錄條數最少的表作為基礎表如果有個以上的表連接查詢 那就需要選擇交叉表(intersection table)作為基礎表 交叉表是指那個被其他表所引用的表
  （） WHERE子句中的連接順序．
    ORACLE采用自下而上的順序解析WHERE子句根據這個原理表之間的連接必須寫在其他WHERE條件之前 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾
  （） SELECT子句中避免使用 *
    ORACLE在解析的過程中 會將* 依次轉換成所有的列名 這個工作是通過查詢數據字典完成的 這意味著將耗費更多的時間
  （） 減少訪問數據庫的次數
    ORACLE在內部執行了許多工作: 解析SQL語句 估算索引的利用率 綁定變量 讀數據塊等
  （） 在SQL*Plus SQL*Forms和Pro*C中重新設置ARRAYSIZE參數 可以增加每次數據庫訪問的檢索數據量 建議值為
  （） 使用DECODE函數來減少處理時間
    使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表
  （） 整合簡單無關聯的數據庫訪問
    如果你有幾個簡單的數據庫查詢語句你可以把它們整合到一個查詢中(即使它們之間沒有關系)
  （） 刪除重復記錄
    最高效的刪除重復記錄方法 ( 因為使用了ROWID)例子
DELETE FROM EMP E WHERE EROWID > (SELECT MIN(XROWID)
FROM EMP X WHERE XEMP_NO = EEMP_NO);
  （） 用TRUNCATE替代DELETE
    當刪除表中的記錄時在通常情況下 回滾段(rollback segments ) 用來存放可以被恢復的信息 如果你沒有COMMIT事務ORACLE會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況) 而當運用TRUNCATE時 回滾段不再存放任何可被恢復的信息當命令運行後數據不能被恢復因此很少的資源被調用執行時間也會很短 (譯者按: TRUNCATE只在刪除全表適用TRUNCATE是DDL不是DML) 
  （） 盡量多使用COMMIT
    只要有可能在程序中盡量多使用COMMIT 這樣程序的性能得到提高需求也會因為COMMIT所釋放的資源而減少:
COMMIT所釋放的資源:
a 回滾段上用於恢復數據的信息
b 被程序語句獲得的鎖
c redo log buffer 中的空間
d ORACLE為管理上述種資源中的內部花費

　　（） 用Where子句替換HAVING子句
    避免使用HAVING子句 HAVING 只會在檢索出所有記錄之後才對結果集進行過濾 這個處理需要排序總計等操作 如果能通過WHERE子句限制記錄的數目那就能減少這方面的開銷 (非oracle中)onwherehaving這三個都可以加條件的子句中on是最先執行where次之having最後因為on是先把不符合條件的記錄過濾後才進行統計它就可以減少中間運算要處理的數據按理說應該速度是最快的where也應該比having快點的因為它過濾數據後才進行sum在兩個表聯接時才用on的所以在一個表的時候就剩下where跟having比較了在這單表查詢統計的情況下如果要過濾的條件沒有涉及到要計算字段那它們的結果是一樣的只是where可以使用rushmore技術而having就不能在速度上後者要慢如果要涉及到計算的字段就表示在沒計算之前這個字段的值是不確定的根據上篇寫的工作流程where的作用時間是在計算之前就完成的而having就是在計算後才起作用的所以在這種情況下兩者的結果會不同在多表聯接查詢時on比where更早起作用系統首先根據各個表之間的聯接條件把多個表合成一個臨時表後再由where進行過濾然後再計算計算完後再由having進行過濾由此可見要想過濾條件起到正確的作用首先要明白這個條件應該在什麼時候起作用然後再決定放在那裡
  （） 減少對表的查詢
    在含有子查詢的SQL語句中要特別注意減少對表的查詢例子
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAMEDB_VER) = ( SELECT
TAB_NAMEDB_VER FROM TAB_COLUMNS WHERE VERSION = )
  （） 通過內部函數提高SQL效率
    復雜的SQL往往犧牲了執行效率 能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的
  （） 使用表的別名(Alias)
    當在SQL語句中連接多個表時 請使用表的別名並把別名前綴於每個Column上這樣一來就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤
  （） 用EXISTS替代IN用NOT EXISTS替代NOT IN
    在許多基於基礎表的查詢中為了滿足一個條件往往需要對另一個表進行聯接在這種情況下 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率 在子查詢中NOT IN子句將執行一個內部的排序和合並 無論在哪種情況下NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷) 為了避免使用NOT IN 我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS
例子
（高效）SELECT * FROM EMP (基礎表) WHERE EMPNO > AND EXISTS (SELECT X FROM DEPT WHERE DEPTDEPTNO = EMPDEPTNO AND LOC = MELB)
(低效)SELECT * FROM EMP (基礎表) WHERE EMPNO > AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = MELB)
  （） 識別低效執行的SQL語句
    雖然目前各種關於SQL優化的圖形化工具層出不窮但是寫出自己的SQL工具來解決問題始終是一個最好的方法
SELECT EXECUTIONS DISK_READS BUFFER_GETS ROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS) Hit_radio ROUND(DISK_READS/EXECUTIONS) Reads_per_run
SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS> AND BUFFER_GETS > AND (BUFFER_GETSDISK_READS)/BUFFER_GETS < ORDER BY DESC;
  （） 用索引提高效率
    索引是表的一個概念部分用來提高檢索數據的效率ORACLE使用了一個復雜的自平衡Btree結構 通常通過索引查詢數據比全表掃描要快 當ORACLE找出執行查詢和Update語句的最佳路徑時 ORACLE優化器將使用索引 同樣在聯結多個表時使用索引也可以提高效率 另一個使用索引的好處是它提供了主鍵(primary key)的唯一性驗證那些LONG或LONG RAW數據類型 你可以索引幾乎所有的列 通常 在大型表中使用索引特別有效 當然你也會發現 在掃描小表時使用索引同樣能提高效率 雖然使用索引能得到查詢效率的提高但是我們也必須注意到它的代價 索引需要空間來存儲也需要定期維護 每當有記錄在表中增減或索引列被修改時 索引本身也會被修改 這意味著每條記錄的INSERT DELETE UPDATE將為此多付出 次的磁盤I/O 因為索引需要額外的存儲空間和處理那些不必要的索引反而會使查詢反應時間變慢定期的重構索引是有必要的
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
  （） 用EXISTS替換DISTINCT
    當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時避免在SELECT子句中使用DISTINCT 一般可以考慮用EXIST替換 EXISTS 使查詢更為迅速因為RDBMS核心模塊將在子查詢的條件一旦滿足後立刻返回結果 例子
(低效): SELECT DISTINCT DEPT_NODEPT_NAME FROM DEPT D EMP E
WHERE DDEPT_NO = EDEPT_NO (高效): SELECT DEPT_NODEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT X FROM EMP E WHERE EDEPT_NO = DDEPT_NO);
  （） sql語句用大寫的因為oracle總是先解析sql語句把小寫的字母轉換成大寫的再執行
  （） 在java代碼中盡量少用連接符＋連接字符串！

　　（） 避免在索引列上使用NOT 通常　
    我們要避免在索引列上使用NOT NOT會產生在和在索引列上使用函數相同的影響 當ORACLE遇到NOT他就會停止使用索引轉而執行全表掃描

  （） 避免在索引列上使用計算．
    WHERE子句中如果索引列是函數的一部分．優化器將不使用索引而使用全表掃描． 舉例: 低效