ORA-12015 Cannot create a fast refresh materialized view from a complex query-CodePudding

I have been running into this error trying to set up a MV to fast refresh, I don't believe anything in the query itself is causing this and might be all the code directly below the create statement. Would those prevent a fast refresh from happening? QUERY

CodePudding user response：

According to this:

https://docs.oracle.com/database/121/SQLRF/statements_6002.htm#SQLRF01302

Restrictions on FAST Refresh FAST refresh is subject to the following restrictions:

When you specify FAST refresh at create time, Oracle Database verifies that the materialized view you are creating is eligible for fast refresh. When you change the refresh method to FAST in an ALTER MATERIALIZED VIEW statement, Oracle Database does not perform this verification. If the materialized view is not eligible for fast refresh, then Oracle Database returns an error when you attempt to refresh this view.

Materialized views are not eligible for fast refresh if the defining query contains an analytic function or the XMLTable function.

Materialized views are not eligible for fast refresh if the defining query references a table on which an XMLIndex index is defined.

You cannot fast refresh a materialized view if any of its columns is encrypted.

There are few restrictions you have to check.

CodePudding user response：

Based on your comment that the source is a view, there are a couple of things potentially going on here.

Oracle is smart enough not to be fooled by the view in the MV definition if that view contains joins. In order to create a fast refresh materialized view on a complex query, you need to have materialized view logs with rowid created on all of the underlying source tables, and include each table's rowid column explicitly in the materialized view definition.

Additionally, there has been a bug reported a few years ago when building materialized views based on views, as described here: https://connor-mcdonald.com/2018/07/05/complex-materialized-views-and-fast-refresh/. The workaround for the bug is to inline the view definition in the materialized view definition and not select from the view itself (i.e. don't abstract the MV query with the separate view). I'm not sure if this particular bug has been patched at the current time.

Examples, from the blog:

SQL> create materialized view log on dept
  2  with rowid, primary key, sequence
  3  including new values;
 
Materialized view log created.
 
SQL>
SQL> create materialized view log on emp
  2  with rowid, primary key, sequence
  3  including new values;
 
Materialized view log created.
 
SQL> create materialized view mv
  2  --build deferred
  3  refresh fast on demand
  4  with primary key
  5  enable query rewrite
  6  as
  7  select a.rowid erowid,b.rowid drowid ,b.dname, a.*
  8  from emp a, dept b
  9  where a.dept_id=b.dept_id;
 
Materialized view created.

and of the bug:

SQL>
SQL> create or replace view vw
  2  as
  3  select a.rowid erowid,b.rowid drowid ,b.dname, a.*
  4  from emp a, dept b
  5  where a.dept_id=b.dept_id;
 
View created.
 
SQL>
SQL> create materialized view mv
  2  --build deferred
  3  refresh fast on demand
  4  with primary key
  5  enable query rewrite
  6  as
  7  select * from vw;
select * from vw
              *
ERROR at line 7:
ORA-12015: cannot create a fast refresh materialized view from a complex query