一个完整的以在线重定义方式普通表转分区表的方案

来源：互联网发布：医药政策法规数据库编辑：程序博客网时间：2024/04/28 06:01

1. 转分区表原因

生产数据库，一张表，一亿多行数据，绝大部分查询按月为维度做时间范围查

询，未分区状态下，查询IO量大，计划以分区截剪的方式减少IO量，提升前前台查询性能

2. 实施目的

将未分区的oXX_XXt_owner.id_oXXXdx_mX表以（stat_XXcle）字段为分区键，以在定义的方式转换成按月分区表的时间范围分区表，利且在线重定义的特性，保留权限的不漏赋以及极少时间的影响生产。

3. 注意事项

(1) 数据库若是双节点，只需在其中一个节点的数据库上实施。

(2) 实施中部分命令执行时间较长，命令一旦执行后请不要中断命令的执行一条命令执行完毕后，不能再次重复执行该命令。

(3) 执行命令的数据库用户需要为sys或者system用户。

4.实施步骤

4.1. 创建中间表

CREATE TABLE OXX_XXT_OWNER.ID_OXXXDX_MX_TMP

(

iXx_data_id VARCHAR2 (42),

oXg_no VARCHAR2 (16),

iXx_id VARCHAR2 (24),

iXx_code VARCHAR2 (16),

PXRIOD VARCHAR2 (8),

stat_XXcle VARCHAR2 (16),

……

)

TABLESPACE OXXXIEW

PARTITION BY RANGE

(STAT_XXCLE)

(

PARTITION ONXXDX_MIN VALUES LESS THAN('20140101'),

PARTITION ONXXDX_2014_01 VALUES LESS THAN('20140201'),

PARTITION ONXXDX_2014_02 VALUES LESS THAN('20140301'),

PARTITION ONXXDX_2014_03 VALUES LESS THAN('20140401'),

……

PARTITION ONXXDX_2018_02 VALUES LESS THAN('20180301'),

PARTITION ONXXDX_2018_03 VALUES LESS THAN('20180401'),

PARTITION ONXXDX_2018_04 VALUES LESS THAN('20180501'),

PARTITION ONXXDX_2018_05 VALUES LESS THAN('20180601'),

PARTITION ONXXDX_2018_06 VALUES LESS THAN('20180701'),

PARTITION ONXXDX_2018_07 VALUES LESS THAN('20180801'),

PARTITION ONXXDX_2018_08 VALUES LESS THAN('20180901'),

PARTITION ONXXDX_2018_09 VALUES LESS THAN('20181001'),

PARTITION ONXXDX_2018_10 VALUES LESS THAN('20181101'),

PARTITION ONXXDX_2018_11 VALUES LESS THAN('20181201'),

PARTITION ONXXDX_2018_12 VALUES LESS THAN('20190101'),

PARTITION ONXXDX_MAX VALUES LESS THAN(MAXVALUE));

4.2. 表在线重定义

4.2.1. 异常回退措施（正常情况不用执行，用于失败回滚用的）

若执行完以下步骤3.2.3开始表在线重定义，在执行3.2.4,3.2.5,3.2.6时报错，命令不能执行，则执行以下命令回退表在线重定义，并中断整个表的实施过程。

BEGIN

DBMS_REDEFINITION.ABORT_REDEF_TABLE(

UNAME => 'OXX_XXT_OWNER',

ORIG_TABLE => 'ID_OXXXDX_MX',

INT_TABLE => 'ID_OXXXDX_MX_TMP');

END;

4.2.2. 验证表能否能在线重定义

BEGIN

DBMS_REDEFINITION.CAN_REDEF_TABLE(

UNAME => 'OXX_XXT_OWNER',

TNAME => 'ID_OXXXDX_MX',

OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK);

END;

4.2.3. 开始表在线重定义

BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE(

UNAME => 'OXX_XXT_OWNER',

ORIG_TABLE => 'ID_OXXXDX_MX',

INT_TABLE => 'ID_OXXXDX_MX_TMP',

OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK);

END;

4.2.4. 复制原表上的依赖对象

DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(

'OXX_XXT_OWNER',

'ID_OXXXDX_MX',

'ID_OXXXDX_MX_TMP',

DBMS_REDEFINITION.CONS_ORIG_PARAMS,

TRUE,

num_errors);

END;

4.2.5. 同步原表与中间表的数据

BEGIN

DBMS_REDEFINITION.SYNC_INTERIM_TABLE('OXX_XXT_OWNER',

'ID_OXXXDX_MX',

'ID_OXXXDX_MX_TMP');

END;

4.2.6. 完成表在线重定义

BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE(

UNAME => 'OXX_XXT_OWNER',

ORIG_TABLE => 'ID_OXXXDX_MX',

INT_TABLE => 'ID_OXXXDX_MX_TMP');

END;

4.3. 添加表注释

comment on column OXX_XXT_OWNER.ID_OXXXDX_MX.dim_code1

is 'XX代码1';

4.4. 重建含有stat_XXcle列的索引为本地分区索引

DROP INDEX OXX_XXT_OWNER. IDX_ID_OXXXDX_MX

DROP INDEX OXX_XXT_OWNER.IDX_ID_OXXXDX_MX2;

DROP INDEX OXX_XXT_OWNER.IDX_ID_OXXXDX_MX3

CREATE UNIQUE INDEX OXX_XXT_OWNER.IDX_ID_OXXXDX_MX

ON OXX_XXT_OWNER.ID_OXXXDX_MX(OXG_NO,

IXX_CODE,

……

SXPPLY_TYPE,

DXTA_SRC)

TABLESPACE TBS_XXN_DT

LOCAL;

CREATE INDEX OXX_XXT_OWNER.IDX_ID_OXXXDX_MX2

ON OXX_XXT_OWNER.ID_OXXXDX_MX(STAT_XXCLE)

TABLESPACE TBS_XXN_DT

LOCAL;

CREATE INDEX OXX_XXT_OWNER.IDX_ID_OXXXDX_MX3

ON OXX_XXT_OWNER.ID_OXXXDX_MX(OXG_NO, STAT_XXCLE)

TABLESPACE TBS_XXN_DT

LOCAL;

4.5. 重新统计表及索引信息

BEGIN

DBMS_STATS.gather_table_stats (ownname => 'OXX_XXT_OWNER',

tabname =>'ID_OXXXDX_MX',

estimate_percent =>20,

degree => 8,

cascade => TRUE);

END;

4.6. 开启行迁移

ALTER TABLE OXX_XXT_OWNER.ID_OXXXDX_MX ENABLE ROW MOVEMENT;

5. 验证

执行如下sql语句：

SELECT *

FROM dba_tab_partitions

WHERE table_owner ='OXX_XXT_OWNER' AND table_name= 'ID_OXXXDX_MX'

若返回结果不为空 则说明执行成功。

本文作者：黎俊杰（网名：踩点），从事”系统架构、操作系统、存储设备、数据库、中间件、应用程序“六个层面系统性的性能优化工作

欢迎加入系统性能优化专业群，共同探讨性能优化技术。群号：258187244

0 0