核心優化策略分類
1. 優化器模式選擇
- 三種模式：RULE(規則)、COST(成本)、CHOOSE(選擇)。
- 透過
OPTIMIZER_MODE (RULE/COST/CHOOSE/ALL_ROWS/FIRST_ROWS) 設定預設，可在 SQL 或 Session 層級覆蓋。
- 使用 CBO 必須定期
ANALYZE 更新物件統計資訊。
- CHOOSE 模式下，表已分析則用 CBO，否則回退 RULE。建議顯式指定 RULE 或 COST，避免非預期全表掃描。
2. 表存取路徑
- 全表掃描：循序讀取每筆記錄，多區塊讀取優化。
- ROWID 存取：透過索引快速取得 ROWID 再存取表，基於索引欄位查詢效能最佳。
3. 共享 SQL (Shared Pool)
- 首次解析後存入 SGA Shared Pool，完全相同 SQL (含空格、換行) 可共用解析樹與執行計劃。
- 需在
init.ora 設定適當 Shared Pool 大小。
- 共享條件：字元級完全相同、引用物件完全相同(含同義詞解析)、綁定變數名稱相同。
4. 連接順序與 WHERE 子句編寫 (RBO 有效)
- FROM 子句由右至左處理，最後的表為驅動表，選擇記錄最少的表作為驅動表。
- 多表連接 >3 表時，選擇交叉表(被其他表引用)作為驅動表。
- WHERE 子句自下而上解析：表連接條件寫在前，過濾力最強的條件寫在最後。
5. 常見改寫技巧
- 避免 SELECT *：解析時需查資料字典轉換欄位名，額外開銷大。
- 減少資料庫呼叫：合併多次查詢為單一 SQL (如自連接或 IN 列表)。
- 善用 DECODE/CASE：單次掃描完成多條件聚合，避免重複掃描或連接。
- 整合無關聯簡單查詢：利用 DUAL 與外部連接合併為單一查詢。
- 刪除重複記錄：
DELETE FROM emp e WHERE e.rowid > (SELECT MIN(x.rowid) FROM emp x WHERE x.emp_no = e.emp_no);
- TRUNCATE 替代 DELETE：不產生 Rollback、Redo，速度極快 (DDL 操作，不可回滾)。
- 適度 COMMIT：釋放 Rollback、鎖、Redo Log Buffer、內部管理資源 (注意事務完整性)。
- 計數優化：
COUNT(*) 略快於 COUNT(1)，有索引時 COUNT(index_col) 最快。
- WHERE 替代 HAVING：HAVING 在分組後過濾，需排序總計；WHERE 先過濾減少開銷。
- 減少子查詢重複存取表：合併子查詢欄位 (如
WHERE (col1,col2) = (SELECT col1,col2 ...))。
- PL/SQL 函數封裝查詢邏輯：將複雜連接移入函數，主查詢變簡單，提升效率。
- 使用表別名並前綴欄位：減少解析時間，避免欄位歧義錯誤。
- EXISTS 替代 IN：子查詢驗證存在性時，EXISTS 效率更高。
- NOT EXISTS 替代 NOT IN：NOT IN 需內部排序合並、全表掃描，最低效；改寫為外部連接或 NOT EXISTS。
- 表連接替代 EXISTS：通常連接效率優於 EXISTS (RBO 下 NESTED LOOP vs FILTER)。
- EXISTS 替代 DISTINCT：一對多查詢去重時，EXISTS 讓 RDBMS 滿足條件即返回，避免排序去重。
6. 診斷與分析工具
- 識別低效 SQL：查詢
V$SQLAREA 計算緩存命中率、每次執行磁碟讀取，定位問題 SQL。
- TKPROF：
ALTER SESSION SET SQL_TRACE TRUE 或 init.ora 設 SQL_TRACE=TRUE、TIMED_STATISTICS=TRUE 產生 Trace 檔，再用 TKPROF 轉換分析。
- EXPLAIN PLAN / AUTOTRACE：不執行 SQL 即可分析連接順序、掃描方式、索引使用。解讀原則：由內而外、由上而下；NESTED LOOP 需檢查提供資料的操作。
7. 索引應用原則
- 索引加速查詢與 UPDATE，提供 PK 唯一性驗證；除 LONG/LOB 外幾乎所有欄位可建索引。
- 大表索引效益明顯，小表同樣有效。代價：額外儲存空間、DML 時維護開銷 (每筆 INSERT/DELETE/UPDATE 額外 4-5 次 I/O)。不必要索引反而拖慢查詢。
- 定期
ALTER INDEX REBUILD 重建索引。
8. 索引掃描模式
- INDEX UNIQUE SCAN：WHERE 條件命中唯一索引，單值查找。若 SELECT 列包含於索引，省略 TABLE ACCESS BY ROWID。
- INDEX RANGE SCAN：範圍查詢 (LIKE 'M%') 或非唯一索引查詢。效率略低於 UNIQUE SCAN。
- WHERE 索引欄位首字元為萬用字元 (LIKE '%...') 時，索引失效，轉全表掃描。
9. 驅動表選擇
- CBO：依物理大小、索引狀態選擇成本最低路徑。
- RBO：若所有連接條件有索引，FROM 子句最後的表為驅動表；否則無索引的表優先。
10. 多索引合併與比較規則
- 多表多索引時，Oracle 合併記錄取交集。唯一索引優先級 > 非唯一索引 (僅限索引欄位與常數比較)。
- 同等級索引：FROM 子句順序決定優先權 (最後的表最高)；同表同等級：WHERE 子句出現順序決定 (最先出現最高)。
- 等值比較與範圍比較混合時，Oracle 採用範圍比較 (如
DEPTNO > 20 AND EMP_CAT = 'A' 僅用 EMP_CAT 索引)。
- 無法判斷優先級時，僅使用 WHERE 子句最前面的索引欄位。
掌握這 30 條法則，配合 EXPLAIN PLAN 與 TKPROF 實測驗證，將大幅提升 Oracle SQL 執行效能。
