印刻数据库

外观

Oracle 分区索引优化 ​

分区索引概述 ​

分区索引是与分区表关联的索引，通过将索引分成多个分区，与表的分区策略相匹配，从而提高查询性能，减少维护成本。分区索引在处理大型表时具有明显优势，能够支持更高的并发访问和更高效的数据管理。

分区索引类型 ​

1. 本地分区索引 (Local Partitioned Index) ​

本地分区索引与表的分区策略完全一致，每个表分区对应一个索引分区。

特点 ​

  • 索引与表的分区策略相同
  • 支持分区独立性，单个分区维护不影响其他分区
  • 分区维护操作高效（如 TRUNCATE、DROP、SPLIT 等）
  • 自动与表分区同步

适用场景 ​

  • 分区表的大多数查询
  • 需要频繁进行分区维护的场景
  • 星型模型中的维度表

示例 ​

sql
-- 创建本地分区索引
CREATE INDEX idx_sales_date_local ON SALES(sale_date) LOCAL;

-- 创建本地分区唯一索引
CREATE UNIQUE INDEX idx_sales_id_local ON SALES(sale_id, sale_date) LOCAL;

2. 全局分区索引 (Global Partitioned Index) ​

全局分区索引的分区策略与表无关，可以自定义分区键和分区范围。

特点 ​

  • 索引分区策略独立于表分区策略
  • 支持跨表分区的查询
  • 维护成本较高，分区维护可能导致全局索引失效
  • 适合范围查询

适用场景 ​

  • 需要跨表分区的范围查询
  • 非分区表或分区策略不适合查询模式的场景
  • 数据仓库中的大型事实表

示例 ​

