# 重组所有索引
REORG INDEXES ALL FOR TABLE ORDERS;

# 重组指定索引
REORG INDEX IDX_ORDERS_CUSTOMER FOR TABLE ORDERS;

# 在线索引重组
REORG INDEXES ALL FOR TABLE ORDERS ALLOW WRITE ACCESS;

# 查看重组进度
SELECT
TABNAME,
INDNAME,
REORG_PHASE,
REORG_STATUS,
REORG_COMPLETION
FROM SYSIBMADM.SNAPREORG;

# 查看索引碎片
SELECT
INDNAME,
TABNAME,
NLEAF,
NLEVELS,
CLUSTERRATIO,
CLUSTERFACTOR
FROM SYSCAT.INDEXES
WHERE TABSCHEMA = ‘FGEDB’
ORDER BY CLUSTERRATIO ASC;

# 删除索引
DROP INDEX IDX_ORDERS_CUSTOMER;

# 重新创建索引
CREATE INDEX IDX_ORDERS_CUSTOMER ON ORDERS(CUSTOMER_ID, CREATE_TIME DESC);

# 使用REPLACE重建
CREATE OR REPLACE INDEX IDX_ORDERS_CUSTOMER ON ORDERS(CUSTOMER_ID);

# 批量重建索引
CREATE OR REPLACE PROCEDURE SP_REBUILD_INDEXES(
IN p_table_name VARCHAR(100)
)
LANGUAGE SQL
BEGIN
DECLARE v_indname VARCHAR(100);
DECLARE v_colnames VARCHAR(500);
DECLARE v_sql VARCHAR(1000);

FOR idx AS cur CURSOR FOR
SELECT INDNAME, COLNAMES
FROM SYSCAT.INDEXES
WHERE TABNAME = UPPER(p_table_name)
AND TABSCHEMA = ‘FGEDB’
AND INDNAME NOT LIKE ‘SQL%’
DO
SET v_indname = idx.INDNAME;
SET v_colnames = idx.COLNAMES;

SET v_sql = ‘DROP INDEX ‘ || v_indname;
EXECUTE IMMEDIATE v_sql;

SET v_sql = ‘CREATE INDEX ‘ || v_indname || ‘ ON ‘ || p_table_name || v_colnames;
EXECUTE IMMEDIATE v_sql;
END FOR;
END;

# 创建复合索引
CREATE INDEX IDX_ORDERS_CUSTOMER_DATE ON ORDERS(CUSTOMER_ID, CREATE_TIME DESC);

# 创建包含列索引
CREATE INDEX IDX_ORDERS_COVER ON ORDERS(CUSTOMER_ID)
INCLUDE (TOTAL_AMOUNT, ORDER_STATUS);

# 创建分区索引
CREATE INDEX IDX_ORDERS_PART ON ORDERS(CREATE_TIME, ORDER_ID)
PARTITIONED;

# 创建MDC索引
CREATE INDEX IDX_ORDERS_MDC ON ORDERS(CREATE_DATE, REGION);

# 查看索引使用情况
SELECT
INDNAME,
TABNAME,
COLNAMES,
INDEXTYPE,
CARD,
FULLKEYCARD
FROM SYSCAT.INDEXES
WHERE TABSCHEMA = ‘FGEDB’;

# 查看索引大小
SELECT
INDNAME,
TABNAME,
NLEAF * 8 / 1024 AS SIZE_MB
FROM SYSCAT.INDEXES
WHERE TABSCHEMA = ‘FGEDB’
ORDER BY NLEAF DESC;

# 查找未使用的索引
SELECT
INDNAME,
TABNAME,
LAST_USED
FROM SYSCAT.INDEXES
WHERE LAST_USED < CURRENT DATE - 30 DAYS AND INDNAME NOT LIKE 'SQL%' ORDER BY LAST_USED; # 查找重复索引 SELECT TABNAME, COLNAMES, COUNT(*) AS INDEX_COUNT FROM SYSCAT.INDEXES WHERE TABSCHEMA = 'FGEDB' GROUP BY TABNAME, COLNAMES HAVING COUNT(*) > 1;

