平台2.0索引重建
背景：平台2.0数据库在各地应用有很长一段时间了，索引对数据库查询速度的影响是非常关键的，经分析，目前平台2.0数据库索引主要存在两方面问题：
1、各地平台数据库表结构基本保持一致，但索引的命名规范，创建时间可能各地都不一致（因上线时间不一致），导致无法保证数据库基本结构的一致；
2、随着上线时间的累积，各地数据量大，也没有专职DBA做数据库日常优化，数据增、删比较频繁，导致索引产生碎片，性能下降，并占用空间不能有效释放。
当前各地上线平台已经存储了客户的业务数据内容，对数据库的更新我们必须认真分析和严格测试才能在正式库上更新，针对以上两个问题，我们分别制定了不同的解放方案。
一、 索引的统一和升级
备注：
1、这个过程只在第一次升级时执行一次即可，这个执行过程会长一些，需要认真比对和检查；
2、索引库和创建索引表务必从测试后的标准库统一导出建立，以下流程完成后形成文件XT_INDEXTAB.SQL，CREATINDEX.SQL，DROPINDEX.SQL作为升级使用。
第一步：索引库准备
--创建标准索引表
-- CREATE TABLE
CREATE TABLE XT_INDEXTAB
(
TABLE_NAME VARCHAR2(100),
INDEX_NAME VARCHAR2(200),
INDEX_TYPE CHAR(1)
);
-- ADD COMMENTS TO THE TABLE
COMMENT ON TABLE XT_INDEXTAB
IS '索引表';
-- ADD COMMENTS TO THE COLUMNS
COMMENT ON COLUMN XT_INDEXTAB.TABLE_NAME
IS '表名';
COMMENT ON COLUMN XT_INDEXTAB.INDEX_NAME
IS '索引名';
COMMENT ON COLUMN XT_INDEXTAB.INDEX_TYPE
IS '索引类型';
--将主键索引到入到标准索引库
INSERT INTO XT_INDEXTAB
(TABLE_NAME, INDEX_NAME,INDEX_TYPE)
SELECT AU.TABLE_NAME ASTABLE_NAME,
AU.INDEX_NAME ASINDEX_NAME,
'0' AS INDEX_TYPE
FROM USER_CONSTRAINTS AU
WHERE AU.CONSTRAINT_TYPE = 'P'
AND AU.INDEX_OWNER ='HEALTH_BASE'--注意更换用户名
--将非主键索引到入到标准索引库
INSERT INTO XT_INDEXTAB
(TABLE_NAME, INDEX_NAME,INDEX_TYPE)
SELECT IU.TABLE_NAME ASTABLE_NAME,
IU.INDEX_NAME ASINDEX_NAME,
'1' AS INDEX_TYPE
FROM USER_INDEXES IU
WHERE IU.INDEX_NAME NOT IN
(SELECT AU.INDEX_NAME
FROM USER_CONSTRAINTSAU
WHEREAU.CONSTRAINT_TYPE = 'P'
AND AU.INDEX_OWNER ='HEALTH_BASE') --注意更换用户名
AND IU.INDEX_TYPE = 'NORMAL'
AND IU.TABLE_OWNER ='HEALTH_BASE' --注意更换用户名
--导出标准索引库：XT_INDEXTAB.SQL
insert into XT_INDEXTAB (TABLE_NAME, INDEX_NAME, INDEX_TYPE)
values ('HRB0309', 'HRB0309_PK', '0');
insert into XT_INDEXTAB (TABLE_NAME, INDEX_NAME, INDEX_TYPE)
values ('HRB0310', 'HRB0310_PK', '0');
insert into XT_INDEXTAB (TABLE_NAME, INDEX_NAME, INDEX_TYPE)
values ('HRB0311', 'HRB0311_PK', '0');
insert into XT_INDEXTAB (TABLE_NAME, INDEX_NAME, INDEX_TYPE)
values ('HRB0399_01_02', 'HRB0399_01_02_PK', '0');
insert into XT_INDEXTAB (TABLE_NAME, INDEX_NAME, INDEX_TYPE)
values ('HRB0399_01_04', 'HRB0399_01_04_PK', '0');
…
--创建非主键索引文件：CREATINDEX.SQL;
-- CREATE/RECREATE INDEXES
CREATE INDEX I_HRA00_01_01_HR99_02_001 ON HRA00_01_01 (HR42_02_002,HR99_99_007, HR99_02_001)
TABLESPACE HEALTH_BASE
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE
(
INITIAL 49M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
);
CREATE INDEX LDX_ZDYDAH ON HRA00_01_01 (ZDYJKDAH)
TABLESPACE HEALTH_BASE
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE
(
INITIAL 31M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
);
………
第二步：索引库比对
--查询全部主键索引 与标准主键索引库对比是否一致；
SELECT AU.TABLE_NAME AS 表名, AU.INDEX_NAME AS 主键索引名
FROM USER_CONSTRAINTS AU
WHERE AU.CONSTRAINT_TYPE = 'P'
AND AU.INDEX_OWNER ='HEALTH_BASE'
ORDER BY AU.TABLE_NAME
--如果不一致，逐一修改主键索引；
--查询全部非主键索引库，对比非数据索引与标准库是否一致；
SELECT IU.TABLE_NAME AS 表名, IU.INDEX_NAME AS 非主键索引名
FROM USER_INDEXES IU
WHERE IU.INDEX_NAME NOT IN
(SELECT AU.INDEX_NAME
FROM USER_CONSTRAINTSAU
WHERE AU.CONSTRAINT_TYPE= 'P'
AND AU.INDEX_OWNER ='HEALTH_BASE') --注意更换用户名
AND IU.INDEX_TYPE = 'NORMAL'
AND IU.TABLE_OWNER ='HEALTH_BASE' --注意更换用户名
ORDER BY IU.TABLE_NAME
第三步：索引库删除
--如果不一致，删除全部非主键索引；
SELECT 'DROP INDEX ' || IU.INDEX_NAME || ';'
FROM USER_INDEXES IU
WHERE IU.INDEX_NAME NOT IN
(SELECT AU.INDEX_NAME
FROM USER_CONSTRAINTSAU
WHERE AU.CONSTRAINT_TYPE= 'P'
AND AU.INDEX_OWNER ='HEALTH_BASE') --注意更换用户名
AND IU.INDEX_TYPE = 'NORMAL'
AND IU.TABLE_OWNER ='HEALTH_BASE' --注意更换用户名
ORDER BY IU.TABLE_NAME
--执行以上查询语句另存为 DROPINDEX.SQL
DROP INDEX INX_OLDDAH;
DROP INDEX INX_NEWDAH;
DROP INDEX STU_NAME_EMAIL_UNI;
DROP INDEX UK_GLVCH;
DROP INDEX LDX_ZDYDAH;
DROP INDEX I_HRA00_01_01_HR99_02_001;
DROP INDEX DAH_UN;
DROP INDEX ZDYDAH;
DROP INDEX INX9901004_HRA99_01_01;
…
--执行索引删除脚本：DROPINDEX.SQL
第三步：标准索引库创建
执行创建索引脚本：CREATINDEX.SQL
第四步：同第二步，再次检查主键索引和非主键索引建立情况，直到索引与标准索引库一致；
二、 索引的定时重建
备注：
1、 这个过程可定时执行，可以每隔30天执行一次
2、 这个执行完后生成索引重建日志。
第一步：创建索引重建日志记录表：
-- CREATE TABLE
CREATE TABLE XT_INDEXLOG
(
REBUILDDATE CHAR(19),
REBUILDSTATE CHAR(1),
REBUILDLOG VARCHAR2(1024)
);
-- ADD COMMENTS TO THE TABLE
COMMENT ON TABLE XT_INDEXLOG
IS '索引日志表';
-- ADD COMMENTS TO THE COLUMNS
COMMENT ON COLUMN XT_INDEXLOG.REBUILDDATE
IS '重建日期';
COMMENT ON COLUMN XT_INDEXLOG.REBUILDSTATE
IS '重建结果 0 -成功，1-失败';
COMMENT ON COLUMN XT_INDEXLOG.REBUILDLOG
IS '重建信息';
第二步：定义索引重建存储过程
CREATE OR REPLACE PROCEDURE REBUILD_INDEX AS
/*全部索引重建*/
CURSOR INDEXCURSOR IS
SELECT *
FROM USER_INDEXES
WHERE TABLE_OWNER ='HEALTH_BASE'--根据用户名修改
AND INDEX_TYPE = 'NORMAL';
INDEXROW INDEXCURSOR %ROWTYPE;
SQLTEXT VARCHAR2(1024);
BEGIN
OPEN INDEXCURSOR;
LOOP
FETCH INDEXCURSOR INTOINDEXROW;
EXIT WHEN INDEXCURSOR%NOTFOUND;
SQLTEXT := ' ALTER INDEX ' ||INDEXROW . INDEX_NAME || ' REBUILD ';
BEGIN
EXECUTE IMMEDIATE(SQLTEXT);
INSERT INTO XT_INDEXLOG
(REBUILDDATE,REBUILDSTATE, REBUILDLOG )
VALUES
(SYSDATE, '0', 'REBUILDINDEX SUCCESS:' || INDEXROW . INDEX_NAME);
EXCEPTION
WHEN OTHERS THEN
INSERT INTO XT_INDEXLOG
(REBUILDDATE,REBUILDSTATE, REBUILDLOG )
VALUES
(SYSDATE, '1', 'REBUILDINDEX FAIL:' || INDEXROW . INDEX_NAME);
END;
END LOOP;
END;
第三步：索引重建定时执行
DECLARE
JOB NUMBER; --每月定时执行
BEGIN
SYS .DBMS_JOB.SUBMIT(JOB => JOB,
WHAT => 'REBUILD_INDEX;',
NEXT_DATE=> TRUNC(SYSDATE + 30),
INTERVAL => 'TRUNC(SYSDATE +30)',
NO_PARSE => FALSE);
END;
第四步：定期检查索引重建日志。