Oracle 索引类型

索引是 Oracle 数据库中提高查询性能的重要手段。不同类型的索引适用于不同的业务场景，理解各种索引类型的特点和适用场景对于 DBA 进行性能调优至关重要。

索引基础概念

索引的作用

提高查询速度：通过索引可以快速定位数据，减少磁盘 I/O
加速排序和分组操作：索引本身是有序的，可以避免额外的排序操作
强制数据唯一性：唯一索引可以确保数据的唯一性
支持外键约束：外键通常需要索引来提高关联查询性能

索引的结构

B树索引：最常用的索引类型，采用平衡树结构
位图索引：适合低基数列，采用位图结构
函数索引：基于函数或表达式创建的索引
分区索引：与分区表对应的索引

索引的存储

索引存储在索引段中，位于指定的表空间
索引由索引条目组成，每个条目包含索引键值和指向数据行的指针
索引的高度决定了访问数据所需的 I/O 次数

主要索引类型

B树索引（B-Tree Index）

特点

最常用的索引类型，适合高基数列
采用平衡树结构，高度通常为 2-4 层
支持等值查询、范围查询、排序和分组
维护成本相对较低

适用场景

主键和唯一约束
频繁用于查询条件的列
高基数列（不同值的数量较多）
需要进行范围查询的列

创建方法

sql

-- 创建普通 B 树索引
CREATE INDEX emp_last_name_idx ON employees(last_name);

-- 创建唯一索引
CREATE UNIQUE INDEX emp_email_idx ON employees(email);

-- 创建复合索引
CREATE INDEX emp_dept_job_idx ON employees(department_id, job_id);

-- 指定表空间
CREATE INDEX emp_hire_date_idx ON employees(hire_date) TABLESPACE indexes;

版本差异

Oracle 11g：增强了 B 树索引的压缩功能
Oracle 12c：支持在线索引重建
Oracle 19c：增强了索引维护的并行度

位图索引（Bitmap Index）

特点

适合低基数列（不同值的数量较少，如性别、状态等）
采用位图结构，每个键值对应一个位图
占用存储空间小
查询性能优异，尤其是在多列组合查询时
维护成本高，不适合频繁更新的列

适用场景

数据仓库环境
低基数列（不同值的数量 < 1% 的表行数）
只读或很少更新的表
复杂的多列查询

创建方法

sql

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

-- 创建复合位图索引
CREATE BITMAP INDEX emp_dept_status_idx ON employees(department_id, status);

-- 指定表空间
CREATE BITMAP INDEX emp_region_idx ON employees(region) TABLESPACE indexes;

注意事项

避免在频繁更新的列上创建位图索引
位图索引不适合 OLTP 环境，适合数据仓库环境
多个位图索引组合查询时性能优异

函数索引（Function-Based Index）

特点

基于函数或表达式创建的索引
可以加速包含函数或表达式的查询
支持 SQL 函数、用户自定义函数和表达式
维护成本较高

适用场景

查询中频繁使用函数或表达式
需要对列进行转换后查询（如 UPPER(column)）
需要基于计算列查询

创建方法

sql

-- 创建基于 UPPER 函数的索引
CREATE INDEX emp_upper_last_name_idx ON employees(UPPER(last_name));

-- 创建基于表达式的索引
CREATE INDEX emp_salary_bonus_idx ON employees(salary * 1.1);

-- 创建基于用户自定义函数的索引
CREATE INDEX emp_calculated_idx ON employees(calculate_value(salary, commission_pct));

-- 创建唯一函数索引
CREATE UNIQUE INDEX emp_unique_email_idx ON employees(UPPER(email));

版本差异

Oracle 11g：支持基于正则表达式的函数索引
Oracle 12c：支持在线创建函数索引
Oracle 19c：增强了对 JSON 函数的索引支持

分区索引（Partitioned Index）

特点

与分区表对应的索引
可以提高查询性能和管理效率
支持局部分区索引和全局分区索引
可以独立维护和管理

类型

局部分区索引（Local Partitioned Index）
- 索引分区与表分区一一对应
- 每个索引分区只包含对应表分区的数据
- 维护简单，自动随表分区变化
- 适合与表分区相同的查询模式
全局分区索引（Global Partitioned Index）
- 索引分区与表分区不一一对应
- 可以使用与表不同的分区键
- 维护复杂，表分区变化可能导致索引失效
- 适合全局查询
全局非分区索引（Global Nonpartitioned Index）
- 整个索引作为一个整体，不分区
- 适合需要全局访问的场景

