oracle12c视图刷新,Oracle 12c 新特性 --- 在重定义期间,快速刷新依赖的物化视图

概念

https://www.cndba.cn/leo1990/article/2203

https://www.cndba.cn/leo1990/article/2203

The new SYNC_INTERIM_TABLE procedure for the DBMS_REDEFINITION package enables incremental refresh of dependent materialized views on the interim table. The SYNC_INTERIM_TABLE procedure refreshes the materialized view as part of its execution.

DBMS_REDEFINITION包的新SYNC_INTERIM_TABLE程序允许在临时表上增加依赖物化视图的增量刷新。SYNC_INTERIM_TABLE过程作为其执行的一部分刷新物化视图。

All dependent materialized views on the source table must be fully refreshed after redefinition completes, increasing the time when the table is fully usable. Refreshing the materialized views during the redefinition process eliminates the time to perform a complete refresh at the end.

所有依赖于源表的物化视图必须在重新定义完成后得到完全的刷新，从而增加了该表完全可用的时间。在重新定义过程中刷新物化视图，消除了在结束时执行完全刷新的时间。

实验

https://www.cndba.cn/leo1990/article/2203

https://www.cndba.cn/leo1990/article/2203https://www.cndba.cn/leo1990/article/2203

1 创建基础数据

1.1 创建表基础表

[leo@www.cndba.cn ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 31 12:01:01 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn test/test@pdbcndba

Connected.

SQL> CREATE TABLE orders(

order_id NUMBER(12) PRIMARY KEY,

order_date TIMESTAMP WITH LOCAL TIME ZONE,

order_mode VARCHAR2(8),

customer_id NUMBER(6),

order_status NUMBER(2),

order_total NUMBER(8,2),

sales_rep_id NUMBER(6),

promotion_id NUMBER(6));

Table created.

1.2 创建重新定义表，将order_mode列的大小增加到16。临时表定义为:

SQL> CREATE TABLE int_orders(

order_id NUMBER(12),

order_date TIMESTAMP WITH LOCAL TIME ZONE,

order_mode VARCHAR2(16),

customer_id NUMBER(6),

order_status NUMBER(2),

order_total NUMBER(8,2),

sales_rep_id NUMBER(6),

promotion_id NUMBER(6)); 9

Table created.

1.3 还假设这个表有相关的具体化视图。该表有一个物化视图日志，其创建的语句如下:

SQL> CREATE MATERIALIZED VIEW LOG ON orders WITH PRIMARY KEY, ROWID;

Materialized view log created.

1.4 创建以下物化视图

SQL> CREATE MATERIALIZED VIEW orders_pk REFRESH FAST AS

SELECT * FROM orders; 2

Materialized view created.

SQL> CREATE MATERIALIZED VIEW orders_rowid REFRESH FAST WITH ROWID AS

SELECT * FROM orders; 2

Materialized view created.

oe.orders_pk物化视图是一种快速可刷新、主键物化视图。因此，可以在在线表重定义期间刷新。

oe.orders_rowid物化视图是快速可刷新的，但它是一个ROWID物化视图。因此，不能在在线表重定义期间刷新。

2 完成以下步骤，在orders表上执行在线表重定义，同时刷新orders_pk物化视图:

2.1 开始重新定义过程。

SQL> BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE(

uname => 'TEST',

orig_table => 'orders',

int_table => 'int_orders',

options_flag => DBMS_REDEFINITION.CONS_USE_PK,

refresh_dep_mviews => 'Y');

END;

/

PL/SQL procedure successfully completed.

2.2 复制依赖对象。(在int_orders上自动创建任何触发器、索引、物化视图日志、授予和约束。)

SQL>

DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(

uname => 'TEST',

orig_table => 'orders',

int_table => 'int_orders',

copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,

copy_triggers => TRUE,

copy_constraints => TRUE,

copy_privileges => TRUE,

ignore_errors => TRUE,

num_errors => num_errors);

END;

/

PL/SQL procedure successfully completed.

2.3 检查重新定义状态:

SQL> SELECT REDEFINITION_ID, REFRESH_DEP_MVIEWS

FROM DBA_REDEFINITION_STATUS

WHERE BASE_TABLE_OWNER = 'TEST' AND BASE_TABLE_NAME = 'ORDERS'; 2 3

REDEFINITION_ID R

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

1 Y

2.4 在另一个会话orders上执行DML。例如:

SQL> INSERT INTO orders VALUES(3000,sysdate,'direct',102,1,42283.2,154,NULL);

1 row created.

SQL> commit;

Commit complete.

2.5 同步临时表int_orders。这一步刷新依赖的物化视图orders_pk。

SQL> BEGIN

DBMS_REDEFINITION.SYNC_INTERIM_TABLE(

uname => 'TEST',

orig_table => 'ORDERS',

int_table => 'INT_ORDERS');

END;

/

PL/SQL procedure successfully completed.

2.6 检查订单的相关物化视图的刷新状态:

SQL> col OWNER for a10

SQL> col MVIEW_NAME for a10

SQL> col STALENESS for a20

SQL> SELECT m.OWNER, m.MVIEW_NAME, m.STALENESS, m.LAST_REFRESH_DATE

FROM ALL_MVIEWS m, ALL_MVIEW_DETAIL_RELATIONS d

WHERE m.OWNER=d.OWNER AND

m. MVIEW_NAME=d.MVIEW_NAME AND

d.DETAILOBJ_OWNER = 'TEST' AND

d.DETAILOBJ_NAME = 'ORDERS';

OWNER MVIEW_NAME STALENESS LAST_REFRESH

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

TEST ORDERS_PK FRESH 31-AUG-17

TEST ORDERS_ROWID NEEDS_COMPILE 31-AUG-17

在前一个步骤中，orders_pk物化视图被刷新，因此它对它的过时状态有了新的认识。在上一个步骤中，orders_rowid物化视图并没有刷新，因此它的跟踪状态是需要的。

2.7 完成重新定义

SQL> BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE(

uname => 'TEST',

orig_table => 'ORDERS',

int_table => 'INT_ORDERS');

END;

/

PL/SQL procedure successfully completed.

2.8 查询orders_pk物化视图，以确认插入到orders表中的新行存在于物化视图中，因为它在在线表重定义期间刷新。

SQL> set line 200

SQL> select * from orders_pk ;

ORDER_ID ORDER_DATE ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID

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

3000 31-AUG-17 12.13.36.000000 PM direct102 1 42283.2 154

SQL> select * from INT_ORDERS;

ORDER_ID ORDER_DATE ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID

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

3000 31-AUG-17 12.13.36.000000 PM direct102 1 42283.2 154

https://www.cndba.cn/leo1990/article/2203

https://www.cndba.cn/leo1990/article/2203https://www.cndba.cn/leo1990/article/2203

版权声明：本文为博主原创文章，未经博主允许不得转载。