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 as ( 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 as ( 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
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 :)