创建Writable Materialized View在DB之间增量同步数据

数据库之间可以通过DBLINK+Materialized View实现数据同步; 若需要对物化视图做UPDATE更新操作,则需要建立Writable Materialized View,在建立物化视图时指定FOR UPDATE子句。由于Writable Materialized View要求物化视图能够快速刷新,所以建立必要的物化视图日志Materialized View Log。

 

以下为创建远程可更新物化视图及其管理示例:

 

 

 

source 源端

 

 

SQL> conn c##maclean/oracle

Connected.

SQL>

SQL> create table sourcet as select * from dba_objects;

 

Table created.

 

 

 

 

SQL> select count(*) from sourcet;

 

COUNT(*)

———-

89134

必须建立基于rowid的物化视图日志

 

SQL> create MATERIALIZED VIEW  log on  sourcet with rowid including new values;

 

Materialized view log created.

 

 

 

 

 

target  目标端 ,首先创建必要的DBLINK

 

create database link remote connect to c##maclean identified by oracle using ‘C12’;

 

创建远程物化视图,NEXT SYSDATE + 1/1440 为 每分钟刷新一次

create MATERIALIZED VIEW  target refresh fast with rowid START WITH sysdate NEXT SYSDATE + 1/1440 for update as select * from sourcet@remote;

 

SQL> select count(*) from target;

 

COUNT(*)

———-

89134

Writeable Materialiezd View 可以正常更新

 

 

SQL> delete target where rownum<100;

 

已删除99行。

 

SQL> commit;

 

提交完成。

 

 

Materialiezd View 上可以创建索引和视图

 

 

 

SQL> create index objd_ind on target(object_id);

 

索引已创建。

 

SQL> create view view_objd as select * from target where object_id>1000;

 

视图已创建。

 

 

Materialiezd View 指定了 NEXT SYSDATE + 1/1440 后会 每分钟自动刷新一次

 

 

 

SQL> select count(*) from target;

 

COUNT(*)

———-

89134

可以将Materialiezd View 修改为手动刷新

 

 

ALTER MATERIALIZED VIEW target  refresh on demand;

 

将Materialiezd View  改为自动刷新

 

ALTER MATERIALIZED VIEW target   refresh START WITH sysdate NEXT SYSDATE + 1/1440;

 

 

手动COMPLETE刷新 物化视图

 

exec dbms_mview.refresh(‘MVIEWA’,’C’);

 

 

手动FAST刷新 物化视图

 

exec dbms_mview.refresh(‘MVIEWA’,’F’);

 

 

重新编译 物化视图

 

alter MATERIALIZED VIEW target compile;

 

enq: JI – contention等待事件

Sessions waiting on this event are waiting on locks held during materialized view operations (such as refresh, alter) to prevent concurrent operations on the same materialized view.

Solutions

A materialized view cannot be fast refreshed more than once in a given period because it is serialized during the commit phase. Ensure that only one session at a time is performing the refreshes. If there is more than one session, the first session will work normally but the subsequent sessions will wait on “enq: JI – contention”.

Waits on this event can also be caused by on-commit time logic within the materialized view. Normally when a session updates record 1 and commits and then another session updates record 2 and commits, they do not have to wait for each other. However, when using an on commit-time fast refreshable materialized view on top of the table, we do have to wait when two sessions do totally unrelated transactions concurrently against the same table. This is not a problem when the table is modified infrequently or only by a single session, but it can be a big problem when applied to a table that performs a lot of modifications concurrently. Be sure to use on commit-time fast refreshable materialized views for implementing business rules only on tables that are not concurrently accessed or infrequently changed.

利用DBMS_ADVISOR.TUNE_MVIEW包生成物化视图创建语句

不少人大概和我一样在创建物化视图的时候会犯头痛,怎样合理的改写SQL语句以及添加物化视图日志需要经过慎重精密的考虑。有了DBMS_ADVISOR.TUNE_MVIEW存储过程这个帮手后,极大地方便了DBA或应用设计人员创建和优化物化视图。该TUNE_MVIEW存储过程可以做到优化物化视图中的查询定义,修正物化视图日志的问题,此外它还能为原先不能refresh fast的物化视图提出建议以使得其可以快速刷新。

