堆表转换成分区表

最新推荐文章于 2022-06-19 19:55:24 发布

ckth47350

最新推荐文章于 2022-06-19 19:55:24 发布

阅读量231

点赞数

文章标签：数据库数据结构与算法大数据

堆表转换成分区表

方法一：利用原表重建分区表

优点：

方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。

不足：

对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

适用场景：

适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。

1 创建堆表，插入数据

SQL> create table t(id number primary key,time date);

Table created.

SQL> insert into t select rownum,created from dba_objects;

72632 rows created.

SQL> commit;

Commit complete.

2 创建分区表并加载T表数据

SQL> create table t_new(id,time) partition by range(time)

(partition p1 values less than(to_date('2009-07-01','yyyy-mm-dd')),

partition p2 values less than(to_date('2010-07-01','yyyy-mm-dd')),

partition p3 values less than(maxvalue))

as select id,time from t;

Table created.

SQL> select count(*) from t_new partition(p1);

COUNT(*)

----------

SQL> select count(*) from t_new partition(p2);

COUNT(*)

----------

71812

SQL> select count(*) from t_new partition(p3);

COUNT(*)

----------

820

3 重命名表

SQL> rename t to t_old;

Table renamed.

SQL> rename t_new to t;

Table renamed.

SQL> select count(*) from t;

COUNT(*)

----------

72632

SQL> select count(*) from t partition(p2);

COUNT(*)

----------

71812

===============================================================================

方法二：使用交换分区的方法

优点：

只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。

不足：

仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。

适用场景：

适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。

1 创建堆表，插入数据

SQL> create table t(id number primary key,time date);

Table created.

SQL> insert into t select rownum,created from dba_objects;

72632 rows created.

2 创建分区表

SQL> create table t_new(id number primary key,time date) partition by range(time)

(partition p1 values less than(to_date('2009-07-01','yyyy-mm-dd')),

partition p2 values less than(to_date('2010-07-01','yyyy-mm-dd')),

partition p3 values less than(maxvalue));

Table created.

3 交换分区

SQL> alter table t_new exchange partition p1 with table t;

alter table t_new exchange partition p1 with table t

ERROR at line 1:

ORA-14099: all rows in table do not qualify for specified partition

原因：数据中含有大于'2009-07-01'的数据,但是分区只能存放小区'2011-10-01'的数据,所以报错

SQL> select min(time),max(time) from t;

MIN(TIME) MAX(TIME)

------------------- -------------------

2009-08-15 00:16:51 2015-07-15 13:07:34

解决办法：without validation(尽量不使用，会是分区数据不准确)

SQL> alter table t_new exchange partition p1 with table t without validation;

Table altered.

4 重命名

SQL> rename t to t_old;

Table renamed.

SQL> rename t_new to t;

Table renamed.

SQL> select count(*) from t partition(p1);

COUNT(*)

----------

72632

SQL> select count(*) from t partition(p2);

COUNT(*)

----------

SQL> select count(*) from t partition(p3);

COUNT(*)

----------

===============================================================================

三 利用在线重定义功能(Oracle9i以上版本)

优点：

保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。

不足：

实现上比上面两种略显复杂,适用于各种情况。

Oracle9i重定义过程的部分限制：

1 必须有足以维护两份表格拷贝的空间。　　

2 不能更改主键栏。　　

3 表格必须有主键。　　

4 必须在同一个大纲中进行表格重定义。　　

5 在重定义操作完成之前，你不能对新加栏加以NOT NULL约束。

6 表格不能包含LONG、BFILE以及用户类型（UDT）。　　

7 不能重定义链表（clustered tables）。

8 不能在SYS和SYSTEM大纲中重定义表格。　　

9 不能用具体化视图日志（materialized view logs）来重定义表格；不能重定义含有具体化视图的表格。

10不能在重定义过程中进行横向分集（horizontal subsetting）。

1 创建堆表，插入数据

SQL> create table t(id number primary key,time date);

Table created.

SQL> insert into t select rownum,created from dba_objects;

72632 rows created.

SQL> commit;

Commit complete.

2 不能重定义SYS，SYSTEM用户下的表

SQL> exec dbms_redefinition.can_redef_table('SYS','T',dbms_redefinition.cons_use_pk);

BEGIN dbms_redefinition.can_redef_table('SYS','T',dbms_redefinition.cons_use_pk); END;

ERROR at line 1:

ORA-12087: online redefinition not allowed on tables owned by "SYS"

ORA-06512: at "SYS.DBMS_REDEFINITION", line 139

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1782

ORA-06512: at line 1

SQL> conn chen/chen

SQL> create table t(id number primary key,time date);

SQL> insert into t select rownum,created from dba_objects;

72631 rows created.

SQL> commit;

Commit complete.

3判断目标数据表是否可以进行重定义

可以使用dbms_redefinition包的can_redef_table方法进行判断。

SQL> exec dbms_redefinition.can_redef_table('CHEN','T',dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

注意该方法的第三个参数，使用主键还是rowid方法。本质上，Online Redefinition是使用物化视图Materialized View技术。过程定义记录就是主键和rowid两种策略。通常而言，我们还是推荐数据表有一个明确主键，也就是使用cons_use_pk。如果希望使用rowid，就使用dbms_redefinition.cons_use_rowid。

通过了检查之后，就可以进行下一步，定义目标数据表格式。无论是何种变化，我们需要创建一个中间表interim，将我们“期望”的数据表定义实现在里面。其中包括表类型、列定义、分区定义和索引等。但是注意，约束（主外键）可以不定义在其中。

4 创建分区表

SQL> create table t_new(id number primary key,time date) partition by range(time)

(partition p1 values less than(to_date('2009-07-01','yyyy-mm-dd')),

partition p2 values less than(to_date('2010-07-01','yyyy-mm-dd')),

partition p3 values less than(maxvalue));

Table created.

5 开始重定义过程

使用dbms_redefinition的start_redef_table方法。

SQL> exec dbms_redefinition.start_redef_table('CHEN','T','T_NEW')

PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.sync_interim_table('CHEN','T','T_NEW')