Oracle 添加索引核心就是使用 CREATE INDEX 语句，给表的字段建立索引，目的是加快 WHERE 查询、关联 JOIN、ORDER BY 排序的检索速度，索引属于 B 树结构为主，适合高选择性字段，但是索引不是越多越好，索引会占用存储空间，并且 INSERT UPDATE DELETE 的时候数据库需要同步维护索引，写操作性能会下降，所以不能盲目给所有字段都加索引，添加之前要确认 SQL 确实存在全表扫描，通过执行计划验证优化器是否会使用这个索引，Oracle 还支持普通 B 树索引、唯一索引、组合索引、函数索引等多种类型，大表创建索引的时候默认在线创建会锁表，生产大表推荐使用 ONLINE 在线创建，减少对业务的阻塞。索引原理就是给字段建立有序的索引条目，查询的时候不用扫描整张表，直接通过索引定位数据行，但是如果字段区分度很低，比如性别这种只有男和女，就算添加索引，优化器依然会选择全表扫描，索引不会生效，这点是新手最容易踩坑的地方。

第一步 常用索引创建语法

清单

1 普通 B 树索引（最常用）

CREATE INDEX idx_emp_empno ON emp (empno);

2 唯一索引，字段值不允许重复

CREATE UNIQUE INDEX idx_emp_id ON emp (id);

3 组合索引，多个字段联合建立索引，遵循最左前缀原则

CREATE INDEX idx_emp_dept_sal ON emp (deptno,sal);

4 函数索引，字段使用函数查询的时候使用

CREATE INDEX idx_emp_upper_name ON emp (UPPER (ename));

5 在线创建索引，生产大表推荐，创建过程不锁表

CREATE INDEX idx_emp_empno ON emp (empno) ONLINE;

第二步 添加索引完整操作步骤

操作清单

1 先确认表名、字段名，确认该字段经常出现在 WHERE JOIN ORDER BY 条件

2 查看现有索引，避免重复创建同名或者重复索引

SELECT INDEX_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE TABLE_NAME='EMP';

3 执行 CREATE INDEX 创建语句

4 创建完成之后，使用执行计划验证索引是否被 SQL 命中

EXPLAIN PLAN FOR SELECT FROM emp WHERE empno=7788;

SELECT FROM TABLE (DBMS_XPLAN.DISPLAY);

5 如果优化器没有走索引，可以检查统计信息是否过期，执行 DBMS_STATS 收集统计信息

第三步 索引相关常用管理语句

清单

1 查看表所有索引

SELECT INDEX_NAME,TABLE_NAME,STATUS FROM USER_INDEXES WHERE TABLE_NAME='EMP';

2 索引失效之后重建索引

ALTER INDEX idx_emp_empno REBUILD;

3 重建的时候在线重建

ALTER INDEX idx_emp_empno REBUILD ONLINE;

4 删除索引

DROP INDEX idx_emp_empno;

第四步 添加索引注意事项

操作清单

1 组合索引遵守最左前缀，索引是 (deptno,sal)，查询只写 sal 条件不会命中这个索引

2 低区分度字段不建议建索引，比如状态标记，索引收益很低

3 频繁更新的表，索引数量要控制，写操作维护成本会上升

4 大表创建索引会消耗临时表空间，提前确认临时表空间余量

5 分区表可以创建本地索引 LOCAL，或者全局索引 GLOBAL，根据业务选择

整套流程就是先确认查询瓶颈，确认字段，编写 CREATE INDEX 语句创建，再通过执行计划验证索引是否生效，oracle 添加索引是优化 SQL 最常用手段，但是一定要权衡查询提速和写操作开销，不要无限制添加索引。

【常见问题】

问题 1 oracle 添加索引之后为什么 SQL 还是全表扫描

回答 1 oracle 添加索引之后依旧全表扫描，常见原因是字段区分度太低，统计信息过期，查询条件不符合组合索引最左前缀，或者查询条件使用函数导致普通索引失效，可以收集统计信息，确认查询写法是否匹配索引。

问题 2 oracle 添加索引可以在线创建不锁表吗

回答 1 oracle 添加索引加上 ONLINE 关键字就可以在线创建，创建过程不会长时间锁表，适合生产大表，如果不加 ONLINE，大表 oracle 添加索引会阻塞 DML 业务，生产环境优先使用 ONLINE 方式。

问题 3 oracle 添加索引之后会影响 update 吗

回答 1 会，oracle 添加索引之后，UPDATE 如果修改了索引对应的字段，数据库需要同步更新索引数据，写操作开销会增加，所以高频更新表添加索引需要权衡查询收益和写性能损耗。

【免责申明】

本文 oracle 添加索引为 SQL 优化基础知识，大表创建索引属于生产高风险操作，请提前评估临时表空间和业务影响，建议业务低峰执行，本文不对操作造成数据库性能异常承担责任。