在线重定义原理探讨

1、准备测试表

SQL> create table t_d as select * from all_objects;     Table created     SQL> alter table t_d add primary key (object_id);     Table altered     SQL> create table t_d_t as select * from t_d where 1=0;     Table created     SQL> alter table t_d_t add primary key (object_id);     Table altered    SQL> select count (*) from T_D;        COUNT (*) ----------       48945     SQL> select count (*) from T_D_T;        COUNT (*) ----------       0

2、执行在线同步

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE( user , 'T_D' , DBMS_REDEFINITION.CONS_USE_PK);     PL/SQL procedure successfully completed     SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE( USER , 'T_D' , 'T_D_T' );     PL/SQL procedure successfully completed

3、查询相关物化视图

SQL> select owner,mview_name from user_mviews;     OWNER                          MVIEW_NAME ------------------------------ ------------------------------ CHF                            T_D_T    SQL> select log_owner,master,log_table from user_mview_logs;     LOG_OWNER                      MASTER                         LOG_TABLE ------------------------------ ------------------------------ ------------------------------ CHF                            T_D                            MLOG$_T_D       CREATE MATERIALIZED VIEW T_D_T ON PREBUILT TABLE REFRESH FAST ON DEMAND AS SELECT "T_D" . "OWNER" "OWNER" , "T_D" . "OBJECT_NAME" "OBJECT_NAME" , "T_D" . "SUBOBJECT_NAME" "SUBOBJECT_NAME" , "T_D" . "OBJECT_ID" "OBJECT_ID" , "T_D" . "DATA_OBJECT_ID" "DATA_OBJECT_ID" , "T_D" . "OBJECT_TYPE" "OBJECT_TYPE" , "T_D" . "CREATED" "CREATED" , "T_D" . "LAST_DDL_TIME" "LAST_DDL_TIME" , "T_D" . "TIMESTAMP" "TIMESTAMP" , "T_D" . "STATUS" "STATUS" , "T_D" . "TEMPORARY" "TEMPORARY" , "T_D" . "GENERATED" "GENERATED" , "T_D" . "SECONDARY" "SECONDARY" FROM "CHF" . "T_D" "T_D" ;

4、结束物化视图

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE( USER , 'T_D' , 'T_D_T' );     PL/SQL procedure successfully completed

5、继续查询相关物化视图

SQL>  select log_owner,master,log_table from user_mview_logs;         SQL> select * from user_mviews;     SQL> select owner,mview_name from user_mviews;     OWNER                          MVIEW_NAME ------------------------------ ------------------------------    SQL> select log_owner,master,log_table from user_mview_logs;     LOG_OWNER                      MASTER                         LOG_TABLE ------------------------------ ------------------------------ ------------------------    SQL> select count (*) from T_D_T;        COUNT (*) ----------       48945

6、由试验可以得出
6.1)在线重定义本质就是利用物化视图刷新实现数据迁移
6.2)DBMS_REDEFINITION.START_REDEF_TABLE实现功能：
6.2.1)创建含on prebuilt table的物化视图和物化视图日志
6.2.2)实现通过物化视图刷新当前表中数据进入中间表
6.3)dbms_redefinition.sync_interim_table实现物化视图刷新在执行5.2）操作过程中变化数据
6.4)DBMS_REDEFINITION.FINISH_REDEF_TABLE将锁定原表，防止表上的DML,物化视图执行刷新，完成刷新后，将删除物化视图和对应的日志，将中间表rename成目标表

7、如果表无主键时，区别有
7.1）执行在线定义语句，

7.2）创建物化视图语句上

create materialized view T_D_T on prebuilt table refresh fast on demand with rowid as select OWNER OWNER,  OBJECT_NAME OBJECT_NAME,   SUBOBJECT_NAME SUBOBJECT_NAME,    OBJECT_ID OBJECT_ID,    DATA_OBJECT_ID DATA_OBJECT_ID,    OBJECT_TYPE OBJECT_TYPE,    CREATED CREATED,    LAST_DDL_TIME LAST_DDL_TIME,    TIMESTAMP TIMESTAMP ,    STATUS STATUS,    TEMPORARY TEMPORARY ,    GENERATED GENERATED,    SECONDARY SECONDARY    from "CHF" . "T_D"   "T_D" ;