The restrictions regarding nested MV's are not in the fast-refreshable part of the documentation, but a little earlier: here.
I noticed three restrictions in there:
1) All parent and base materialized views must contain joins or aggregates.Let's inspect the related error messages, but I'll start again with a situation where everything works. Two parent materialized views and on top of them one nested materialized view:
2) All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log.
3) You cannot create both a materialized view and a prebuilt materialized view on the same table.
rwijk@ORA11GR1> create materialized view log on emp
2 with primary key, rowid (deptno,sal) including new values
3 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view log on dept with rowid
2 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view parent1_mv
2 refresh fast on commit
3 as
4 select emp.rowid emp_rowid
5 , dept.rowid dept_rowid
6 , emp.ename
7 , emp.deptno
8 , emp.sal
9 , dept.dname
10 from emp
11 , dept
12 where emp.deptno = dept.deptno
13 /
Materialized view created.
rwijk@ORA11GR1> create materialized view parent2_mv
2 refresh fast on commit
3 as
4 select deptno
5 , sum(sal) sumsal
6 , count(sal) countsal
7 , count(*) countall
8 from emp
9 group by deptno
10 /
Materialized view created.
rwijk@ORA11GR1> create materialized view log on parent1_mv with rowid
2 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view log on parent2_mv with rowid
2 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view nested_mv
2 refresh fast on commit
3 as
4 select p1.rowid p1_rowid
5 , p2.rowid p2_rowid
6 , p1.ename
7 , p1.sal
8 , p1.dname
9 , p2.sumsal
10 from parent1_mv p1
11 , parent2_mv p2
12 where p1.deptno = p2.deptno
13 /
Materialized view created.
Please note that the materialized view log on emp is already fully equipped for future uses. Parent1_mv is a join MV and parent2_mv is an aggregate MV. And the nested MV joins both. This works as can be seen by this little test where KING's salary is doubled to 10,000:
rwijk@ORA11GR1> select * from nested_mv
2 /
P1_ROWID P2_ROWID ENAME SAL DNAME SUMSAL
------------------ ------------------ ---------- ---------- -------------- ----------
AAAWnXAAEAAABRrAAA AAAWnZAAEAAABRzAAB SMITH 800 RESEARCH 10875
AAAWnXAAEAAABRrAAB AAAWnZAAEAAABRzAAA ALLEN 1600 SALES 9400
AAAWnXAAEAAABRrAAC AAAWnZAAEAAABRzAAA WARD 1250 SALES 9400
AAAWnXAAEAAABRrAAD AAAWnZAAEAAABRzAAB JONES 2975 RESEARCH 10875
AAAWnXAAEAAABRrAAE AAAWnZAAEAAABRzAAA MARTIN 1250 SALES 9400
AAAWnXAAEAAABRrAAF AAAWnZAAEAAABRzAAA BLAKE 2850 SALES 9400
AAAWnXAAEAAABRrAAG AAAWnZAAEAAABRzAAC CLARK 2450 ACCOUNTING 8750
AAAWnXAAEAAABRrAAH AAAWnZAAEAAABRzAAB SCOTT 3000 RESEARCH 10875
AAAWnXAAEAAABRrAAI AAAWnZAAEAAABRzAAC KING 5000 ACCOUNTING 8750
AAAWnXAAEAAABRrAAJ AAAWnZAAEAAABRzAAA TURNER 1500 SALES 9400
AAAWnXAAEAAABRrAAK AAAWnZAAEAAABRzAAB ADAMS 1100 RESEARCH 10875
AAAWnXAAEAAABRrAAL AAAWnZAAEAAABRzAAA JAMES 950 SALES 9400
AAAWnXAAEAAABRrAAM AAAWnZAAEAAABRzAAB FORD 3000 RESEARCH 10875
AAAWnXAAEAAABRrAAN AAAWnZAAEAAABRzAAC MILLER 1300 ACCOUNTING 8750
14 rows selected.
rwijk@ORA11GR1> update emp set sal = 10000 where ename = 'KING'
2 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from nested_mv
2 /
P1_ROWID P2_ROWID ENAME SAL DNAME SUMSAL
------------------ ------------------ ---------- ---------- -------------- ----------
AAAWnXAAEAAABRrAAA AAAWnZAAEAAABRzAAB SMITH 800 RESEARCH 10875
AAAWnXAAEAAABRrAAB AAAWnZAAEAAABRzAAA ALLEN 1600 SALES 9400
AAAWnXAAEAAABRrAAC AAAWnZAAEAAABRzAAA WARD 1250 SALES 9400
AAAWnXAAEAAABRrAAD AAAWnZAAEAAABRzAAB JONES 2975 RESEARCH 10875
AAAWnXAAEAAABRrAAE AAAWnZAAEAAABRzAAA MARTIN 1250 SALES 9400
AAAWnXAAEAAABRrAAF AAAWnZAAEAAABRzAAA BLAKE 2850 SALES 9400
AAAWnXAAEAAABRrAAH AAAWnZAAEAAABRzAAB SCOTT 3000 RESEARCH 10875
AAAWnXAAEAAABRrAAJ AAAWnZAAEAAABRzAAA TURNER 1500 SALES 9400
AAAWnXAAEAAABRrAAK AAAWnZAAEAAABRzAAB ADAMS 1100 RESEARCH 10875
AAAWnXAAEAAABRrAAL AAAWnZAAEAAABRzAAA JAMES 950 SALES 9400
AAAWnXAAEAAABRrAAM AAAWnZAAEAAABRzAAB FORD 3000 RESEARCH 10875
AAAWnXAAEAAABRrAAG AAAWnZAAEAAABRzAAC CLARK 2450 ACCOUNTING 13750
AAAWnXAAEAAABRrAAN AAAWnZAAEAAABRzAAC MILLER 1300 ACCOUNTING 13750
AAAWnXAAEAAABRtAAA AAAWnZAAEAAABRzAAC KING 10000 ACCOUNTING 13750
14 rows selected.
1) All parent and base materialized views must contain joins or aggregates.
Let's replace the join MV, parent1_mv, by a basic MV. Note that I'll have to recreate the other MV's as well to avoid the "ORA-12034: materialized view log younger than last refresh" message. In real life with large tables you don't want to recreate everything from scratch when doing a little maintenance. In that case, use prebuilt tables so you can split the MV in a data part (the table) and a definition part (the MV). You can then recover from an ORA-12034 by just replacing the definition part. This is a useful link regarding this subject. Although it looks like the scripts at the end have not been tested, the ideas are sound. But I digress.
rwijk@ORA11GR1> update emp set sal = 5000 where ename = 'KING'
2 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> drop materialized view parent1_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> drop materialized view parent2_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> drop materialized view nested_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view parent1_mv
2 refresh fast on commit
3 as
4 select empno
5 , ename
6 , deptno
7 , sal
8 from emp
9 /
Materialized view created.
rwijk@ORA11GR1> create materialized view parent2_mv
2 refresh fast on commit
3 as
4 select deptno
5 , sum(sal) sumsal
6 , count(sal) countsal
7 , count(*) countall
8 from emp
9 group by deptno
10 /
Materialized view created.
rwijk@ORA11GR1> create materialized view log on parent1_mv with rowid
2 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view log on parent2_mv with rowid
2 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view nested_mv
2 refresh fast on commit
3 as
4 select p1.rowid p1_rowid
5 , p2.rowid p2_rowid
6 , p1.ename
7 , p1.sal
8 , p2.sumsal
9 from parent1_mv p1
10 , parent2_mv p2
11 where p1.deptno = p2.deptno
12 /
from parent1_mv p1
*
ERROR at line 9:
ORA-12053: this is not a valid nested materialized view
I'm not exactly fond of a "is not valid" error message, as it can be paraphrased more meaningful, but we've seen worse in this series.
Let's try the other possibility: a union all MV.
rwijk@ORA11GR1> drop materialized view parent1_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> drop materialized view parent2_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view parent1_mv
2 refresh fast on commit
3 as
4 select emp.rowid emp_rowid
5 , empno
6 , ename
7 , deptno
8 , sal
9 , 'E1' umarker
10 from emp
11 where deptno = 10
12 union all
13 select emp.rowid emp_rowid
14 , empno
15 , ename
16 , deptno
17 , sal
18 , 'E2' umarker
19 from emp
20 where deptno = 20
21 /
Materialized view created.
rwijk@ORA11GR1> create materialized view parent2_mv
2 refresh fast on commit
3 as
4 select deptno
5 , sum(sal) sumsal
6 , count(sal) countsal
7 , count(*) countall
8 from emp
9 group by deptno
10 /
Materialized view created.
rwijk@ORA11GR1> create materialized view log on parent1_mv with rowid
2 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view log on parent2_mv with rowid
2 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view nested_mv
2 refresh fast on commit
3 as
4 select p1.rowid p1_rowid
5 , p2.rowid p2_rowid
6 , p1.ename
7 , p1.sal
8 , p2.sumsal
9 from parent1_mv p1
10 , parent2_mv p2
11 where p1.deptno = p2.deptno
12 /
Materialized view created.
rwijk@ORA11GR1>
rwijk@ORA11GR1> select * from parent1_mv
2 /
EMP_ROWID EMPNO ENAME DEPTNO SAL UM
------------------ ---------- ---------- ---------- ---------- --
AAARhlAAEAAAAI3AAG 7782 CLARK 10 2450 E1
AAARhlAAEAAAAI3AAI 7839 KING 10 5000 E1
AAARhlAAEAAAAI3AAN 7934 MILLER 10 1300 E1
AAARhlAAEAAAAI3AAA 7369 SMITH 20 800 E2
AAARhlAAEAAAAI3AAD 7566 JONES 20 2975 E2
AAARhlAAEAAAAI3AAH 7788 SCOTT 20 3000 E2
AAARhlAAEAAAAI3AAK 7876 ADAMS 20 1100 E2
AAARhlAAEAAAAI3AAM 7902 FORD 20 3000 E2
8 rows selected.
rwijk@ORA11GR1> select * from nested_mv
2 /
P1_ROWID P2_ROWID ENAME SAL SUMSAL
------------------ ------------------ ---------- ---------- ----------
AAAWnoAAEAAABRrAAA AAAWnqAAEAAABRzAAC CLARK 2450 8750
AAAWnoAAEAAABRrAAB AAAWnqAAEAAABRzAAC KING 5000 8750
AAAWnoAAEAAABRrAAC AAAWnqAAEAAABRzAAC MILLER 1300 8750
AAAWnoAAEAAABRrAAD AAAWnqAAEAAABRzAAB SMITH 800 10875
AAAWnoAAEAAABRrAAE AAAWnqAAEAAABRzAAB JONES 2975 10875
AAAWnoAAEAAABRrAAF AAAWnqAAEAAABRzAAB SCOTT 3000 10875
AAAWnoAAEAAABRrAAG AAAWnqAAEAAABRzAAB ADAMS 1100 10875
AAAWnoAAEAAABRrAAH AAAWnqAAEAAABRzAAB FORD 3000 10875
8 rows selected.
rwijk@ORA11GR1> update emp set sal = 10000 where ename = 'KING'
2 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from parent1_mv
2 /
EMP_ROWID EMPNO ENAME DEPTNO SAL UM
------------------ ---------- ---------- ---------- ---------- --
AAARhlAAEAAAAI3AAG 7782 CLARK 10 2450 E1
AAARhlAAEAAAAI3AAN 7934 MILLER 10 1300 E1
AAARhlAAEAAAAI3AAA 7369 SMITH 20 800 E2
AAARhlAAEAAAAI3AAD 7566 JONES 20 2975 E2
AAARhlAAEAAAAI3AAH 7788 SCOTT 20 3000 E2
AAARhlAAEAAAAI3AAK 7876 ADAMS 20 1100 E2
AAARhlAAEAAAAI3AAM 7902 FORD 20 3000 E2
AAARhlAAEAAAAI3AAI 7839 KING 10 10000 E1
8 rows selected.
rwijk@ORA11GR1> select * from nested_mv
2 /
P1_ROWID P2_ROWID ENAME SAL SUMSAL
------------------ ------------------ ---------- ---------- ----------
AAAWnoAAEAAABRrAAD AAAWnqAAEAAABRzAAB SMITH 800 10875
AAAWnoAAEAAABRrAAE AAAWnqAAEAAABRzAAB JONES 2975 10875
AAAWnoAAEAAABRrAAF AAAWnqAAEAAABRzAAB SCOTT 3000 10875
AAAWnoAAEAAABRrAAG AAAWnqAAEAAABRzAAB ADAMS 1100 10875
AAAWnoAAEAAABRrAAH AAAWnqAAEAAABRzAAB FORD 3000 10875
AAAWnoAAEAAABRrAAA AAAWnqAAEAAABRzAAC CLARK 2450 13750
AAAWnoAAEAAABRrAAC AAAWnqAAEAAABRzAAC MILLER 1300 13750
AAAWnoAAEAAABRtAAA AAAWnqAAEAAABRzAAC KING 10000 13750
8 rows selected.
This scenario works. And parent1_mv is clearly neither a join MV nor an aggregate MV. So it looks like the Oracle documentation needs an update here.
When an underlying MV is more complex (join, aggregate, union all), then everything works, but when the underlying MV is simple, it breaks. Makes me wonder why. Does anyone have a clue? Or is it just Oracle saying to the developer that he made his MV design too complex, and he can do with one MV less?
2) All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log.
A rather obvious one, but for the record:
rwijk@ORA11GR1> update emp set sal = 5000 where ename = 'KING'And yes I know, if dropping the materialized view log would have worked this way, it would lead to an ORA-12034. But that's not the point here.
2 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> drop materialized view log on parent1_mv
2 /
Materialized view log dropped.
rwijk@ORA11GR1> update emp set sal = 10000 where ename = 'KING'
2 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
commit
*
ERROR at line 1:
ORA-23413: table "RWIJK"."PARENT1_MV" does not have a materialized view log
3) You cannot create both a materialized view and a prebuilt materialized view on the same table.
The documentation adds an example of what is meant here:
For example, If you have a table costs with a materialized view cost_mv based on it, you cannot then create a prebuilt materialized view on table costs. The result would make cost_mv a nested materialized view and this method of conversion is not supported.I simulated this situation by removing the previous setup and introducing an auxiliary table myemp, acting as the costs table above.
rwijk@ORA11GR1> drop materialized view nested_mvA nice clear error message.
2 /
Materialized view dropped.
rwijk@ORA11GR1> drop materialized view parent2_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> drop materialized view parent1_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create table myemp as select * from emp
2 /
Table created.
rwijk@ORA11GR1> alter table myemp add primary key (empno)
2 /
Table altered.
rwijk@ORA11GR1> create materialized view log on myemp with rowid
2 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view parent1_mv
2 refresh fast on commit
3 as
4 select emp.rowid emp_rowid
5 , dept.rowid dept_rowid
6 , emp.ename
7 , emp.deptno
8 , emp.sal
9 , dept.dname
10 from myemp emp
11 , dept
12 where emp.deptno = dept.deptno
13 /
Materialized view created.
rwijk@ORA11GR1> create materialized view myemp
2 on prebuilt table
3 refresh fast on commit
4 as
5 select empno
6 , ename
7 , deptno
8 , sal
9 from emp
10 /
create materialized view myemp
*
ERROR at line 1:
ORA-32334: cannot create prebuilt materialized view on a table already referenced
by a MV
In the next part I'll go through all previous situations again, and use the explain_mview procedure and mv_capabilities_table to see if this gives us more information than the error message itself.
Thanks Rob, this post of yours has saved my day today ... I hit the error "ORA-12053: this is not a valid nested materialized view" and google has sent me straight here.
ReplyDeleteI have followed your very same road - turning a simple MV into a UNION ALL one - problem solved. Thanks!
I am intrigued as well by this strange behaviour, that is, why "When an underlying MV is more complex (join, aggregate, union all), then everything works, but when the underlying MV is simple, it breaks"; quite strange, maybe Oracle tries to "merge" the simple MV (much like it merges regular view) and it fails. Or maybe, they have not simply implemented the simple case. It is just a shot in the dark, but ...