ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 普通表转化为分区表

普通表转化为分区表

原创 Linux操作系统 作者:wangxiangtao 时间:2011-09-20 15:06:36 0 删除 编辑

OLTP系统中,由于时间的推移,部分表的数据会不断增大,当数据量到达一定程度后,相关对象的DML语句效率会比较低,因此在前期设计时, 对于海量数据的表一定要确认数据的生命周期,在项目前期就做好分区表的设计, 这样的优点有以下几点:

(1)     方便历史数据的提取,利用分区表的exchange partition的功能可以将历史数据安分区分离。

(2)     7*24 的业务系统中,可以防止后期进行普通表 到分区表的转换,一方面增加维护工作量, 一方面或多或少影响业务。

(3)     利用分区的 add partition(hash partition)split partition(list and range partition) 可以高效的进行数据管理。

但是在前期的设计中,由于预估的错误,可能会导致部分表需要在后期进行普通表到分区表的转换,下面我总结一下普通表到分区的方法(仅供参考)

(1)     采用CTAS 的方式:

实现方法:创建新的分区表, 将原表的数据insert  into 到分区表中。 然后 rename

缺点:此方法对于更新比较频繁的对象无法数据的同步。速度比较慢, 对业务影响大

(2)     采用expdp/impdp

缺点:如果要实现数据的同步,需要进行停业务的操作, 速度较方法(1)块, 对业务影响比较大

(3)     采用 Materialized  view

实现方法:创建一个基于日志快速刷新, 手动刷新的物化视图(容器表为分区表, 相关索引已经创建),然后不断的快速刷新materialized view 直到时间在可控范围内,停业务, 刷新materialized view rename表名。

优点: 操作较上复杂,将业务影响时间降到最低,切换时间在瞬间可以完成

(4)     采用oracle  dbms_redefinition(在线重定义)

实现方法:

A.调用dbms_redefinition.can_redef_table() 检查是否满足重定义条件

B.创建中间表, 中间表的结构即为你期望的结构,在中间表中创建索引,约束等

C.调用dbms_redefinition.start_redef_table();

D. 调用dbms_redefinition.sync_interim_table();减少dbms_redefinition.finish_redef_table()锁定 时间

E 执行 dbms_redefinition.finish_redef_table() 完成 表的重定义功能

优点: 无需停业务, 操作相对比较复杂

下面对(3) (4) 两种方法分别做小实验:

(一)  Materialized view 进行转换

SQL> show user;

     USER is "P_TDZX"

SQL> create  table  t_exchange  as select rownum as  id,created from  dba_objects;

 

Table created.

 

SQL> select count(*) from  t_exchange;

 

  COUNT(*)

----------

     58392

     SQL> alter  table t_exchange add constraint pk_exchange primary key (id) using       index tablespace tdzx1_index;

 

Table altered.

 

SQL> create  index  i_exchange_created on  t_exchange(created);

 

Index created.

SQL> select  index_name, table_name,STATUS from  user_indexes where  table_name ='T_EXCHANGE' ;

 

INDEX_NAME                     TABLE_NAME                     STATUS

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

PK_EXCHANGE                    T_EXCHANGE                     VALID

I_EXCHANGE_CREATED             T_EXCHANGE                     VALID

SQL> create table exchange_mv (id number ,created date)

partition  by  range (created)

(partition p1 values less than(to_date('2011-5-1','YYYY-MM-DD')) tablespace tdzx1,

partition p2 values less than(to_date('2011-6-1','YYYY-MM-DD'))tablespace tdzx1 ,

partition p3 values less than(to_date('2011-7-1','YYYY-MM-DD'))tablespace tdzx1,

partition p4 values less than(to_date('2011-8-1','YYYY-MM-DD'))tablespace tdzx1,

partition p5 values less than(to_date('2011-9-1','YYYY-MM-DD'))tablespace tdzx1,

partition p6 values less than(maxvalue) tablespace tdzx1

);

 

Table created.

