物化视图复制快速刷新的限制

最新推荐文章于 2025-06-15 16:23:39 发布

转载最新推荐文章于 2025-06-15 16:23:39 发布 · 193 阅读

探讨Oracle中物化视图复制与预计算物化视图的快速刷新限制及解决方案，包括不同版本间的差异。

物化视图复制快速刷新的限制（一）

http://yangtingkun.itpub.net/post/468/207875

Oracle的物化视图复制的使用范围比较广，但是Oracle的官方文档中却很少建议将物化视图的复制用作数据仓库的数据抽取。

物化视图的用处可以非为两个大类：复制和预计算。复制用来和远端数据库中同步数据。预计算一般配合查询重新功能，为数据仓库中的复杂查询——一般分为连接、聚集两类——快速的提供查询结果。

这两种用途有较大的差别，因此虽然都是物化视图，但是Oracle在介绍两种不同技术的时候是在两个完全不同的文档中。一个是高级复制，另一个是数据仓库手册。

数据仓库主要利用物化视图的快速刷新和查询重写功能。而数据仓库在进行数据抽取的时候却从来不提物化视图的同步功能，这是为什么呢？测试发现，用作同步功能的物化视图作为预计算物化视图的基表，会导致预计算物化视图无法快速刷新。

看一个简单的例子：

