一、操作符優化
1、IN 操作符?用IN寫出來的SQL的優點是比較容易寫及清晰易懂，這比較適合現代軟件開發的風格。但是用IN的SQL性能總是比較低的，從Oracle執行的步驟來分析用IN的SQL與不用IN的SQL有以下區別：
ORACLE 試圖將其轉換成多個表的連接，如果轉換不成功則先執行IN里面的子查詢，再查詢外層的表記錄，如果轉換成功則直接采用多個表的連接方式查詢。由此可見用 IN的SQL至少多了一個轉換的過程。一般的SQL都可以轉換成功，但對于含有分組統計等方面的SQL就不能轉換了。
推薦方案：在業務密集的SQL當中盡量不采用IN操作符，用EXISTS 方案代替。
2、NOT IN操作符?此操作是強列不推薦使用的，因為它不能應用表的索引。
推薦方案：用NOT EXISTS 方案代替?
3、IS NULL 或IS NOT NULL操作（判斷字段是否為空） 判斷字段是否為空一般是不會應用索引的，因為索引是不索引空值的。
推薦方案：用其它相同功能的操作運算代替，如：a is not null 改為 a>0 或a>’’等。不允許字段為空，而用一個缺省值代替空值，如申請中狀態字段不允許為空，缺省為申請。?
4、> 及 < 操作符（大于或小于操作符）?大于或小于操作符一般情況下是不用調整的，因為它有索引就會采用索引查找，但有的情況下可以對它進行優化，如一個表有100萬記錄，一個數值型字段 A，30萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。那么執行A>2與A>=3的效果就有很大的區別了，因 為A>2時ORACLE會先找出為2的記錄索引再進行比較，而A>=3時ORACLE則直接找到=3的記錄索引。
5、LIKE操作符?LIKE操作符可以應用通配符查詢，里面的通配符組合可能達到幾乎是任意的查詢，但是如果用得不好則會產生性能上的問題，如LIKE ‘%5400%’ 這種查詢不會引用索引，而LIKE ‘X5400%’則會引用范圍索引。 一個實際例子：用YW_YHJBQK表中營業編號后面的戶標識號可來查詢營業編號 YY_BH LIKE ‘%5400%’ 這個條件會產生全表掃描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 則會利用YY_BH的索引進行兩個范圍的查詢，性能肯定大大提高。?
6、UNION操作符?UNION在進行表鏈接后會篩選掉重復的記錄，所以在表鏈接后會對所產生的結果集進行排序運算，刪除重復的記錄再返回結果。實際大部分應用中是不會產生重復的記錄，最常見的是過程表與歷史表UNION。如：
select * from gc_dfys
union
select * from ls_jg_dfys
這個SQL在運行時先取出兩個表的結果，再用排序空間進行排序刪除重復的記錄，最后返回結果集，如果表數據量大的話可能會導致用磁盤進行排序。
推薦方案：采用UNION ALL操作符替代UNION，因為UNION ALL操作只是簡單的將兩個結果合并后就返回。
select * from gc_dfys
union all
select * from ls_jg_dfys
二、SQL書寫的影響
1、同一功能同一性能不同寫法SQL的影響。
如一個SQL在A程序員寫的為 Select * from zl_yhjbqk
B 程序員寫的為 Select * from dlyx.zl_yhjbqk（帶表所有者的前綴） C程序員寫的為 Select * from DLYX.ZLYHJBQK（大寫表名） D程序員寫的為 Select * from DLYX.ZLYHJBQK（中間多了空格） 以上四個SQL在ORACLE分析整理之后產生的結果及執行的時間是一樣的，但是從ORACLE共享內存SGA的原理，可以得出ORACLE對每個SQL 都會對其進行一次分析，并且占用共享內存，如果將SQL的字符串及格式寫得完全相同，則ORACLE只會分析一次，共享內存也只會留下一次的分析結果，這 不僅可以減少分析SQL的時間，而且可以減少共享內存重復的信息，ORACLE也可以準確統計SQL的執行頻率。?
2、WHERE后面的條件順序影響?
WHERE子句后面的條件順序對大數據量表的查詢會產生直接的影響。如：
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'
以 上兩個SQL中dy_dj（電壓等級）及xh_bz（銷戶標志）兩個字段都沒進行索引，所以執行的時候都是全表掃描，第一條SQL的dy_dj = '1KV以下'條件在記錄集內比率為99%，而xh_bz=1的比率只為0.5%，在進行第一條SQL的時候99%條記錄都進行dy_dj及xh_bz的 比較，而在進行第二條SQL的時候0.5%條記錄都進行dy_dj及xh_bz的比較，以此可以得出第二條SQL的CPU占用率明顯比第一條低。
3、查詢表順序的影響?
在 FROM后面的表中的列表順序會對SQL執行性能影響，在沒有索引及ORACLE沒有對表進行統計分析的情況下，ORACLE會按表出現的順序進行鏈接， 由此可見表的順序不對時會產生十分耗服物器資源的數據交叉。（注：如果對表進行了統計分析，ORACLE會自動先進小表的鏈接，再進行大表的鏈接）
三、SQL語句索引的利用?
1、操作符優化（同上）?
2、對條件字段的一些優化?采用函數處理的字段不能利用索引，如： substr(hbs_bh,1,4)=’5400’，優化處理：hbs_bh like ‘5400%’ trunc(sk_rq)=trunc(sysdate)， 優化處理：sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1) 進行了顯式或隱式的運算的字段不能進行索引，如：ss_df+20>50，優化處理：ss_df>30 ‘X’ || hbs_bh>’X5400021452’，優化處理：hbs_bh>’5400021542’
sk_rq+5=sysdate，優化處理：sk_rq=sysdate-5 hbs_bh=5401002554，優化處理：hbs_bh=’ 5401002554’，注：此條件對hbs_bh 進行隱式的to_number轉換，因為hbs_bh字段是字符型。?
條件內包括了多個本表的字段運算時不能進行索引，
如：ys_df>cx_df，無法進行優化
qc_bh || kh_bh=’5400250000’，優化處理：qc_bh=’5400’ and kh_bh=’250000’
四、其他
ORACLE 的提示功能是比較強的功能，也是比較復雜的應用，并且提示只是給ORACLE執行的一個建議，有時如果出于成本方面的考慮ORACLE也可能不會按提示進 行。根據實踐應用，一般不建議開發人員應用ORACLE提示，因為各個數據庫及服務器性能情況不一樣，很可能一個地方性能提升了，但另一個地方卻下降 了，ORACLE在SQL執行分析方面已經比較成熟，如果分析執行的路徑不對首先應在數據庫結構（主要是索引）、服務器當前性能（共享內存、磁盤文件碎 片）、數據庫對象（表、索引）統計信息是否正確這幾方面分析。

