常用的SQL優化技巧

從使用索引來考慮SQL語句

• 避免索引列上的函數，如SUBSTR/UPPER/NVL/TO_CHAR/TO_DATE/TRUNC

• 避免索引列上的計算公式：如果索引列上使用了計算公式，則索引不能使用，可以通過更改計算公式來避免

  不使用索引：Select e.Ename From Emp e Where e.Sal * 1.1 > 900

  使用索引：Select e.Ename From Emp e Where e.Sal > 900 / 1.1

  低效: Select … FROM DEPARTMENT Where DEPT_CODE IS NOT NULL;

  高效: Select … FROM DEPARTMENT Where DEPT_CODE >=0;

• 避免使用not in，使用not exists代替

  Select e.Ename From Emp e Where e.Deptno Not In (Select d.Deptno From Dept d)

  Select e.Ename From Emp e Where e.Deptno Not Exists (Select ‘x’ From Dept d Where d.Deptno = e.Deptno)

• LIKE的使用： LIKE用于模糊檢索，LIKE檢索的樣式有三種：前比對(XX%)、中間比對(X%X)、後比對(%XX)，對于前比對可以使用索引，而使用中間比對和後比對，都不能使用索引。是以除非必要，否則應盡量避免使用中間比對和後比對
• 複合索引的使用：要使用複合索引，where語句中必須包括複合索引中的所有列或前幾個列。如果複合索引的第一個列不在where語句中則不應該使用該複合索引

從減少系統負荷來考慮SQL語句

• 使用表别名：通過對表附加别名，SQL編譯時可以明确列的來源表，進而使得SQL的編譯時間縮短
• ROWNUM的使用：ROWNUM可以限制檢索資料的數量，如果為了判斷對象是否存在，使用ROWNUM=1是非常有效的
• UNION和UNION ALL：如果确定聯合中不會出現重複資料的話，必須設定UNION ALL來取消自動分類以提高檢索速度

• 替代DISTINCT：盡量少使用DISTINCT，使用DISTINCT将引起内部排序處理，如果可以的話，盡量使用EXISTS、NOT EXISTS或子查詢來避免

  Select Distinct d.Deptno, d.Dname From Dept d, Emp e Where d.Deptno = e.DeptnoSelect d.Deptno, d.Dname From Dept d Where Exists (Select ‘x’ From Emp e Where d.Deptno = e.Deptno)

• 避免視圖濫用：如果查詢隻檢索基表的幾個字段，應避免直接使用視圖，造成不必要的資料塊檢索
• 編寫可再利用性的SQL語句：需要動态組合條件時，應避免直接将變量值組合到條件中去，而應該使用變量綁定，進而提高SQL語句的再利用性

Select子句中避免使用*

當你想在SELECT子句中列出所有的COLUMN時,使用動态SQL列引用 * 是一個友善的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會将 * 依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味着将耗費更多的時間.

使用decode函數來減少處理時間

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

例如：

select count() from pts_work_centers t where t.organization_id = 85

and t.work_center_name like ‘%氧化%’;

select count() from pts_work_centers t where t.organization_id = 86

and t.work_center_name like ‘%氧化%’;

用decode可以寫成：

select count(decode(t.organization_id,85,‘X’)),

count(decode(t.organization_id,86,‘X’))

from pts_work_centers t

where t.work_center_name like ‘%氧化%’;

用Where子句替換HAVING子句

避免使用HAVING子句, HAVING 隻會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷.

低效：

select t.organization_id,count(*)

from pts_work_centers t

group by t.organization_id

having t.organization_id not in(85,86);

高效：

select t.organization_id,count(*)

from pts_work_centers t

where t.organization_id not in(85,86)

group by t.organization_id

HAVING 中的條件一般用于對一些集合函數的比較,如COUNT() 等等. 除此而外,一般的條件應該寫在WHERE子句中

需要當心的Where子句:

某些Select 語句中的Where子句不使用索引. 這裡有一些例子.

在下面的例子裡

• ‘!=’ 将不使用索引. 記住, 索引隻能告訴你什麼存在于表中, 而不能告訴你什麼不存在于表中.
• ‘||'是字元連接配接函數. 就象其他函數那樣, 停用了索引.
• ‘+'是數學函數. 就象其他數學函數那樣, 停用了索引.
• 相同的索引列不能互相比較,這将會啟用全表掃描.

利用空值不會出現在索引的原則來提高SQL性能

假如某個字段，對于大多數記錄而言是一個固定值，隻有少數記錄是另外其他值，而我們的程式中通常要查詢這些少資料記錄。

例如，庫存事務表的‘成本核算’字段，大多數記錄肯定是已核算成本，隻有少部分記錄是未核算成本，而成本管理器就僅僅想查詢這些未核算成本的記錄，如果我們設計‘已核算’狀态的值為‘Y’，‘未核算’狀态為‘N’，那這個查詢會消耗巨大。如果我們設計‘已核算’狀态的值為‘’，‘未核算’狀态為‘N’，那麼查詢速度會非常快： select transaction_id from mmt where cost_flag = ‘N’

避免改變索引列的類型

避免ORACLE對你的SQL進行隐式的類型轉換, 最好把類型轉換用顯式表現出來. 注意當字元和數值比較時, ORACLE會優先轉換數值類型到字元類型.

假設 EMPNO是一個數值類型的索引列.

SELECT …

FROM EMP

WHERE EMPNO = ‘123’

實際上,經過ORACLE類型轉換, 語句轉化為:

SELECT …

FROM EMP

WHERE EMPNO = TO_NUMBER(‘123’)

幸運的是,類型轉換沒有發生在索引列上,索引的用途沒有被改變.

現在,假設EMP_TYPE是一個字元類型的索引列.

SELECT …

FROM EMP

WHERE EMP_TYPE = 123

這個語句被ORACLE轉換為:

SELECT …

FROM EMP

WHERE TO_NUMBER(EMP_TYPE)=123

因為内部發生的類型轉換, 這個索引将不會被用到!

識别’低效執行’的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;

select * from (  
    select * from V$SQLSTATS        
    -- 最耗時的 SQL  
    -- ELAPSED_TIME 指的是總耗時（毫秒），平均耗時 = ELAPSED_TIME/EXECUTIONS  
    -- order by ELAPSED_TIME DESC  
      
    -- 查詢執行次數最多的 SQL  
    -- order by EXECUTIONS DESC  
      
    -- 讀硬碟最多的 SQL  
    -- order by DISK_READS DESC  
      
    -- 最費 CPU 的 SQL  
    -- order by BUFFER_GETS DESC  

) where rownum <=50; 
           

查找前十條性能差的sql

SELECT * FROM

(

SELECT PARSING_USER_ID

EXECUTIONS,

SORTS,

COMMAND_TYPE,

DISK_READS,

sql_text

FROM v$sqlarea

ORDER BY disk_reads DESC

) WHERE ROWNUM<10 ;