Oracle 索引限制、實用準則與優化建議：從 B-Tree 到位圖索引的全維度指南

索引失效「四大經典陷阱」與破解之道

陷阱 1：不等於操作符 (<>、!=) 導致全表掃描

-- 失效場景
SELECT * FROM dept WHERE dept_id <> 1000; -- 即使有 dept_id 索引，仍 FTS

-- 破解：改寫為範圍 OR 條件 (利用索引範圍掃描)
SELECT * FROM dept WHERE dept_id < 1000 OR dept_id > 1000;
-- 或使用 UNION ALL (避免重複排序)
SELECT * FROM dept WHERE dept_id < 1000
UNION ALL
SELECT * FROM dept WHERE dept_id > 1000;

原理：優化器無法通過「不等於」定位索引範圍，只能掃描全表。範圍 OR 條件可轉為兩次索引範圍掃描合併。

陷阱 2：IS NULL / IS NOT NULL 導致索引失效 (B-Tree 索引不存 NULL)

-- 失效場景
SELECT * FROM emp WHERE commission IS NULL; -- B-Tree 索引不存全 NULL 行，FTS

-- 破解方案：
-- 1. 建表時定義 NOT NULL (業務允許)
ALTER TABLE emp MODIFY commission DEFAULT 0 NOT NULL; -- 0 替代 NULL，索引生效

-- 2. 創建函數索引 (FBI) 包含 NULL
CREATE INDEX idx_emp_comm_nvl ON emp(NVL(commission, -1));
SELECT * FROM emp WHERE NVL(commission, -1) = -1; -- 索引生效

-- 3. 位圖索引 (天然包含 NULL，適合低基數列)
CREATE BITMAP INDEX bmx_emp_comm ON emp(commission);
SELECT * FROM emp WHERE commission IS NULL; -- 位圖索引生效

核心知識：B-Tree 索引**不存儲完全為 NULL 的鍵值**，故 `IS NULL` 無法走索引；位圖索引**存儲 NULL**，適合低基數列 (性別、狀態、類型) 的 NULL 判斷。

陷阱 3：索引列參與函數/運算/隱式轉換 導致索引失效

-- 失效場景
SELECT * FROM emp WHERE UPPER(ename) = 'SMITH';           -- 函數
SELECT * FROM emp WHERE sal * 12 > 50000;                -- 運算
SELECT * FROM emp WHERE dept_id = '10';                  -- 隱式轉換 (dept_id NUMBER, '10' VARCHAR2)
SELECT * FROM emp WHERE TRUNC(hiredate) = DATE '2023-01-01'; -- 函數

-- 破解方案：
-- 1. 函數索引 (FBI) / 虛擬列索引 (12c+)
CREATE INDEX idx_emp_upper_ename ON emp(UPPER(ename));
CREATE INDEX idx_emp_annual_sal ON emp(sal * 12);
-- 虛擬列 (12c+)
ALTER TABLE emp ADD (annual_sal GENERATED ALWAYS AS (sal * 12) VIRTUAL);
CREATE INDEX idx_emp_annual_sal ON emp(annual_sal);

-- 2. 條件改寫 (將函數/運算移至常量側)
SELECT * FROM emp WHERE hiredate >= DATE '2023-01-01' AND hiredate < DATE '2023-01-02'; -- 範圍查詢走索引
SELECT * FROM emp WHERE dept_id = 10; -- 顯式類型匹配

-- 3. 避免隱式轉換：應用層綁定變量類型與列定義一致

陷阱 4：復合索引列順序錯誤 / WHERE 子句未包含前導列

-- 索引：CREATE INDEX idx_emp_dept_job ON emp(dept_id, job_id);
-- 失效場景
SELECT * FROM emp WHERE job_id = 'CLERK'; -- 未包含前導列 dept_id，索引失效 (除非 INDEX SKIP SCAN 10g+)

-- 破解：
-- 1. 調整索引列順序：高選擇性/等值列優先
--    WHERE dept_id=10 AND job_id='CLERK' → 索引 (dept_id, job_id) 最優
--    WHERE job_id='CLERK' AND dept_id=10 → 索引 (job_id, dept_id) 最優
-- 2. 創建單獨索引或額外索引
CREATE INDEX idx_emp_job ON emp(job_id);
-- 3. INDEX SKIP SCAN (10g+ 自動) 適用於前導列基數極低 (如性別 M/F) 時

Oracle 索引類型全景與適用場景決策表

索引創建與維護「六大鐵律」

