ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 2.普通表转分区表方法

2.普通表转分区表方法

原创 Linux操作系统 作者:lwitpub 时间:2011-05-30 14:45:57 0 删除 编辑

将普通表转换成有4种方法:

       1. Export/import method

       2. Insert with a subquery method

       3. Partition exchange method

       4. DBMS_REDEFINITION

具体参考:

       How to Partition a Non-partitioned Table [ID 1070693.6]

       http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218704.aspx

逻辑导出导入这里就不做说明,我们看看其他三种方法。

 

2.1  插入: Insert with a subquery method

     这种方法就是使用insert 来实现。 当然在创建分区表的时候可以一起插入数据,也可以创建好后在insert 进去。 这种方法采用DDL语句,不产生UNDO,只产生少量REDO,建表完成后数据已经在分布到各个分区中。

    

SQL> select count(*) from dba;

  COUNT(*)

----------

   2713235

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

会话已更改。

 

SQL> select time_fee from dba where rownum<5;

TIME_FEE

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

2011-02-17 19:29:09

2011-02-17 19:29:15

2011-02-17 19:29:18

2011-02-17 19:29:20

SQL>

 

2.1.1   Interval

       11g里的Interval创建,这种方法对没有写全的分区会自动创建。 比如我这里只写了1月日期,如果插入的数据有其他月份的,会自动生成对应的分区。

/* Formatted on 2011/03/02 15:41:09 (QP5 v5.115.810.9015) */

CREATE TABLE intervaldave

PARTITION BY RANGE (time_fee)

   INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )

   (PARTITION part1

       VALUES LESS THAN (TO_DATE ('01/12/2010', 'MM/DD/YYYY')))

AS SELECT   ID, TIME_FEE FROM DAVE;

SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALDAVE';

 

TABLE_NAME                     PARTITION_NAME

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

INTERVALDAVE                   PART1

INTERVALDAVE                   SYS_P24

INTERVALDAVE                   SYS_P25

INTERVALDAVE                   SYS_P26

INTERVALDAVE                   SYS_P33

INTERVALDAVE                   SYS_P27

INTERVALDAVE                   SYS_P28

 

2.1.2  Oracle 10g 版本

       里面,我需要写全所有的分区。

 

sql> create table pdba (id, time) partition by range (time)

  2 (partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

  3    partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

  4    partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

  5    partition p4 values less than (maxvalue))

  6    as select id, time_fee from dba;

表已创建。

SQL> select table_name,partition_name from user_tab_partitions where table_name='PDBA';

TABLE_NAME                     PARTITION_NAME

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

PDBA                           P1

PDBA                           P2

PDBA                           P3

PDBA                           P4

sql> select count(*) from pdba partition (p1);

  count(*)

----------

   1718285

sql> select count(*) from pdba partition (p2);

  count(*)

----------

    183667

sql> select count(*) from pdba partition (p3);

  count(*)

----------

    188701

sql> select count(*) from pdba partition (p4);

  count(*)

----------

    622582

sql>

 

现在分区表已经建好了,但是表名不一样,需要用rename对表重命名一下:

SQL> rename dba to dba_old;

表已重命名。

SQL> rename pdba to dba;

表已重命名。

SQL> select table_name,partition_name from user_tab_partitions where table_name='DBA';

TABLE_NAME                     PARTITION_NAME

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

DBA                            P1

DBA                            P2

DBA                            P3

DBA                            P4

 

2.2 . 交换分区:Partition exchange method

     这种方法只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。适用于包含大数据量的表转到分区表中的一个分区的操作。尽量在闲时进行操作。

 

交换分区的操作步骤如下:

     1. 创建分区表,假设有2个分区,P1P2.

     2. 创建表A存放P1规则的数据。

     3. 创建表存放P2规则的数据。

     4. 用表P1 分区交换。 把表A的数据放到到P1分区

     5. 用表p2 分区交换。 把表B的数据存放到P2分区。

 

创建分区表:

