一、问题背景

生产中由于管理不规范，导致新建了很多索引，实际可能有用的索引没几个，怎么知道哪些是有用的索引，哪些是没用的索引，并对这些索引进行处理呢？

二、方法思路

达梦有一个功能就是可以开启索引使用监控，监控索引有没有被使用到，根据这个我们可以对一些冗余索引进行处理。

涉及ini参数MONITOR_INDEX_FLAG：是否对索引进行监控，其中

• 0：关闭自动监控，可使用 ALTER INDEX语句启用索引监控；
• 1：打开自动监控，对用户定义的二级索引进行监控；
• 2：禁止索引监控

生产环境请勿将参数直接设置为1，设为0也需谨慎使用，用完及时取消。

如果参数MONITOR_INDEX_FLAG=0，对索引进行监控的sql语法如下：

添加监控
ALTER INDEX 索引所属者.索引名 MONITORING USAGE;
取消监控
ALTER INDEX 索引所属者.索引名 NOMONITORING USAGE;

索引监控起来了，对应的索引使用情况怎样的？去哪里获知呢？
通过以下视图可以获知监控的索引使用情况

SELECT * FROM SYS.V$OBJECT_USAGE;

三、运维案例

（1）确定需要监控的索引【达梦只能监控用户创建的二级索引】

BEGIN
    FOR RS IN (SELECT
                   'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' MONITORING USAGE;' AS EXEC_SQL,*
               FROM
                   SYS.DBA_INDEXES
               WHERE
                   INDEX_TYPE = 'NORMAL'
                   AND OWNER = 'TEST') --监控模式下面的二级索引
    LOOP
        EXECUTE IMMEDIATE RS.EXEC_SQL;
    END LOOP;
END;

（2）监控系统运行一段时间

（3）查看监控的索引使用情况USED字段YES表示使用过NO表示未被使用过。

SELECT * FROM SYS.V$OBJECT_USAGE;

（4）备份未使用的索引定义到表op_index，然后删除未使用的索引

--创建备忘表
CREATE TABLE OP_INDEX (A DATETIME DEFAULT NOW(), B CLOB);
--备份并删除
BEGIN
    FOR RS IN (SELECT
                   'DROP INDEX ' || SCH_NAME || '.' || INDEX_NAME || ';' AS                                                                                                                                            EXEC_SQL,
                   CAST(' INSERT INTO OP_INDEX(B) SELECT DBMS_METADATA.GET_DDL(''INDEX'',''' AS VARCHAR(8000)) || CAST(INDEX_NAME AS VARCHAR(8000)) || ''',''' || CAST(SCH_NAME AS VARCHAR(8000)) || '''); COMMIT;' AS EXEC_INDEX,
                   *
               FROM
                   V$OBJECT_USAGE
               WHERE
                   USED = 'NO') LOOP
        EXECUTE IMMEDIATE RS.EXEC_INDEX; --备份冗余索引
        PRINT RS.EXEC_INDEX;
        EXECUTE IMMEDIATE RS.EXEC_SQL; --删除冗余索引
    END LOOP;
END;

（5）取消索引的监控

BEGIN
    FOR RS IN (SELECT
                   'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' NOMONITORING USAGE;' AS EXEC_SQL,*
               FROM
                   SYS.DBA_INDEXES
               WHERE
                   INDEX_TYPE = 'NORMAL'
                   AND OWNER = 'TEST') LOOP
        EXECUTE IMMEDIATE RS.EXEC_SQL;
    END LOOP;
END;