SELECT *：減少網路傳輸、解析開銷、避免新增欄位導致應用錯誤。WHERE id = :b1 而非字面值，促進游標共用、減少硬解析、防 SQL 注入。JOIN)，其次高選擇性過濾條件，最後低選擇性條件。優化器會自動重排，但良好順序利於閱讀與 Hint 生效。WHERE num_col = '123' 導致全表掃描，改為 WHERE num_col = 123 或 TO_NUMBER。EXISTS 替代 IN (子查詢)：EXISTS 可在找到第一筆即停止，IN 可能物化子查詢結果集；NOT EXISTS 優於 NOT IN (後者含 NULL 時語意不同且效能差)。WHERE TRUNC(date_col) = SYSDATE → 失效索引，改為 WHERE date_col >= TRUNC(SYSDATE) AND date_col < TRUNC(SYSDATE)+1。UNION ALL 替代 UNION：無需去重排序，效能差異巨大；業務允許重複時優先 UNION ALL。OFFSET ... FETCH NEXT ...；舊版用 ROW_NUMBER() OVER 或 ROWNUM 兩層嵌套，避免 ROWNUM 直接配合 ORDER BY 導致全排序。DECODE/CASE 條件聚合、PIVOT/UNPIVOT、單次掃描計算多指標，減少 I/O。ROWID：DELETE FROM t WHERE ROWID > (SELECT MIN(ROWID) FROM t x WHERE x.key = t.key); 單語句、無需臨時表。INDEX (a,b,c) 支援 WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3，不支援 WHERE b=2。INDEX (a,b) INCLUDE (c) 12c+)，避免回表 (Table Access by Index Rowid)。ALTER INDEX ... REBUILD ONLINE 或 COALESCE，消除碎片、回收空間、更新統計資訊。ALTER INDEX ... MONITORING USAGE → USER_OBJECT_USAGE，長期未用者評估刪除。DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')，直方圖自動識別傾斜欄位。DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 建立 SQL Plan Baseline，防止統計變動導致計畫突變。DBMS_XPLAN.DISPLAY_CURSOR 觀察實際執行計畫：關注 E-Rows vs A-Rows、Cost、Access Predicates/Filter Predicates、Pstart/Pstop (分區裁剪)。/*+ INDEX(t idx_name) */、/*+ LEADING(t1 t2) */、/*+ OPT_PARAM('_optim_peek_user_binds','false') */ 等，作為臨時干預，長期靠統計與 Baseline。CREATE MATERIALIZED VIEW ... ENABLE QUERY REWRITE，預聚合、預聯結，透明加速報表查詢。RESULT_CACHE_MODE = FORCE 或 Hint /*+ RESULT_CACHE */，適用於頻繁查詢、低更新頻率的參考表。OPTIMIZER_MODE、OPTIMIZER_INDEX_COST_ADJ、OPTIMIZER_INDEX_CACHING、CURSOR_SHARING，需在測試環境驗證後再上線。| 反模式 | 問題 | 修正 |
|---|---|---|
WHERE col LIKE '%abc%' | 前導萬用字元導致全表掃描 | Oracle Text / 反向鍵索引 / 應用層前綴搜尋 |
SELECT DISTINCT * FROM huge_table | 大量排序/雜湊去重 | 業務確認是否必要、改用 GROUP BY、增加唯一索引 |
隱性轉換 WHERE num = '123' | 索引失效、計畫錯誤 | 應用層修正資料型別、或建立函數索引 TO_NUMBER(col) |
| 長交易不提交 | Undo 膨脹、鎖爭用、Snapshot Too Old | 拆分批次、頻繁 Commit、調整 UNDO_RETENTION |
| 游標未關閉 (應用層) | ORA-01000、Library Cache 壓力 | 連線池配置 maxOpenPreparedStatements、代碼審查 finally 關閉 |
[ ] 是否使用綁定變數？
[ ] 是否避免 SELECT *？
[ ] WHERE 條件是否利用索引 (無函數/運算/隱性轉換)？
[ ] 是否收集最新統計資訊 (含直方圖)？
[ ] 執行計畫是否符合預期 (無全表掃描、無笛卡爾積)？
[ ] 是否有排序/雜湊操作溢寫磁碟 (PGA 足夠？)
[ ] 大表是否分區/平行？
[ ] 是否有 Baseline 保護關鍵 SQL？
[ ] 索引是否過多/未使用/碎片化？
[ ] 應用層是否正確關閉游標/連線？SQL 優化是「寫對語法 → 建對索引 → 收好統計 → 看懂計畫 → 鎖定計畫 → 持續監控」的工程循環。沒有萬能銀彈，只有針對具體負載、數據分佈、業務容忍度的持續微調。