sql> create table p_dba

  2  (id number,time date)

  3  partition by range(time)

  4  (

  5  partition p1 values less than (to_date('2010-09-1', 'yyyy-mm-dd')),

  6  partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd'))

  7  );

表已创建。

 

注意:我这里只创建了2个分区,没有创建存放其他数据的分区。

 

创建2个分别对应分区的基表:

SQL> CREATE TABLE dba_p1 as SELECT id,time_fee FROM dba_old WHERE  time_fee

表已创建。

 

SQL> CREATE TABLE dba_p2 as SELECT id,time_fee FROM dba_old WHERE  time_feeTO_DATE('2010-09-1', 'YYYY-MM-DD');

表已创建。

 

SQL> select count(*) from dba_p1;

  COUNT(*)

----------

   1536020

SQL> select count(*) from dba_p2;

  COUNT(*)

----------

    365932

 

SQL>

 

2个基表与2个分区进行交换:

SQL> alter table p_dba exchange partition p1 with table dba_p1;

表已更改。

SQL> alter table p_dba exchange partition p2 with table dba_p2;

表已更改。

 

查询2个分区:

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

  COUNT(*)

----------

   1536020

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

  COUNT(*)

----------

    365932

注意:数据和之前的基表一致。

 

查询原来的2个基表:

SQL> select count(*) from dba_p2;

  COUNT(*)

----------

         0

SQL> select count(*) from dba_p1;

  COUNT(*)

----------

         0

注意: 2个基表的数据变成成0

    在这里我们看一个问题,一般情况下,我们在创建分区表的时候,都会有一个其他分区,用来存放不匹配分区规则的数据。 在这个例子中,我只创建了2个分区,没有创建maxvalue分区。 现在我来插入一条不满足规则的数据,看结果:

 

SQL> insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd'));

insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd'))

            *

 1 行出现错误:

ORA-14400: 插入的分区关键字未映射到任何分区

SQL> insert into p_dba values(999999,to_date('2009-12-29','yyyy-mm-dd'));

已创建 1 行。

SQL> select * from p_dba where id=999999;

 

        ID TIME

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

    999999 29-12-09

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

会话已更改。

SQL> select * from p_dba where id=999999;

 

        ID TIME

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

    999999 2009-12-29 00:00:00

SQL>

        通过这个测试可以清楚,如果插入的数据不满足分区规则,会报ORA-14400错误。

 

2.3 . 使用在线重定义:DBMS_REDEFINITION

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

 

关于DBMS_REDEFINITION的介绍,参考官方连接:

      http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBFDJBC

 

关于用在线重定义创建分区表,参考:

       How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]

       http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218693.aspx

 

这个功能只在9.2.0.4以后的版本才有,在线重定义表具有以下功能:

       1)修改表的存储参数;

       2)将表转移到其他表空间;

       3)增加并行查询选项;

       4)增加或删除分区;

       5)重建表以减少碎片;

       6)将堆表改为索引组织表或相反的操作;

       7)增加或删除一个列。

 

使用在线重定义的一些限制条件:

1 There must be enough space to hold two copies of the table.

2 Primary key columns cannot be modified.

3 Tables must have primary keys.

4 Redefinition must be done within the same schema.

5 New columns added cannot be made NOT NULL until after the redefinition operation.

6 Tables cannot contain LONGs, BFILEs or User Defined Types.

7 Clustered tables cannot be redefined.

8 Tables in the SYS or SYSTEM schema cannot be redefined.

9 Tables with materialized view logs or materialized views defined on them cannot be redefined.

10 Horizontal sub setting of cannot be performed during the redefinition.

 

Oracle 10.2.0.411.1.0.7 版本下,在线重定义可能会遇到如下bug

       Bug 7007594 - ORA-600 [12261]

       http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx

 在线重定义的大致操作流程如下:

       1)创建基础表A,如果存在,就不需要操作。

       2)创建临时的分区表B

       3)开始重定义,将基表A的数据导入临时分区表B

       4)结束重定义,此时在DB Name Directory里,已经将2个表进行了交换。即此时基表A成了分区表,我们创建的临时分区表成了普通表。 此时我们可以删除我们创建的临时表B。它已经是普通表。

 

