直接路径法和在线重定义分区比较

      在对大表进行分区时，有两种方式，一种是直接路径法分区，另一种是在线重定义分区，脚本示例如附录。最近对公司一张600多万的表进行分区，表上有9个索引，索引占的空间比表大。对比了下两种方式的优缺点：

a. 用直接路径转换分区
     只是分区完成耗时2分9秒，共产生5.18M日志。
     建索引耗时8分39秒，产生5.5M日志。
     总计：耗时10分48秒，产生10.68M。
     原理：直接路径插入
     优点：速度快，产生redo少
     缺点：要停服务

b. 在线重定义
     只是分区完成耗时3分15秒，共产生183M日志。

     建索引耗时8分39秒，产生5.5M日志。

     总计：耗时11分54秒，产生188.5M。
     原理：利用物化视图更新表
     优点：不用停服务，适合7*24小时
     缺点：慢一点点，会锁表，对DML语句造成等待；如果在客户端操作断开，那重定义会在后台运行，对你是不可见。

总计：两种方式都各自有优势，具体选择哪种方式要看你的业务场景。

 

附录：

create table test as select * from dba_objects;
alter table test  add constraint PK_ID primary key (object_id);
create index ind_t_object_id on test(data_object_id);
create table test_partition
(
  OWNER          VARCHAR2(30),
  OBJECT_NAME    VARCHAR2(128),
  SUBOBJECT_NAME VARCHAR2(30),
  OBJECT_ID      NUMBER,
  DATA_OBJECT_ID NUMBER,
  OBJECT_TYPE    VARCHAR2(19),
  CREATED        DATE,
  LAST_DDL_TIME  DATE,
  TIMESTAMP      VARCHAR2(19),
  STATUS         VARCHAR2(7),
  TEMPORARY      VARCHAR2(1),
  GENERATED      VARCHAR2(1),
  SECONDARY      VARCHAR2(1)
)partition by list (object_type)
(
  partition p1 values ('TABLE'),
  partition p2 values ('TABLE PARTITION'),
  partition p3 values ('INDEX'),
  partition p4 values ('INDEX PARTITION'),
  partition p5 values ('PROCEDURE'),
  partition p6 values ('FUNCTION'),
  partition other values (default)
);

 alter table test_partition  add constraint PK_PID primary key (object_id);

方式一手工分区：

alter table test_partition nologging;
insert /*+append*/ into test_partition select * from test;
commit;
alter table test_partition logging;
create index ind_tp_object_id on test_partition(data_object_id) nologging;
ALTER TABLE test RENAME TO test_BAK;
ALTER TABLE test_partition RENAME TO test;
exec dbms_stats.gather_table_stats(user,'test',cascade=>true);

 

方式二在线重定义分区：
在线重定义脚本（具体的实验请见 http://blog.csdn.net/stevendbaguo/article/details/9849311）：
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'TEST', DBMS_REDEFINITION.CONS_USE_PK);
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TEST', 'TEST_PARTITION');
EXEC dbms_redefinition.sync_interim_table(user, 'TEST','TEST_PARTITION') ;
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'TEST', 'TEST_PARTITION');

create index ind_tp_object_id on test_partition(data_object_id) nologging;
ALTER TABLE test RENAME TO test_BAK;
ALTER TABLE test_partition RENAME TO test;
exec dbms_stats.gather_table_stats(user,'test',cascade=>true);