Oracle如何寫出高效的SQL

標籤：

轉載：http://www.blogjava.net/ashutc/archive/2009/07/19/277215.html

 

1、選擇最有效率的表明順序（只在基於規則的最佳化器中有效）

　　Oracle的解析器按照從右至左的順序處理FROM 子句中的表名，FROM 子句中寫在最後的表（基礎資料表 driving table）將被最先處理，在FROM 子句中包含多個表的情況下，你必須選擇記錄條數最少的表作為基礎資料表。假如有3 個以上的表串連查詢，那就需要選擇交叉表（intersection table）作為基礎資料表，交叉表是指那個被其他表所引用的表

 

2、WHERE 子句中的串連順序

　　Oracle 採用自下而上的順序解析 WHERE子句，根據這個原理，表之間的串連必須寫在其他的 WHERE 條件之前，那些可以過濾掉最大數量記錄的條件必須寫在 WHERE子句的末尾（保證最先處理的條件能過濾掉最多的記錄，使得之後的條件處理能夠處理儘可能少的記錄）

 

3、SELECT 子句中避免使用 "*"

　　Oracle 在解析的過程中，會將 * 一次轉換成所有的列名，這個工作時通過查詢資料字典完成的，這意味著將耗費更多的時間

 

4、減少訪問資料庫的次數

　　Oracle 在內部執行了許多工作：解析SQL 陳述式、估算索引的利用率、綁定變數、讀資料區塊等

 

5、在 SQL*Plus、SQL*Forms 和 Proc*C中重新設定 ARRAYSIZE參數，可以增加每次資料庫訪問的索引資料量，建議值為200

 

6、使用 DECODE 函數來減少處理時間

　　使用DECODE 函數可以避免重複掃描相同記錄或重複串連相同的表

 

7、整合簡單、無關聯的資料庫訪問

　　假如你有幾個簡單的資料庫查詢語句，你可以把它們整合到一個查詢中（即使它們之間沒有關係）

 

8、重複資料刪除記錄

　　最高效的重複資料刪除記錄的方法（因為使用了 ROWID）例子

DELETE FROM EMP E WHERE E.ROWID > (    SELECT MIN(X.ROWID)    FROM EMP X    WHERE X.EMP_NO = E.EMP_NO)

　　

9、用 TRUNCATE代替 DELETE

　　當刪除表中的記錄時，在通常情況下，復原段（rollback segments）用來存放可以被恢複的資訊，假如你沒有 COMMINT 事務，Oracle會將資料恢複到刪除之前的狀態（準確地說是恢複到執行刪除命令之前的狀態），而當運用 TRUNCATE時，復原段不再存放任何可被恢複的資訊。當命令運行之後，資料不能被恢複，因此很少的資源被調用，執行時間也會很短

　　但是TRUNCATE 只在刪除全表使用，TRUNCATE 是DDL 不是DML

 

10、儘可能多使用 COMMIT

　　只要有可能，在程式中盡量多使用 COMMIT，這樣程式的效能得到提升，需求也會因為 COMMIT所釋放的欄位而減少

　　COMMIT所釋放的資源：

a. 復原段上用於恢複資料的資訊

b. 被程式語句獲得的鎖

c. redo log buffer 中的空間

d. Oracle為治理上述3 中資源中的內部花費

 

11、用WHERE 子句替代 HAVING子句

　　避免使用 HAVING子句，HAVING 只會在檢索出所有記錄之後才對結果集進行過濾。這個處理需要排序、總計等操作。假如能通過 WHERE子句限制記錄的數目，那就能減少這方面的開銷。（非Oracle中）on、where、having這單個都可以加條件的子句中，on是最先執行，where次之，having最後。

　　因為on 是先把不合格記錄過濾之後才進行統計，它就可以減少中間運算要處理的資料，按理說應該是最快的。

　　where也應該比having 快，因為它過濾資料後才進行sum ，在兩個表聯結時才用 on的，所以在一個表的時候，就剩下where跟 having比較了。

　　在單表查詢統計的情況下，假如要過濾的條件沒有涉及到計算欄位，那它們的結果是一樣的，只是 where可以使用 rushmore 技術，而having 就不能，在速度上後者要慢。假如要涉及到計算的欄位，就表示在沒有計算之前，這個欄位的值是不確定的，根據上面寫的工作流程，where的作用時間是在計算之前就完成的，而having 就是在計算之後才起作用的，所以在這種情況下，兩者的結果會不同。

　　在奪標聯結查詢時，on比where更早起作用。系統首先根據各個表之間的聯結條件，把多個表合成一個暫存資料表之後，再由where 進行過濾，然後再計算，計算完成之後再由 having 進行過濾。

　　由此可見，要想過濾條件起到正確的作用，首先要明報這個條件應該在什麼時候起作用，然後在決定放在哪裡

 

12、減少對錶的查詢

　　在含有子查詢的SQL 陳述式中，要非常注重減少對錶的查詢，例子

SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME, DB_VER)=(    SELECT TAB_NAME, DB_VER    FROM TAB_COLUMNS    WHERE VERSION=604    )

　　

13、通過內建函式提高SQL 效率

　　複雜的SQL 往往犧牲了執行效率。能夠把握上面的運算函數解決問題的方法在實際工作中往往是非常有意義的

　　

14、使用表的別名（Alias）

