How create materialized view refresh fast using where day>to_date(20200531)

Question

working code :

create materialized view log on TABLEAU.GW_STATISTICS 
WITH ROWID,
SEQUENCE(AD_ID,day)including new values;

create materialized view log on TABLEAU.GW_CLIENTS
WITH ROWID,
SEQUENCE(id,NAME)including new values;

CREATE MATERIALIZED VIEW MV_CREATIVE_DCO_ADWORDS_2
NOLOGGING
NOCOMPRESS 
CACHE 
NOPARALLEL 
REFRESH FAST ON DEMAND 
as
   select TABLEAU.GW_STATISTICS.ROWID, 
          ACC.ROWID, 
          ACC.NAME,
          TABLEAU.GW_STATISTICS.AD_ID,
          min(TABLEAU.GW_STATISTICS.DAY),
          TABLEAU.GW_STATISTICS.DAY
     FROM TABLEAU.GW_STATISTICS, 
          TABLEAU.GW_CLIENTS ACC
    WHERE TABLEAU.GW_STATISTICS.CLIENTID=ACC.ID(+) and  TABLEAU.GW_STATISTICS.DAY>TO_DATE('20200531','yyyymmdd')
 group by TABLEAU.GW_STATISTICS.ROWID, 
          ACC.ROWID, 
          ACC.NAME,
          TABLEAU.GW_STATISTICS.AD_ID,
          TABLEAU.GW_STATISTICS.DAY;

I need:

WHERE TABLEAU.GW_STATISTICS.CLIENTID=ACC.ID(+)
                AND TABLEAU.GW_STATISTICS.DAY>TO_DATE('20200531','yyyymmdd')

00000 - "cannot fast refresh materialized view %s.%s" *Cause: Either ROWIDs of certain tables were missing in the definition or the inner table of an outer join did not have UNIQUE constraints on join columns. *Action: Specify the FORCE or COMPLETE option. If this error is got during creation, the materialized view definition may have be changed. Refer to the documentation on materialized views.

Why cant i add a filter by date ? How to get around this limitation ?

pmdba pmdba · Accepted Answer · 2021-09-09T12:27:12

Cause: Either ROWIDs of certain tables were missing in the definition...

For starters, you need to include the ROWID from each table in your select:

CREATE MATERIALIZED VIEW MV_CREATIVE_DCO_ADWORDS 
NOLOGGING
NOCOMPRESS 
CACHE 
NOPARALLEL 
REFRESH FAST ON DEMAND 
as
   select TABLEAU.GW_STATISTICS.ROWID, 
          ACC.ROWID, 
          ACC.NAME,
          TABLEAU.GW_STATISTICS.AD_ID,
          min(TABLEAU.GW_STATISTICS.DAY),
          TABLEAU.GW_STATISTICS.DAY
     FROM TABLEAU.GW_STATISTICS, 
          TABLEAU.GW_CLIENTS ACC
    WHERE TABLEAU.GW_STATISTICS.CLIENTID=ACC.ID(+)
      AND TABLEAU.GW_STATISTICS.DAY>TO_DATE('20200531','yyyymmdd')
 group by TABLEAU.GW_STATISTICS.ROWID, 
          ACC.ROWID, 
          ACC.NAME,
          TABLEAU.GW_STATISTICS.AD_ID,
          TABLEAU.GW_STATISTICS.DAY;

or the inner table of an outer join did not have UNIQUE constraints on join columns.

Also, TABLEAU.GW_STATISTICS.CLIENTID needs to have a unique constraint.

How create materialized view refresh fast using where day>to_date(20200531)

1 Answers