普通表转化为分区表
在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博客 ” ，链接：https://blog.itpub.net/8117479/viewspace-707947/ ，如需转载，请注明出处，否则将追究法律责任。
