Previous post about join MV's
In the third part I'm going to examine all restrictions for aggregate materialized views, as described in the documentation.
For convenience of this post, I have numbered and slightly rearranged them to contain one restriction per number:1) All restrictions from "General Restrictions on Fast Refresh".
As mentioned in the first blogpost in this series, there is an extra restriction regarding aggregate MV's:
2) All tables in the materialized view must have materialized view logs
3) The materialized view logs must contain all columns from the table referenced in the materialized view.
4) None of the columns in the base table, referred to in the materialized view log, can be encrypted.
5) All tables in the materialized view must have materialized view logs specified with ROWID
6) All tables in the materialized view must have materialized view logs specified with INCLUDING NEW VALUES.
7) All tables in the materialized view must have materialized view logs, specified with the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.
8) Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.
9) COUNT(*) must be specified.
10) Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.
11) For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present. Oracle recommends that SUM(expr) be specified.
12) If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified.
13) The SELECT list must contain all GROUP BY columns.
14) If the materialized view has MIN or MAX aggregates, then fast refresh is supported only on conventional DML inserts and direct loads.
15) If the materialized view has SUM(expr) but no COUNT(expr), then fast refresh is supported only on conventional DML inserts and direct loads.
16) If the materialized view has no COUNT(*), then fast refresh is supported only on conventional DML inserts and direct loads.
17) A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause.
18) Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged.
19) For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable.
20) For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".
21) If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause.
22) The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view.
23) Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified.
24) Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided unique constraints exist on the join columns of the inner join table.
25) If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.26) It cannot contain a HAVING clause with a subquery.
So this will be quite a lengthy and even tedious post, as you can imagine by the list above ... but for a good cause :-)
First, let's start with a base situation containing an aggregate MV:
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
2 with rowid (deptno) including new values
3 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , count(*) cnt
6 from myemp
7 group by deptno
8 /
Materialized view created.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO CNT
---------- ----------
30 6
20 5
10 3
3 rows selected.
And to prove that the MV is fast refreshed on commit:
rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
2 values (7777,'VAN WIJK','JANITOR',500,10)
3 /
1 row created.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO CNT
---------- ----------
30 6
20 5
10 4
3 rows selected.
1) All restrictions from "General Restrictions on Fast Refresh".
These have been examined in part one.
2) All tables in the materialized view must have materialized view logs
This one is quite obvious, but I'm still executing it anyway to collect the error message.
rwijk@ORA11GR1> delete myemp where empno = 7777
2 /
1 row deleted.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> drop materialized view emp_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> drop materialized view log on myemp
2 /
Materialized view log dropped.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , count(*) cnt
6 from myemp
7 group by deptno
8 /
from myemp
*
ERROR at line 6:
ORA-23413: table "RWIJK"."MYEMP" does not have a materialized view log
The error message is clear as to what you should be doing to resolve this situation.
3) The materialized view logs must contain all columns from the table referenced in the materialized view.
To test this one, I create the materialized view log without the deptno column:
rwijk@ORA11GR1> create materialized view log on myemp
2 with rowid including new values
3 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , count(*) cnt
6 from myemp
7 group by deptno
8 /
from myemp
*
ERROR at line 6:
ORA-12033: cannot use filter columns from materialized view log on
"RWIJK"."MYEMP"
And the materialized view does not get created, and with quite a clear error message, although mentioning a missing column would be convenient.
4) None of the columns in the base table, referred to in the materialized view log, can be encrypted.
I never did encryption of columns before, but it's actually quite easy to setup. First I add this line to my sqlnet.ora to specify the location of my wallet file:
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\oracle\product\11.1.0\mydb2\NETWORK\ADMIN\)))
Then I set the encryption key once. For subsequent runs it is not needed to set anymore, so I commented it out. The step creates a file called "ewallet.p12" in the location specified in the previous step.
rwijk@ORA11GR1> --alter system set encryption key identified by rwijk
rwijk@ORA11GR1> --/
Next action is to open the encryption wallet and encrypt the deptno column:
rwijk@ORA11GR1> alter system set encryption wallet open identified by rwijk
2 /
System altered.
rwijk@ORA11GR1> alter table myemp modify (deptno encrypt)
2 /
Table altered.
Now everything is ready to do the test:
rwijk@ORA11GR1> drop materialized view log on myemp
2 /
Materialized view log dropped.
rwijk@ORA11GR1> create materialized view log on myemp
2 with rowid (deptno) including new values
3 /
create materialized view log on myemp
*
ERROR at line 1:
ORA-32412: encrypted column "DEPTNO" not allowed in the materialized view log
rwijk@ORA11GR1> alter system set encryption wallet close
2 /
System altered.
The error message cannot be more clear.
5) All tables in the materialized view must have materialized view logs specified with ROWID.
rwijk@ORA11GR1> drop table myemp purge
2 /
Table 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
2 with sequence (deptno) including new values
3 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , count(*) cnt
6 from myemp
7 group by deptno
8 /
from myemp
*
ERROR at line 6:
ORA-12032: cannot use rowid column from materialized view log on
"RWIJK"."MYEMP"
Clear.
6) All tables in the materialized view must have materialized view logs specified with INCLUDING NEW VALUES.
rwijk@ORA11GR1> drop materialized view log on myemp
2 /
Materialized view log dropped.
rwijk@ORA11GR1> create materialized view log on myemp
2 with rowid (deptno)
3 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , count(*) cnt
6 from myemp
7 group by deptno
8 /
from myemp
*
ERROR at line 6:
ORA-32401: materialized view log on "RWIJK"."MYEMP" does not have new values
Again clear.
7) All tables in the materialized view must have materialized view logs, specified with the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.
I cannot reproduce this one, although I must admit that I did try, but not too hard. For a simple case it just works:
rwijk@ORA11GR1> drop materialized view log on myemp
2 /
Materialized view log dropped.
rwijk@ORA11GR1> create materialized view log on myemp
2 with rowid (deptno) including new values
3 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , count(*) cnt
6 from myemp
7 group by deptno
8 /
Materialized view created.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO CNT
---------- ----------
30 6
20 5
10 3
3 rows selected.
rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
2 values (7777,'VAN WIJK','JANITOR',500,10)
3 /
1 row created.
rwijk@ORA11GR1> delete myemp
2 where empno = 7777
3 /
1 row deleted.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO CNT
---------- ----------
30 6
20 5
10 3
3 rows selected.
But you'd better specify the sequence clause in production code, after you've been warned by the documentation.
8) Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.
Let's try another aggregate function: the MAX...KEEP...FIRST/LAST one.
rwijk@ORA11GR1> drop table myemp purge
2 /
Table dropped.
rwijk@ORA11GR1> drop materialized view emp_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
2 with rowid (deptno,sal,hiredate) including new values
3 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , max(sal) keep (dense_rank first order by hiredate) sal_first_hired_emp
6 , count(*) cnt
7 from myemp
8 group by deptno
9 /
from myemp
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
We've seen the ORA-12054 before...
9) COUNT(*) must be specified.
This one doesn't reproduce for simple examples:
rwijk@ORA11GR1> drop materialized view log on myemp
2 /
Materialized view log dropped.
rwijk@ORA11GR1> create materialized view log on myemp
2 with rowid (deptno,sal) including new values
3 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , sum(sal) sumsal
6 from myemp
7 group by deptno
8 /
Materialized view created.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO SUMSAL
---------- ----------
30 9400
20 10875
10 8750
3 rows selected.
rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
2 values (7777,'VAN WIJK','JANITOR',500,10)
3 /
1 row created.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO SUMSAL
---------- ----------
30 9400
20 10875
10 9250
3 rows selected.
It simply works, but again: I'd still specify count(*) in production code.
10) Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.
rwijk@ORA11GR1> delete myemp where empno = 7777
2 /
1 row deleted.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> drop materialized view emp_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , avg(sal) + avg(sal) twice_avg_sal
6 , count(*) cnt
7 from myemp
8 group by deptno
9 /
from myemp
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Again, an ORA-12054.
11) For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present. Oracle recommends that SUM(expr) be specified.
For the below materialized view, COUNT(sal) must be present according to this restriction, but:
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , sum(sal) sumsal
6 , count(*) cnt
7 from myemp
8 group by deptno
9 /
Materialized view created.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO SUMSAL CNT
---------- ---------- ----------
30 9400 6
20 10875 5
10 8750 3
3 rows selected.
rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
2 values (7777,'VAN WIJK','JANITOR',500,10)
3 /
1 row created.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO SUMSAL CNT
---------- ---------- ----------
30 9400 6
20 10875 5
10 9250 4
3 rows selected.
Again it just works and again I'd still add the COUNT(expr) anyway.
12) If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified.
An example where I don't specify the SUM(sal):
rwijk@ORA11GR1> delete myemp where empno = 7777
2 /
1 row deleted.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> drop materialized view emp_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , variance(sal) varsal
6 , count(sal) cntsal
7 , count(*) cnt
8 from myemp
9 group by deptno
10 /
from myemp
*
ERROR at line 8:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
And again the multifunctional ORA-12054.
13) The SELECT list must contain all GROUP BY columns.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select count(*) cnt
5 from myemp
6 group by deptno
7 /
from myemp
*
ERROR at line 5:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Again, ORA-12054.
14) If the materialized view has MIN or MAX aggregates, then fast refresh is supported only on conventional DML inserts and direct loads.
Let's try an update, as that should be impossible with aggregate MV's containing a MIN or MAX:
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , max(sal) maxsal
6 , count(sal) cntsal
7 , count(*) cnt
8 from myemp
9 group by deptno
10 /
Materialized view created.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO MAXSAL CNTSAL CNT
---------- ---------- ---------- ----------
30 2850 6 6
20 3000 5 5
10 5000 3 3
3 rows selected.
rwijk@ORA11GR1> update myemp
2 set sal = 0
3 where ename = 'KING'
4 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO MAXSAL CNTSAL CNT
---------- ---------- ---------- ----------
30 2850 6 6
20 3000 5 5
10 2450 3 3
3 rows selected.
And it works, but when you use it and it doesn't work, Oracle can say that you've been warned by the documentation :-)
15) If the materialized view has SUM(expr) but no COUNT(expr), then fast refresh is supported only on conventional DML inserts and direct loads.
This one contains SUM(sal), but no COUNT(sal):
rwijk@ORA11GR1> update myemp
2 set sal = 5000
3 where ename = 'KING'
4 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> drop materialized view emp_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , sum(sal) sumsal
6 , count(*) cnt
7 from myemp
8 group by deptno
9 /
Materialized view created.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO SUMSAL CNT
---------- ---------- ----------
30 9400 6
20 10875 5
10 8750 3
3 rows selected.
rwijk@ORA11GR1> update myemp
2 set sal = 0
3 where ename = 'KING'
4 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO SUMSAL CNT
---------- ---------- ----------
30 9400 6
20 10875 5
10 8750 3
3 rows selected.
A nice example of a phenomenon I've experience several times myself: the materialized view is created successfully, but when testing the code, it simply doesn't refresh.
16) If the materialized view has no COUNT(*), then fast refresh is supported only on conventional DML inserts and direct loads.
The same phenomenon as in number 15:
rwijk@ORA11GR1> update myemp
2 set sal = 5000
3 where ename = 'KING'
4 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> drop materialized view emp_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , sum(sal) sumsal
6 , count(sal) cntsal
7 from myemp
8 group by deptno
9 /
Materialized view created.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO SUMSAL CNTSAL
---------- ---------- ----------
30 9400 6
20 10875 5
10 8750 3
3 rows selected.
rwijk@ORA11GR1> update myemp
2 set sal = 0
3 where ename = 'KING'
4 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO SUMSAL CNTSAL
---------- ---------- ----------
30 9400 6
20 10875 5
10 8750 3
3 rows selected.
No error message and no refresh.
17) A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause.
Another example of no error message and no refresh:
rwijk@ORA11GR1> update myemp
2 set sal = 5000
3 where ename = 'KING'
4 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> drop materialized view emp_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , max(sal) maxsal
6 , count(sal) cntsal
7 , count(*) cnt
8 from myemp
9 where sal > 1000
10 group by deptno
11 /
Materialized view created.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO MAXSAL CNTSAL CNT
---------- ---------- ---------- ----------
30 2850 5 5
20 3000 4 4
10 5000 3 3
3 rows selected.
rwijk@ORA11GR1> delete myemp
2 where ename = 'KING'
3 /
1 row deleted.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from emp_mv
2 /
DEPTNO MAXSAL CNTSAL CNT
---------- ---------- ---------- ----------
30 2850 5 5
20 3000 4 4
10 5000 3 3
3 rows selected.
18) For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable.
Deliberately forgetting the GROUPING or GROUPING_ID functions:
rwijk@ORA11GR1> drop table myemp purge
2 /
Table 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
2 with rowid (deptno,sal) including new values
3 /
Materialized view log created.
rwijk@ORA11GR1> drop materialized view emp_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , sal
6 , count(*) cnt
7 from myemp
8 group by cube(deptno,sal)
9 /
from myemp
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
19) For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".
Let's create a duplicate grouping set containing deptno.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , sal
6 , grouping_id(deptno,sal)
7 , count(*) cnt
8 from myemp
9 group by grouping sets(deptno,deptno,sal)
10 /
from myemp
*
ERROR at line 8:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
20) If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause.
I'm not sure how to test this one. It is not really a restriction, it just says that there is no limit on the number of joins and selected column if no outer join is used in the underlying query.
21) The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select e.deptno + d.deptno
5 , count(*) cnt
6 from myemp e
7 , mydept d
8 where e.deptno = d.deptno
9 group by e.deptno
10 , d.deptno
11 /
from myemp e
*
ERROR at line 6:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
22) Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified.
First, let's remove department 10 and then create the MV with an outer join.
rwijk@ORA11GR1> delete mydept
2 where deptno = 10
3 /
1 row deleted.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select e.deptno emp_deptno
5 , d.deptno dept_deptno
6 , count(*) cnt
7 from myemp e
8 , mydept d
9 where e.deptno = d.deptno (+)
10 group by e.deptno
11 , d.deptno
12 /
Materialized view created.
rwijk@ORA11GR1> select * from emp_mv
2 /
EMP_DEPTNO DEPT_DEPTNO CNT
---------- ----------- ----------
10 3
30 30 6
20 20 5
3 rows selected.
And now insert a record in the outer table.
rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
2 values (7777,'VAN WIJK','JANITOR',500,10)
3 /
1 row created.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from emp_mv
2 /
EMP_DEPTNO DEPT_DEPTNO CNT
---------- ----------- ----------
10 4
30 30 6
20 20 5
3 rows selected.
This works. But a modification on the inner table should not work:
rwijk@ORA11GR1> update mydept
2 set deptno = 50
3 where deptno = 20
4 /
1 row updated.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from emp_mv
2 /
EMP_DEPTNO DEPT_DEPTNO CNT
---------- ----------- ----------
10 4
30 30 6
20 20 5
3 rows selected.
And indeed, it doesn't work. And we were not warned by an error message. And the view log is empty:
rwijk@ORA11GR1> select * from mlog$_mydept
2 /
no rows selected
And this is how the materialized view should look like if it was allowed:
rwijk@ORA11GR1> select e.deptno emp_deptno
2 , d.deptno dept_deptno
3 , count(*) cnt
4 from myemp e
5 , mydept d
6 where e.deptno = d.deptno (+)
7 group by e.deptno
8 , d.deptno
9 /
EMP_DEPTNO DEPT_DEPTNO CNT
---------- ----------- ----------
20 5
10 4
30 30 6
3 rows selected.
23) Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided unique constraints exist on the join columns of the inner join table.
Let's drop the primary key of mydept.
rwijk@ORA11GR1> alter table mydept drop constraint mydept_pk
2 /
Table altered.
rwijk@ORA11GR1> select * from emp_mv
2 /
EMP_DEPTNO DEPT_DEPTNO CNT
---------- ----------- ----------
10 4
30 30 6
20 20 5
3 rows selected.
rwijk@ORA11GR1> delete myemp
2 where empno = 7777
3 /
1 row deleted.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from emp_mv
2 /
EMP_DEPTNO DEPT_DEPTNO CNT
---------- ----------- ----------
10 4
30 30 6
20 20 5
3 rows selected.
No change in the materialized view. The change is still pending in the materialized view log:
rwijk@ORA11GR1> select * from mlog$_myemp
2 /
DEPTNO SAL
---------- ----------
M_ROW$$
--------------------------------------------------------------------------------
SNAPTIME$$ D O
------------------- - -
CHANGE_VECTOR$$
--------------------------------------------------------------------------------
10 500
AAATX0AAEAAAALWAAB
01-01-4000 00:00:00 D O
0000
1 row selected.
And the count for department 10 should now be 3, as can be seen by executing the query that's behind the materialized view:
rwijk@ORA11GR1> select e.deptno emp_deptno
2 , d.deptno dept_deptno
3 , count(*) cnt
4 from myemp e
5 , mydept d
6 where e.deptno = d.deptno (+)
7 group by e.deptno
8 , d.deptno
9 /
EMP_DEPTNO DEPT_DEPTNO CNT
---------- ----------- ----------
20 5
10 3
30 30 6
3 rows selected.
So no error message, but it just doesn't refresh.
24) If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.
First recreate the primary key on mydept and then change the where clause from "e.deptno = d.deptno (+)" to "e.deptno between d.deptno (+) and 100". Functionally the same, as all deptno's are less than 100.
rwijk@ORA11GR1> alter table mydept add constraint mydept_pk primary key (deptno)
2 /
Table altered.
rwijk@ORA11GR1> drop materialized view emp_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select e.deptno emp_deptno
5 , d.deptno dept_deptno
6 , count(*) cnt
7 from myemp e
8 , mydept d
9 where e.deptno between d.deptno (+) and 100
10 group by e.deptno
11 , d.deptno
12 /
where e.deptno between d.deptno (+) and 100
*
ERROR at line 9:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
25) Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged.
Before showing the case where the view cannot be merged, first a case where the view can be merged:
rwijk@ORA11GR1> drop table mydept purge
2 /
Table dropped.
rwijk@ORA11GR1> create table mydept as select * from dept
2 /
Table created.
rwijk@ORA11GR1> alter table mydept add constraint mydept_pk primary key (deptno)
2 /
Table altered.
rwijk@ORA11GR1> create materialized view log on mydept
2 with rowid (deptno) including new values
3 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select e.deptno emp_deptno
5 , d.deptno dept_deptno
6 , count(*) cnt
7 from myemp e
8 , (select deptno from mydept) d
9 where e.deptno = d.deptno
10 group by e.deptno
11 , d.deptno
12 /
Materialized view created.
rwijk@ORA11GR1> select * from emp_mv
2 /
EMP_DEPTNO DEPT_DEPTNO CNT
---------- ----------- ----------
10 10 3
30 30 6
20 20 5
3 rows selected.
rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
2 values (7777,'VAN WIJK','JANITOR',500,10)
3 /
1 row created.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> select * from emp_mv
2 /
EMP_DEPTNO DEPT_DEPTNO CNT
---------- ----------- ----------
10 10 4
30 30 6
20 20 5
3 rows selected.
And now I add a dummy group by clause. It doesn't change the result of the query, but the view is not mergeable anymore.
rwijk@ORA11GR1> drop materialized view emp_mv
2 /
Materialized view dropped.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select e.deptno emp_deptno
5 , d.deptno dept_deptno
6 , count(*) cnt
7 from myemp e
8 , (select deptno from mydept group by deptno) d
9 where e.deptno = d.deptno
10 group by e.deptno
11 , d.deptno
12 /
from myemp e
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
26) It cannot contain a HAVING clause with a subquery.
rwijk@ORA11GR1> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno
5 , count(*) cnt
6 from myemp
7 group by deptno
8 having avg(sal) > (select 1000 from dual)
9 /
from myemp
*
ERROR at line 6:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
And with number 26, all documented restrictions on aggregate MV's are examined. Next up are union all MV's.
Very enlightening and helpful.
ReplyDeletefor me this post is giving a good start from many confusing details and adding CLARITY. Thank you.
This is best post I have ever seen for all the MV realted errors. Thank you making it simple and perfect.
ReplyDeleteThanks,
Sourav