Oracle索引全解析：B-tree、聚簇、反向索引創建與失效場景

Oracle索引核心知識體系：類型、語法、失效場景與最佳實踐

索引核心作用與原則

• 提高查詢效率，加速數據檢索
• 過多索引嚴重降低DML (INSERT/UPDATE/DELETE) 效能
• 建立原則：經常作為WHERE條件的列、連接條件列；大量重複值列不宜建索引

索引失效五大場景 (必須避坑)

1. 不等於操作：<> 或 != 導致索引失效，建議改用 < 或 >
2. 前置模糊匹配：LIKE '%abc' 失效，建議用 INSTR 函數替代
3. 列上使用函數：WHERE TO_CHAR(date_col, 'YYYY') = '2023' 失效，除非建立函數式索引 CREATE INDEX idx ON t(TO_CHAR(date_col, 'YYYY')) (注意查詢大小寫需一致)
4. 隱式類型轉換：數據類型不匹配導致隱式轉換，索引失效
5. 列含NULL值：單列索引不存儲NULL，IS NULL查詢失效，建議列定義為 NOT NULL

標準B-tree索引語法

-- 基本創建
CREATE INDEX index_ename ON emp (ename);

-- ONLINE創建 (建立過程中允許DML操作)
CREATE INDEX index_sname ON db_student(sname) ONLINE;

-- 聯合索引 (多列組合，順序影響選擇性)
CREATE INDEX index_emp_sal_job ON emp(sal, job);

-- 刪除索引
DROP INDEX index_hiredate;

聚簇索引：關聯表查詢加速器

將關聯表數據物理存放在一起，提升關聯字段查詢效能。三步驟：

-- 1. 創建聚簇
CREATE CLUSTER cluster_deptno(deptno NUMBER(3));

-- 2. 建表時指定列歸屬聚簇
CREATE TABLE dept (deptno NUMBER(3), dname VARCHAR2(32))
CLUSTER cluster_deptno(deptno);
ALTER TABLE dept ADD CONSTRAINT pk_dept_deptno PRIMARY KEY (deptno);

CREATE TABLE emp (empno NUMBER(5) PRIMARY KEY, ename VARCHAR2(32), age NUMBER(3), sal NUMBER(5), deptno NUMBER(3) REFERENCES dept(deptno))
CLUSTER cluster_deptno(deptno);  -- 外鍵必須在聚簇創建前定義

-- 3. 在聚簇上建立索引 (即聚簇索引)
CREATE INDEX cindex_deptno ON CLUSTER cluster_deptno;

-- 刪除聚簇 (需先刪除依賴表)
DROP CLUSTER cluster_deptno INCLUDING TABLES;

反向索引：消除熱塊競爭

適用於序列生成的遞增鍵值 (如主鍵ID)，反轉鍵值字節分散插入點，減緩右側葉塊熱點競爭。

CREATE INDEX index_stu_sno ON db_student (sno) REVERSE;