针对那些确实无法快速刷新的复杂查询,TUNE_MVIEW过程也可能给出将一个查询分解为多个物化视图达到快速刷新和查询重写的目的:

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTD 
2  USING INDEX REFRESH FAST
3  ON DEMAND ENABLE QUERY REWRITE AS 
4  select t2,t3,count(*) from strc group by t2,t3 
5  union all
6  select t2,t3,count(*) from strd group by t2,t3;
select t2,t3,count(*) from strd group by t2,t3
*
ERROR at line 6:
ORA-12015: cannot create a fast refresh materialized view from a complex query
SQL> set serveroutput on;
SQL> declare
2    tn varchar2(200);
3  begin
4    DBMS_ADVISOR.TUNE_MVIEW(tn,
5                            mv_create_stmt => 'CREATE MATERIALIZED VIEW MACLEAN.STRMTC
6                                                   USING INDEX REFRESH FAST
7                                                   ON DEMAND ENABLE QUERY REWRITE AS
8                                                   select t2,t3,count(*) from strc group by t2,t3
9                                                   union all
10                                                  select t2,t3,count(*) from strd group by t2,t3');
11    dbms_output.put_line(tn);
12  end;
13  /
TASK_547
PL/SQL procedure successfully completed.
SQL> select statement
2    from dba_tune_mview
3   where task_name = 'TASK_547'
4   order by action_id;
CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRC" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRC" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRD" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRD" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB1 USING INDEX  REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRC.T3 C1, MACLEAN.STRC.T2 C2, COUNT(*) M1 FROM MACLEAN.STRC GROUP BY MACLEAN.STRC.T3, MACLEAN.STRC.T2
DROP MATERIALIZED VIEW MACLEAN.STRMTC$SUB1
CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB2 USING INDEX  REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRD.T3 C1, MACLEAN.STRD.T2 C2, COUNT(*) M1 FROM MACLEAN.STRD GROUP BY MACLEAN.STRD.T3, MACLEAN.STRD.T2
DROP MATERIALIZED VIEW MACLEAN.STRMTC$SUB2
CREATE MATERIALIZED VIEW MACLEAN.STRMTC USING INDEX  REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS  (SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") UNION ALL  (SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2")
DROP MATERIALIZED VIEW MACLEAN.STRMTC
DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('MACLEAN.STRMTC$RWEQ','select t2,t3,count(*) from strc group by t2,t3
union all
select t2,t3,count(*) from strd group by t2,t3',' (SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") UNION ALL  (SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2")',600916906)
DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('MACLEAN.STRMTC$RWEQ')
SQL> CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRC" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;
Materialized view log created.
SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRC" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;
Materialized view log altered.
SQL> CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRD" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;
Materialized view log created.
SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRD" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;
Materialized view log altered.
SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB1 USING INDEX  REFRESH FAST WITH ROWID 
ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRC.T3 C1, MACLEAN.STRC.T2 C2, COUNT(*) M1 
FROM MACLEAN.STRC GROUP BY MACLEAN.STRC.T3, MACLEAN.STRC.T2;
Materialized view created.
SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB2 USING INDEX  REFRESH FAST WITH ROWID 
ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRD.T3 C1, MACLEAN.STRD.T2 C2, COUNT(*) M1 
FROM MACLEAN.STRD GROUP BY MACLEAN.STRD.T3, MACLEAN.STRD.T2;
Materialized view created.
SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTC USING INDEX  REFRESH FORCE WITH ROWID 
ENABLE QUERY REWRITE AS  
(SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") 
UNION ALL  
(SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2");
Materialized view created.
declare
v_state varchar2(2000);
begin
select statement
into v_state
from dba_tune_mview
where task_name = 'TASK_547'
and action_id = 15;
v_state := 'begin ' || v_state || '; end;';
dbms_output.put_line(v_state);
execute immediate v_state;
end;
PL/SQL procedure successfully completed.
SQL> set linesize 200 pagesize 1400;
SQL> select t2,t3,count(*) from strc group by t2,t3 
2  union all
3  select t2,t3,count(*) from strd group by t2,t3;
no rows selected
---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    74 |     4  (50)| 00:00:01 |
|   1 |  UNION-ALL                    |             |       |       |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| STRMTC$SUB1 |     1 |    37 |     2   (0)| 00:00:01 |
|   3 |   MAT_VIEW REWRITE ACCESS FULL| STRMTC$SUB2 |     1 |    37 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
/* 可以看到查询成功被rewrite为对2个物化视图的扫描 */

沪ICP备14014813号

沪公网安备 31010802001379号