利用DBMS_REDEFINITION包将非分区表转化成分区表

 

将普通表格转化分区表的方法大致有四种：

A. 通过 Export/import 方法
B. 通过 Insert with a subquery 方法
C. 通过 Partition Exchange 方法
D. 通过 DBMS_REDEFINITION 方法

 

下面举例使用DBMS_REDEFINITION的方法将普通表格转化成分区表

1.创建测试表

SQL> CREATE TABLE T(
a NUMBER,
y number,
name VARCHAR2(100),
date_used date,
constraint pk_ay primary key(a,y));

Table created.

 

2.生成测试数据

begin
for i in 1 .. 1000
loop
for j in 1 .. 1000
loop
insert into t values ( i, j, dbms_random.random, sysdate-j );
end loop;
end loop;
end;
/
PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

 

3.收集T表格的统计信息

SQL> EXEC DBMS_STATS.gather_table_stats('SCOTT', 'T', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT num_rows FROM user_tables WHERE table_name = 'T';

NUM_ROWS
----------
1000000


4.创建分区表(中间临时表）

SQL> CREATE TABLE p_t(
a NUMBER,
y number,
name VARCHAR2(100),
date_used DATE)
PARTITION BY RANGE (date_used)
(PARTITION unpar_table_15 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
PARTITION unpar_table_16 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
PARTITION unpar_table_17 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
PARTITION unpar_table_MX VALUES LESS THAN (MAXVALUE));

Table created.

5.检验表格能否重定义
SQL> exec dbms_Redefinition.can_redef_table('SCOTT', 'T');

PL/SQL procedure successfully completed.

 

6.开始在线重定义，此过程会创建物化视图P_T和物化视图日志MLOG$_T
SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'SCOTT',
orig_table => 'T',
int_table => 'P_T');
END;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

COUNT(*)
----------
1000000

Elapsed: 00:00:00.05
SQL> select count(*) from p_t;

COUNT(*)
----------
1000000

SQL> select mview_name,container_name, build_mode from user_mviews;

MVIEW_NAME             CONTAINER_NAME                  BUILD_MOD
------------------------------ ------------------------------           ---------
P_T                                       P_T                                  PREBUILT

 

7. 向原表插入1000行数据，检查MLOG$_T表是否记录了更新

SQL> begin
for i in 1001 .. 1010
loop
for j in 1001 .. 1100
loop
insert into t values ( i, j, dbms_random.random, sysdate-j );
end loop;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select count(*) from MLOG$_T;

COUNT(*)
----------
1000

 

8.运行dbms_redefinition.sync_interim_table 填充表数据，在执行 dbms_redefinition.finish_redef_table前可以多次执行

SQL> BEGIN
dbms_redefinition.sync_interim_table(
uname => 'SCOTT',
orig_table => 'T',
int_table => 'P_T');
END;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from p_t;

COUNT(*)
----------
1001000

 

SQL> ALTER TABLE p_t ADD (CONSTRAINT p_t_pk PRIMARY KEY (a,y));

Table altered.

SQL> EXEC DBMS_STATS.gather_table_stats('SCOTT', 'P_T', cascade => TRUE);

PL/SQL procedure successfully completed.

 

 

9.使用dbms_redefinition.finish_redef_table 交换表名，过程中原表T会被锁定。

SQL> BEGIN
dbms_redefinition.finish_redef_table(
uname => 'SCOTT',
orig_table => 'T',
int_table => 'P_T');
END;
/

PL/SQL procedure successfully completed.

10.验证重定义的结果

SQL> SELECT partitioned FROM user_tables WHERE table_name = 'T';

PAR
---
YES

 

SQL> SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'T';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
UNPAR_TABLE_15      178000
UNPAR_TABLE_16      366000
UNPAR_TABLE_17      365000
UNPAR_TABLE_MX     92000

11.删除中间临时表
drop TABLE p_t cascade constraints;