无法在表的物化视图日志中使用过滤器列

问题描述

我正在尝试创建汇总的实体化视图：

I'm trying to create an aggregate Materialized View:

CREATE MATERIALIZED VIEW MV_LLATTRDATA_MAX_VERSIONS
    NOLOGGING
    BUILD IMMEDIATE 
    REFRESH FAST ON COMMIT
    ENABLE QUERY REWRITE
    AS 
        SELECT AD.DEFID, AD.ATTRID, MAX(AD.VERNUM) AS MAX_VERNUM, MIN(AD.DEFVERN) AS MAX_DEFVERN
        FROM   CSTST.MV_LLATTRDATA_PARTITIONED_TEST AD, DTREECORE DT
        WHERE AD.ID = DT.DATAID
        GROUP BY AD.DEFID, AD.ATTRID;

我不明白该错误。我已经为LLATTRDATA时间和DTREECORE表创建了MV日志文件

I don't understand the error. I have created a MV Log file for both the LLATTRDATA time and the DTREECORE table

CREATE MATERIALIZED VIEW LOG ON CSTST.DTREECORE (and LLATTR) WITH ROWID INCLUDING NEW VALUES; 

错误：

原因：物化视图日志没有记录过滤器列
，或者

Cause: The materialized view log either did not have filter columns logged, or the timestamp associated with the filter columns was more recent than the last refresh time.

操作：在下一次快速刷新之前，需要完全刷新。
如果需要，将过滤器列添加到实例化视图日志。

Action: A complete refresh is required before the next fast refresh. Add filter columns to the materialized view log, if required.

DTREECORE DDL：

DTREECORE DDL:

CREATE MATERIALIZED VIEW LOG ON "CSTST"."DTREECORE"
 PCTFREE 10 PCTUSED 30 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "CSPRD" 
  WITH ROWID INCLUDING NEW VALUES;

LLATTRCORE DDL：

LLATTRCORE DDL:

  CREATE MATERIALIZED VIEW LOG ON "CSTST"."LLATTRDATA"
 PCTFREE 10 PCTUSED 30 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "CSPRD" 
  WITH ROWID INCLUDING NEW VALUES;

我尝试添加LLATTRCORE视图日志以包括新值，但是我遇到了权限问题

I've tried adding the LLATTRCORE View Log to Include New Values but I'm running into a permission issue

推荐答案

文档显示：

• 包含物化视图中引用的表中的所有列。


• 用ROWID和包含新值指定。


• 如果期望表包含插入/直接加载，删除和更新的组合，则指定SEQUENCE子句。

您需要包括MV i中使用的所有列在日志中，例如：

You need to include all of the columns used in your MV in the logs, e.g.:

CREATE MATERIALIZED VIEW LOG ON MV_LLATTRDATA_PARTITIONED_TEST
WITH ROWID, SEQUENCE(ID, DEFID, ATTRID, VERNUM, DEFVERN) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON DTREECORE
WITH ROWID, SEQUENCE(DATAID) INCLUDING NEW VALUES;

然后您可以创建MV：

CREATE MATERIALIZED VIEW MV_LLATTRDATA_MAX_VERSIONS
    NOLOGGING
    BUILD IMMEDIATE 
    REFRESH FAST ON COMMIT
    ENABLE QUERY REWRITE
    AS 
        SELECT AD.DEFID, AD.ATTRID, MAX(AD.VERNUM) AS MAX_VERNUM, MIN(AD.DEFVERN) AS MAX_DEFVERN
        FROM   MV_LLATTRDATA_PARTITIONED_TEST AD, DTREECORE DT
        WHERE AD.ID = DT.DATAID
        GROUP BY AD.DEFID, AD.ATTRID;

Materialized view MV_LLATTRDATA_MAX_VERSIONS created.

不是最有用的错误消息...

Not the most helpful error message...

这篇关于无法在表的物化视图日志中使用过滤器列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！