# 删除冗余索引
DROP INDEX IDX_ORDERS_CUSTOMER_OLD;

# 查找主键和外键
SELECT
CONSTNAME,
TABNAME,
COLNAMES
FROM SYSCAT.KEYCOLS
WHERE TABSCHEMA = ‘FGEDB’;

# 禁用索引（临时）
ALTER INDEX IDX_ORDERS_CUSTOMER DISABLE;

# 启用索引
ALTER INDEX IDX_ORDERS_CUSTOMER ENABLE;

# 更新索引统计信息
RUNSTATS ON TABLE ORDERS FOR INDEX ALL;

# 更新指定索引统计信息
RUNSTATS ON TABLE ORDERS FOR INDEX IDX_ORDERS_CUSTOMER;

# 查看索引统计信息
SELECT
INDNAME,
TABNAME,
CARD,
FULLKEYCARD,
CLUSTERRATIO,
CLUSTERFACTOR,
SEQUENTIAL_PAGES,
DENSITY
FROM SYSCAT.INDEXES
WHERE TABSCHEMA = ‘FGEDB’;

# 查看索引基数
SELECT
INDNAME,
TABNAME,
FIRST2KEYCARD,
FIRST3KEYCARD,
FIRST4KEYCARD,
FULLKEYCARD
FROM SYSCAT.INDEXES
WHERE TABSCHEMA = ‘FGEDB’;

# 监控索引性能
SELECT
SUBSTR(INDNAME, 1, 30) AS INDNAME,
TABNAME,
INDEX_SCANS,
INDEX_ONLY_SCANS,
KEY_UPDATES,
PAGE_REORGS
FROM SYSIBMADM.INDEXMETRICS
ORDER BY INDEX_SCANS DESC;

# 检查索引碎片
SELECT
INDNAME,
TABNAME,
CLUSTERRATIO,
CASE
WHEN CLUSTERRATIO < 0.8 THEN 'HIGH FRAGMENTATION' WHEN CLUSTERRATIO < 0.9 THEN 'MODERATE FRAGMENTATION' ELSE 'GOOD' END AS FRAGMENTATION_LEVEL FROM SYSCAT.INDEXES WHERE TABSCHEMA = 'FGEDB' ORDER BY CLUSTERRATIO ASC; # 检查索引层级 SELECT INDNAME, TABNAME, NLEVELS, CASE WHEN NLEVELS > 5 THEN ‘TOO MANY LEVELS’
ELSE ‘OK’
END AS LEVEL_STATUS
FROM SYSCAT.INDEXES
WHERE TABSCHEMA = ‘FGEDB’
ORDER BY NLEVELS DESC;

# 检查索引大小
SELECT
INDNAME,
TABNAME,
NLEAF * 8 / 1024 AS SIZE_MB,
CASE
WHEN NLEAF * 8 / 1024 > 1000 THEN ‘LARGE INDEX’
ELSE ‘OK’
END AS SIZE_STATUS
FROM SYSCAT.INDEXES
WHERE TABSCHEMA = ‘FGEDB’
ORDER BY NLEAF DESC;

# 创建索引健康报告
CREATE OR REPLACE PROCEDURE SP_INDEX_HEALTH_REPORT()
LANGUAGE SQL
BEGIN
INSERT INTO INDEX_HEALTH_REPORT (
REPORT_TIME, INDNAME, TABNAME, CLUSTERRATIO, NLEVELS, SIZE_MB, STATUS
)
SELECT
CURRENT TIMESTAMP,
INDNAME,
TABNAME,
CLUSTERRATIO,
NLEVELS,
NLEAF * 8 / 1024,
CASE
WHEN CLUSTERRATIO < 0.8 THEN 'NEEDS REORG' WHEN NLEVELS > 5 THEN ‘TOO MANY LEVELS’
WHEN NLEAF * 8 / 1024 > 1000 THEN ‘LARGE INDEX’
ELSE ‘HEALTHY’
END
FROM SYSCAT.INDEXES
WHERE TABSCHEMA = ‘FGEDB’;
END;