Cannot use filter columns from materialized view log on Table

Question

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;

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;

Error:

Error report - ORA-12033: cannot use filter columns from materialized view log on "CSTST"."DTREECORE" 12033. 0000 - "cannot use filter columns from materialized view log on \"%s\".\"%s\""

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:

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:

  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;

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

Please edit your question to show the full DDL for all the objects, and the full error you get. That seems to be a fragment of the description from an ORA-12033 error, but not sure. — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2018-02-15T18:54:40

The documentation shows "Restrictions on Fast Refresh on Materialized Views with Aggregates":

All tables in the materialized view must have materialized view logs, and the materialized view logs must:

Contain all columns from the table referenced in the materialized view.

Specify with ROWID and INCLUDING NEW VALUES.

Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.

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;

Then you can create the 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...

Cannot use filter columns from materialized view log on Table

1 Answers