创建方法

sql

-- 创建局部分区索引
CREATE INDEX sales_product_id_idx ON sales(product_id) LOCAL;

-- 创建全局分区索引
CREATE INDEX sales_date_idx ON sales(sale_date)
GLOBAL PARTITION BY RANGE (sale_date) (
  PARTITION sales_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
  PARTITION sales_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
  PARTITION sales_q3 VALUES LESS THAN (TO_DATE('2024-10-01', 'YYYY-MM-DD')),
  PARTITION sales_q4 VALUES LESS THAN (MAXVALUE)
);

-- 创建全局非分区索引
CREATE INDEX sales_customer_id_idx ON sales(customer_id) GLOBAL;

版本差异

Oracle 11g：增强了分区索引的并行创建和维护
Oracle 12c：支持在线分区索引重建
Oracle 19c：增强了分区索引的压缩功能

反向键索引（Reverse Key Index）

特点

将索引键值反转后存储
可以减少索引热点，提高并发插入性能
适合序列生成的主键列
不支持范围查询

适用场景

高并发插入场景
序列生成的主键列
容易产生索引热点的列

创建方法

sql

-- 创建反向键索引
CREATE INDEX emp_emp_id_rev_idx ON employees(employee_id) REVERSE;

-- 创建唯一反向键索引
CREATE UNIQUE INDEX orders_order_id_rev_idx ON orders(order_id) REVERSE;

注意事项

反向键索引不支持范围查询
适合等值查询，不适合 ORDER BY 操作
维护成本与普通 B 树索引相当

索引组织表（Index-Organized Table）

特点

数据直接存储在索引中，没有单独的数据段
适合频繁通过主键访问的数据
减少了表和索引的存储空间
查询性能优异，尤其是主键查询

适用场景

频繁通过主键访问的数据
小表或中等大小的表
数据需要按照主键顺序存储

创建方法

sql

-- 创建索引组织表
CREATE TABLE employees_iot (
  employee_id   NUMBER(6) PRIMARY KEY,
  first_name    VARCHAR2(20),
  last_name     VARCHAR2(25) NOT NULL,
  email         VARCHAR2(100) NOT NULL UNIQUE,
  hire_date     DATE NOT NULL
) ORGANIZATION INDEX;

-- 创建带有溢出段的索引组织表
CREATE TABLE employees_iot_overflow (
  employee_id   NUMBER(6) PRIMARY KEY,
  first_name    VARCHAR2(20),
  last_name     VARCHAR2(25) NOT NULL,
  email         VARCHAR2(100) NOT NULL UNIQUE,
  hire_date     DATE NOT NULL,
  long_description CLOB
) ORGANIZATION INDEX
OVERFLOW TABLESPACE users;

版本差异

Oracle 11g：增强了索引组织表的溢出段管理
Oracle 12c：支持索引组织表的分区
Oracle 19c：增强了索引组织表的压缩功能

其他索引类型

空间索引（Spatial Index）

用于地理空间数据类型（SDO_GEOMETRY）
支持空间查询，如距离查询、包含查询等

创建方法：

sql

CREATE INDEX locations_spatial_idx ON locations(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

文本索引（Text Index）

用于全文搜索
支持文本的索引和查询

创建方法：

sql

CREATE INDEX documents_text_idx ON documents(content)
INDEXTYPE IS CTXSYS.CONTEXT;

JSON 索引（JSON Index）

Oracle 19c 引入，用于 JSON 数据类型
支持 JSON 数据的高效查询

创建方法：

sql

CREATE INDEX employees_json_idx ON employees(json_data)
INDEXTYPE IS JSON_search_index;

索引管理

索引创建与重建

sql

-- 创建索引
CREATE INDEX emp_last_name_idx ON employees(last_name) TABLESPACE indexes;

-- 在线重建索引
ALTER INDEX emp_last_name_idx REBUILD ONLINE TABLESPACE new_indexes;

-- 并行重建索引
ALTER INDEX emp_last_name_idx REBUILD PARALLEL 4;

-- 压缩重建索引
ALTER INDEX emp_last_name_idx REBUILD COMPRESS 2;

索引监控与维护

sql

-- 监控索引使用情况
ALTER INDEX emp_last_name_idx MONITORING USAGE;

-- 查看索引使用情况
SELECT index_name, table_name, monitoring, used FROM v$object_usage;

-- 停止监控索引
ALTER INDEX emp_last_name_idx NOMONITORING USAGE;

-- 收集索引统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => 'HR', INDNAME => 'EMP_LAST_NAME_IDX');

