After I run the next code on Oracle 10g:

drop materialized view test4;
drop materialized view test3;
drop table test2;
drop table test1;

create table test1
  x1 varchar2(1000),
  constraint test1_pk primary key (x1)

create materialized view log on test1 with sequence;

create table test2
  x2 varchar2(1000),
  constraint test2_pk primary key (x2)

create materialized view log on test2 with sequence;

create materialized view test3
refresh complete on demand 
  select x1 from test1
  union all
  select null from dual where 0 = 1

alter table test3 add constraint test3_pk primary key (x1);

create materialized view log on test3 with sequence;

create materialized view test4
refresh fast on commit
  select t1.rowid as rid1, t2.rowid as rid2, t1.x1 u1, t2.x2
  from test3 t1, test2 t2
  where t1.x1 = t2.x2

I recieve this error upon attempting to produce the materialized view test4:

SQL Error: ORA-12053: this is not a valid nested materialized view  
12053. 00000 -  "this is not a valid nested materialized view"  
*Cause:    The list of objects in the FROM clause of the definition of this  
           materialized view had some dependencies upon each other.  
*Action:   Refer to the documentation to see which types of nesting are valid.

I do not know how the objects within the "FROM clause" rely on one another.

How do you get this work? Presently the only real deal with I'm able to think about would be to replace test3 having a regular table and by hand remove and refresh the information. This method works, but appears like a little of the hack.

Alternatively (and possibly ideally) I'd just want to see a good example where might have two tables, and join them right into a materialized view, where among the base tables is bulk up-to-date (and need not be reflected within the materialized view) however the others updates ought to be reflected within the materialized view (i.e. it's type of "half" fast refresh on commit, and half complete refresh on demand). I attempted using refresh force, however when using EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW() I discovered no proof of fash refresh on commit being offered. I'd also enjoy this with union alls too.

Estimating from Oracle

Limitations for implementing Multitier Materialized Sights

Both master materialized sights and materialized sights according to materialized sights must:

  • Be primary key materialized sights
  • Live in a database that's at 9..1 or greater compatibility level

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

However, I'll consider using a solution for you personally. I'm going to be back.

Update: Sorry I did not succeded. You've a lot of limitations :)