oracle sql語句優化（轉載）

（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?和?Pro*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 )?用來存放可以被恢復的信息?.?如果你沒有?COMMIT?事務?,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_NAME?FROM?TABLES?WHERE?(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?替代?I?N?、?用?NOT EXISTS?替代?NOT IN?： 在許多基于基礎表的查詢中?,?為了滿足一個條件?,?往往需要對另一個表進行聯接?.?在這種情況下?,?使用EXISTS(?或?NOT EXISTS)?通常將提高查詢的效率?.?在子查詢中?,NOT IN?子句將執行一個內部的排序和合并?.?無論在哪種情況下?,NOT IN?都是最低效的?(?因為它對子查詢中的表執行了一個全表遍歷?).?為了避免使用?NOT IN ,?我們可以把它改寫成外連接?(Outer Joins)?或?NOT EXISTS. 例子： （?高效?）?SELECT?*?FROM??EMP (?基礎表?) ??WHERE??EMPNO > 0 ??AND???EXISTS?(?SELECT?‘X' ?FROM?DEPT ??WHERE??DEPT.DEPTNO = EMP.DEPTNO ??AND??LOC = ‘MELB') (?低效?)?SELECT??*?FROM??EMP (?基礎表?) ??WHERE??EMPNO > 0 ??AND??DEPTNO?IN?(SELECTDEPTNO ??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.?一般可以考慮用?EXIST?替換?, EXISTS?使查詢更為迅速?,?因為?RDBMS?核心模塊將在?子查詢的條件一旦滿足后?,?立刻返回結果?.?例子： ???????(?低效?):?
SELECT???DISTINCT??DEPT_NO,DEPT_NAME ??FROM??DEPT D , EMP E WHERE??D.DEPT_NO = E.DEPT_NO?
(?高效?):?
SELECT??DEPT_NO,DEPT_NAME ??FROM??DEPT D ??WHERE???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?子句中，如果索引列是函數的一部分．優化器將不使用索引而使用全表掃描．?
舉例?:?
低效：?
SELECT … FROM ?DEPT ?WHERE SAL * 12 > 25000;?
高效?:?
SELECT … FROM DEPT WHERE SAL > 25000/12; （23）??用?>=?替代?> 高效?:?
SELECT * FROM ?EMP ?WHERE ?DEPTNO >=4?
低效?:?
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?上都建有索引?.?
高效?:?
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,?那就需要返回記錄最少的索引列寫在最前面?. （25）??用?IN?來替換?OR ? 這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在?ORACLE8i?下，兩者的執行路徑似乎是相同的．　 低效?:?
SELECT?….?FROM?LOCATION?WHERE?LOC_ID = 10?OR?LOC_ID = 20?OR?LOC_ID = 30?
高效?
SELECT?…?FROM?LOCATION?WHERE?LOC_IN ??IN?(10,20,30); （26）??避免在索引列上使用?IS NULL?和?IS NOT NULL 避免在索引中使用任何可以為空的列，?ORACLE?將無法使用該索引?．對于單列索引，如果列包含空值，索引中將不存在此記錄?.?對于復合索引，如果每個列都為空，索引中同樣不存在此記錄?.?　如果至少有一個列不為空，則記錄存在于索引中．?舉例?:?如果唯一性索引建立在表的?A?列和?B?列上?,?并且表中存在一條記錄的?A,B?值為(123,null) , ORACLE?將不接受下一條具有相同?A,B?值（?123,null?）的記錄?(?插入?).?然而如果?所有的索引列都為空，?ORACLE?將認為整個鍵值為空而空不等于空?.?因此你可以插入?1000?條具有相同鍵值的記錄?,?當然它們都是空?!?因為空值不存在于索引列中?,?所以?WHERE?子句中對索引列進行空值比較將使?ORACLE?停用該索引?. 低效?: (?索引失效?)?
SELECT?…?FROM??DEPARTMENT ??WHERE??DEPT_CODE?IS NOT NULL?;?
高效?: (?索引有效?)?
SELECT?…?FROM??DEPARTMENT ??WHERE??DEPT_CODE?>=?0; （27）??總是使用索引的第一個列?： 如果索引是建立在多個列上?,?只有在它的第一個列?(leading column)?被?where?子句引用時?,?優化器才會選擇使用該索引?.?這也是一條簡單而重要的規則，當僅引用索引的第二個列時?,?優化器使用了全表掃描而忽略了索引 （28）??用?UNION-ALL?替換?UNION (?如果有可能的話?)?： 當?SQL?語句需要?UNION?兩個查詢結果集合時?,?這兩個結果集合會以?UNION-ALL?的方式被合并?,?然后在輸出最終結果前進行排序?.?如果用?UNION ALL?替代?UNION,?這樣排序就不是必要了?.?效率就會因此得到提高?.?需要注意的是?，?UNION ALL?將重復輸出兩個結果集合中相同記錄?.?因此各位還是?要從業務需求分析使用?UNION ALL?的可行性?. UNION?將對結果集合排序?,?這個操作會使用到?SORT_AREA_SIZE?這塊內存?.?對于這?塊內存的優化也是相當重要的?.?下面的?SQL?可以用來查詢排序的消耗量 低效：?
SELECT??ACCT_NUM, BALANCE_AMT?
FROM??DEBIT_TRANSACTIONS?
WHERE?TRAN_DATE = '31-DEC-95'?
UNION?
SELECT?ACCT_NUM, BALANCE_AMT?
FROM?DEBIT_TRANSACTIONS?
WHERE?TRAN_DATE = '31-DEC-95'?
高效?:?
SELECT?ACCT_NUM, BALANCE_AMT?
FROM?DEBIT_TRANSACTIONS?
WHERE TRAN_DATE = '31-DEC-95'?
UNION?ALL?
SELECT?ACCT_NUM, BALANCE_AMT?
FROM?DEBIT_TRANSACTIONS?
WHERE?TRAN_DATE = '31-DEC-95' （29）??用?WHERE?替代?ORDER BY?： ORDER BY?子句只在兩種嚴格的條件下使用索引?.?
ORDER BY?中所有的列必須包含在相同的索引中并保持在索引中的排列順序?.?
ORDER BY?中所有的列必須定義為非空?.?
WHERE?子句使用的索引和?ORDER BY?子句中所使用的索引不能并列?. 例如?:?
表?DEPT?包含以下列?:?
DEPT_CODE PK NOT NULL?
DEPT_DESC NOT NULL?
DEPT_TYPE NULL 低效?: (?索引不被使用?)?
SELECT?DEPT_CODE??FROM??DEPT ??ORDER BY??DEPT_TYPE?
高效?: (?使用索引?)?
SELECT?DEPT_CODE ??FROM??DEPT ??WHERE??DEPT_TYPE > 0 （30）??避免改變索引列的類型?.: 當比較不同數據類型的數據時?, 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?
因為內部發生的類型轉換?,?這個索
from:?http://lya041.blog.51cto.com/337966/680485

總結

以上是生活随笔為你收集整理的程序开发是编写sql语句的注意事项的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。