　　當在 SQL語句中串連多個表時，請使用表的別名並把別名首碼於每個Column 上，這樣一來，就可以減少解析的時間並減少那些由 Column歧義引起的語法錯誤

 

15、用 EXISTS 替代IN；用 NO EXISTS 替代NOT IN

　　在許多基於基礎資料表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯結，在這種情況下，使用EXISTS（或者NOT EXISTS）通常將提高查詢的效率，在子查詢中，NOT IN子句將執行一個內部的排序和合并。

　　無論在哪種情況下，NOT IN都是低效的（因為它對子查詢中的表執行了一個全表遍曆）。為了避免使用NOT IN，可以把它改成外串連（Outer Joins）或者 NOT EXISTS

　　高效的SQL執行個體

SELECT* FROM EMP(基礎資料表)WHERE EMPNO>0 AND EXISTS(    SELECT ‘X‘    FROM DEPT    WHERE DEPT.DEPTNO=EMP.DEPTNO AND LOC=‘MELB‘    )

　　低效的SQL執行個體

SELECT * FROM EMP(基礎資料表)WHERE EMPNO>0 AND DEPTNO IN(    SELECT DEPTNO    FROM DEPT    WHERE LOC=‘MELB‘    )

　　

16、識別“低效執行”的SQL 陳述式

　　雖然目前各種關於SQL 最佳化的圖形化工具層出不窮，但是寫出自己的SQL 工具來解決問題始終是一個最好的方法

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC; 

　　

17、用索引提高效率

　　索引是表的一個概念部分，用來提高檢索資料的效率，Oracle 使用了一個複雜的自平衡 B-Tree 結構。通常，通過所以查詢資料比全表掃描要快。

　　當Oracle 找出執行查詢和 Update語句的最佳路徑時，Oracle最佳化器將使用索引。

　　同樣在連接多個表時使用索引也能提高效率。

　　另一個使用索引的好處是，它提供了主鍵（PRIMARY KEY）的唯一性驗證。那些LONG 或 LONG RAW 資料類型，你可以索引幾乎所有的列。

　　通常，在大型表中使用索引能得到查詢效率的提高，但是我們也必須注重到它的代價。所以需要空間來儲存，也需要定期維護，每當有記錄在表中增減或索引列被修改時，縮印本身也被修改。這意味著沒條記錄的INSERT、DELETE、UPDATE將為此多付出4、5 次的磁碟I/O。

　　因為索引需要額外的儲存空間和處理，那些不必要的索引反而會使查詢時間變慢。週期性重構索引是很有必要的

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

　　

18、用EXISTS 替代 DISTINCT

　　當提交一個包含一對多表資訊（比如部門表和僱員表）的查詢時，避免在SELECT 子句中使用DISTINCT。一般可以考慮使用 EXISTS替換，EXISTS是查詢更為迅速，因為 RDBMS 核心模組將在子查詢的條件一單滿足後，馬上返回結果

　　低效的SQL 執行個體

SELECT DISTINCT DEPT_NO, DEPT_NAMEFROM DEPT D, EMP EWHERE D.DEPT_NO=E.DEPT_NO

　　高效的SQL 執行個體

SELECT DEPT_NO, DEPT_NAMEFROM DEPT DWHERE EXISTS (    SELECT ‘X‘     FROM EMP E    WHERE E.DEPT_NO=D.DEPT_NO    )

　　

19、SQL 陳述式用大寫的

　　因為 Oracle總是先解析SQL語句，把小寫字母轉換成大寫的在執行

 

20、在Java 代碼中盡量少使用串連符 “+” 連接字串

 

21、避免在索引列上使用 NOT

　　通常，我們在索引列上使用 NOT，NOT會產生在和在索引列上使用函數相同的印影響。當Oracle“碰到” NOT，它就會停止使用索引轉而窒息感全表掃描

 

22、避免在索引列上使用計算

　　WHERE 子句中，假如索引列是函數的一部分，最佳化器將不再使用索引而使用全表掃描

　　低效的SQL 執行個體

SELECT ...FROM DEPTWHERE SQL*12>25000

　　高效的SQL 執行個體

SELECT ...FROM DEPTWHERE SAL > 25000/12

　　

23、用>= 替代 >

　　高效的SQL 執行個體

SELECT *FROM EMPWHERE DEPTNO>=4

　　低效的SQL 執行個體

SELECT * FROM EMP WHERE DEPTNO >3

　　兩者的區別在於，前置DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄. 

　　同理，用 <= 替代 <

 

24、用 UNION 替代 OR（適用於索引列）

　　通常情況下，用UNION 替換 WHERE子句中的OR 將會起到很好的效果。對索引列使用 OR 將造成全表掃描。

　　注意，以上的規則只針對多個索引列有效。假如有 column沒有被索引，查詢效率可能因為你沒有選擇OR而降低。下面的例子中，LOC_ID和 REGION上都建立索引

　　高效的SQL 執行個體

SELECT LOC_ID, LOC_DESC, REGIONFROM LOCATIONWHERE LOC_ID=10 UNIONSELECT LOC_ID, LOC_DESC, REGIONFROM LOCATIONWHERE REGION=‘MELBOURNE‘

　　低效的SQL 執行個體

SELECT LOC_ID, LOC_DESC, REGIONFROM LOCATIONWHERE LOC_ID=10 OR REGION=‘MELBOURNE‘

　　假如你堅持要用 OR，那就需要返回記錄最少的索引列寫在最前面

 

25、

Oracle如何寫出高效的SQL