简要介绍如果将达梦普通表转换为HASH分区表。

一、导出dmp文件

./dexpdp USERID=DMHS/DMHS12345 FILE=/opt/dmdata/WF_BUSS_FORM0519.dmp LOG=WF_BUSS_FORM.log TABLES=WF_BUSS_FORM

二、备份表和索引

（1）重命名WF_BUSS_FORM为历史表
ALTER TABLE DMHS.WF_BUSS_FORM RENAME TO DMHS.WF_BUSS_FORM_OLD;
（2）重命名索引
ALTER INDEX WORK_ID_INDEX RENAME TO WORK_ID_INDEX_0519;
ALTER INDEX IDX_WORKID_NODEID RENAME TO IDX_WORKID_NODEID_0519;

三、创建分区表

CREATE TABLE “DMHS”.“WF_BUSS_FORM”
(
“ID” NVARCHAR(100) NOT NULL,
“BUSS_ID” NVARCHAR(100),
“WORK_ID” NVARCHAR(100) NOT NULL,
“NODE_ID” NVARCHAR(100) NOT NULL,
“TONODE_ID” NVARCHAR(100),
“GROUP_ID” NVARCHAR(100),
“GROUP_NAME” NVARCHAR(400),
“ATT_KEY” NVARCHAR(200),
“ATT_VALUE” NVARCHAR(2000),
“ATT_TYPE” NVARCHAR(100),
“CREATETIME” TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
“UPDATETIME” TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
“ISDELETE” NUMBER(11,0) DEFAULT 0 NOT NULL,
“ATT_VALUE_JSON” CLOB,
“TRACK_ID” NVARCHAR(100),
“SEND_LEVEL” NUMBER(11,0))
PARTITION BY HASH(ID)(
PARTITION PART_01,
PARTITION PART_02,
PARTITION PART_03,
PARTITION PART_04,
PARTITION PART_05,
PARTITION PART_06,
PARTITION PART_07,
PARTITION PART_08,
PARTITION PART_09,
PARTITION PART_10,
PARTITION PART_11,
PARTITION PART_12);
COMMENT ON COLUMN “DMHS”.“WF_BUSS_FORM”.“CREATETIME” IS ‘创建时间’;
COMMENT ON COLUMN “DMHS”.“WF_BUSS_FORM”.“ID” IS ‘主键’;
COMMENT ON COLUMN “DMHS”.“WF_BUSS_FORM”.“ISDELETE” IS ‘是否删除(1:已删除 0：未删除)’;
COMMENT ON COLUMN “DMHS”.“WF_BUSS_FORM”.“NODE_ID” IS ‘节点ID’;
COMMENT ON COLUMN “DMHS”.“WF_BUSS_FORM”.“SEND_LEVEL” IS ‘业务数据发送级别’;
COMMENT ON COLUMN “DMHS”.“WF_BUSS_FORM”.“TONODE_ID” IS ‘发送到的节点ID’;
COMMENT ON COLUMN “DMHS”.“WF_BUSS_FORM”.“TRACK_ID” IS ‘轨迹ID’;
COMMENT ON COLUMN “DMHS”.“WF_BUSS_FORM”.“UPDATETIME” IS ‘修改时间’;
COMMENT ON COLUMN “DMHS”.“WF_BUSS_FORM”.“WORK_ID” IS ‘流程实例ID’;

四、导入数据到分区表

./dimpdp USERID=DMHS/DMHS12345 FILE=/opt/dmdata/WF_BUSS_FORM0519.dmp LOG=WF_BUSS_FORM0519imp.log TABLES=DMHS.WF_BUSS_FORM TABLE_EXISTS_ACTION=APPEND

五、给新表增加索引

CREATE INDEX “WORK_ID_INDEX” ON “DMHS”.“WF_BUSS_FORM”(“WORK_ID” ASC) ;
CREATE INDEX “IDX_WORKID_NODEID” ON “DMHS”.“WF_BUSS_FORM”(“WORK_ID” ASC,“NODE_ID” ASC);

六、核对分区表数据

–核对从表总数据量,看两者是否一致
select count(ID) from DMHS.WF_BUSS_FORM;
select count(ID) from DMHS.WF_BUSS_FORM_OLD;

七、查看分区是否有数据

（1）抽样查看
SELECT COUNT(1) FROM DMHS.WF_BUSS_FORM PARTITION(PART_01);
SELECT COUNT(1) FROM DMHS.WF_BUSS_FORM PARTITION(PART_04);
SELECT COUNT(1) FROM DMHS.WF_BUSS_FORM PARTITION(PART_07);
SELECT COUNT(1) FROM DMHS.WF_BUSS_FORM PARTITION(PART_12);

八、更新统计信息

（1）在命令窗口执行表统计信息
CALL DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=‘DMHS’,TABNAME=‘WF_BUSS_FORM’,DEGREE=8,ESTIMATE_PERCENT=100);
–统计完成后查看各分区数据量
SELECT
T.TABLE_NAME ,
T.PARTITION_NAME,
T.NUM_ROWS ,
T.LAST_ANALYZED
FROM
DBA_TAB_PARTITIONS T
WHERE
T.TABLE_NAME = ‘WF_BUSS_FORM’ ORDER BY 2;

九、启动应用服务

应用连接数据库进行测试。

十、清理备份表释放磁盘空间

（1）待生产环境正常后，再执行
drop table DMHS.WF_BUSS_FORM_OLD;

达梦普通表转换为分区表

签到成功

CNDBA社区