Oracle 索引维护

索引维护概述

索引维护是 Oracle 数据库性能管理的重要组成部分，定期的索引维护能够确保索引的高效运行，提高查询性能，减少系统资源消耗。索引维护包括索引重建、索引统计收集、索引监控和索引优化等任务。

索引维护的目标是：

保持索引的高效性
减少索引碎片
确保索引统计信息的准确性
识别和删除无用的索引
优化索引结构

索引维护的必要性

索引碎片

频繁的 DML 操作（插入、更新、删除）会导致索引产生碎片，影响索引的性能。索引碎片主要包括：

内部碎片：索引块内部的空闲空间
外部碎片：索引块之间的空闲空间
逻辑碎片：索引键值的顺序与物理存储顺序不一致

统计信息过时

不准确或过时的统计信息会导致优化器生成错误的执行计划，影响查询性能。统计信息包括：

表的行数和块数
列的选择性和分布
索引的高度和叶子块数量

无用的索引

随着业务的发展和查询模式的变化，一些索引可能不再被使用，成为无用的索引。无用的索引会导致：

存储资源浪费
DML 操作变慢
索引维护成本增加

索引维护任务

1. 索引重建

索引重建是最常用的索引维护任务，能够消除索引碎片，优化索引结构。

什么时候需要重建索引

索引碎片率超过 30%
索引高度超过 4 层
索引的叶子块使用率低于 50%
查询性能明显下降
索引统计信息不准确

索引重建方法

方法 1：使用 ALTER INDEX REBUILD

sql

-- 重建索引
ALTER INDEX SCOTT.IDX_EMP_DEPT REBUILD;

-- 在线重建索引（允许并发 DML）
ALTER INDEX SCOTT.IDX_EMP_DEPT REBUILD ONLINE;

-- 重建分区索引
ALTER INDEX SCOTT.IDX_SALES_DATE REBUILD PARTITION SALES_Q1_2023;

-- 在线重建分区索引
ALTER INDEX SCOTT.IDX_SALES_DATE REBUILD PARTITION SALES_Q1_2023 ONLINE;

方法 2：使用 ALTER INDEX COALESCE

COALESCE 命令能够合并索引的叶子块，减少索引碎片，但不重建索引结构。

sql

-- 合并索引
ALTER INDEX SCOTT.IDX_EMP_DEPT COALESCE;

-- 合并分区索引
ALTER INDEX SCOTT.IDX_SALES_DATE COALESCE PARTITION SALES_Q1_2023;

方法 3：使用 CREATE INDEX ... WITH ONLINE DDL

对于大型索引，可以使用 CREATE INDEX ... WITH ONLINE DDL 命令重建索引。

sql

-- 使用 ONLINE DDL 重建索引
CREATE INDEX SCOTT.IDX_EMP_DEPT_NEW ON SCOTT.EMP(department_id) WITH ONLINE DDL;
DROP INDEX SCOTT.IDX_EMP_DEPT;
ALTER INDEX SCOTT.IDX_EMP_DEPT_NEW RENAME TO IDX_EMP_DEPT;

2. 索引统计信息收集

准确的统计信息对于优化器生成正确的执行计划至关重要。Oracle 提供了 DBMS_STATS 包用于收集统计信息。

收集统计信息的方法

方法 1：收集表和索引的统计信息

sql

-- 收集表和索引的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    cascade => TRUE,
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    degree => DBMS_STATS.AUTO_DEGREE
);

方法 2：仅收集索引的统计信息

sql

-- 仅收集索引的统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS(
    ownname => 'SCOTT',
    indname => 'IDX_EMP_DEPT',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    degree => DBMS_STATS.AUTO_DEGREE
);

方法 3：收集分区索引的统计信息

sql

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

统计信息收集策略

手动收集：适用于小型表或需要立即更新统计信息的场景
自动收集：使用 Oracle 自动维护任务，适用于大多数场景
增量收集：适用于分区表，只收集新增或修改的分区的统计信息

3. 索引监控

定期监控索引的使用情况，能够识别无用的索引，优化索引结构。

监控索引使用情况

方法 1：使用索引监控功能

sql

-- 启用索引监控
ALTER INDEX SCOTT.IDX_EMP_DEPT MONITORING USAGE;

-- 查看索引监控结果
SELECT * FROM V$OBJECT_USAGE WHERE index_name = 'IDX_EMP_DEPT';

-- 禁用索引监控
ALTER INDEX SCOTT.IDX_EMP_DEPT NOMONITORING USAGE;

方法 2：使用 AWR 报告

