首页 文章

ORACLE：物化视图上的快速刷新在某些情况下无法使用OUTER JOIN

提问于
浏览 次
4

问题很简单 . 我创建了由外键链接的TableFather和TableChild . 我为两者创建了物化视图日志 . 我创建物化视图，快速刷新从TableFather到TableChild的左连接 .

结果是

  • 添加子记录，物化视图将刷新

  • 修改子字段，刷新实体化视图

  • 添加父记录，物化视图不会刷新

这是代码

-- Tables
CREATE TABLE TABLE_FATHER (
  ID   NUMBER(10, 0),
  TEXT NVARCHAR2(50),
  CONSTRAINT PK__TABLE1 PRIMARY KEY (ID)
);

CREATE TABLE TABLE_CHILD (
  ID        NUMBER(10, 0),
  TEXT      NVARCHAR2(50),
  ID_FATHER NUMBER(10, 0),
  CONSTRAINT PK__TABLE2 PRIMARY KEY (ID),
  CONSTRAINT FK_TABLE_FATHER FOREIGN KEY (ID_FATHER)
  REFERENCES TABLE_FATHER (ID)
);

-- Some record before materialized view creation
INSERT INTO TABLE_FATHER(ID, TEXT) VALUES(1, 'father1');
INSERT INTO TABLE_CHILD(ID, TEXT, ID_FATHER) VALUES(1, 'child1', 1);
INSERT INTO TABLE_CHILD(ID, TEXT, ID_FATHER) VALUES(2, 'child2', 1);

-- Logs
CREATE MATERIALIZED VIEW LOG on TABLE_FATHER WITH PRIMARY KEY, ROWID;
CREATE MATERIALIZED VIEW LOG on TABLE_CHILD  WITH PRIMARY KEY, ROWID;  

-- Materialized View
CREATE MATERIALIZED VIEW TABLE_MV
REFRESH FAST ON COMMIT
AS
SELECT TABLE_FATHER.ID ID_FATHER,
       TABLE_FATHER.TEXT TEXT_FATHER,
       TABLE_CHILD.ID ID_CHILD,
       TABLE_CHILD.TEXT TEXT_CHILD,
       TABLE_FATHER.ROWID FATHER_ROWID,
       TABLE_CHILD.ROWID CHILD_ROWID
  FROM TABLE_FATHER,
       TABLE_CHILD
  WHERE TABLE_FATHER.ID = TABLE_CHILD.ID_FATHER (+);

此时您可以验证，第一个结果

INSERT INTO TABLE_CHILD(ID, TEXT, ID_FATHER) VALUES(3, 'child3', 1);
COMMIT;
SELECT * FROM TABLE_MV;

然后第二个结果

UPDATE TABLE_CHILD SET TEXT = 'child33' WHERE ID = 3;
COMMIT;
SELECT * FROM TABLE_MV;

然后第三个结果

INSERT INTO TABLE_FATHER(ID, TEXT) VALUES(2, 'father2');
COMMIT;
SELECT * FROM TABLE_MV;

如您所见，Materialized View在最后一种情况下不会刷新 . 我猜可能是什么问题，但我想先看看你的解释 . 我希望我做错了，这不是一种MView限制问题 . 我正在研究Oracle 11g版本11.2.0.1.0 .

非常感谢你的帮助

sql oracle oracle11g materialized-views materialized

1 回答

  • 3

    根据My Oracle Support - 我发现了Bug 8856349：OUTER join物化视图的快速刷新不起作用

    错误在11.2.0.1中确认，在11.2.0.2基本版本中修复 .

    MOS注释说解决方法是：

    设置参数“_mv_refresh_pkfk_relationship_opt”= false .

    您可以通过以下方式更改此参数：

    alter system set "_mv_refresh_pkfk_relationship_opt"=false scope=both;

    However, as this is a hidden parameter, I would either consult Oracle Support (if you have access) as to any potential side effects of setting this parameter, or thoroughly test materialized view refreshes on a test system first before rolling this out to a production system.

    回复于

相关问题