SQL> create  materialized view exchange_mv

on  prebuilt table

using no index

refresh force on demand

as  select id, created from  t_exchange;

 

Materialized view created.

SQL> select  REFRESH_METHOD,REFRESH_MODE,STALENESS,LAST_REFRESH_TYPE,

  2  LAST_REFRESH_DATE  from  user_mviews;

 

REFRESH_ REFRES STALENESS           LAST_REF LAST_REFR

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

FORCE    DEMAND UNUSABLE            NA

创建materialized view  日志, 以及物化视图的相关索引。

SQL> create materialized view log on t_exchange with rowid,sequence(id,created) including new values;

 

Materialized view log created.

SQL> alter  table exchange_mv add constraint pk_exchange_mv primary key (id) using       index tablespace tdzx1_index;

 

Table altered.

 

SQL> create index i_exchange_mv on exchange_mv(created);

 

Index created.

SQL> select index_name, table_name, status, GLOBAL_STATS  from  user_indexes where  table_name='EXCHANGE_MV';

 

INDEX_NAME                     TABLE_NAME                     STATUS   GLO

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

PK_EXCHANGE_MV                 EXCHANGE_MV                    VALID    NO

I_EXCHANGE_MV                  EXCHANGE_MV                    VALID    NO

 

执行手工刷新

SQL> select count(*)  from  exchange_mv;

 

  COUNT(*)

----------

         0

SQL> execute dbms_mview.refresh(list => 'exchange_mv');

 

PL/SQL procedure successfully completed.

 

SQL> select count(*)  from  exchange_mv;

 

  COUNT(*)

----------

     58392

 

SQL> insert into t_exchange(id, created) select rownum+58392 as id, created from  dba_objects;

 

58405 rows created.

继续刷新 视图……

进行重命名转换:

SQL> drop materialized view log on t_exchange;

 

Materialized view log dropped.

SQL> alter table t_exchange rename to t_exchange_old;

 

Table altered.

SQL> drop  materialized view exchange_mv;

 

Materialized view dropped

SQL> alter  table exchange_mv  rename to t_exchange;

 

Table altered.

SQL> select index_name, table_name, status, GLOBAL_STATS  from  user_indexes where  table_name='T_EXCHANGE';

 

INDEX_NAME                     TABLE_NAME                     STATUS   GLO

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

PK_EXCHANGE_MV                 T_EXCHANGE                     VALID    NO

I_EXCHANGE_MV                  T_EXCHANGE                     VALID    NO

整个过程结束 转换完成。可以看到整个停机过程即为rename 的过程, 可以在先前准备好脚本, 运行即可, 对于失效对象, utlrp.sql 即可。

(二)  采用dbms_redefinition实现转换, 以下也用一个实验演示整个过程

 

1.       准备环境, 创建非分区表,并且在分分区表上创建约束,索引,trigger 等对象

SQL> show user;

USER is "P_TDZX"

SQL> create  table  t_unpar  as select rownum as  id,created,object_name from dba_objects;

 

Table created.

 

SQL> select count(*) from  t_unpar;

 

  COUNT(*)

----------

     58368

 

SQL> alter table t_unpar add  constraint pk_unpar primary key(id)

  2  using index tablespace tdzx1_index;

 

Table altered.

 

SQL> create index i_unpar_created on  t_unpar(created);

 

Index created.

 

SQL> create index  i_unpar_name on t_unpar(object_name);

 

Index created.

SQL> create or replace trigger tri_created

before update of created on  t_unpar for each row

  2    3  begin

  4  :NEW.created := sysdate;

  5  end tri_created;

  6  /

 

Trigger created.

 

SQL> conn / as sysdba

Connected.

SQL> exec dbms_stats.gather_table_stats('P_TDZX','t_unpar',cascade => true);

 

PL/SQL procedure successfully completed.

SQL> show user;

USER is "P_TDZX"

SQL> create table t_par (id number ,created date,object_name varchar2(128))