AWR 报告中的 "SQL Plan Statistics" 部分提供了索引使用的统计信息，包括：

索引的使用次数
索引的逻辑读和物理读
索引的 CPU 时间和执行时间

方法 3：使用 ASH 报告

ASH 报告能够显示索引的实时使用情况，包括：

当前正在使用的索引
索引的等待事件
索引的使用频率

4. 索引优化

索引优化包括调整索引结构、删除无用的索引、优化索引设计等任务。

删除无用的索引

sql

-- 查看最近 30 天未使用的索引
SELECT
    owner,
    index_name,
    table_name
FROM
    dba_indexes
WHERE
    owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
    AND index_name NOT IN (
        SELECT DISTINCT index_name
        FROM dba_hist_sql_plan
        WHERE
            operation LIKE '%INDEX%'
            AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE begin_interval_time > SYSDATE - 30)
    );

-- 删除无用的索引
DROP INDEX SCOTT.IDX_EMP_DEPT_UNUSED;

调整索引结构

调整复合索引的列顺序
增加或删除索引列
改变索引类型
调整索引的存储参数

sql

-- 调整索引的存储参数
ALTER INDEX SCOTT.IDX_EMP_DEPT STORAGE (INITIAL 10M NEXT 5M PCTINCREASE 0);

优化索引设计

根据查询模式调整索引设计
合并冗余索引
优化索引的列选择性
考虑使用索引压缩

索引维护计划

日常维护任务

监控索引使用情况：每天查看索引监控结果，识别无用的索引
监控索引碎片：每天检查索引碎片率，识别需要重建的索引
监控索引统计信息：每天检查索引统计信息的准确性

每周维护任务

收集统计信息：每周收集一次表和索引的统计信息
重建碎片化的索引：每周重建碎片率超过 30% 的索引
分析索引使用情况：每周分析索引使用情况，识别需要优化的索引

每月维护任务

全面索引审计：每月进行一次全面的索引审计，包括索引设计、索引使用情况和索引性能
优化索引结构：每月根据审计结果优化索引结构
删除无用的索引：每月删除确认无用的索引

季度维护任务

索引设计评估：每季度评估索引设计，根据业务需求调整索引设计
索引性能测试：每季度进行索引性能测试，验证索引维护的效果
索引维护策略调整：每季度根据索引性能测试结果调整索引维护策略

索引维护最佳实践

1. 选择合适的维护窗口

选择业务低峰期进行索引维护
对于大型索引，考虑使用在线重建
提前通知相关业务团队
监控维护操作的进度和资源使用

2. 优化维护操作

使用并行度提高维护操作的速度
对于大型表，考虑使用增量统计信息收集
使用 ONLINE 选项允许并发 DML 操作
避免在维护期间进行大量的 DML 操作

3. 监控维护操作

监控维护操作的进度
监控维护操作对系统性能的影响
记录维护操作的结果
分析维护操作的效果

4. 测试维护操作

在测试环境中测试维护操作
评估维护操作对查询性能的影响
验证维护操作的正确性
制定回滚计划

5. 自动化维护任务

使用 Oracle 自动维护任务
使用 shell 脚本或 PL/SQL 存储过程自动化维护任务
使用调度工具（如 cron 或 Oracle Scheduler）定期执行维护任务

19c 和 21c 索引维护新特性

Oracle 19c 新特性

自动索引：Oracle 19c 引入了自动索引功能，能够自动识别、创建、维护和删除索引
索引重建增强：改进了索引重建算法，减少了索引重建的时间和资源消耗
统计信息收集增强：改进了统计信息收集算法，提高了统计信息的准确性和收集速度
分区索引维护增强：支持在线分区索引维护，允许在维护期间进行并发 DML 操作

Oracle 21c 新特性

智能索引维护：引入了机器学习算法，能够根据索引的使用情况自动调整维护策略
实时索引监控：提供实时的索引使用监控，能够及时识别需要维护的索引
索引压缩增强：改进了索引压缩算法，提高了压缩率和查询性能
索引碎片自动修复：能够自动识别和修复索引碎片
新视图 V$INDEX_MAINTENANCE：提供索引维护的实时监控信息

生产环境索引维护案例

案例 1：大型表索引重建

场景：一个包含 1 亿行数据的大型表，其索引碎片率超过 50%，查询性能明显下降。

解决方案：

在线重建索引：使用 ONLINE 选项允许并发 DML 操作
使用并行度：设置适当的并行度提高重建速度
监控重建进度：使用 V$INDEX_REBUILD_STATUS 视图监控重建进度
验证重建效果：重建完成后，验证索引碎片率和查询性能

sql

