1

EDIT: I solved my problem changing JOINs to non-ANSI format... but I still wish to know how to make FAST REFRESH work with Materialized Views built on queries containing JOINs, if possible.

on Oracle 11g it seems that I cannot FAST REFRESH materialized views. To perform some tests I've created the following two tables:

CREATE TABLE test_1
(n   NUMBER NOT NULL,
 v   VARCHAR2 (1),
 d   DATE DEFAULT SYSDATE NOT NULL);

CREATE UNIQUE INDEX t_1_pk ON test_1 (n);

ALTER TABLE test_1 ADD (
  CONSTRAINT t_1_pk
  PRIMARY KEY (n)
  USING INDEX t_1_pk
  ENABLE VALIDATE);

CREATE MATERIALIZED VIEW LOG ON test_1 WITH ROWID, PRIMARY KEY;

and

CREATE TABLE test_2
(n    NUMBER NOT NULL,
 n1   NUMBER,
 v    VARCHAR2 (1),
 d    DATE DEFAULT SYSDATE NOT NULL);

CREATE UNIQUE INDEX t_2_pk ON test_2 (n);

ALTER TABLE test_2 ADD (
  CONSTRAINT t_2_pk
  PRIMARY KEY (n)
  USING INDEX t_2_pk
  ENABLE VALIDATE,
  CONSTRAINT t_2_fk
  FOREIGN KEY (n1)
  REFERENCING test_1 (n));

CREATE MATERIALIZED VIEW LOG ON test_2 WITH ROWID, PRIMARY KEY;

each filled with random data. Then I tried to create a simple Materialized View:

CREATE MATERIALIZED VIEW test_mv
   BUILD IMMEDIATE
   REFRESH FAST ON DEMAND AS
   SELECT t1.ROWID rid1,
          t2.ROWID rid2,
          t1.n n1,
          t1.v v1,
          t2.n n2,
          t2.v v2
     FROM test_1 t1 JOIN test_2 t2 ON t1.n = t2.n1;

but I got the following error:

ORA-12015: cannot create a fast refresh materialized view from a complex query

Removing the JOIN (i.e. selecting only from one base table) I'm able to build the MView. I tried to search for some system parameter to check, but it seems only a problem of static query. The strange thing is that my query does not contain any invalid expression (I've checked this question, this article and in many other documentation sites and posts). Also, I have worked for a long time with Oracle 12c and I have never had difficulty in creating materialized views.

Alessandro Cucina
  • 148
  • 1
  • 12
  • You should read https://stackoverflow.com/questions/7505020/oracle-how-to-create-a-materialized-view-with-fast-refresh-and-joins also. – Ankit Bajpai Sep 30 '19 at 11:16
  • @AnkitBajpai: I've done both steps suggested in the accepted answer already (base tables' ROWIDs included in SELECT clause, materialized view logs created with WITH ROWID clause). But the accepted answer uses non-ANSI joins in the materialized view query, not standard JOIN as mentioned in the question. I came to the same conclusion at the end, read my EDIT in the question. – Alessandro Cucina Sep 30 '19 at 12:48

0 Answers0