Oracle materialized view error: code included

Question

When I run the following 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 get this error upon trying to create 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 don't understand how any of the objects in the "FROM clause" depend on each other.

How do I get this to work? Currently the only work around I can think of is to replace test3 with a ordinary table and manually delete and refresh the data. This approach works, but seems like a bit of a hack.

Alternatively (and perhaps preferably) I'd just like to see an example where can have two tables, and join them into a materialized view, where one of the base tables is bulk updated (and does not need to be reflected in the materialized view) but the others updates should be reflected in the materialized view (i.e. it's kind of "half" fast refresh on commit, and half complete refresh on demand). I tried using refresh force, but when using EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW() I found no evidence of fash refresh on commit being available. I'd also like to do this with union alls as well.

`test3` doesn't make much sense: `select null from dual where 0 = 1` will never return a row. — Allan, Sep 20 '11 at 13:33
@Allan: This is a hack to make it into an aggregate. Feel free to remove it if it stops the error. — Clinton, Sep 20 '11 at 13:52
@Clinton: Just for the sake of clarity: in database terms a compound query has a `union` statement (or any other set operator); an aggregate query has a `group by` clause. It doesn't make much difference here, but it might make the Oracle documentation make a little more sense. — Allan, Sep 20 '11 at 14:08

Rob van Wijk · Answer 1 · 2011-09-29T20:28:36.323

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 AAATU8AAEAAAssvAAA
hello
hello


1 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.

Added: 29-09-2011

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 AAATXXAAEAAAstNAAA
hello
hello


1 row selected.

Hope this helps!

"This sounds strange at first, but you'd need a trick like you did with test3 to make it work": Your answer only gave code to base a materialized view on two tables, which I can already do. Can you give me example code with this trick that allows me to base a fast refresh view on a complete refresh on demand view? — Clinton, Sep 28 '11 at 00:47
The solution to that would depend on how your complete refresh view looks like. Is it a basic, join, aggregate, union or nested MV? In your example, the test3 MV is unnecessary. — Rob van Wijk, Sep 28 '11 at 06:37
Or was your test3 MV not intended as a trick to make it a union all MV instead of a basic MV, I wonder ... — Rob van Wijk, Sep 28 '11 at 06:40
Lets say `test3` is based on something complex that isn't fast refreshable (not just `test2` as in this trivial example). I'm currently implementing `test3` as a table, and doing a `delete * from test3`, `insert into test3 ( complicated SQL or view )` to update it. Of course then I can base a fast refreshable view on this bulk updated table. I'd like to make this `test3` into instead a materialized view, so I don't have to do a delete...insert cycle on it (which is what a materialized view should do anyway on refresh) — Clinton, Sep 28 '11 at 13:11
It IS possible, but it might lead to MV's on top of MV's on top of MV's etcetera. One of the restrictions of nested MV's is that the underlying MV's cannot be basic MV's. In your case your test2 MV is a basic one, so it won't work. If you make test2 a union all MV, like you did with test3, then it will work. If you still run into problems, can you please post it by adding it to your question? — Rob van Wijk, Sep 28 '11 at 21:05
I don't understand how the question I posted isn't a what you're asking to add to my question. In the question, `test3` is a complex (note the union all) materialized view, and another materialized view `test4` is based on it. Isn't that what you're asking for? Could you answer with modified the code in the question so it does not give an error when it runs, and is still a fash refresh materialized view based on a complete refresh on demand one? — Clinton, Sep 29 '11 at 02:06
In your example, test2 is a basic MV, not a union all MV. Make it a union all MV, and add rowid to the underlying MV logs, and your example works. — Rob van Wijk, Sep 29 '11 at 07:16
Rob: In my example, isn't `test2` a table, not a MV at all? Could you please post the full code that runs? — Clinton, Sep 29 '11 at 11:52
Yes you are right. I meant table, and you need a non-basic MV. I'll post a working example this evening. — Rob van Wijk, Sep 29 '11 at 12:00
Sorry, I think I haven't been clear here. In your example you've posted, you haven't included a `complete refresh on demand` MV. As I mentioned in the comment above "Lets say `test3` is based on something complex that isn't fast refreshable". The idea that `test3` gets bulk updated but has a MV log, so `test4` based on `test3` (which is bulk updated but has a MV log) and `test1` (which also has an MV log) can be refreshed fast. Can you please adjust your example to include a `refresh complete on demand` MV? — Clinton, Sep 30 '11 at 00:46

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

2

Quoting from Oracle

Restrictions for Using Multitier Materialized Views

Both master materialized views and materialized views based on materialized 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's compatibility level.

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

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 20 '11 at 06:54

Florin Ghita

17,525
6
57
76

"Too many restrictions"? What do you mean? What are my restrictions? – Clinton Sep 20 '11 at 13:52

score 0 · Answer 3 · answered Sep 26 '11 at 15:14

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.

Oracle materialized view error: code included

3 Answers3