Previous post about aggregate MV's
The fourth part will discuss the restrictions and the accompanying error messages of union all MV's. There are only a few restrictions mentioned in the documentation here.
Again, I numbered them and edited them a little bit to contain only one restriction per item:1) The defining query must have the UNION ALL operator at the top level.
2) Each query block in the UNION ALL query must satisfy the requirements of a fast refreshable materialized view with aggregates or a fast refreshable materialized view with joins.
3) The SELECT list of each query must include a UNION ALL marker, and the UNION ALL column must have a distinct constant numeric or string value in each UNION ALL branch. Further, the marker column must appear in the same ordinal position in the SELECT list of each query block.
4) Outer joins are not supported for materialized views with UNION ALL.
5) Insert-only aggregate materialized view queries are not supported for materialized views with UNION ALL.
6) Remote tables are not supported for materialized views with UNION ALL.
7) The compatibility initialization parameter must be set to 9.2.0 or higher to create a fast refreshable materialized view with UNION ALL.
First things first: let's start with a working union all MV.
rwijk@ORA11GR1> create materialized view log on dept with rowid
2 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view log on emp with rowid
2 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select rowid rid
5 , empno nr
6 , ename name
7 , 'E' umarker
8 from emp
9 union all
10 select rowid
11 , deptno
12 , dname
13 , 'D'
14 from dept
15 /
Materialized view created.
rwijk@ORA11GR1> select * from empdept_mv
2 /
RID NR NAME U
------------------ ---------- -------------- -
AAARhlAAEAAAAI3AAA 7369 SMITH E
AAARhlAAEAAAAI3AAB 7499 ALLEN E
AAARhlAAEAAAAI3AAC 7521 WARD E
AAARhlAAEAAAAI3AAD 7566 JONES E
AAARhlAAEAAAAI3AAE 7654 MARTIN E
AAARhlAAEAAAAI3AAF 7698 BLAKE E
AAARhlAAEAAAAI3AAG 7782 CLARK E
AAARhlAAEAAAAI3AAH 7788 SCOTT E
AAARhlAAEAAAAI3AAI 7839 KING E
AAARhlAAEAAAAI3AAJ 7844 TURNER E
AAARhlAAEAAAAI3AAK 7876 ADAMS E
AAARhlAAEAAAAI3AAL 7900 JAMES E
AAARhlAAEAAAAI3AAM 7902 FORD E
AAARhlAAEAAAAI3AAN 7934 MILLER E
AAARhmAAEAAAAI/AAA 10 ACCOUNTING D
AAARhmAAEAAAAI/AAB 20 RESEARCH D
AAARhmAAEAAAAI/AAC 30 SALES D
AAARhmAAEAAAAI/AAD 40 OPERATIONS D
18 rows selected.
rwijk@ORA11GR1> insert into dept values (50,'IT','UTRECHT')
2 /
1 row created.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from empdept_mv
2 /
RID NR NAME U
------------------ ---------- -------------- -
AAARhlAAEAAAAI3AAA 7369 SMITH E
AAARhlAAEAAAAI3AAB 7499 ALLEN E
AAARhlAAEAAAAI3AAC 7521 WARD E
AAARhlAAEAAAAI3AAD 7566 JONES E
AAARhlAAEAAAAI3AAE 7654 MARTIN E
AAARhlAAEAAAAI3AAF 7698 BLAKE E
AAARhlAAEAAAAI3AAG 7782 CLARK E
AAARhlAAEAAAAI3AAH 7788 SCOTT E
AAARhlAAEAAAAI3AAI 7839 KING E
AAARhlAAEAAAAI3AAJ 7844 TURNER E
AAARhlAAEAAAAI3AAK 7876 ADAMS E
AAARhlAAEAAAAI3AAL 7900 JAMES E
AAARhlAAEAAAAI3AAM 7902 FORD E
AAARhlAAEAAAAI3AAN 7934 MILLER E
AAARhmAAEAAAAI/AAA 10 ACCOUNTING D
AAARhmAAEAAAAI/AAB 20 RESEARCH D
AAARhmAAEAAAAI/AAC 30 SALES D
AAARhmAAEAAAAI/AAD 40 OPERATIONS D
AAARhmAAEAAAAI+AAA 50 IT D
19 rows selected.
And now some variations on this theme per restriction.
1) The defining query must have the UNION ALL operator at the top level.
If the top level does nothing but selecting the columns of the inner query, then this restriction doesn't apply:
rwijk@ORA11GR1> drop materialized view empdept_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select rid
5 , nr
6 , name
7 , umarker
8 from ( select rowid rid
9 , empno nr
10 , ename name
11 , 'E' umarker
12 from emp
13 union all
14 select rowid
15 , deptno
16 , dname
17 , 'D'
18 from dept
19 )
20 /
Materialized view created.
rwijk@ORA11GR1> insert into dept values (50,'IT','UTRECHT')
2 /
1 row created.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from empdept_mv
2 /
RID NR NAME U
------------------ ---------- -------------- -
AAARhlAAEAAAAI3AAA 7369 SMITH E
AAARhlAAEAAAAI3AAB 7499 ALLEN E
AAARhlAAEAAAAI3AAC 7521 WARD E
AAARhlAAEAAAAI3AAD 7566 JONES E
AAARhlAAEAAAAI3AAE 7654 MARTIN E
AAARhlAAEAAAAI3AAF 7698 BLAKE E
AAARhlAAEAAAAI3AAG 7782 CLARK E
AAARhlAAEAAAAI3AAH 7788 SCOTT E
AAARhlAAEAAAAI3AAI 7839 KING E
AAARhlAAEAAAAI3AAJ 7844 TURNER E
AAARhlAAEAAAAI3AAK 7876 ADAMS E
AAARhlAAEAAAAI3AAL 7900 JAMES E
AAARhlAAEAAAAI3AAM 7902 FORD E
AAARhlAAEAAAAI3AAN 7934 MILLER E
AAARhmAAEAAAAI/AAA 10 ACCOUNTING D
AAARhmAAEAAAAI/AAB 20 RESEARCH D
AAARhmAAEAAAAI/AAC 30 SALES D
AAARhmAAEAAAAI/AAD 40 OPERATIONS D
AAARhmAAEAAAAI+AAB 50 IT D
19 rows selected.
And it works. But if I add a where clause to the MV query, the restriction becomes apparent:
rwijk@ORA11GR1> delete dept where deptno = 50
2 /
1 row deleted.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> drop materialized view empdept_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select rid
5 , nr
6 , name
7 , umarker
8 from ( select rowid rid
9 , empno nr
10 , ename name
11 , 'E' umarker
12 from emp
13 union all
14 select rowid
15 , deptno
16 , dname
17 , 'D'
18 from dept
19 )
20 where nr > 0
21 /
where nr > 0
*
ERROR at line 20:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
The error message is ORA-12054. That's the one we've been seeing many times before.
2) Each query block in the UNION ALL query must satisfy the requirements of a fast refreshable materialized view with aggregates or a fast refreshable materialized view with joins.
This one is pretty obvious. If a query block doesn't fulfill all requirements, then wrapping it inside a union all MV certainly won't make it fast refreshable. And so by covering all other restrictions, this particular restriction is covered as well.
3) The SELECT list of each query must include a UNION ALL marker, and the UNION ALL column must have a distinct constant numeric or string value in each UNION ALL branch. Further, the marker column must appear in the same ordinal position in the SELECT list of each query block.
Let's try to create a union all MV without a union all marker:
rwijk@ORA11GR1> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select rowid rid
5 , empno nr
6 , ename name
7 from emp
8 union all
9 select rowid
10 , deptno
11 , dname
12 from dept
13 /
from dept
*
ERROR at line 12:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
4) Outer joins are not supported for materialized views with UNION ALL.
This one is not always true:
rwijk@ORA11GR1> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select emp.rowid rid
5 , dept.rowid rid2
6 , emp.deptno no
7 , dept.dname name
8 , 'E' umarker
9 from dept
10 , emp
11 where dept.deptno = emp.deptno(+)
12 union all
13 select rowid
14 , null
15 , deptno
16 , dname
17 , 'D'
18 from dept
19 /
Materialized view created.
rwijk@ORA11GR1> select * from empdept_mv
2 /
RID RID2 NO NAME U
------------------ ------------------ ---------- -------------- -
AAARhlAAEAAAAI3AAA AAARhmAAEAAAAI/AAB 20 RESEARCH E
AAARhlAAEAAAAI3AAB AAARhmAAEAAAAI/AAC 30 SALES E
AAARhlAAEAAAAI3AAC AAARhmAAEAAAAI/AAC 30 SALES E
AAARhlAAEAAAAI3AAD AAARhmAAEAAAAI/AAB 20 RESEARCH E
AAARhlAAEAAAAI3AAE AAARhmAAEAAAAI/AAC 30 SALES E
AAARhlAAEAAAAI3AAF AAARhmAAEAAAAI/AAC 30 SALES E
AAARhlAAEAAAAI3AAG AAARhmAAEAAAAI/AAA 10 ACCOUNTING E
AAARhlAAEAAAAI3AAH AAARhmAAEAAAAI/AAB 20 RESEARCH E
AAARhlAAEAAAAI3AAI AAARhmAAEAAAAI/AAA 10 ACCOUNTING E
AAARhlAAEAAAAI3AAJ AAARhmAAEAAAAI/AAC 30 SALES E
AAARhlAAEAAAAI3AAK AAARhmAAEAAAAI/AAB 20 RESEARCH E
AAARhlAAEAAAAI3AAL AAARhmAAEAAAAI/AAC 30 SALES E
AAARhlAAEAAAAI3AAM AAARhmAAEAAAAI/AAB 20 RESEARCH E
AAARhlAAEAAAAI3AAN AAARhmAAEAAAAI/AAA 10 ACCOUNTING E
AAARhmAAEAAAAI/AAD OPERATIONS E
AAARhmAAEAAAAI/AAA 10 ACCOUNTING D
AAARhmAAEAAAAI/AAB 20 RESEARCH D
AAARhmAAEAAAAI/AAC 30 SALES D
AAARhmAAEAAAAI/AAD 40 OPERATIONS D
19 rows selected.
rwijk@ORA11GR1> insert into dept values (50,'IT','UTRECHT')
2 /
1 row created.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from empdept_mv
2 /
RID RID2 NO NAME U
------------------ ------------------ ---------- -------------- -
AAARhlAAEAAAAI3AAA AAARhmAAEAAAAI/AAB 20 RESEARCH E
AAARhlAAEAAAAI3AAB AAARhmAAEAAAAI/AAC 30 SALES E
AAARhlAAEAAAAI3AAC AAARhmAAEAAAAI/AAC 30 SALES E
AAARhlAAEAAAAI3AAD AAARhmAAEAAAAI/AAB 20 RESEARCH E
AAARhlAAEAAAAI3AAE AAARhmAAEAAAAI/AAC 30 SALES E
AAARhlAAEAAAAI3AAF AAARhmAAEAAAAI/AAC 30 SALES E
AAARhlAAEAAAAI3AAG AAARhmAAEAAAAI/AAA 10 ACCOUNTING E
AAARhlAAEAAAAI3AAH AAARhmAAEAAAAI/AAB 20 RESEARCH E
AAARhlAAEAAAAI3AAI AAARhmAAEAAAAI/AAA 10 ACCOUNTING E
AAARhlAAEAAAAI3AAJ AAARhmAAEAAAAI/AAC 30 SALES E
AAARhlAAEAAAAI3AAK AAARhmAAEAAAAI/AAB 20 RESEARCH E
AAARhlAAEAAAAI3AAL AAARhmAAEAAAAI/AAC 30 SALES E
AAARhlAAEAAAAI3AAM AAARhmAAEAAAAI/AAB 20 RESEARCH E
AAARhlAAEAAAAI3AAN AAARhmAAEAAAAI/AAA 10 ACCOUNTING E
AAARhmAAEAAAAI/AAD OPERATIONS E
AAARhmAAEAAAAI/AAA 10 ACCOUNTING D
AAARhmAAEAAAAI/AAB 20 RESEARCH D
AAARhmAAEAAAAI/AAC 30 SALES D
AAARhmAAEAAAAI/AAD 40 OPERATIONS D
AAARhmAAEAAAAI+AAB 50 IT D
AAARhmAAEAAAAI+AAB IT E
21 rows selected.
As shown, this union all MV with an outer join is fast refreshable here. I wouldn't rely on it, though.
5) Insert-only aggregate materialized view queries are not supported for materialized views with UNION ALL.
According to the documentation, an insert-only aggregate materialized view is a materialized view with one of the following:
* Materialized views with MIN or MAX aggregates
* Materialized views which have SUM(expr) but no COUNT(expr)
* Materialized views without COUNT(*)
And it is indeed not allowed:
rwijk@ORA11GR1> delete dept where deptno = 50
2 /
1 row deleted.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> drop materialized view empdept_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select max(rowid) rid
5 , max(empno) no
6 , max(ename) name
7 , 'E' umarker
8 from emp
9 union all
10 select rowid
11 , deptno
12 , dname
13 , 'D'
14 from dept
15 /
, 'D'
*
ERROR at line 13:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
6) Remote tables are not supported for materialized views with UNION ALL.
rwijk@ORA11GR1> create database link ora10
2 connect to rwijk
3 identified by rwijk
4 using 'ora10gr2'
5 /
Database link created.
rwijk@ORA11GR1> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select emp.rowid rid
5 , emp.empno no
6 , emp.ename name
7 , 'E' umarker
8 from emp@ora10
9 union all
10 select rowid
11 , deptno
12 , dname
13 , 'D'
14 from dept
15 /
from dept
*
ERROR at line 14:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
7) The compatibility initialization parameter must be set to 9.2.0 or higher to create a fast refreshable materialized view with UNION ALL.
According to the documentation here, I can only set the COMPATIBLE parameter to a value below 9.2.0 when the database itself is a 9iR2 database. I don't have such a database here with me, so I'm unable to test this one. I'd be happy to hear the error message from someone with a 9iR2 database who can test this one.
Next up are nested MV's.
Looking at your query you seem to have run it on 11 but on 9iR2 no chance
ReplyDeletecreate materialized view MV_CALL_SERVICE_TYPE
refresh fast on commit as
SELECT TB_ROW_ID,AREA,SITE,CDN_NAME,DESTINATION_KEY, DESTINATION,LOAD_DATE
FROM (
SELECT rowid tb_row_id ,
'DBT' area,
'MN' site,
campaign_name cdn_name,
campaign_name_key cdn,
campaign_name_key destination_key,
TO_CHAR (campaign_name_key) destination,
load_date
FROM opal_calls_codes op
UNION ALL
SELECT rowid tb_row_id,
area,
site,
cdn_name,
cdn cdn,
cdn destination_key,
TO_CHAR (cdn) destination,
load_date
FROM dcn_service_type