Oracle materialized view error: code included

You can make the test4 materialized view refresh fast like this:

SQL> create table test1  2  ( x1 varchar2(1000)  3  , constraint test1_pk primary key (x1)  4  )  5  /Table created.SQL> create materialized view log on test1 with rowid  2  /Materialized view log created.SQL> create table test2  2  ( x2 varchar2(1000)  3  , constraint test2_pk primary key (x2)  4  )  5  /Table created.SQL> create materialized view log on test2 with rowid  2  /Materialized view log created.SQL> create materialized view test4  2  refresh fast on commit  3  as  4    select t1.rowid as rid1  5         , t2.rowid as rid2  6         , t1.x1 u1  7         , t2.x2  8      from test1 t1  9         , test2 t2 10     where t1.x1 = t2.x2 11  /Materialized view created.SQL> insert into test1 values ('hello')  2  /1 row created.SQL> insert into test2 values ('hello')  2  /1 row created.SQL> commit  2  /Commit complete.SQL> select * from test4  2  /RID1               RID2------------------ ------------------U1---------------------------------------------X2---------------------------------------------AAATU5AAEAAAssfAAA AAATU8AAEAAAssvAAAhellohello1 row selected.

Your case doesn't work because for a nested MV to work, an underlying MV cannot be a basic MV. This sounds strange at first, but you'd need a trick like you did with test3 to make it work. Also, for a join MV to work, the materialized view logs of the underlying table need to be created WITH ROWID.

You might want to look at a series of blog posts I wrote about fast refreshable materialized view errors. They describe almost all restrictions:

Basic MV's
Join MV's
Aggregate MV's
Union all MV's
Nested MV's
MV_CAPABILITIES_TABLE
Summary

Regards,
Rob.

Here is an example with a nested MV using the union all trick on test2 as well:

SQL> create table test1  2  ( x1 varchar2(1000)  3  , constraint test1_pk primary key (x1)  4  )  5  /Table created.SQL> create materialized view log on test1 with rowid  2  /Materialized view log created.SQL> create table test2  2  ( x2 varchar2(1000)  3  , constraint test2_pk primary key (x2)  4  )  5  /Table created.SQL> create materialized view log on test2 with rowid  2  /Materialized view log created.SQL> create materialized view test2_mv  2  refresh fast on commit  3  as  4  select rowid rid  5       , x2  6       , 'A' umarker  7    from test2  8   union all  9  select rowid 10       , x2 11       , 'B' 12    from test2 13   where 1=0 14  /Materialized view created.SQL> alter table test2_mv add constraint test2_mv_pk primary key(x2)  2  /Table altered.SQL> create materialized view log on test2_mv with rowid  2  /Materialized view log created.SQL> create materialized view test3  2  refresh fast on commit  3  as  4  select rowid rid  5       , x1  6       , 'A' umarker  7    from test1  8   union all  9  select rowid 10       , x1 11       , 'B' 12    from test1 13   where 0 = 1 14  /Materialized view created.SQL> alter table test3 add constraint test3_pk primary key (x1)  2  /Table altered.SQL> create materialized view log on test3 with rowid  2  /Materialized view log created.SQL> create materialized view test4  2  refresh fast on commit  3  as  4    select t1.rowid as rid1  5         , t2.rowid as rid2  6         , t1.x1 u1  7         , t2.x2  8      from test3 t1  9         , test2_mv t2 10     where t1.x1 = t2.x2 11  /Materialized view created.SQL> insert into test1 values ('hello')  2  /1 row created.SQL> insert into test2 values ('hello')  2  /1 row created.SQL> commit  2  /Commit complete.SQL> select * from test4  2  /RID1               RID2------------------ ------------------U1---------------------------------------------------X2---------------------------------------------------AAATXbAAEAAAstdAAA AAATXXAAEAAAstNAAAhellohello1 row selected.

Hope this helps!

Quoting from Oracle

Restrictions for Using Multitier Materialized Views

Both master materialized views and materialized views based onmaterialized views must:

• Be primary key materialized views
• Reside in a database that is at 9.0.1 or higher compatibility level

Note: The COMPATIBLE initialization parameter controls a database'scompatibility level.

However, I'll try a solution for you. I'll be back.

Update: Sorry I didn't succeded. You have too many restrictions :)

You may be out of luck, per the Oracle documentation:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/basicmv.htm#i1006734

You can create a nested materialized view on materialized views, but all parent and base materialized views must contain joins or aggregates. If the defining queries for a materialized view do not contain joins or aggregates, it cannot be nested. All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log. All the underlying objects are treated as if they were tables. In addition, you can use all the existing options for materialized views.