partition  by  range (created)

  2    3  (partition p1 values less than(to_date('2011-5-1','YYYY-MM-DD')) tablespace tdzx1,

  4  partition p2 values less than(to_date('2011-6-1','YYYY-MM-DD'))tablespace tdzx1 ,

  5  partition p3 values less than(to_date('2011-7-1','YYYY-MM-DD'))tablespace tdzx1,

  6  partition p4 values less than(to_date('2011-8-1','YYYY-MM-DD'))tablespace tdzx1,

  7  partition p5 values less than(to_date('2011-9-1','YYYY-MM-DD'))tablespace tdzx1,

  8  partition p6 values less than(maxvalue) tablespace tdzx1

  9  );

 

Table created.

SQL> conn  / as sysdba

Connected.

SQL> grant execute_catalog_role to  p_tdzx;

 

Grant succeeded.

SQL> grant create any table to p_tdzx;

 

Grant succeeded.

 

SQL> grant alter any table to p_tdzx;

 

Grant succeeded.

 

SQL> grant drop any table to  p_tdzx;

 

Grant succeeded.

 

SQL> grant lock any  table to  p_tdzx;

 

Grant succeeded.

 

SQL> grant select any table to  p_tdzx;

 

Grant succeeded.

SQL> GRANT execute ON dbms_redefinition TO p_tdzx;

 

Grant succeeded.

2.        开始进行redefinition

SQL> exec dbms_redefinition.can_redef_table('P_TDZX','t_unpar');

 

PL/SQL procedure successfully completed.

 

SQL> BEGIN

DBMS_REDEFINITION.start_redef_table(

  2    3  uname => 'P_TDZX',

  4  orig_table => 't_unpar',

  5  int_table => 't_par');

  6  END;

  7  /

 

PL/SQL procedure successfully completed.

 

SQL> select count(*) from  t_par;

 

  COUNT(*)

----------

     58368

----模拟数据增量:

 

SQL> select count(*) from  t_par;

 

  COUNT(*)

----------

     58368

 

SQL> insert into t_unpar(id, created,object_name) select rownum+58368 as id, created,object_name from  dba_objects;

 

58395 rows created.

 

SQL> commit;

 

Commit complete.

SQL> BEGIN

  2  dbms_redefinition.sync_interim_table(

  3  uname => 'P_TDZX',

  4  orig_table => 't_unpar',

  5  int_table => 't_par');

  6  END;

  7  /

 

PL/SQL procedure successfully completed.

 

SQL> select  count(*) from  t_par;

 

  COUNT(*)

----------

116763

在中间表 t_par 中创建相关约束,索引 以及trigger

SQL>  alter table t_par add  constraint pk_par primary key(id)

 using index tablespace tdzx1_index;

  2

Table altered.

 

SQL>

SQL> create index i_par_created on  t_par(created);

 

Index created.

 

SQL> create index  i_par_name on t_par(object_name);

 

Index created.

 

SQL> create or replace trigger tri_created1

before update of created on  t_par for each row

  2    3  begin

  4  :NEW.created := sysdate;

  5  end tri_created1;

  6  /

 

Trigger created.

SQL> EXEC DBMS_STATS.gather_table_stats('P_TDZX', 't_par', cascade => TRUE);

 

 

PL/SQL procedure successfully completed.

 

SQL> BEGIN

  2  dbms_redefinition.finish_redef_table(

  3  uname => 'P_TDZX',

  4  orig_table => 't_unpar',

  5  int_table => 't_par');

  6  END;

  7  /

 

PL/SQL procedure successfully completed.

整个转化过程结束, 首先看看相关索引,约束 分区 的情况:

SQL> select TABLE_NAME,PARTITIONING_TYPE,STATUS from  user_part_tables where table_name in ('T_PAR','T_UNPAR');

 

TABLE_NAME                     PARTITI STATUS

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

T_UNPAR                        RANGE   VALID

 