-- 查看索引统计信息
SELECT index_name, leaf_blocks, distinct_keys, num_rows FROM dba_ind_statistics WHERE index_name = 'EMP_LAST_NAME_IDX';

-- 查看索引碎片情况
SELECT index_name, blevel, leaf_blocks, distinct_keys, num_rows, clustering_factor
FROM dba_indexes WHERE index_name = 'EMP_LAST_NAME_IDX';

索引失效与修复

sql

-- 查看失效的索引
SELECT index_name, status FROM dba_indexes WHERE status = 'UNUSABLE';

-- 修复失效的索引
ALTER INDEX emp_invalid_idx REBUILD;

-- 修复分区索引
ALTER INDEX sales_part_idx REBUILD PARTITION sales_q1;

索引删除

sql

-- 删除索引
DROP INDEX emp_unused_idx;

-- 删除分区索引的指定分区
ALTER INDEX sales_part_idx DROP PARTITION sales_old;

索引设计最佳实践

索引创建原则

根据查询需求创建索引：分析 SQL 语句，为频繁使用的 WHERE 条件、JOIN 条件和 ORDER BY 列创建索引
优先创建复合索引：对于多列查询，复合索引的性能优于多个单列索引
选择高基数列作为索引前缀：复合索引的前导列应该是选择性高的列
避免过度索引：每个索引都会增加 DML 操作的开销
为外键创建索引：提高关联查询性能，避免锁表
考虑索引的存储位置：将索引存储在与表不同的磁盘上，减少 I/O 竞争

索引使用原则

避免在索引列上使用函数：会导致索引失效，应该使用函数索引
**避免使用 SELECT ***：只查询需要的列，减少 I/O
合理使用索引提示：在必要时使用 /*+ INDEX(table index) */ 提示
定期分析索引使用情况：删除不使用的索引
监控索引碎片：定期重建或重组碎片化严重的索引

不同环境的索引策略

OLTP 环境

适合 B 树索引
避免位图索引
合理控制索引数量
优先考虑主键和外键索引
适合使用反向键索引（对于序列生成的主键）

数据仓库环境

适合位图索引和分区索引
可以使用函数索引
适合创建复合索引
支持星型模型和雪花模型的索引设计
可以使用位图连接索引

版本差异

版本	索引特性
Oracle 11g	增强了索引压缩功能，支持基于正则表达式的函数索引
Oracle 12c	支持在线索引重建，支持索引组织表的分区，增强了分区索引功能
Oracle 19c	引入 JSON 索引，增强了文本索引功能，优化了索引维护性能
Oracle 23c	增强了向量索引支持，优化了高并发环境下的索引性能

常见问题（FAQ）

Q1: 如何判断一个索引是否被使用？

A1: 可以使用索引监控功能：

sql

-- 开始监控
ALTER INDEX emp_last_name_idx MONITORING USAGE;

-- 运行一段时间后查看
SELECT index_name, table_name, used FROM v$object_usage;

-- 停止监控
ALTER INDEX emp_last_name_idx NOMONITORING USAGE;

Q2: 如何选择合适的索引类型？

A2: 根据业务场景选择：

高基数列：B 树索引
低基数列：位图索引（数据仓库环境）
频繁使用函数：函数索引
分区表：分区索引
序列生成的主键：反向键索引
频繁通过主键访问：索引组织表

Q3: 如何处理碎片化的索引？

A3: 可以通过重建索引来解决：

sql

-- 在线重建索引
ALTER INDEX emp_fragmented_idx REBUILD ONLINE;

-- 并行重建索引
ALTER INDEX emp_fragmented_idx REBUILD PARALLEL 4;

-- 压缩重建索引
ALTER INDEX emp_fragmented_idx REBUILD COMPRESS;

Q4: 为什么有些查询没有使用索引？

A4: 可能的原因：

索引列上使用了函数或表达式
统计信息过时
优化器认为全表扫描更高效
索引选择性差
使用了不等于（<>）或 NOT IN 操作符

Q5: 如何查看索引的碎片情况？

A5: 使用以下查询：

sql

SELECT index_name, blevel, leaf_blocks, distinct_keys, num_rows, clustering_factor
FROM dba_indexes WHERE index_name = 'EMP_LAST_NAME_IDX';

-- 计算索引碎片率
SELECT index_name, 
       (1 - (distinct_keys / num_rows)) * 100 AS fragmentation_pct
FROM dba_ind_statistics WHERE index_name = 'EMP_LAST_NAME_IDX';