1. 導數據後再建索引：`SQL*Loader Direct Path` / `INSERT /*+ APPEND */` / `CTAS` 導入大量數據時，**先禁用/刪除索引，導入完成後重建**，避免逐行維護索引產生大量 REDO/UNDO、碎片、耗時。
2. 小表不建索引：表塊數 < `DB_FILE_MULTIBLOCK_READ_COUNT` (通常 128/256)，FTS 成本極低，索引反而增加維護開銷。**經驗值：< 10,000 行或 < 100MB 審慎評估**。
3. 低基數列建位圖索引：性別、狀態、類型、是否標誌、地區代碼等 **NDV (Number of Distinct Values) / 行數 < 1%**，首選位圖索引。**嚴禁 OLTP 高併發表使用位圖索引**。
4. 限制表上索引數量：每個索引增加 INSERT/UPDATE/DELETE 開銷 (維護 B-Tree/位圖、生成 REDO/UNDO)、**建議單表索引 ≤ 5-8 個**，核心業務表更少。定期掃描 `DBA_INDEXES` 刪除未用索引 (`OBJECT_USAGE` 監控)。
5. 設置合適 PCTFREE：索引葉塊預留空間減少分裂。`CREATE INDEX ... PCTFREE 10` (默認 10)，**頻繁 INSERT 熱點索引可調大至 20-30**；唯讀/批量載入索引可設 0。
6. 索引表空間獨立：`CREATE INDEX ... TABLESPACE app_index`，**分離數據與索引 I/O**，便於備份恢復策略差異化 (索引可重建、無需備份)、存儲分級 (索引放高性能 SSD)。

復合索引列順序黃金法則

, <, >=, <=, LIKE 'prefix%', BETWEEN) → 放等值列之後
3. 排序列 (ORDER BY) → 放範圍列之後 (若範圍列選擇性極高可前置)
4. 低選擇性列 (如狀態) → 盡量不放入索引，或放最後

實戰案例：
WHERE dept_id = 10           -- 等值
  AND status = 'ACTIVE'      -- 等值 (低基數)
  AND hiredate > '2023-01-01' -- 範圍
  ORDER BY sal DESC;          -- 排序

最優索引列順序：
(dept_id, status, hiredate, sal DESC)
或 (dept_id, hiredate, status, sal DESC) -- 若 hiredate 選擇性極高

索引監控與廢棄清理機制

-- 1. 開啟索引使用監控 (11g+ 推薦統一審計替代)
ALTER INDEX idx_emp_name MONITORING USAGE;

-- 2. 定期檢查 (如每周)
SELECT io.name index_name, ou.start_monitoring, ou.end_monitoring, ou.used
FROM sys.object_usage ou
JOIN dba_objects io ON ou.obj# = io.object_id
WHERE io.owner = 'SCHEMA_CORE' AND io.object_type = 'INDEX';

-- 3. 長期未用 (used='NO' 且監控期 > 30 天) → 評估刪除
--    注意：外鍵索引、唯一約束索引、主鍵索引不可刪除
--    刪除前驗證：SQL Tuning Advisor / SPM 計劃依賴 / 應用代碼搜索

-- 4. 碎片化檢測 (高度/葉塊/聚簇因子)
ANALYZE INDEX idx_emp_name VALIDATE STRUCTURE;
SELECT height, lf_rows, lf_blks, br_rows, br_blks, del_lf_rows, pct_used
FROM index_stats;
-- height > 4 或 pct_used < 50% 或 del_lf_rows/lf_rows > 20% → 考慮 REBUILD/COALESCE

-- 5. 在線重建 (生產無停機)
ALTER INDEX idx_emp_name REBUILD ONLINE TABLESPACE app_index COMPRESS ADVANCED LOW;
-- 或合併碎片 (輕量)
ALTER INDEX idx_emp_name COALESCE;

總結：索引優化決策流程圖

1. **確認業務查詢模式** (WHERE/JOIN/ORDER BY/GROUP BY 列組合)。
2. **收集統計信息** (表/索引/列/直方圖)，確保優化器決策基準準確。
3. **分析執行計劃** (`DISPLAY_CURSOR ALLSTATS LAST`)，識別 FTS/索引失效/掃描過廣/排序溢寫。
4. **應用破解法則**：改寫 SQL (不等於→範圍 OR、函數移常量側)、創建 FBI/虛擬列索引、調整復合索引列順序 (等值>範圍>排序)。
5. **選擇正確索引類型**：OLTP 高基數 → B-Tree；DW 低基數 → 位圖；熱點序列 → 反向鍵；降序排序 → 降序索引；全文/空間 → 域索引。
6. **遵循六大鐵律**創建/維護，獨立表空間、限制數量、合適 PCTFREE。
7. **建立監控清理機制**：定期 `MONITORING USAGE`、`ANALYZE VALIDATE STRUCTURE`、刪除廢棄索引、重建碎片索引。
8. **驗證效果**：對比調優前後邏輯讀、物理讀、CPU、Elapsed Time、執行計劃變化。