Oracle索引技术：理论与实操全解析

索引是Oracle数据库性能优化的核心组件，如同书籍的目录，能快速定位数据位置，减少磁盘I/O开销。合理设计和维护索引可显著提升查询效率，而误用则可能导致性能下降。
一、核心理论：索引的本质与分类

1. 索引的核心作用

• 加速查询：通过索引结构快速定位数据，避免全表扫描。
  
• 强制执行约束：主键、唯一键约束依赖索引确保数据唯一性。
  
• 优化表连接：外键列上的索引可减少表连接时的锁定问题，提升关联查询性能。
  

2. 主流索引类型及适用场景

Oracle提供多种索引类型，需根据数据特征和业务场景选择：

• B树索引：默认索引类型，适用于高基数（列值唯一性高）列，支持等值查询、范围查询，是OLTP系统的首选。
  
• 位图索引：适用于低基数列（如性别、状态），数据仓库环境中优势明显，可高效处理多列组合查询，但不支持高并发DML操作。
  
• 索引组织表（IOT）：数据存储与索引结构融合，适用于主键查询频繁的场景，节省存储空间，但插入性能略低。
  
• 专门索引：包括不可见索引（用于测试验证）、基于函数的索引（适配WHERE子句中的函数操作）、反向键索引（解决RAC环境索引块争用）等。
  
• 分区索引：分为本地分区索引（与表分区一一对应，维护成本低）和全局分区索引（跨分区全局生效，适用于特定查询场景）。
  

3. 索引与性能的平衡

索引并非越多越好：

• 索引会占用磁盘空间，增加DML操作（INSERT/UPDATE/DELETE）的开销（需同步更新索引）。
  
• 低基数列创建B树索引、高并发更新列创建位图索引，都会导致性能反噬。
  
• 优化器会自动判断是否使用索引，当查询需扫描表中大部分数据时，可能选择全表扫描而非索引。
  

二、实操指南：索引的创建、维护与调优

1. 索引创建：精准落地最佳实践

（1）基础B树索引创建

适用于主键、唯一键、外键及高基数查询列：

1. -- 普通B树索引（单列）
2. CREATE INDEX cust_idx_lastname ON cust(last_name) TABLESPACE reporting_index;
4. -- 复合B树索引（多列，前导列需高频查询）
5. CREATE INDEX cust_idx_name_sal ON cust(last_name, salary) COMPRESS 2;
7. -- 唯一索引（强制执行唯一性）
8. CREATE UNIQUE INDEX cust_idx_ssn ON cust(ssn) NOLOGGING;

复制代码

关键参数说明：

• TABLESPACE：指定索引表空间，建议与表数据分离，便于维护。
  
• COMPRESS N：压缩前导列重复值，节省存储空间（复合索引优先使用）。
  
• NOLOGGING：创建时不生成重做日志，加速大索引创建（数据仓库场景适用）。
  

（2）位图索引创建

仅适用于数据仓库、低基数列、少DML操作场景：

1. -- 单位列位图索引
2. CREATE BITMAP INDEX f_sales_date_idx ON f_sales(d_date_id) LOCAL NOLOGGING;
4. -- 位图连接索引（星型模式事实表与维度表关联）
5. CREATE BITMAP INDEX f_sales_cust_bmj ON f_sales(d_customers.cust_name)
6. FROM f_sales, d_customers
7. WHERE f_sales.d_customer_id = d_customers.d_customer_id
8. LOCAL;

复制代码

（3）索引组织表创建

适用于主键查询频繁、列数较少的表：

1. CREATE TABLE prod_sku (
2.   prod_sku_id NUMBER,
3.   sku VARCHAR2(256),
4.   CONSTRAINT prod_sku_pk PRIMARY KEY(prod_sku_id, sku)
5. ) ORGANIZATION INDEX
6. OVERFLOW TABLESPACE overflow_s; -- 非键列存储到溢出段，优化性能

复制代码

2. 索引维护：保障长期高效运行

（1）统计信息收集

优化器依赖统计信息判断索引使用效率，需定期更新：

1. -- 收集表及索引统计信息
2. EXEC DBMS_STATS.GATHER_TABLE_STATS(
3.   ownname => 'USER',
4.   tabname => 'CUST',
5.   cascade => TRUE, -- 级联收集索引统计
6.   method_opt => 'FOR ALL COLUMNS SIZE AUTO'
7. );
9. -- 单独收集索引统计信息
10. EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname => 'USER', indexname => 'CUST_IDX_LASTNAME');

复制代码

（2）索引碎片处理

索引经频繁删除/更新后会产生碎片，需通过重建、合并优化：

1. -- 重建索引（碎片严重、索引不可用时）
2. ALTER INDEX cust_idx_lastname REBUILD ONLINE; -- ONLINE允许重建期间查询
3. ALTER INDEX cust_idx_lastname REBUILD PARTITION p2023 TABLESPACE new_index_ts; -- 分区索引重建
5. -- 合并索引（碎片较轻，无需额外空间）
6. ALTER INDEX cust_idx_lastname COALESCE;
8. -- 收缩索引（释放未使用空间）
9. ALTER INDEX cust_idx_lastname SHRINK SPACE COMPACT;

