在 Oracle 中,索引是一种数据库对象,用于提高查询性能。通过索引,Oracle 可以更快地找到数据,尤其是在处理大量数据时。常见的索引类型包括 B-Tree 索引、位图索引、唯一索引等。索引可以加速查询,但过多的索引可能会影响数据修改的性能,因此需要合理使用。
1. 索引的基本操作
- 创建索引 (
CREATE INDEX
) - 查看索引 (
USER_INDEXES
、USER_IND_COLUMNS
) - 删除索引 (
DROP INDEX
) - 重建索引 (
ALTER INDEX REBUILD
) - 禁用/启用索引 (
ALTER INDEX UNUSABLE
)
2. 创建索引
语法
CREATE [UNIQUE] INDEX 索引名
ON 表名 (列名1, 列名2, ...);
UNIQUE
:指定创建唯一索引,确保索引列的值是唯一的。列名
:可以为一个或多个列创建索引(单列索引或组合索引)。
实例
-
创建单列索引
对employees
表的emp_name
列创建索引:CREATE INDEX idx_emp_name ON employees(emp_name);
-
创建组合索引
对employees
表的dept_id
和hire_date
列创建组合索引:CREATE INDEX idx_dept_hire ON employees(dept_id, hire_date);
-
创建唯一索引
在employees
表的email
列上创建唯一索引:CREATE UNIQUE INDEX idx_email ON employees(email);
-
位图索引
位图索引适用于低基数的列,例如性别、状态等:CREATE BITMAP INDEX idx_gender ON employees(gender);
3. 查看索引
可以通过查询数据字典表来查看表上的索引信息:
-
查看索引信息:
SELECT index_name, table_name, uniqueness FROM user_indexes WHERE table_name = 'EMPLOYEES';
-
查看索引列信息:
SELECT index_name, column_name FROM user_ind_columns WHERE table_name = 'EMPLOYEES';
4. 删除索引
删除索引是比较常见的操作,当某个索引不再需要或者对性能产生负面影响时,可以删除它。
语法
DROP INDEX 索引名;
实例
删除索引 idx_emp_name
:
DROP INDEX idx_emp_name;
5. 重建索引
当索引变得碎片化或需要优化时,可以通过重建索引来恢复其效率。重建索引不会影响数据访问,但可能会消耗大量的系统资源。
语法
ALTER INDEX 索引名 REBUILD;
实例
重建索引 idx_emp_name
:
ALTER INDEX idx_emp_name REBUILD;
还可以指定表空间、并行度等选项:
ALTER INDEX idx_emp_name REBUILD TABLESPACE users PARALLEL 4;
6. 禁用和启用索引
有时为了避免索引影响数据加载性能,可以暂时禁用索引。
禁用索引
ALTER INDEX 索引名 UNUSABLE;
启用索引
索引不可用状态下,可以通过重建索引的方式重新启用:
ALTER INDEX 索引名 REBUILD;
7. 常见的索引类型
1. B-Tree 索引
- 默认索引类型。
- 适合高基数(不同值很多)的列,如主键、唯一键等。
- 适用于大多数场景。
2. 位图索引(BITMAP
索引)
- 适合低基数(不同值较少)的列,如性别、婚姻状况等。
- 适合多维数据仓库应用,不适合频繁更新的数据表。
- 创建示例:
CREATE BITMAP INDEX idx_gender ON employees(gender);
3. 唯一索引(UNIQUE
索引)
- 强制唯一性约束,确保索引列的值唯一。
- 创建示例:
CREATE UNIQUE INDEX idx_email ON employees(email);
4. 函数索引(FUNCTION-BASED INDEX
)
- 可以在表达式或函数上创建索引,适用于列经常在查询中经过某些操作(如
UPPER()
、LOWER()
)时。 - 创建示例:
CREATE INDEX idx_upper_name ON employees(UPPER(emp_name));
5. 逆序索引(REVERSE KEY INDEX
)
- 反转索引列的值,适合用于解决数据集中插入时的瓶颈问题(如序列列)。
- 创建示例:
CREATE INDEX idx_reverse_emp_id ON employees(emp_id) REVERSE;
8. 索引的优化建议
-
选择性
- 索引的选择性指的是列中不同值的数量与总记录数的比例。选择性越高,索引的效果越好。一般来说,选择性低的列(如
gender
)不适合创建 B-Tree 索引,可能更适合位图索引。
- 索引的选择性指的是列中不同值的数量与总记录数的比例。选择性越高,索引的效果越好。一般来说,选择性低的列(如
-
组合索引
- 对频繁一起使用的列创建组合索引,而不是为每个列分别创建单列索引。例如
dept_id
和hire_date
常一起在查询条件中使用时,可以创建组合索引。
- 对频繁一起使用的列创建组合索引,而不是为每个列分别创建单列索引。例如
-
避免过多的索引
- 每个索引都增加了插入、更新和删除操作的开销,因为这些操作需要维护索引。因此,避免在频繁更新的表上创建过多的索引。
-
适用的场景
- 索引适用于查询较多、更新较少的场景,如 OLAP 系统。如果是 OLTP 系统,可能需要平衡查询和写入的性能,避免过多索引。
总结
在 Oracle 中,索引是一种提升查询性能的强大工具,但使用不当可能会对写操作带来负面影响。因此,了解如何创建、维护和删除索引,以及适当选择索引类型(如 B-Tree、位图、唯一索引等)至关重要。通过查看索引的使用情况,及时重建索引或删除不必要的索引,能更好地管理数据库的性能。