oracle查詢優(yōu)化的方法：1、UNION操作符，在表鏈接后會對所產(chǎn)生的結(jié)果集進行排序運算，刪除重復的記錄再返回結(jié)果；2、大于或小于操作符一般情況下是不用調(diào)整的，因為它有索引就會采用索引查找，但有的情況下可以對它進行優(yōu)化。
oracle查詢優(yōu)化的方法：
1、IN 操作符
用 IN 寫出來的 SQL的優(yōu)點是比較容易寫及清晰易懂，這比較適合現(xiàn)代軟件開發(fā)的風格。
?
但是用 IN 的 SQL 性能總是比較低的，從 ORACLE 執(zhí)行的步驟來分析用 IN 的 SQL 與不用 IN 的 SQL有以下區(qū)別：
?
ORACLE 試圖將其轉(zhuǎn)換成多個表的連接，如果轉(zhuǎn)換不成功則先執(zhí)行IN里面的子查詢，再查詢外層的表記錄，如果轉(zhuǎn)換成功則直接采用多個表的連接方式查詢。由此可見用IN 的 SQL 至少多了一個轉(zhuǎn)換的過程。一般的SQL都可以轉(zhuǎn)換成功，但對于含有分組統(tǒng)計等方面的 SQL 就不能轉(zhuǎn)換了。
相關(guān)學習推薦：oracle數(shù)據(jù)庫學習教程
2、NOT IN 操作符
此操作是強列推薦不使用的，因為它不能應(yīng)用表的索引。
?
推薦方案：用NOT EXISTS 或（外連接+ 判斷為空）方案代替
?
3、 操作符（不等于）
不等于操作符是永遠不會用到索引的，因此對它的處理只會產(chǎn)生全表掃描。
?
推薦方案：用其它相同功能的操作運算代替，如
?
a0 改為 a>0 or a
?
a” 改為 a>”
?
4、> 及
大于或小于操作符一般情況下是不用調(diào)整的，因為它有索引就會采用索引查找，但有的情況下可以對它進行優(yōu)化，如一個表有100萬記錄，一個數(shù)值型字段 A， 30 萬記錄的A=0 ， 30 萬記錄的 A=1 ， 39 萬記錄的 A=2 ，1萬記錄的 A=3 。那么執(zhí)行A>2 與 A>=3的效果就有很大的區(qū)別了，因為 A>2 時 ORACLE 會先找出為 2 的記錄索引再進行比較，而 A>=3 時 ORACLE 則直接找到 =3 的記錄索引。
?
5、IS NULL 或 IS NOT NULL 操作（判斷字段是否為空）
判斷字段是否為空一般是不會應(yīng)用索引的，因為B 樹索引是不索引空值的。
?
推薦方案：
?
用其它相同功能的操作運算代替，如
?
a is not null 改為 a>0 或 a>” 等。
?
不允許字段為空，而用一個缺省值代替空值，如業(yè)擴申請中狀態(tài)字段不允許為空，缺省為申請。
?
建立位圖索引（有分區(qū)的表不能建，位圖索引比較難控制，如字段值太多索引會使性能下降，多人更新操作會增加數(shù)據(jù)塊鎖的現(xiàn)象）
?
6、UNION 操作符
UNION 在進行表鏈接后會篩選掉重復的記錄，所以在表鏈接后會對所產(chǎn)生的結(jié)果集進行排序運算，刪除重復的記錄再返回結(jié)果。實際大部分應(yīng)用中是不會產(chǎn)生重復的記錄，最常見的是過程表與歷史表UNION 。如：
select?*?from?gc_dfys?union?select?*?fromls_jg_dfys
?
這個 SQL在運行時先取出兩個表的結(jié)果，再用排序空間進行排序刪除重復的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會導致用磁盤進行排序。
?
推薦方案：采用UNION ALL 操作符替代UNION ，因為 UNION ALL操作只是簡單的將兩個結(jié)果合并后就返回。
?
7、WHERE 后面的條件順序影響
WHERE 子句后面的條件順序?qū)Υ髷?shù)據(jù)量表的查詢會產(chǎn)生直接的影響，如
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（銷戶標志）兩個字段都沒進行索引，所以執(zhí)行的時候都是全表掃描，第一條SQL 的 dy_dj = ‘1KV以下‘ 條件在記錄集內(nèi)比率為 99% ，而 xh_bz=1 的比率只為 0.5% ，在進行第一條 SQL 的時候 99% 條記錄都進行 dy_dj及xh_bz 的比較，而在進行第二條 SQL 的時候 0.5% 條記錄都進行 dy_dj及xh_bz 的比較，以此可以得出第二條 SQL 的 CPU 占用率明顯比第一條低。
?
8、目標方面的提示：
-
COST （按成本優(yōu)化）
-
RULE （按規(guī)則優(yōu)化）
-
CHOOSE （缺省）（ORACLE自動選擇成本或規(guī)則進行優(yōu)化）
-
ALL_ROWS （所有的行盡快返回）
-
FIRST_ROWS （第一行數(shù)據(jù)盡快返回）
?
9、執(zhí)行方法的提示：
-
USE_NL （使用 NESTED LOOPS 方式聯(lián)合）
-
USE_MERGE （使用 MERGE JOIN 方式聯(lián)合）
-
USE_HASH （使用 HASH JOIN 方式聯(lián)合）
?
10、索引提示：
INDEX （ TABLE INDEX）（使用提示的表索引進行查詢）
?
11、其它高級提示（如并行處理等等）
ORACLE 的提示功能是比較強的功能，也是比較復雜的應(yīng)用，并且提示只是給ORACLE執(zhí)行的一個建議，有時如果出于成本方面的考慮 ORACLE也可能不會按提示進行。根據(jù)實踐應(yīng)用，一般不建議開發(fā)人員應(yīng)用ORACLE提示，因為各個數(shù)據(jù)庫及服務(wù)器性能情況不一樣，很可能一個地方性能提升了，但另一個地方卻下降了，ORACLE 在 SQL執(zhí)行分析方面已經(jīng)比較成熟，如果分析執(zhí)行的路徑不對首先應(yīng)在數(shù)據(jù)庫結(jié)構(gòu)（主要是索引）、服務(wù)器當前性能（共享內(nèi)存、磁盤文件碎片）、數(shù)據(jù)庫對象（表、索引）統(tǒng)計信息是否正確這幾方面分析。
?
12、IN和EXISTS
有時候會將一列和一系列值相比較。最簡單的辦法就是在where子句中使用子查詢。在where子句中可以使用兩種格式的子查詢。
?
第一種格式是使用IN操作符：
...?where?column?in(select?*?from?...?where...);
?
第二種格式是使用EXIST操作符：
...?where?exists?(select?'X'?from?...where...);
?
我相信絕大多數(shù)人會使用第一種格式，因為它比較容易編寫，而實際上第二種格式要遠比第一種格式的效率高。在Oracle中可以幾乎將所有的IN操作符子查詢改寫為使用EXISTS的子查詢。
?
第二種格式中，子查詢以’select ‘X’開始。運用EXISTS子句不管子查詢從表中抽取什么數(shù)據(jù)它只查看where子句。這樣優(yōu)化器就不必遍歷整個表而僅根據(jù)索引就可完成工作（這里假定在where語句中使用的列存在索引）。相對于IN子句來說，EXISTS使用相連子查詢，構(gòu)造起來要比IN子查詢困難一些。
?
通過使用EXIST，Oracle系統(tǒng)會首先檢查主查詢，然后運行子查詢直到它找到第一個匹配項，這就節(jié)省了時間。Oracle系統(tǒng)在執(zhí)行IN子查詢時，首先執(zhí)行子查詢，并將獲得的結(jié)果列表存放在在一個加了索引的臨時表中。在執(zhí)行子查詢之前，系統(tǒng)先將主查詢掛起，待子查詢執(zhí)行完畢，存放在臨時表中以后再執(zhí)行主查詢。這也就是使用EXISTS比使用IN通常查詢速度快的原因。
?
同時應(yīng)盡可能使用NOT EXISTS來代替NOT IN，盡管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查詢效率更高。
?
任何在where子句中使用is null或is notnull的語句優(yōu)化器是不允許使用索引的。
?
13、order by語句
ORDER BY語句決定了Oracle如何將返回的查詢結(jié)果排序。Orderby語句對要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中（象聯(lián)接或者附加等）。任何在Orderby語句的非索引項或者有計算表達式都將降低查詢速度。
仔細檢查orderby語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是重寫orderby語句以使用索引，也可以為所使用的列建立另外一個索引，同時應(yīng)絕對避免在orderby子句中使用表達式。
14、NOT
我們在查詢時經(jīng)常在where子句使用一些邏輯表達式，如大于、小于、等于以及不等于等等，也可以使用and（與）、or（或）以及not（非）。NOT可用來對任何邏輯運算符號取反。下面是一個NOT子句的例子：
...?where?not?(status?='VALID')
如果要使用NOT，則應(yīng)在取反的短語前面加上括號，并在短語前面加上NOT運算符。NOT運算符包含在另外一個邏輯運算符中，這就是不等于（）運算符。換句話說，即使不在查詢where子句中顯式地加入NOT詞，NOT仍在運算符中，見下例：
...?where?status?'INVALID';
再看下面這個例子：
select?*?from?employee?where?salary3000;
對這個查詢，可以改寫為不使用NOT：
select?*?from?employee?where?salary3000;
雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引，而第一種查詢則不能使用索引。
全表掃描就是順序地訪問表中每條記錄.ORACLE采用一次讀入多個數(shù)據(jù)塊(databaseblock)的方式優(yōu)化全表掃描。
15、使用DECODE函數(shù)來減少處理時間
使用DECODE函數(shù)可以避免重復掃描相同記錄或重復連接相同的表。例如:
SELECT?COUNT(*)，SUM(SAL) FROM EMP WHERE?DEPT_NO?=?0020 AND?ENAME?LIKE ‘SMITH%’;
你可以用DECODE函數(shù)高效地得到相同結(jié)果．
SELECT?COUNT(DECODE(DEPT_NO,0020,’X’,NULL))?D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,’X’,NULL))?D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL))?D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL))?D0030_SAL FROM?EMP?WHERE?ENAME?LIKE?‘SMITH%’;
類似的,DECODE函數(shù)也可以運用于GROUP BY 和ORDER BY子句中.
16、用Where子句替換HAVING子句
避免使用HAVING子句, HAVING只會在檢索出所有記錄之后才對結(jié)果集進行過濾.這個處理需要排序,總計等操作.如果能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷.例如:
低效:
SELECT?REGION，AVG(LOG_SIZE) FROM?LOCATION GROUP?BY?REGION HAVING?REGION?REGION?!=?‘SYDNEY’ AND?REGION?!=?‘PERTH’
高效:
SELECT?REGION，AVG(LOG_SIZE) FROM?LOCATION WHERE?REGION?REGION?!=?‘SYDNEY’ AND?REGION?!=?‘PERTH’ GROUP?BY?REGION
17、減少對表的查詢
在含有子查詢的SQL語句中,要特別注意減少對表的查詢.例如:
低效:
SELECT?TAB_NAME FROM?TABLES WHERE?TAB_NAME?=?(?SELECT?TAB_NAME FROM?TAB_COLUMNS WHERE?VERSION?=?604) AND DB_VER=?(?SELECT?DB_VER FROM?TAB_COLUMNS WHERE?VERSION?=?604)
高效:
SELECT?TAB_NAME FROM?TABLES WHERE?(TAB_NAME,DB_VER) =?(?SELECT?TAB_NAME,DB_VER) FROM?TAB_COLUMNS WHERE?VERSION?=?604) Update?多個Column?例子:
低效:
UPDATE?EMP SET?EMP_CAT?=?(SELECT?MAX(CATEGORY)?FROM?EMP_CATEGORIES), SAL_RANGE?=?(SELECT?MAX(SAL_RANGE)?FROM?EMP_CATEGORIES) WHERE?EMP_DEPT?=?0020;
高效:
UPDATE?EMP SET?(EMP_CAT,?SAL_RANGE) =?(SELECT?MAX(CATEGORY)?,?MAX(SAL_RANGE) FROM?EMP_CATEGORIES) WHERE?EMP_DEPT?=?0020;
18、通過內(nèi)部函數(shù)提高SQL效率.
SELECT?H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*) FROM?HISTORY_TYPE?T,EMP?E,EMP_HISTORY?H WHERE?H.EMPNO?=?E.EMPNO AND?H.HIST_TYPE?=?T.HIST_TYPE GROUP?BY?H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
通過調(diào)用下面的函數(shù)可以提高效率.
FUNCTION?LOOKUP_HIST_TYPE(TYP?IN?NUMBER)?RETURN?VARCHAR2 AS TDESC?VARCHAR2(30); CURSOR?C1?IS SELECT?TYPE_DESC FROM?HISTORY_TYPE WHERE?HIST_TYPE?=?TYP; BEGIN OPEN?C1; FETCH?C1?INTO?TDESC; CLOSE?C1; RETURN?(NVL(TDESC,’?’)); END; FUNCTION?LOOKUP_EMP(EMP?IN?NUMBER)?RETURN?VARCHAR2 AS ENAME?VARCHAR2(30); CURSOR?C1?IS SELECT?ENAME FROM?EMP WHERE?EMPNO=EMP; BEGIN OPEN?C1; FETCH?C1?INTO?ENAME; CLOSE?C1; RETURN?(NVL(ENAME,’?’)); END; SELECT?H.EMPNO,LOOKUP_EMP(H.EMPNO), H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*) FROM?EMP_HISTORY?H GROUP?BY?H.EMPNO?,?H.HIST_TYPE;
