Oracle索引常用操作详解

在 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：指定创建唯一索引，确保索引列的值是唯一的。
• 列名：可以为一个或多个列创建索引（单列索引或组合索引）。

实例

1. 创建单列索引
   对 employees 表的 emp_name 列创建索引：

   CREATE INDEX idx_emp_name ON employees(emp_name);
   

2. 创建组合索引
   对 employees 表的 dept_id 和 hire_date 列创建组合索引：

   CREATE INDEX idx_dept_hire ON employees(dept_id, hire_date);
   

3. 创建唯一索引
   在 employees 表的 email 列上创建唯一索引：

   CREATE UNIQUE INDEX idx_email ON employees(email);
   

4. 位图索引
   位图索引适用于低基数的列，例如性别、状态等：

   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. 索引的优化建议

1. 选择性

   • 索引的选择性指的是列中不同值的数量与总记录数的比例。选择性越高，索引的效果越好。一般来说，选择性低的列（如 gender）不适合创建 B-Tree 索引，可能更适合位图索引。

2. 组合索引

   • 对频繁一起使用的列创建组合索引，而不是为每个列分别创建单列索引。例如 dept_id 和 hire_date 常一起在查询条件中使用时，可以创建组合索引。

3. 避免过多的索引

   • 每个索引都增加了插入、更新和删除操作的开销，因为这些操作需要维护索引。因此，避免在频繁更新的表上创建过多的索引。

4. 适用的场景

   • 索引适用于查询较多、更新较少的场景，如 OLAP 系统。如果是 OLTP 系统，可能需要平衡查询和写入的性能，避免过多索引。

总结

在 Oracle 中，索引是一种提升查询性能的强大工具，但使用不当可能会对写操作带来负面影响。因此，了解如何创建、维护和删除索引，以及适当选择索引类型（如 B-Tree、位图、唯一索引等）至关重要。通过查看索引的使用情况，及时重建索引或删除不必要的索引，能更好地管理数据库的性能。