下面看一个示例:

1. 创建基本表和索引

sql> conn icd/icd;

已连接。

sql> create table unpar_table (

  2  id number(10) primary key,

  3  create_date date

  4  );

表已创建。

sql> insert into unpar_table select rownum, created from dba_objects;

已创建72288行。

sql> create index create_date_ind on unpar_table(create_date);

索引已创建。

sql> commit;

提交完成。

 

2. 收集表的统计信息

sql> exec dbms_stats.gather_table_stats('icd', 'unpar_table', cascade => true);

pl/sql 过程已成功完成。

 

3. 创建临时分区表

sql> create table  par_table (id number primary key, time date) partition by range (time)

  2  (partition p1 values less than (to_date('2004-7-1', 'yyyy-mm-dd')),

  3  partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),

  4  partition p3 values less than (to_date('2005-7-1', 'yyyy-mm-dd')),

  5  partition p4 values less than (maxvalue));

表已创建。

 

4. 进行重定义操作

 

4.1 检查重定义的合理性

sql> exec dbms_redefinition.can_redef_table('icd', 'unpar_table');

pl/sql 过程已成功完成。

 

4.2 如果4.1 没有问题,开始重定义,这个过程可能要等一会。

 

这里要注意:如果分区表和原表列名相同,可以用如下方式进行:

SQL> BEGIN

DBMS_REDEFINITION.start_redef_table(

uname => 'ICD', 

orig_table => 'unpar_table',

int_table => 'par_table');

END;

/

       如果分区表的列名和原表不一致,那么在开始重定义的时候,需要重新指定映射关系:

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(

'ICD',

'unpar_table',

'par_table',

'ID ID, create_date TIME', -- 在这里指定新的映射关系

DBMS_REDEFINITION.CONS_USE_PK);

 

这一步操作结束后,数据就已经同步到这个临时的分区表里来了。

 

4.3 同步新表,这是可选的操作

SQL> BEGIN

  2  dbms_redefinition.sync_interim_table(

  3  uname => 'ICD',

  4  orig_table => 'unpar_table',

  5  int_table => 'par_table');

  6  END;

  7  /

PL/SQL 过程已成功完成。

 

4.4 创建索引,在线重定义只重定义数据,索引还需要单独建立。

sql> create index create_date_ind2 on par_table(time);

索引已创建。

 

4.5 收集新表的统计信息

sql> exec dbms_stats.gather_table_stats('icd', 'par_table', cascade => true);

pl/sql 过程已成功完成。

 

4.6 结束重定义

SQL> BEGIN

  2  dbms_redefinition.finish_redef_table(

  3  uname => 'ICD',

  4  orig_table => 'unpar_table',

  5  int_table => 'par_table');

  6  END;

  7  /

PL/SQL 过程已成功完成。

 

结束重定义的意义:

       基表unpar_table 和临时分区表par_table 进行了交换。 此时临时分区表par_table成了普通表,我们的基表unpar_table成了分区表。

        我们在重定义的时候,基表unpar_table是可以进行DML操作的。 只有在2个表进行切换的时候会有短暂的锁表。

 

5. 删除临时表

SQL> DROP TABLE par_table;

表已删除。

 

6. 索引重命名

SQL> ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

索引已更改。

 

7. 验证

sql> select partitioned from user_tables where table_name = 'UNPAR_TABLE';

par

---

yes

sql> select partition_name from user_tab_partitions where table_name = 'UNPAR_TABLE';

partition_name

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

p1

p2

p3

p4

sql> select count(*) from unpar_table;

  count(*)

----------

     72288

sql> select count(*) from unpar_table partition (p4);

  count(*)

----------

     72288

这个转换方法,还有http://space.itpub.net/4227/viewspace-68553

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

请登录后发表评论 登录
全部评论

注册时间:2009-05-08

  • 博文量
    107
  • 访问量
    397932