Oracle 索引优化指南：CREATE INDEX命令高级用法

一、Oracle索引核心概念

1.1 索引的本质与作用

索引是Oracle数据库中提高查询性能的关键数据结构，它类似于书籍的目录，允许数据库引擎快速定位数据而不必扫描整个表。索引通过减少I/O操作和排序开销来显著提升查询效率。

1.2 索引类型全景图

索引类型	适用场景	特点	创建语法示例
B树索引	高基数列	标准索引，支持范围查询	CREATE INDEX idx_name ON table(column)
位图索引	低基数列	适合数据仓库，DML操作成本高	CREATE BITMAP INDEX idx_name ON table(column)
函数索引	基于函数的查询	预先计算函数结果	CREATE INDEX idx_name ON table(UPPER(column))
反向键索引	递增键上的插入热点	反转键值分布	CREATE INDEX idx_name ON table(column) REVERSE
降序索引	需要降序扫描	优化ORDER BY DESC	CREATE INDEX idx_name ON table(column DESC)

二、基础索引创建与优化

2.1 标准B树索引创建

-- 基本单列索引
CREATE INDEX idx_emp_lastname ON employees(last_name)
TABLESPACE users
STORAGE (INITIAL 64K NEXT 64K PCTINCREASE 0);

-- 复合索引（多列索引）
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary)
COMPRESS 1;  -- 启用前缀压缩

参数说明：

• TABLESPACE：指定索引存储的表空间
• STORAGE：配置存储参数
• COMPRESS：启用索引键压缩

2.2 索引存储参数优化

CREATE INDEX idx_orders_date ON orders(order_date)
PCTFREE 10  -- 预留10%空间用于更新
INITRANS 4  -- 初始事务槽数
MAXTRANS 255  -- 最大事务槽数
PARALLEL 4  -- 并行度
NOLOGGING;  -- 减少重做日志生成

三、高级索引技术详解

3.1 函数索引实战

-- 大小写不敏感查询优化
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

-- 日期部分查询优化
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM order_date));

-- 使用函数索引后查询
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

3.2 位图索引与位图连接索引

-- 位图索引创建
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

-- 位图连接索引
CREATE BITMAP INDEX idx_emp_dept_name ON employees(d.department_name)
FROM employees e, departments d
WHERE e.department_id = d.department_id;

适用场景对比：

特性	位图索引	B树索引
DML性能	差	好
空间占用	小	大
基数要求	低基数(＜100)	高基数
并发性	差	好
典型场景	数据仓库	OLTP系统

3.3 反向键索引技术

CREATE INDEX idx_emp_id_reverse ON employees(employee_id) REVERSE;

-- 与普通索引对比测试
SELECT * FROM employees WHERE employee_id = 100;  -- 普通索引
SELECT * FROM employees WHERE employee_id = REVERSE('100');  -- 反向索引

四、索引优化策略

4.1 索引选择性分析

-- 计算列的选择性
SELECT 
    column_name, 
    num_distinct, 
    num_rows/num_distinct as selectivity_ratio
FROM 
    user_tab_columns 
WHERE 
    table_name = 'EMPLOYEES'
ORDER BY 
    selectivity_ratio DESC;

选择性参考标准：

• 优秀：＞0.95
• 良好：0.7-0.95
• 一般：0.3-0.7
• 较差：＜0.3

4.2 索引合并与跳跃扫描

-- 创建优化后的复合索引
CREATE INDEX idx_emp_comp ON employees(department_id, hire_date, salary);

-- 索引跳跃扫描示例
SELECT * FROM employees 
WHERE hire_date > SYSDATE - 365 
AND salary > 10000;
-- 即使未指定department_id，也可能使用索引

五、特殊索引类型

5.1 基于函数的域索引

-- 创建文本域索引
CREATE INDEX idx_docs_content ON documents(text_content)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('SYNC (ON COMMIT)');

-- 空间数据索引
CREATE INDEX idx_properties_geom ON properties(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

5.2 不可见索引与虚拟索引

-- 创建不可见索引（测试用）
CREATE INDEX idx_emp_test ON employees(phone_number) INVISIBLE