CODEWITHSUNDEEP
sqldatabaseoracle-database
Batman
Batman

Reputation: 6363

Cannot use filter columns from materialized view log on Table

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

Upvotes: 0

Views: 8359

Answers (1)

Alex Poole
Alex Poole

Reputation: 191435

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...

Upvotes: 8

Related Questions