SQL> CREATE TABLE DIM_A (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE DIM_B (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE FACT (ID NUMBER PRIMARY KEY, AID NUMBER, BID NUMBER, NUM NUMBER,
2 CONSTRAINT FK_FACT_AID FOREIGN KEY (AID) REFERENCES DIM_A(ID),
3 CONSTRAINT FK_FACT_BID FOREIGN KEY (BID) REFERENCES DIM_B(ID));

表已创建。

SQL> INSERT INTO DIM_A SELECT ROWNUM, 'A'||ROWNUM FROM USER_OBJECTS;

已创建109行。

SQL> INSERT INTO DIM_B SELECT ROWNUM, 'B'||ROWNUM FROM USER_OBJECTS;

已创建109行。

SQL> INSERT INTO FACT SELECT ROWNUM, MOD(ROWNUM, 6) + 1, MOD(ROWNUM, 5 ) + 1, ROWNUM *2
2 FROM USER_OBJECTS;

已创建109行。

SQL> COMMIT;

提交完成。

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

实体化视图日志已创建。

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

实体化视图日志已创建。

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

实体化视图日志已创建。

实体化视图已创建。

这是一个简单的基于连接的快速刷新物化视图。上面建立物化视图日志同时包括了PRIMARY KEY和ROWID，就是为了下面的例子建立物化视图同步环境时，不必重建物化视图日志。下面将上面的基表换成同步后的物化视图，看看是何种结果。

SQL> CONN TEST/TEST@TEST2已连接。
SQL> CREATE MATERIALIZED VIEW DIM_A REFRESH FAST AS SELECT * FROM DIM_A@YTK.YANGTINGKUN;

实体化视图已创建。

SQL> CREATE MATERIALIZED VIEW DIM_B REFRESH FAST AS SELECT * FROM DIM_B@YTK.YANGTINGKUN;

实体化视图已创建。

SQL> CREATE MATERIALIZED VIEW FACT REFRESH FAST AS SELECT * FROM FACT@YTK.YANGTINGKUN;

实体化视图已创建。

SQL> CREATE MATERIALIZED VIEW LOG ON DIM_A WITH ROWID;

实体化视图日志已创建。

SQL> CREATE MATERIALIZED VIEW LOG ON DIM_B WITH ROWID;

实体化视图日志已创建。

SQL> CREATE MATERIALIZED VIEW LOG ON FACT WITH ROWID;

实体化视图日志已创建。

SQL> CREATE MATERIALIZED VIEW MV_FACT REFRESH FAST AS
2 SELECT F.ROWID F_ROWID, A.ROWID A_ROWID, B.ROWID B_ROWID, F.ID,
3 A.NAME A_NAME, B.NAME B_NAME, NUM
4 FROM FACT F, DIM_A A, DIM_B B
5 WHERE F.AID = A.ID
6 AND F.BID = B.ID;
FROM FACT F, DIM_A A, DIM_B B
*第 4 行出现错误:
ORA-12053: this is not a valid nested materialized view

SQL> @?/RDBMS/ADMIN/UTLXMV

表已创建。

SQL> BEGIN
2 DBMS_MVIEW.EXPLAIN_MVIEW('SELECT F.ROWID F_ROWID, A.ROWID A_ROWID, B.ROWID B_ROWID, F.ID,
3 A.NAME A_NAME, B.NAME B_NAME, NUM
4 FROM FACT F, DIM_A A, DIM_B B
5 WHERE F.AID = A.ID
6 AND F.BID = B.ID');
7 END;
8 /

PL/SQL 过程已成功完成。

SQL> COL MSGTXT FORMAT A72SQL> SELECT CAPABILITY_NAME, POSSIBLE, MSGTXT
2 FROM MV_CAPABILITIES_TABLE
3 WHERE CAPABILITY_NAME LIKE 'REFRESH%';

CAPABILITY_NAME P MSGTXT
------------------------------ - -------------------------------------------
REFRESH_COMPLETE Y
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N requirements not satisfied for fast refresh of nested mv
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

看来，复制物化视图作为预计算物化视图的基表是不支持快速刷新的，如果建立完全刷新的物化视图则没有问题。

SQL> CREATE MATERIALIZED VIEW MV_FACT AS
2 SELECT F.ROWID F_ROWID, A.ROWID A_ROWID, B.ROWID B_ROWID, F.ID,
3 A.NAME A_NAME, B.NAME B_NAME, NUM
4 FROM FACT F, DIM_A A, DIM_B B
5 WHERE F.AID = A.ID
6 AND F.BID = B.ID;

实体化视图已创建。

物化视图复制快速刷新的限制（二）

Oracle的物化视图复制的使用范围比较广，但是Oracle的官方文档中却很少建议将物化视图的复制用作数据仓库的数据抽取。

物化视图复制快速刷新的限制（一）：http://yangtingkun.itpub.net/post/468/207875

Oracle中，复制物化视图作为预计算物化视图的基表是不支持快速刷新的。但是，复制物化视图作为复制物化视图的基表是可以快速刷新的。多级的物化视图复制就是利用了这个特性。在我曾经做过的例子中，只读物化视图复制达到了4层，仍然可以快速刷新。

预计算物化视图不能基于复制物化视图建立快速刷新，那么，能否直接对远端基表建立快速刷新的物化视图呢？

看下面的例子，这个例子的代码就建立在上面一章例子的基础之上。环境的搭建就不重复了，参考上一篇文章。

SQL> CONN TEST/TEST@TEST2已连接。
SQL> DROP MATERIALIZED VIEW MV_FACT;

实体化视图已删除。

SQL> CREATE MATERIALIZED VIEW MV_FACT REFRESH FAST AS
2 SELECT F.ROWID F_ROWID, A.ROWID A_ROWID, B.ROWID B_ROWID, F.ID,
3 A.NAME A_NAME, B.NAME B_NAME, NUM
4 FROM FACT@YTK.YANGTINGKUN F, DIM_A@YTK.YANGTINGKUN A, DIM_B@YTK.YANGTINGKUN B
5 WHERE F.AID = A.ID
6 AND F.BID = B.ID;
AND F.BID = B.ID
*第 6 行出现错误:
ORA-12015: cannot create a fast refresh materialized view from a complex query

SQL> TRUNCATE TABLE MV_CAPABILITIES_TABLE;

表被截断。

SQL> BEGIN
2 DBMS_MVIEW.EXPLAIN_MVIEW('SELECT F.ROWID F_ROWID, A.ROWID A_ROWID, B.ROWID B_ROWID, F.ID,
3 A.NAME A_NAME, B.NAME B_NAME, NUM
4 FROM FACT@YTK.YANGTINGKUN F, DIM_A@YTK.YANGTINGKUN A, DIM_B@YTK.YANGTINGKUN B
5 WHERE F.AID = A.ID
6 AND F.BID = B.ID');
7 END;
8 /

PL/SQL 过程已成功完成。

SQL> COL MSGTXT FORMAT A72SQL> SELECT CAPABILITY_NAME, POSSIBLE, MSGTXT
2 FROM MV_CAPABILITIES_TABLE
3 WHERE CAPABILITY_NAME LIKE 'REFRESH%';

CAPABILITY_NAME P MSGTXT
------------------------------ - ------------------------------------------------------
REFRESH_COMPLETE Y
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N mv references a remote table or view in the FROM list
REFRESH_FAST_AFTER_INSERT N one or more joins present in mv
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

已选择6行。

这次的错误信息也很明显，对于预计算的物化视图，基表是不能包括远端表的。

那么就没有任何办法将预计算的物化视图快速刷新到远端了吗？也并不是没有办法，但是这个方法与建立预计算的初衷可能并不一致。那就是，在基表所在数据库建立快速刷新的预计算物化视图，在利用物化视图复制将预计算物化视图复制到远端数据库。

SQL> CONN YANGTK/YANGTK@YTK已连接。
SQL> DROP MATERIALIZED VIEW MV_FACT;

实体化视图已删除。

实体化视图已创建。

SQL> ALTER TABLE MV_FACT ADD PRIMARY KEY (F_ROWID, A_ROWID, B_ROWID);

表已更改。

SQL> CREATE MATERIALIZED VIEW LOG ON MV_FACT;

实体化视图日志已创建。

SQL> CONN TEST/TEST@TEST2已连接。
SQL> CREATE MATERIALIZED VIEW MV_MV_FACT REFRESH FAST AS
2 SELECT * FROM MV_FACT@YTK.YANGTINGKUN;

实体化视图已创建。

使用这个方法的缺点是大部分的工作量全部有基表所在站点承担，除了物化视图日志增加的负载，还包括了建立、刷新大量的预计算物化视图的负载，以及预计算物化视图日志的负载，而对于基表站点本身来说，预计算物化视图是没有实际的作用的。不过，这种方法毕竟解决了预计算物化视图不能快速刷新到远端站点的问题。如果基表所在数据库的负载较轻的话，是可以考虑这个方案的。

yangtingkun 发表于:2006.09.15 19:08 ::分类: ( ORACLE ) ::阅读:(2681次) :: 评论 (2)

re: 物化视图复制快速刷新的限制（二） [回复]

版主，为什么我做的试验是可以的呢？
一下是在oracle 9i上做的：
SQL> CREATE TABLE DIM_A (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

Table created.

SQL> CREATE TABLE DIM_B (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

Table created.

SQL>
SQL> CREATE TABLE FACT (ID NUMBER PRIMARY KEY, AID NUMBER, BID NUMBER, NUM NUMBER,
2 CONSTRAINT FK_FACT_AID FOREIGN KEY (AID) REFERENCES DIM_A(ID),
3 CONSTRAINT FK_FACT_BID FOREIGN KEY (BID) REFERENCES DIM_B(ID));

Table created.

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

Materialized view log created.

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

Materialized view log created.

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

Materialized view log created.

SQL> select name from v$database;

NAME
---------
ORCL

SQL> INSERT INTO DIM_A SELECT ROWNUM, 'A'||ROWNUM FROM USER_OBJECTS;

25 rows created.

SQL> INSERT INTO DIM_B SELECT ROWNUM, 'B'||ROWNUM FROM USER_OBJECTS;

25 rows created.

SQL> INSERT INTO FACT SELECT ROWNUM, MOD(ROWNUM, 6) + 1, MOD(ROWNUM, 5 ) + 1, ROWNUM *2
2 2 FROM USER_OBJECTS;
2 FROM USER_OBJECTS
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

SQL>
SQL> INSERT INTO FACT SELECT ROWNUM, MOD(ROWNUM, 6) + 1, MOD(ROWNUM, 5 ) + 1, ROWNUM *22 FROM USER_OBJECTS;

25 rows created.

SQL> COMMIT;

Commit complete.

SQL> SPOOL OFF

以下是在oracle 10g上做的：

SQL> select name from v$database;

NAME
---------
TENG

SQL> SELECT F.ROWID F_ROWID, A.ROWID A_ROWID, B.ROWID B_ROWID, F.ID,
2 A.NAME A_NAME, B.NAME B_NAME, NUM
3 FROM FACT@PUBLIC_WITH_USER F, DIM_A@PUBLIC_WITH_USER A, DIM_B@PUBLIC_WITH_USER B
4 WHERE F.AID = A.ID
5 AND F.BID = B.ID;

no rows selected

SQL> CREATE MATERIALIZED VIEW MV_FACT REFRESH FAST AS
2 SELECT F.ROWID F_ROWID, A.ROWID A_ROWID, B.ROWID B_ROWID, F.ID,
3 A.NAME A_NAME, B.NAME B_NAME, NUM
4 FROM FACT@PUBLIC_WITH_USER F, DIM_A@PUBLIC_WITH_USER A, DIM_B@PUBLIC_WITH_USER B
5 WHERE F.AID = A.ID
6 AND F.BID = B.ID;

Materialized view created.

SQL> SELECT * FROM MV_FACT;

no rows selected

SQL> EXEC DBMS_MVIEW('MV_FACT');
BEGIN DBMS_MVIEW('MV_FACT'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'DBMS_MVIEW' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> EXEC DBMS_MVIEW.REFRESH('MV_FACT');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM MV_FACT;

F_ROWID A_ROWID B_ROWID ID A_NAME
------------------ ------------------ ------------------ ---------- ------------------------------
B_NAME NUM
------------------------------ ----------
AAAHlRAALAAAC5WAAX AAAHlNAALAAAC42AAA AAAHlPAALAAAC5GAAE 24 A1
B5 528

AAAHlRAALAAAC5WAAR AAAHlNAALAAAC42AAA AAAHlPAALAAAC5GAAD 18 A1
B4 396

AAAHlRAALAAAC5WAAL AAAHlNAALAAAC42AAA AAAHlPAALAAAC5GAAC 12 A1
B3 264

AAAHlRAALAAAC5WAAF AAAHlNAALAAAC42AAA AAAHlPAALAAAC5GAAB 6 A1
B2 132

AAAHlRAALAAAC5WAAS AAAHlNAALAAAC42AAB AAAHlPAALAAAC5GAAE 19 A2
B5 418

AAAHlRAALAAAC5WAAM AAAHlNAALAAAC42AAB AAAHlPAALAAAC5GAAD 13 A2
B4 286

AAAHlRAALAAAC5WAAG AAAHlNAALAAAC42AAB AAAHlPAALAAAC5GAAC 7 A2
B3 154

AAAHlRAALAAAC5WAAA AAAHlNAALAAAC42AAB AAAHlPAALAAAC5GAAB 1 A2
B2 22

AAAHlRAALAAAC5WAAY AAAHlNAALAAAC42AAB AAAHlPAALAAAC5GAAA 25 A2
B1 550

AAAHlRAALAAAC5WAAN AAAHlNAALAAAC42AAC AAAHlPAALAAAC5GAAE 14 A3
B5 308

AAAHlRAALAAAC5WAAH AAAHlNAALAAAC42AAC AAAHlPAALAAAC5GAAD 8 A3
B4 176

AAAHlRAALAAAC5WAAB AAAHlNAALAAAC42AAC AAAHlPAALAAAC5GAAC 2 A3
B3 44

AAAHlRAALAAAC5WAAT AAAHlNAALAAAC42AAC AAAHlPAALAAAC5GAAA 20 A3
B1 440