Nested Materialized Views: Creation / Refresh Overview and ORA-12053

Doc ID: Note:212553.1 Type: BULLETIN
Last Revision Date: 13-NOV-2002 Status: PUBLISHED

PURPOSE
-------

To explain in details how to fast refresh a Nested Materialized View.


SCOPE & APPLICATION
-------------------

This bulletin is intended for DW developers and administrators as well as
support analysts.

Nested Materialized Views
-------------------------

The rapid increase of DW complexity has introduced the need to further
enhance the materialized views flexibility and functionality. A nested
materialized view is simply a materialized view that is based on another
materialized view, this allows more complex queries against the base
materialized view itself to be handled in the fastest response time.

While Nested materialized views have achieved the fastest possible response
time for complex DW queries, we still have restrictions with populating them.
Complete refresh works fine with nested materialized views but it is not
always acceptable especially in Tera byte DW environments, so the question
here ; can we fast refresh Nested materialized views and how?

Fast Refresh Guidelines
------------------------
In order to be able to fast refresh a nested materialized view it has to be
setup according to the following rules:

a) Use Rowid MV logs
b) Nested MV must be a combination of a join view and a single-table aggregate
c) Define a single-table aggregate materialized view on top of a materialized
join view
d) Nested materialized view cannot be a parent and a grandparent of some
table/object in the dependency tree.

Failing to meet any of the above conditions will result with ORA-12053 when
creating the nested materialized view:

Error: ORA-12053 (ORA-12053)
Text: This is not a valid nested materialized view


Troubleshooting Ora-12053
-------------------------
ORA-12053 is sometimes misleading because it has many causes , so even if you
get this error your MV still can be a valid one and fast refreshed as well if
you follow the 4 rules listed above.

The following example was tested in both 8.1.7 & 9.2 databases using Scott
demo tables:

SQL> desc emp
Name Null? Type
----------------------------------------- -------- --------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> desc dept
Name Null? Type
----------------------------------------- -------- --------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL> create materialized view log on emp with primary key,rowid;

Materialized view log created.

SQL> create materialized view log on dept with primary key,rowid;

Materialized view log created.
-- create the first MV as join MV, Rowid columns were added to enable
-- fast refresh on a join view...
SQL> create materialized view empmv1
refresh fast on demand
with primary key
as select e.empno,d.deptno,e.ename,d.dname, e.rowid erowid,d.rowid drowid
from emp e, dept d
where e.deptno=d.deptno
/
Materialized view created.

SQL> alter table empmv1 add primary key(empno);

Materialized view altered.

-- create materialized view log for the empmv1:
sql> create materialized view log on empmv1 with primary key,
rowid(deptno)
including new values;

-- now create the nested materialized view empmv2:

SQL> create materialized view empmv2
refresh fast on demand
with primary key
as select empno,deptno
from empmv1
/
from empmv1
*
ERROR at line 5:
ORA-12053: this is not a valid nested materialized view

-- we are missing one rule here , empmv2 has to be a single-table aggregate..

SQL> create materialized view empmv2
refresh fast on demand
with primary key
as select empno,deptno , count(*)
from empmv1
group by empno,deptno
/

Materialized view created.

9.2 Nested Materialized Views Enhancement
------------------------------------------
In Oracle 9.2 we are able to use any combination of single-table aggregates
and join views , in other words rule C above is waved.

Working Example:

-- create demo tables in Scott schema in 9.2 database and run the following ..

SQL> alter table emp add primary key(empno);

Table altered.

SQL> alter table dept add primary key(deptno);

Table altered.

SQL> create materialized view log on dept
with primary key,
rowid(dname)
including new values;

Materialized view log created.

SQL> create materialized view log on emp
with primary key,
rowid(deptno,sal)
including new values;

Materialized view log created.

SQL> create materialized view deptmv1
refresh fast on demand
with primary key
as select empno,deptno , count(sal) cnt_sal from emp
group by empno,deptno
/

Materialized view created.

SQL> alter table deptmv1 add primary key(empno);

Table altered.

SQL> create materialized view log on deptmv1 with primary key,
rowid(deptno,cnt_sal) including new values
/

Materialized view log created.

SQL> create materialized view deptmv2
refresh fast on demand
with primary key
as select empno,cnt_sal,dname,e.rowid erowid,d.rowid drowid
from deptmv1 e, dept d
where e.deptno=d.deptno
/

Materialized view created.

-- While running the above example in 8.1.7 database or 9.0.1 database will
-- result with ora-12053: this is not a valid nested materialized view .


Restrictions When Using Nested Materialized Views
--------------------------------------------------

1. Nested materialized views has to be explicitly refreshed . For example you
need to explicitly refresh Empmv1 first then Empmv2.
2. Fast refresh for ON COMMIT is not supported for a higher-level
materialized view that contains joins and aggregates.

RELATED DOCUMENTS
-----------------

Oracle8i Data Warehousing Guide.
Oracle9i Data warehousing Guide , release 9.2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50261/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/193161/viewspace-50261/