复制代码

注意：重建索引需占用与原索引相当的临时空间，建议在维护窗口执行；分区索引优先重建受影响分区，而非全索引。
（3）索引状态管理

1. -- 标记索引不可用（批量加载数据前，避免维护开销）
2. ALTER INDEX cust_idx_lastname UNUSABLE;
4. -- 恢复不可用索引
5. ALTER INDEX cust_idx_lastname REBUILD;
7. -- 隐藏索引（测试索引有效性，不影响业务）
8. ALTER INDEX cust_idx_lastname INVISIBLE;
9. -- 会话级启用隐藏索引
10. ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

复制代码

3. 索引调优：解决性能瓶颈

（1）优化器索引使用控制

1. -- 强制使用索引（优化器误判时）
2. SELECT /*+ INDEX(cust cust_idx_lastname) */ * FROM cust WHERE last_name = 'STARK';
4. -- 避免使用索引（全表扫描更高效时）
5. SELECT /*+ FULL(cust) */ * FROM cust WHERE salary > 5000;
7. -- 调整索引成本系数（倾向使用索引）
8. ALTER SESSION SET optimizer_index_cost_adj = 50; -- 默认100，值越小越倾向索引

复制代码

（2）SQL调优顾问：自动获取索引建议

Oracle内置工具，可分析高负载SQL并生成索引建议：

1. -- 查看自动SQL调优作业建议（默认每日运行）
2. SET LONG 1000000;
3. SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;
5. -- 手动运行SQL调优顾问（针对特定SQL）
6. DECLARE
7.   task_id VARCHAR2(100);
8. BEGIN
9.   task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
10.     sql_text => 'SELECT * FROM cust WHERE last_name = ''STARK'' AND salary > 5000',
11.     user_name => 'USER',
12.     task_name => 'cust_query_tune'
13.   );
14.   DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_id => task_id);
15.   DBMS_OUTPUT.PUT_LINE(DBMS_SQLTUNE.REPORT_TUNING_TASK(task_id => task_id));
16. END;
17. /

复制代码

（3）常见索引失效场景及解决

• 函数操作导致失效：WHERE子句中列使用函数，需创建基于函数的索引：
  1. -- 原查询（索引失效）
  2. SELECT * FROM cust WHERE UPPER(last_name) = 'STARK';
  3. -- 创建基于函数的索引
  4. CREATE INDEX cust_idx_upper_lastname ON cust(UPPER(last_name));

  复制代码
• 复合索引前导列未使用：复合索引(a,b)，查询仅用b列，需调整索引顺序或创建单独索引。
  
• 统计信息陈旧：执行DBMS_STATS.GATHER_TABLE_STATS更新统计信息。
  

4. 索引监控与清理

1. -- 监控索引是否被使用
2. ALTER INDEX cust_idx_lastname MONITORING USAGE;
4. -- 查看监控结果（V$OBJECT_USAGE）
5. SELECT index_name, used FROM v$object_usage WHERE index_name = 'CUST_IDX_LASTNAME';
7. -- 删除无用索引（确认无依赖后）
8. DROP INDEX cust_idx_lastname;

复制代码

三、Oracle 索引创建与维护常用 SQL 脚本集

索引创建脚本

1. B树索引（默认类型）

1. -- 1.1 单列B树索引（高基数列首选）
2. CREATE INDEX idx_cust_lastname ON cust(last_name)
3. TABLESPACE idx_ts
4. NOLOGGING; -- 数据仓库场景加速创建，不生成重做日志
6. -- 1.2 复合B树索引（前导列需高频查询）
7. CREATE INDEX idx_cust_name_sal ON cust(last_name, salary)
8. COMPRESS 2; -- 压缩前导列重复值，节省空间
10. -- 1.3 唯一B树索引（强制执行唯一性）
11. CREATE UNIQUE INDEX idx_cust_ssn ON cust(ssn)
12. TABLESPACE idx_ts;

复制代码

2. 位图索引（低基数列、数据仓库）

1. -- 2.1 单列位图索引
2. CREATE BITMAP INDEX idx_f_sales_date ON f_sales(d_date_id)
3. LOCAL NOLOGGING; -- 分区表需指定LOCAL
5. -- 2.2 位图连接索引（星型模式事实表与维度表关联）
6. CREATE BITMAP INDEX idx_f_sales_cust_bmj ON f_sales(d_customers.cust_name)
7. FROM f_sales, d_customers
8. WHERE f_sales.d_customer_id = d_customers.d_customer_id
9. LOCAL;

复制代码

3. 特殊功能索引

