在线重定义原理探讨

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:在线重定义原理探讨

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

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";
此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

在线重定义原理探讨》有 2 条评论

  1. 惜分飞 说:

    有这个可能性,因为重定义本质是换了表,假如从非分区表到分区表,也无法继承以前的统计信息了
    不同版本可能有不同,具体需要结合版本测试分析

    你好,请问重定义后,该表的之前统计信息会失效么?

  2. XX 说:

    你好,请问重定义后,该表的之前统计信息会失效么?