-- 在线并行重建索引
ALTER INDEX SCOTT.IDX_LARGE_TABLE_COL REBUILD ONLINE PARALLEL 8;

-- 监控重建进度
SELECT * FROM V$INDEX_REBUILD_STATUS WHERE index_name = 'IDX_LARGE_TABLE_COL';

-- 恢复索引并行度
ALTER INDEX SCOTT.IDX_LARGE_TABLE_COL NOPARALLEL;

案例 2：分区表索引维护

场景：一个按月份分区的分区表，每月新增一个分区，需要定期维护分区索引。

解决方案：

增量统计信息收集：只收集新增分区的统计信息
分区索引重建：只重建碎片化的分区索引
自动化维护：使用 Oracle Scheduler 自动执行维护任务

sql

-- 创建分区索引维护存储过程
CREATE OR REPLACE PROCEDURE maintain_partition_index IS
BEGIN
    -- 收集新增分区的统计信息
    EXEC DBMS_STATS.GATHER_INDEX_STATS(
        ownname => 'SCOTT',
        indname => 'IDX_SALES_DATE',
        partname => 'SALES_' || TO_CHAR(SYSDATE, 'MON_YYYY'),
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        degree => DBMS_STATS.AUTO_DEGREE
    );
    
    -- 重建碎片化的分区索引
    FOR p IN (SELECT partition_name FROM dba_ind_partitions WHERE index_owner = 'SCOTT' AND index_name = 'IDX_SALES_DATE' AND num_rows > 1000000)
    LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ALTER INDEX SCOTT.IDX_SALES_DATE REBUILD PARTITION ' || p.partition_name || ' ONLINE';
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
    END LOOP;
END;
/

-- 创建调度任务
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'MAINTAIN_PARTITION_INDEX_JOB',
        job_type => 'STORED_PROCEDURE',
        job_action => 'MAINTAIN_PARTITION_INDEX',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MONTHLY; BYDAY=1; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
        enabled => TRUE
    );
END;
/

案例 3：无用索引清理

场景：一个包含多个无用索引的数据库，需要识别和删除这些无用的索引。

解决方案：

启用索引监控：为所有索引启用监控功能
收集监控数据：收集 30 天的索引监控数据
识别无用索引：根据监控数据识别无用的索引
验证无用索引：手动验证索引的使用情况
删除无用索引：删除确认无用的索引

sql

-- 为所有索引启用监控
DECLARE
    CURSOR c_indexes IS
        SELECT owner, index_name FROM dba_indexes WHERE owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP');
BEGIN
    FOR idx IN c_indexes LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.owner || '.' || idx.index_name || ' MONITORING USAGE';
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
    END LOOP;
END;
/

-- 30 天后，查看无用的索引
SELECT
    owner,
    index_name,
    table_name
FROM
    dba_indexes
WHERE
    owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
    AND index_name NOT IN (
        SELECT DISTINCT index_name
        FROM v$object_usage
        WHERE used = 'YES'
    );

-- 删除无用的索引
DECLARE
    CURSOR c_unused_indexes IS
        SELECT owner, index_name
        FROM dba_indexes
        WHERE
            owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
            AND index_name NOT IN (
                SELECT DISTINCT index_name
                FROM v$object_usage
                WHERE used = 'YES'
            );
BEGIN
    FOR idx IN c_unused_indexes LOOP
        BEGIN
            EXECUTE IMMEDIATE 'DROP INDEX ' || idx.owner || '.' || idx.index_name;
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
    END LOOP;
END;
/

常见问题 (FAQ)

如何判断索引是否需要重建？

判断索引是否需要重建的方法包括：

检查索引碎片率：如果碎片率超过 30%，需要重建
检查索引高度：如果索引高度超过 4 层，需要重建
检查索引的叶子块使用率：如果使用率低于 50%，需要重建
检查查询性能：如果查询性能明显下降，可能需要重建索引

在线重建索引和离线重建索引有什么区别？

在线重建索引：允许并发 DML 操作，不阻塞用户访问，但需要更多的系统资源
离线重建索引：不允许并发 DML 操作，会阻塞用户访问，但需要较少的系统资源

如何收集准确的索引统计信息？

收集准确的索引统计信息的方法包括：

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

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

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

使用索引监控功能：ALTER INDEX ... MONITORING USAGE
使用 AWR 报告：查看索引的使用统计信息
使用 ASH 报告：查看索引的实时使用情况
使用 OEM 或其他监控工具：提供可视化的索引使用监控

如何删除无用的索引？

删除无用的索引的步骤包括：

