Oracle 查詢最佳化的基本準則詳解

1：在進行多表關聯時，多用 Where 語句把單個表的結果集最小化，多用彙總函式匯總結果集後再與其它表做關聯，以使結果集資料量最小化
2：在兩張表進行關聯時，應考慮可否使用右串連。以提高查詢速度
3：使用 where 而不是 having ，where是用於過濾行的，而having是用來過濾組的，因為行被分組後，having 才能過濾組，所以盡量使用者 WHERE 過濾
4：使用 exists 而不用 IN 因為 Exists 只檢查行的存在，而 in 檢查實際值。
5：IN操作符
用 IN 寫出來的 SQL 的優點是比較容易寫及清晰易懂，這比較適合現代軟體開發的風格。
但是用 IN 的 SQL 效能總是比較低，原因是：
對於用 IN 的 SQL 陳述式 ORACLE 總是試圖將其轉換成多個表的串連，如果轉換不成功則先執行 IN
裡面的子查詢，再查詢外層的表記錄
如果轉換成功就轉換成多個表的串連。因此 不管理怎麼，用 IN 的 SQL 陳述式總是多了 一個轉換的
過程。一般的 SQL 都可以轉換成功。
但對於含有分組統計等方面的 SQL 就不能轉換了。因此在業務密集的SQL當中盡量不採用IN操作符。
6：NOT IN 操作符
此操作強烈推薦不使用，因為其不能應用表的索引。
如遇這種情況，應該用 EXISTS ，NOT EXISTS 或者（外串連+判斷為空白）方案代替。
7：<> 操作符
不等於操作符是永遠不會用到索引的，因此對它的處理只會產生全表掃描。
對於這種情況，可以用其它方式代替，如：
A<>0 -> A>0 OR A<0
A<>'' -> A>''
8：like 操作符
遇到 需要用到 LIKE 過濾的SQL語句，完全可以用 instr 代替。處理速度將顯著提高。
9：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

10 SQL書寫的影響
同一功能同一效能不同寫法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的執行頻率。
11: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佔用率明顯比第一條低。
12:詢表順序的影響
在FROM後面的表中的列表順序會對SQL執行效能影響，在沒有索引及ORACLE沒有對錶進行統計分析的
情況下ORACLE會按表出現的順序進行連結，
由此因為表的順序不對會產生十分耗伺服器資源的資料交叉。（註：如果對錶進行了統計分析，
ORACLE會自動先進小表的連結，再進行大表的連結）
13:採用函數處理的欄位不能利用索引，如：
複製代碼 代碼如下: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'
14:應用ORACLE的HINT（提示）處理
提示處理是在ORACLE產生的SQL分析執行路徑不滿意的情況下要用到的。它可以對SQL進行以下方
面的提示
目標方面的提示：
COST（按成本最佳化）
RULE（按規則最佳化）
CHOOSE（預設）（ORACLE自動選擇成本或規則進行最佳化）

SELECT EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
ALL_ROWS（所有的行儘快返回）
SELECT EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
FIRST_ROWS（第一行資料儘快返回）
select *
from xxx
where xxx;
select *
from xxx
where xxx
最佳化器提示：用它的目的是提高SQL語句的回應時間，快速的先返回 n 行。
訪問路徑的提示
FULL: 執行全表掃描
ROID: 根據ROWID進行掃描
INDEX: 根據某個索引進行掃描
select * from emp where deptno=200 and sal>300;
如果寫了多個，則ORACLE自動選擇最優的哪個
select * from emp where deptno=200 and sal>300;
INDEX_JOIN: 如果所選的欄位都是索引欄位(是幾個索引的)，那麼可以通過索引串連就可訪問到資料，而不需要訪問
表的資料。

select deptno,sal from emp
where deptno=20;
INDEX_FFS: 執行快速全索引掃描

select count(*) from emp;
NO_INDEX: 指定不使用哪些索引

select * from emp where deptno=200
and sal>300;
AND_EQUAL: 指定合并兩個或以上索引檢索的結果(交集)，最多不能超過5個

執行方法的提示：
USE_NL（使用NESTED LOOPS方式聯合）
USE_MERGE（使用MERGE join方式聯合）
USE_HASH（使用HASH join方式聯合）

根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其串連.
例如:
SELECT A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C
WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

將指定表與嵌套的串連的行源進行串連,並把指定表作為內部表.
例如:
SELECT BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM
FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

將指定的表與其他行源通過合并排序串連方式串連起來.
例如:
SELECT * FROM BSEMPMS,BSDPTMS
WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

將指定的表與其他行源通過雜湊串連方式串連起來.
例如:
SELECT * FROM BSEMPMS,BSDPTMS
WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
其它進階提示（如平行處理等等）
ORACLE的提示功能是比較強的功能，也是比較複雜的應用，並且提示只是給ORACLE執行的一個建議，
有時如果出於成本方面的考慮ORACLE也可能不會按提示進行。根據實踐應用，一般不建議開發人員應用ORACLE提示，
因為各個資料庫及伺服器效能情況不一樣，很可能一個地方效能提升了，但另一個地方卻下降了，
ORACLE在SQL執行分析方面已經比較成熟，如果分析執行的路徑不對首先應在資料庫結構（主要是索引）、
伺服器當前效能（共用記憶體、磁碟檔案片段）、資料庫物件（表、索引）統計資訊是否正確這幾方面分析。