CODEWITHSUNDEEP
oracle-databaseoracle12c
максим ильин
максим ильин

Reputation: 194

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

full 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;

working create materialized view

WHERE TABLEAU.GW_STATISTICS.CLIENTID=ACC.ID(+)

not working create materialized view

WHERE TABLEAU.GW_STATISTICS.CLIENTID=ACC.ID(+) 
    AND TABLEAU.GW_STATISTICS.DAY>TO_DATE('20200531','yyyymmdd')
  1. 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 ?

Upvotes: 0

Views: 512

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59455

Maybe you need to use alias:

select 
      TABLEAU.GW_STATISTICS.ROWID as GW_STATISTICS_ROWID, 
      ACC.ROWID AS ACC_ROWID, 
      ACC.NAME,
      TABLEAU.GW_STATISTICS.AD_ID,
      min(TABLEAU.GW_STATISTICS.DAY) AS MIN_DAY,
      TABLEAU.GW_STATISTICS.DAY

or try

 FROM (SELECT * FROM TABLEAU.GW_STATISTICS WHERE DAY > DATE '2020-05-31') STATS, 
      TABLEAU.GW_CLIENTS ACC
WHERE STATS.CLIENTID = ACC.ID(+)

Should be the same, I think

Upvotes: 0

pmdba
pmdba

Reputation: 7033

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.

Upvotes: 1

Related Questions