启用索引监控功能
收集足够的监控数据（建议 30 天）
根据监控数据识别无用的索引
手动验证索引的使用情况
删除确认无用的索引
监控删除索引后的系统性能

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

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

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

如何自动化索引维护任务？

自动化索引维护任务的方法包括：

使用 Oracle 自动维护任务
使用 PL/SQL 存储过程编写维护脚本
使用 Oracle Scheduler 或 cron 调度维护任务
使用第三方工具，如 OEM 或 GoldenGate

索引维护对系统性能有什么影响？

索引维护对系统性能的影响包括：

增加 CPU 和 I/O 消耗
可能阻塞用户访问（离线维护）
临时增加存储需求
提高查询性能（长期影响）

总结

在进行索引维护时，DBA 应该考虑以下因素：

选择合适的维护窗口
优化维护操作
监控维护操作
测试维护操作
自动化维护任务

随着 Oracle 版本的升级，索引维护功能不断增强，DBA 应充分利用这些新特性，提高索引维护的效率和效果。通过合理的索引维护，可以有效提高数据库系统的性能和可靠性，满足业务需求。

Oracle 索引维护 ​

索引维护概述 ​

索引维护的必要性 ​

索引碎片 ​

统计信息过时 ​

无用的索引 ​

索引维护任务 ​

1. 索引重建 ​

什么时候需要重建索引 ​

索引重建方法 ​

方法 1：使用 ALTER INDEX REBUILD ​

方法 2：使用 ALTER INDEX COALESCE ​

方法 3：使用 CREATE INDEX ... WITH ONLINE DDL ​

2. 索引统计信息收集 ​

收集统计信息的方法 ​

方法 1：收集表和索引的统计信息 ​

方法 2：仅收集索引的统计信息 ​

方法 3：收集分区索引的统计信息 ​

统计信息收集策略 ​

3. 索引监控 ​

监控索引使用情况 ​

方法 1：使用索引监控功能 ​

方法 2：使用 AWR 报告 ​

方法 3：使用 ASH 报告 ​

4. 索引优化 ​

删除无用的索引 ​

调整索引结构 ​

优化索引设计 ​

索引维护计划 ​

日常维护任务 ​

每周维护任务 ​

每月维护任务 ​

季度维护任务 ​

索引维护最佳实践 ​

1. 选择合适的维护窗口 ​

2. 优化维护操作 ​

3. 监控维护操作 ​

4. 测试维护操作 ​

5. 自动化维护任务 ​

19c 和 21c 索引维护新特性 ​

Oracle 19c 新特性 ​

Oracle 21c 新特性 ​

生产环境索引维护案例 ​

案例 1：大型表索引重建 ​

案例 2：分区表索引维护 ​

案例 3：无用索引清理 ​

常见问题 (FAQ) ​

如何判断索引是否需要重建？ ​

在线重建索引和离线重建索引有什么区别？ ​

如何收集准确的索引统计信息？ ​

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

如何删除无用的索引？ ​

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

如何自动化索引维护任务？ ​

索引维护对系统性能有什么影响？ ​

总结 ​

Oracle 索引维护

索引维护概述

索引维护的必要性

索引碎片

统计信息过时

无用的索引

索引维护任务

1. 索引重建

什么时候需要重建索引

索引重建方法

方法 1：使用 ALTER INDEX REBUILD

方法 2：使用 ALTER INDEX COALESCE

方法 3：使用 CREATE INDEX ... WITH ONLINE DDL

2. 索引统计信息收集

收集统计信息的方法

方法 1：收集表和索引的统计信息

方法 2：仅收集索引的统计信息

方法 3：收集分区索引的统计信息

统计信息收集策略

3. 索引监控

监控索引使用情况

方法 1：使用索引监控功能

方法 2：使用 AWR 报告

方法 3：使用 ASH 报告

4. 索引优化

删除无用的索引

调整索引结构

优化索引设计

索引维护计划

日常维护任务

每周维护任务

每月维护任务

季度维护任务

索引维护最佳实践

1. 选择合适的维护窗口

2. 优化维护操作

3. 监控维护操作

4. 测试维护操作

5. 自动化维护任务

19c 和 21c 索引维护新特性

Oracle 19c 新特性

Oracle 21c 新特性

生产环境索引维护案例

案例 1：大型表索引重建

案例 2：分区表索引维护

案例 3：无用索引清理

常见问题 (FAQ)

如何判断索引是否需要重建？

在线重建索引和离线重建索引有什么区别？

如何收集准确的索引统计信息？

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

如何删除无用的索引？

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

如何自动化索引维护任务？

索引维护对系统性能有什么影响？

总结