所以我很确定 Oracle 支持这一点,所以我不知道我做错了什么。这段代码的工作原理:
CREATE MATERIALIZED VIEW MV_Test
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT V.* FROM TPM_PROJECTVERSION V;
如果我添加 JOIN,它就会中断:
CREATE MATERIALIZED VIEW MV_Test
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT V.*, P.* FROM TPM_PROJECTVERSION V
INNER JOIN TPM_PROJECT P ON P.PROJECTID = V.PROJECTID
现在我收到错误:
ORA-12054: 无法设置物化视图的 ON COMMIT 刷新属性
我已在 TPM_PROJECT 和 TPM_PROJECTVERSION 上创建了物化视图日志。 TPM_PROJECT 的主键为 PROJECTID,TPM_PROJECTVERSION 的复合主键为 (PROJECTID,VERSIONID)。这其中有什么技巧呢?我一直在挖掘Oracle手册,但无济于事。谢谢!
首先,从Oracle 数据库数据仓库指南 http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#sthref537:
仅使用连接对物化视图进行快速刷新的限制
...
- FROM列表中所有表的Rowid必须出现在SELECT中
查询的列表。
这意味着您的声明将需要如下所示:
CREATE MATERIALIZED VIEW MV_Test
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT V.*, P.*, V.ROWID as V_ROWID, P.ROWID as P_ROWID
FROM TPM_PROJECTVERSION V,
TPM_PROJECT P
WHERE P.PROJECTID = V.PROJECTID
另一个需要注意的关键方面是您的物化视图日志必须创建为with rowid
.
下面是一个功能测试场景:
CREATE TABLE foo(foo NUMBER, CONSTRAINT foo_pk PRIMARY KEY(foo));
CREATE MATERIALIZED VIEW LOG ON foo WITH ROWID;
CREATE TABLE bar(foo NUMBER, bar NUMBER, CONSTRAINT bar_pk PRIMARY KEY(foo, bar));
CREATE MATERIALIZED VIEW LOG ON bar WITH ROWID;
CREATE MATERIALIZED VIEW foo_bar
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS SELECT foo.foo,
bar.bar,
foo.ROWID AS foo_rowid,
bar.ROWID AS bar_rowid
FROM foo, bar
WHERE foo.foo = bar.foo;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)