1. -- 3.1 基于函数的索引（适配WHERE子句函数操作）
2. CREATE INDEX idx_cust_upper_lastname ON cust(UPPER(last_name));
4. -- 3.2 不可见索引（测试验证，不影响业务）
5. CREATE INDEX idx_cust_invisible ON cust(email) INVISIBLE;
7. -- 3.3 反向键索引（RAC环境避免索引块争用）
8. CREATE INDEX idx_cust_id_reverse ON cust(cust_id) REVERSE;
10. -- 3.4 分区索引（本地分区，与表分区一一对应）
11. CREATE INDEX idx_emp_dept_part ON emp(department_id)
12. LOCAL (
13.   PARTITION p2023_q1 TABLESPACE idx_ts_q1,
14.   PARTITION p2023_q2 TABLESPACE idx_ts_q2
15. );

复制代码

统计信息收集脚本

优化器依赖统计信息判断索引有效性，需定期更新：

1. -- 1. 收集表及关联索引统计信息（推荐）
2. EXEC DBMS_STATS.GATHER_TABLE_STATS(
3.   ownname => 'USER',
4.   tabname => 'CUST',
5.   cascade => TRUE, -- 级联收集索引统计
6.   method_opt => 'FOR ALL COLUMNS SIZE AUTO'
7. );
9. -- 2. 单独收集索引统计信息
10. EXEC DBMS_STATS.GATHER_INDEX_STATS(
11.   ownname => 'USER',
12.   indexname => 'IDX_CUST_LASTNAME'
13. );
15. -- 3. 收集基于函数的索引统计（含隐藏虚拟列）
16. EXEC DBMS_STATS.GATHER_TABLE_STATS(
17.   ownname => 'USER',
18.   tabname => 'CUST',
19.   method_opt => 'FOR ALL HIDDEN COLUMNS SIZE AUTO'
20. );

复制代码

索引碎片处理脚本

1. -- 1. 重建索引（碎片严重、索引不可用时）
2. -- 1.1 普通索引在线重建（不影响查询）
3. ALTER INDEX idx_cust_lastname REBUILD ONLINE;
5. -- 1.2 分区索引重建（仅重建受影响分区）
6. ALTER INDEX idx_emp_dept_part REBUILD PARTITION p2023_q1
7. TABLESPACE new_idx_ts;
9. -- 2. 合并索引（碎片较轻，无需额外空间）
10. ALTER INDEX idx_cust_name_sal COALESCE;
12. -- 3. 收缩索引（释放未使用空间）
13. ALTER INDEX idx_cust_lastname SHRINK SPACE COMPACT;

复制代码

索引状态管理脚本

1. -- 1. 标记索引不可用（批量加载数据前，避免维护开销）
2. ALTER INDEX idx_cust_lastname UNUSABLE;
4. -- 2. 恢复不可用索引
5. ALTER INDEX idx_cust_lastname REBUILD;
7. -- 3. 隐藏/显示索引（测试索引有效性）
8. ALTER INDEX idx_cust_invisible VISIBLE; -- 显示索引
9. ALTER INDEX idx_cust_invisible INVISIBLE; -- 隐藏索引
11. -- 4. 会话级启用隐藏索引
12. ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

复制代码

索引监控与查询脚本

1. -- 1. 监控索引是否被使用
2. ALTER INDEX idx_cust_lastname MONITORING USAGE;
4. -- 2. 查看索引监控结果（V$OBJECT_USAGE）
5. SELECT index_name, used, monitoring
6. FROM v$object_usage
7. WHERE index_name = 'IDX_CUST_LASTNAME';
9. -- 3. 查询索引基本信息（类型、表空间、状态）
10. SELECT index_name, index_type, tablespace_name, status
11. FROM user_indexes
12. WHERE table_name = 'CUST';
14. -- 4. 查询分区索引状态
15. SELECT index_name, partition_name, status
16. FROM user_ind_partitions
17. WHERE index_name = 'IDX_EMP_DEPT_PART';
19. -- 5. 查询索引碎片情况（通过INDEX_STATS视图）
20. ANALYZE INDEX idx_cust_lastname VALIDATE STRUCTURE;
21. SELECT lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 del_pct
22. FROM index_stats;

复制代码

索引删除与清理脚本

1. -- 1. 删除无用索引（确认无依赖后）
2. DROP INDEX idx_cust_invisible;
4. -- 2. 删除约束关联的索引（需先删除约束）
5. ALTER TABLE cust DROP CONSTRAINT cust_pk KEEP INDEX; -- 保留索引
6. ALTER TABLE cust DROP CONSTRAINT cust_pk; -- 自动删除关联索引
8. -- 3. 释放索引未使用空间
9. ALTER INDEX idx_cust_lastname DEALLOCATE UNUSED;

复制代码

核心使用原则

• 高基数列（如主键、唯一键）用B树索引，低基数列（如性别、状态）用位图索引；
  
• 索引创建时指定独立表空间，便于维护和备份；
  
• 定期监控索引使用情况，删除无用索引减少DML开销；
  
• 数据批量加载前禁用索引，加载后重建，提升效率。
  

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！