Q6: 如何批量删除不使用的索引？

A6: 可以使用以下步骤：

监控索引使用情况
分析哪些索引没有被使用

编写脚本批量删除不使用的索引：

sql

DECLARE
  CURSOR c_unused_indexes IS
    SELECT index_name, owner FROM v$object_usage WHERE used = 'NO';
BEGIN
  FOR rec IN c_unused_indexes LOOP
    EXECUTE IMMEDIATE 'DROP INDEX ' || rec.owner || '.' || rec.index_name;
  END LOOP;
END;
/

命令	用途
`CREATE INDEX`	创建索引
`ALTER INDEX`	修改索引
`DROP INDEX`	删除索引
`ALTER INDEX MONITORING USAGE`	监控索引使用
`ALTER INDEX NOMONITORING USAGE`	停止监控索引
`DBMS_STATS.GATHER_INDEX_STATS`	收集索引统计信息
`SELECT * FROM v$object_usage`	查看索引使用情况
`SELECT * FROM dba_indexes`	查看索引信息
`SELECT * FROM dba_ind_statistics`	查看索引统计信息

总结

Oracle 提供了多种索引类型，每种类型都有其特点和适用场景。DBA 应该根据实际业务需求和查询模式，选择合适的索引类型，并定期进行监控和维护。合理的索引设计可以显著提高查询性能，而过度索引则会影响 DML 操作的性能。DBA 应该定期分析索引使用情况，删除不使用的索引，重建碎片化的索引，并收集准确的统计信息，以确保索引的高效使用。

Oracle 索引类型 ​

索引基础概念 ​

索引的作用 ​

索引的结构 ​

索引的存储 ​

主要索引类型 ​

B树索引（B-Tree Index） ​

特点 ​

适用场景 ​

创建方法 ​

版本差异 ​

位图索引（Bitmap Index） ​

特点 ​

适用场景 ​

创建方法 ​

注意事项 ​

函数索引（Function-Based Index） ​

特点 ​

适用场景 ​

创建方法 ​

版本差异 ​

分区索引（Partitioned Index） ​

特点 ​

类型 ​

创建方法 ​

版本差异 ​

反向键索引（Reverse Key Index） ​

特点 ​

适用场景 ​

创建方法 ​

注意事项 ​

索引组织表（Index-Organized Table） ​

特点 ​

适用场景 ​

创建方法 ​

版本差异 ​

其他索引类型 ​

空间索引（Spatial Index） ​

文本索引（Text Index） ​

JSON 索引（JSON Index） ​

索引管理 ​

索引创建与重建 ​

索引监控与维护 ​

索引失效与修复 ​

索引删除 ​

索引设计最佳实践 ​

索引创建原则 ​

索引使用原则 ​

不同环境的索引策略 ​

OLTP 环境 ​

数据仓库环境 ​

版本差异 ​

常见问题（FAQ） ​

Q1: 如何判断一个索引是否被使用？ ​

Q2: 如何选择合适的索引类型？ ​

Q3: 如何处理碎片化的索引？ ​

Q4: 为什么有些查询没有使用索引？ ​

Q5: 如何查看索引的碎片情况？ ​

Q6: 如何批量删除不使用的索引？ ​

相关命令速查 ​

总结 ​

Oracle 索引类型

索引基础概念

索引的作用

索引的结构

索引的存储

主要索引类型

B树索引（B-Tree Index）

特点

适用场景

创建方法

版本差异

位图索引（Bitmap Index）

特点

适用场景

创建方法

注意事项

函数索引（Function-Based Index）

特点

适用场景

创建方法

版本差异

分区索引（Partitioned Index）

特点

类型

创建方法

版本差异

反向键索引（Reverse Key Index）

特点

适用场景

创建方法

注意事项

索引组织表（Index-Organized Table）

特点

适用场景

创建方法

版本差异

其他索引类型

空间索引（Spatial Index）

文本索引（Text Index）

JSON 索引（JSON Index）

索引管理

索引创建与重建

索引监控与维护

索引失效与修复

索引删除

索引设计最佳实践

索引创建原则

索引使用原则

不同环境的索引策略

OLTP 环境

数据仓库环境

版本差异

常见问题（FAQ）

Q1: 如何判断一个索引是否被使用？

Q2: 如何选择合适的索引类型？

Q3: 如何处理碎片化的索引？

Q4: 为什么有些查询没有使用索引？

Q5: 如何查看索引的碎片情况？

Q6: 如何批量删除不使用的索引？

相关命令速查

总结