sql
-- 创建全局分区索引
CREATE INDEX idx_sales_customer_global ON SALES(customer_id)
GLOBAL PARTITION BY RANGE (customer_id) (
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

3. 全局非分区索引 (Global Non-Partitioned Index) ​

全局非分区索引不进行分区，是一个完整的索引结构。

特点 ​

  • 不分区，完整的索引结构
  • 适合全局查询
  • 维护成本较高，表分区维护可能导致索引失效

适用场景 ​

  • 需要全局唯一约束的场景
  • 小表或查询频率较低的表
  • 主键约束

示例 ​

sql
-- 创建全局非分区索引
CREATE UNIQUE INDEX idx_sales_pk ON SALES(sales_id) GLOBAL;

分区索引设计原则 ​

1. 匹配查询模式 ​

  • 对于单分区查询，优先使用本地分区索引
  • 对于跨分区查询，考虑使用全局分区索引
  • 对于全局唯一约束，使用全局索引

2. 考虑分区维护 ​

  • 频繁进行分区维护的表，优先使用本地分区索引
  • 全局分区索引在分区维护时需要重建或标记为 UNUSABLE
  • 考虑使用 UPDATE GLOBAL INDEXES 选项自动维护全局索引

3. 复合索引设计 ​

  • 本地分区索引的前缀列应包含分区键
  • 全局分区索引的分区键应与查询模式匹配
  • 考虑索引压缩减少存储开销

4. 并行度设置 ​

  • 分区索引支持并行查询和并行维护
  • 合理设置并行度，避免资源浪费
  • 维护操作完成后重置并行度

分区索引优化策略 ​

1. 选择合适的索引类型 ​

场景推荐索引类型
单分区查询本地分区索引
跨分区范围查询全局分区索引
全局唯一约束全局非分区索引
频繁分区维护本地分区索引
大型事实表本地分区索引或全局分区索引

2. 优化分区维护操作 ​

本地分区索引维护 ​

sql
-- 截断分区（本地索引自动维护）
ALTER TABLE SALES TRUNCATE PARTITION SALES_Q1_2023 UPDATE INDEXES;

-- 删除分区（本地索引自动维护）
ALTER TABLE SALES DROP PARTITION SALES_Q1_2023 UPDATE INDEXES;

全局分区索引维护 ​

sql
-- 截断分区并维护全局索引
ALTER TABLE SALES TRUNCATE PARTITION SALES_Q1_2023 UPDATE GLOBAL INDEXES;

-- 重建失效的全局索引
ALTER INDEX idx_sales_customer_global REBUILD;

3. 索引压缩 ​

分区索引支持压缩，能够减少存储空间，提高 I/O 性能。

sql
-- 创建压缩本地分区索引
CREATE INDEX idx_sales_date_local ON SALES(sale_date) LOCAL COMPRESS;

-- 创建高级压缩全局分区索引
CREATE INDEX idx_sales_customer_global ON SALES(customer_id)
GLOBAL PARTITION BY RANGE (customer_id) (
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
) COMPRESS ADVANCED;

4. 统计信息收集 ​

定期收集分区索引的统计信息，确保优化器生成正确的执行计划。

sql
-- 收集本地分区索引统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS(
    ownname => 'SCOTT',
    indname => 'IDX_SALES_DATE_LOCAL',
    partname => 'SALES_Q1_2023',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    degree => DBMS_STATS.AUTO_DEGREE
);

-- 收集所有分区索引统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS(
    ownname => 'SCOTT',
    indname => 'IDX_SALES_DATE_LOCAL',
    granularity => 'ALL',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    degree => DBMS_STATS.AUTO_DEGREE
);

5. 分区索引重建 ​

定期重建碎片化的分区索引，提高查询性能。

sql
-- 重建单个分区索引
ALTER INDEX idx_sales_date_local REBUILD PARTITION SALES_Q1_2023 ONLINE;

-- 重建所有分区索引
ALTER INDEX idx_sales_date_local REBUILD ONLINE PARALLEL 4;
ALTER INDEX idx_sales_date_local NOPARALLEL;

19c 和 21c 分区索引新特性 ​

Oracle 19c 新特性 ​

  1. 自动索引增强：19c 自动索引功能支持分区表，能够自动创建和维护分区索引
  2. 分区索引在线重建：支持在线重建分区索引，减少维护窗口
  3. 分区索引统计信息增强：改进了分区索引统计信息的收集算法
  4. 全局索引异步维护：支持异步维护全局索引，提高分区维护性能

Oracle 21c 新特性 ​

  1. 智能分区索引：引入机器学习算法，能够自动选择最优的分区索引类型
  2. 实时分区索引监控：提供实时的分区索引使用监控，能够及时识别需要维护的分区
  3. 分区索引碎片自动修复：能够自动识别和修复碎片化的分区索引
  4. 新视图 V$PARTITION_INDEX：提供分区索引的实时监控信息
  5. 分区索引压缩增强：改进了分区索引压缩算法，提高了压缩率和查询性能

生产环境最佳实践 ​

1. 分区索引设计 ​

  • 优先使用本地分区索引，除非有特殊需求
  • 全局分区索引的分区键应与查询模式匹配
  • 复合索引的前导列应包含分区键
  • 考虑使用索引压缩减少存储开销

2. 分区维护策略 ​

  • 对于本地分区索引，使用 UPDATE INDEXES 选项自动维护
  • 对于全局分区索引，考虑使用 UPDATE GLOBAL INDEXES 选项，或在维护后重建索引
  • 选择业务低峰期进行分区维护
  • 监控分区维护操作的进度和资源使用

3. 监控与告警 ​

  • 监控分区索引的使用情况
  • 监控分区索引的碎片率
  • 监控分区索引的统计信息状态
  • 设置分区索引相关的告警阈值

4. 性能调优 ​

  • 分析查询执行计划，优化索引设计
  • 调整并行度，提高查询和维护性能
  • 考虑使用存储索引（如 Exadata 存储索引）
  • 优化 SQL 语句，充分利用分区索引

常见问题 (FAQ) ​

如何选择本地分区索引还是全局分区索引？ ​

选择本地分区索引还是全局分区索引，主要取决于查询模式和维护需求：

  • 如果大多数查询只访问单个分区，优先使用本地分区索引
  • 如果需要频繁进行分区维护，优先使用本地分区索引
  • 如果需要跨分区范围查询，考虑使用全局分区索引
  • 如果需要全局唯一约束，使用全局索引

分区索引维护时如何避免索引失效？ ​

  • 对于本地分区索引，使用 UPDATE INDEXES 选项
  • 对于全局分区索引，使用 UPDATE GLOBAL INDEXES 选项
  • 考虑使用在线重建索引
  • 提前规划维护窗口，避免影响业务

如何监控分区索引的使用情况？ ​

监控分区索引使用情况的方法包括：

  • 使用 AWR 报告查看分区索引的使用统计
  • 查询 V$SQL_PLAN 视图，查看分区索引的访问情况
  • 使用 OEM 或其他监控工具提供的可视化监控
  • 启用分区索引监控功能

分区索引如何影响查询性能？ ​

分区索引能够提高查询性能的原因包括：

  • 减少 I/O 操作，只访问相关的索引分区
  • 支持并行查询，提高并发访问性能
  • 减少索引维护成本，提高 DML 操作性能
  • 支持分区修剪，减少需要访问的数据量

19c 和 21c 在分区索引方面有什么主要区别？ ​

Oracle 21c 在分区索引方面相比 19c 有以下主要增强：

  • 引入了智能分区索引功能
  • 提供了实时分区索引监控
  • 支持分区索引碎片自动修复
  • 改进了分区索引压缩算法
  • 新增了 V$PARTITION_INDEX 视图

如何优化分区索引的统计信息？ ​

优化分区索引统计信息的方法包括：

  • 使用合适的采样率：建议使用 DBMS_STATS.AUTO_SAMPLE_SIZE
  • 定期收集统计信息：根据表的变化频率确定收集频率
  • 使用增量统计信息收集：对于分区表，只收集新增或修改的分区的统计信息
  • 使用并行度：提高统计信息收集的速度

总结 ​

分区索引是处理大型表的重要手段，合理的分区索引设计能够显著提高查询性能，减少维护成本。DBA 应该根据查询模式、维护需求和系统资源状况，选择合适的分区索引类型，设计优化的分区索引结构。

在生产环境中，DBA 需要定期监控和维护分区索引，确保其高效运行。随着 Oracle 版本的升级，分区索引功能不断增强，DBA 应充分利用这些新特性，提高分区索引的管理效率和性能。

通过合理的分区索引设计和维护，可以有效提高数据库系统的性能和可靠性，支持更大规模的数据和更高的并发访问。