SQL> select TABLE_NAME,PARTITION_NAME from user_tab_partitions where table_name in ('T_PAR','T_UNPAR');

 

TABLE_NAME                     PARTITION_NAME

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

T_UNPAR                        P3

T_UNPAR                        P4

T_UNPAR                        P5

T_UNPAR                        P6

T_UNPAR                        P1

T_UNPAR                        P2

 

6 rows selected.

可以看到 t_unpar 已经转化为 分区表, 分区结构即为中间表t_par的结构。

 

SQL> select  index_name, table_name,STATUS from  user_indexes where  table_name in ('T_PAR','T_UNPAR') ;

 

INDEX_NAME                     TABLE_NAME                     STATUS

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

PK_UNPAR                       T_PAR                          VALID

I_UNPAR_CREATED                T_PAR                          VALID

I_UNPAR_NAME                   T_PAR                          VALID

PK_PAR                         T_UNPAR                        VALID

I_PAR_CREATED                  T_UNPAR                        VALID

I_PAR_NAME                     T_UNPAR                        VALID

 

6 rows selected.

----分区表的索引情况

SQL> select constraint_name, constraint_type,table_name,status from user_constraints where  table_name in ('T_PAR','T_UNPAR');

 

CONSTRAINT_NAME                C TABLE_NAME                     STATUS

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

PK_UNPAR                       P T_PAR                          ENABLED

PK_PAR                         P T_UNPAR                        ENABLED

-----以上为两个表的constraint 情况。

SQL> select TRIGGER_NAME,TRIGGER_TYPE,STATUS from  user_triggers where table_name in ('T_PAR','T_UNPAR');

 

TRIGGER_NAME                   TRIGGER_TYPE     STATUS

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

TRI_CREATED                    BEFORE EACH ROW  ENABLED

TRI_CREATED1                   BEFORE EACH ROW  ENABLED

---以上为两个表的触发器情况。

3.       后续的清理工作

 

SQL> drop table t_par;

 

Table dropped.

 

SQL> select TRIGGER_NAME,TRIGGER_TYPE,STATUS from  user_triggers where table_name in ('T_PAR','T_UNPAR');

 

TRIGGER_NAME                   TRIGGER_TYPE     STATUS

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

TRI_CREATED1                   BEFORE EACH ROW  ENABLED

 

SQL> select constraint_name, constraint_type,table_name,status from user_constraints where  table_name in ('T_PAR','T_UNPAR');

 

CONSTRAINT_NAME                C TABLE_NAME                     STATUS

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

PK_PAR                         P T_UNPAR                        ENABLED

 

SQL> select  index_name, table_name,STATUS from  user_indexes where  table_name in ('T_PAR','T_UNPAR') ;

 

INDEX_NAME                     TABLE_NAME                     STATUS

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

PK_PAR                         T_UNPAR                        VALID

I_PAR_CREATED                  T_UNPAR                        VALID

I_PAR_NAME                     T_UNPAR                        VALID

可以看到 以前建立在中间表的索引,约束 trigger 全部转移到了内部表上, 如有特许要求 可以作rename 处理。如果是生产库 一定要做好回收权限的处理。

SQL> conn / as sysdba

Connected.

SQL> revoke execute_catalog_role from p_tdzx;

 

Revoke succeeded.

 

SQL> revoke create any  table from  p_tdzx;

 

Revoke succeeded.

SQL> revoke alter any  table from  p_tdzx;

 

Revoke succeeded.

 

SQL> revoke  drop any table from p_tdzx;

 

Revoke succeeded.

 

SQL> revoke  lock any table from p_tdzx;

 

Revoke succeeded.

 

SQL> revoke  select any table from p_tdzx;

 

Revoke succeeded.

 

SQL> revoke  execute on dbms_redefinition from p_tdzx;

 

Revoke succeeded.

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

下一篇: nagios 搭建
请登录后发表评论 登录
全部评论

注册时间:2010-08-18

  • 博文量
    26
  • 访问量
    97618