使用dbms_redefinition包在线把大数据量非分区表转换为分区表

最新推荐文章于 2024-03-25 22:52:29 发布

cuchou5321

最新推荐文章于 2024-03-25 22:52:29 发布

阅读量158

点赞数

本实验是在线使用dbms_redefinition包把大数据量非分区表转换为分区表。它适用于
Oracle Database - Enterprise Edition - Version 11.2.0.2.0 以及以后的任何版本.

本文使用DBMS_REDEFINITION包提供了大量详细的方法把普通表在线的转换为分区表。

下面是详细的操作步骤:

1. 创建一个临时的分区表，表结构与要转换的非分区表结构相同.
2. 执行 DBMS_REDEFINITION.can_redef_table...
3.  执行 DBMS_REDEFINITION.start_redef_table...
4.  执行 DBMS_REDEFINITION.sync_interim_table...
5.  执行 DBMS_REDEFINITION.finish_redef_table...
6. 删除临时的分区表 Drop the interim table.

详细的操作步骤：

本实验在 11.2.0.3平台下做的。

-- 创建非分区表，并初始化数据.

SQL>CREATE TABLE unpar_table (
a NUMBER, y number,
name VARCHAR2(100), date_used date);
-- 添加一主键
SQL>alter table unpar_table ADD (CONSTRAINT unpar_table_pk PRIMARY KEY (a,y));

-- 加载数据 1,000,000 行

SQL>begin
        for i in 1 .. 1000
        loop
            for j in 1 .. 1000
            loop
                insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );
            end loop;
        end loop;
end;
   /
commit;

PL/SQL procedure successfully completed.
Elapsed: 00:01:56.90

Commit complete.
--收集统计信息
SQL> EXEC DBMS_STATS.gather_table_stats(user, 'unpar_table', cascade => TRUE);

SQL>SELECT   num_rows FROM user_tables WHERE table_name = 'UNPAR_TABLE';
NUM_ROWS
----------
   1000000

Elapsed: 00:00:00.01
--创建分区表
SQL> CREATE TABLE par_table (
    a NUMBER, y number,
    name VARCHAR2(100),date_used DATE)
   PARTITION BY RANGE (date_used)
    (PARTITION unpar_table_12 VALUES LESS THAN (TO_DATE('10/07/2012', 'DD/MM/YYYY')),
     PARTITION unpar_table_15 VALUES LESS THAN (TO_DATE('15/07/2012', 'DD/MM/YYYY')),
     PARTITION unpar_table_MX VALUES LESS THAN (MAXVALUE));

SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

--  这个存储过程包 (DBMS_REDEFINITION.start_redef_table) 会创建一个物化视图.

SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.69

SQL> select mview_name,container_name, build_mode from user_mviews;
MVIEW_NAME                     CONTAINER_NAME                 BUILD_MOD
------------------------------ ------------------------------ ---------
PAR_TABLE                      PAR_TABLE                      PREBUILT

Elapsed: 00:00:00.13

-- 当 DBMS_REDEF 为活动状态是，我们向非分区表'unpar_table'插入1000数据.
-- 我们会使用到 DBMS_REDEFINITION.start_redef_table创建的物化视图记录更新的信息.
-- 检查物化视图 MLOG$_

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

Elapsed: 00:00:00.24

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

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

-- 执行 dbms_redefinition.sync_interim_table基于物化视图 FAST REFRESH
-- 在线更新数据. 这将清空物化视图中被应用的每一条记录.
-- 在我们执行Finish_REDEF_TABLE存储过程包之前，我们可以执行多次.

SQL> BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.01

SQL> ALTER TABLE par_table ADD (CONSTRAINT par_table_pk2 PRIMARY KEY (a,y));

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);

-- 执行存储过程Finish_redef_table 包，将交换表名，临时分区表名将变为原非分区表名.
-- 执行完这步骤后, 原非分区表将基于临时分区表重新定义表属性和数据.
-- 在此期间原非分区表将会短暂的锁住表.

SQL>BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/

-- 注意，这个两个表将同步数据.

SQL> select count(*) from par_table ;
COUNT(*)
----------
1001000

SQL> select count(*) from unpar_table ;
COUNT(*)
----------
1001000

-- 从数据字典表中我们来确认非分区表 "UNPAR_TABLE"的结构变为了分区表.

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

PAR
---
YES

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

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
UNPAR_TABLE_12                     980000
UNPAR_TABLE_15                       5000
UNPAR_TABLE_MX                      16000

-- 此时我们完成了在线非分区表的转换，我们可以删除临时分区表了.

SQL> drop TABLE par_table cascade constraints;

注意先前的实验版本是在 11.2.0.3完成的.

下面的一个测试是在 11.1.0.7 下，执行 sync_interim_table存储包执行的时间，表的数据量为 (1,000,000 ) .

example:

11.1.0.7
SYNC of 1000 rows -- Elapsed: 00:01:36.30 (1.5 minutes)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12798004/viewspace-1571474/，如需转载，请注明出处，否则将追究法律责任。