繼上篇排序優化差異後，本文聚焦索引設計哲學、物理存儲佈局權衡、SQL 改寫模式三大維度，剖析兩大引擎在查詢優化層面的核心差異與遷移適配策略。
索引策略：相似語法、不同代價模型
共同最佳實踐
- 高選擇性列建立 B+ 樹索引 (唯一/非唯一)；
- 覆蓋索引 (INCLUDE 列) 消除鍵查找；
- 復合索引列順序 = 等值過濾列 → 範圍過濾列 → 排序/分組列；
- 避免過度索引：寫入放大、統計維護、優化器搜索空間膨脹。
核心差異：索引與數據物理分離能力
| 能力 | Oracle | SQL Server |
|---|
| 表空間獨立管理 | ✅ 表、索引、LOB、臨時段可分佈在不同表空間 → 不同磁盤組 | ❌ 文件組級別，但**單表聚集索引/堆與非聚集索引無法分離到不同文件組** (僅分區表可將分區放不同文件組) |
| I/O 隔離效果 | 極佳：索引掃描不爭搶數據文件磁臂/通道 | 受限：同文件組內競爭，需通過分區/文件組規劃緩解 |
| 遷移適配 | 原生支持 | 需重構為分區表或接受 I/O 競爭 |
架構根因：Oracle 表空間是邏輯存儲容器，段 (Segment) 可任意映射數據文件；SQL Server 文件組包含文件，表/索引創建時綁定文件組，**聚集索引/堆與其非聚集索引默認同文件組**，無 `CREATE INDEX ... ON FILEGROUP` 獨立指定語法 (除非分區表)。
SQL 改寫模式：從規則優化到成本優化的思維轉變
Oracle 歷史包袱：規則優化器 (RBO) 遺留規則
- 舊版應用常含 `/*+ RULE */` 或隱式依賴 RBO 行為 (如 `WHERE col = :bind` 不走索引)；
- 遷移至 SQL Server (純 CBO) 需清理 Hint、重寫 `DECODE`/`NVL` → `CASE`/`COALESCE`、`(+)` 外連接 → `LEFT JOIN`；
- `ROWNUM` 分頁 → `OFFSET/FETCH` (2012+) / `ROW_NUMBER()`。
SQL Server 陷阱：參數嗅探與計劃緩存
- 存儲過程首次編譯嗅探參數值 → 計劃緩存 → 後續不同參數復用計劃 → 性能崩塌；
- 對策：`OPTION (RECOMPILE)`、`OPTIMIZE FOR UNKNOWN`、局部變量賦值、動態 SQL、計劃指南；
- Oracle 11g+ ACS (自適應游標共享) 緩解此問題，SQL Server 2022+ PSP (參數敏感計劃) 開始跟進。
通用改寫模式 (雙引擎通用)
| 反模式 | 優化模式 | 原理 |
|---|
| `WHERE func(col) = val` | `WHERE col = func_inv(val)` / 計算列 + 索引 | SARGable，索引可用 |
| `SELECT *` | 顯式列清單 + 覆蓋索引 | 減少 I/O、網絡、內存、鎖 |
| 相關子查詢 (逐行執行) | JOIN / APPLY / 窗口函數 | 集合操作、批次處理 |
| `IN (SELECT ...)` 大結果集 | `EXISTS` / `JOIN` / 物化表變量 | 半連接優化、避免物化大集合 |
| 隱式轉換 `col = '123'` (col 為 INT) | 顯式 `CAST` / 參數類型匹配 | 避免索引失效、CPU 浪費 |
統計信息管理：優化器的眼睛
- Oracle：`DBMS_STATS.GATHER_TABLE_STATS`、自動收集作業 (維護窗口)、動態採樣 (級別 2-11)；
- SQL Server：`UPDATE STATISTICS` / `sp_updatestats`、自動創建/更新 (AUTO_CREATE_STATISTICS / AUTO_UPDATE_STATISTICS)、增量統計 (分區表 2014+)；
- 共同點：**大表採樣率 20-30%、直方圖針對傾斜列、查詢負載驅動統計策略、定期全掃描關鍵表**。
遷移適配清單
- 索引重構：Oracle 非分區表索引分離 → SQL Server 分區表對齊文件組或接受同文件組 I/O；
- Hint 清理：移除 Oracle Hint (`/*+ INDEX */` 等)，依賴 SQL Server CBO，必要時用 `FORCESEEK`/`FORCESCAN`/`OPTION`；
- 分頁重寫：`ROWNUM` → `OFFSET/FETCH` / `ROW_NUMBER()`；
- 外連接語法：`(+)` → `LEFT/RIGHT/FULL JOIN`；
- 函數映射：`NVL`→`ISNULL/COALESCE`、`DECODE`→`CASE`、`SYSDATE`→`GETDATE()`、`DUAL` 表移除；
- 參數化重構：應用層參數化 → 存儲過程參數 → 警惕嗅探，配合 `RECOMPILE` 策略；
- 並行度控制：Oracle `PARALLEL` Hint → SQL Server `MAXDOP` / `Resource Governor` / `trace flag 8649` (慎用)。
總結
查詢優化差異本質是存儲架構 (表空間 vs 文件組)、優化器演進 (RBO→CBO vs 純 CBO)、並行模型 (PX vs 並行計劃)的投影。遷移非語法轉換，而是重新校準優化器代價模型假設：統計信息、索引佈局、參數化策略、並行調優。以「業務 SLA 為錨，執行計劃為鏡，持續迭代為矛」，構建跨平台性能基線。
