如何制作重建所有索引的批次脚本档案? (DBMR2924)
输入以下SQL命令以检查并重建所有索引。 例如,有一个包含五个索引的数据库。
1. 检查原始索引的建立时间“ CREATE_TIME”。
dmSQL> SELECT CREATE_TIME, TRIM(TABLE_OWNER) || '.' || TRIM(TABLE_NAME) as INDEX_NAME from SYSINDEX;
dmSQL> SELECT CREATE_TIME, TRIM(TABLE_OWNER) || '.' || TRIM(TABLE_NAME) as INDEX_NAME from SYSINDEX; CREATE_TIME INDEX_NAME
=================== ===================
2020/01/31 14:55:02 SYSADM.T1
2020/01/31 14:55:13 SYSADM.T2
2020/01/31 14:56:23 SYSADM.T3
2020/01/31 14:57:34 SYSADM.T4
2020/01/31 14:58:45 SYSADM.T5
2.获取所有重建索引命令。
dmSQL> SELECT 'REBUILD INDEX ' ||RTRIM(INDEX_NAME) || ' FOR ' || RTRIM(TABLE_OWNER) || '.' || RTRIM(TABLE_NAME) || ';' FROM SYSINDEX ORDER BY TABLE_OWNER,TABLE_NAME;
REBUILD INDEX IDX1 FOR SYSADM.T1;
REBUILD INDEX IDX2 FOR SYSADM.T2;
REBUILD INDEX IDX3 FOR SYSADM.T3;
REBUILD INDEX IDX4 FOR SYSADM.T4;
REBUILD INDEX IDX5 FOR SYSADM.T5;
3. 将这些重建命令复制到脚本档案中,然后运行它。
4.再次执行步骤1。所有索引的“CREATE_TIME”已更新。
dmSQL> SELECT CREATE_TIME, TRIM(TABLE_OWNER) || '.' || TRIM(TABLE_NAME) as INDEX_NAME from SYSINDEX;
CREATE_TIME INDEX_NAME
=================== ===================
2020/01/31 16:14:40 SYSADM.T1
2020/01/31 16:14:40 SYSADM.T2
2020/01/31 16:14:40 SYSADM.T3
2020/01/31 16:14:40 SYSADM.T4
2020/01/31 16:14:40 SYSADM.T5
<p>Version: 5.2.2 <br />Product: Normal/Bundle<br />Platform: Windows/Linux</p>