I want to create a MATERIALIZED VIEW from a LEFT JOIN of 2 tables. However the following gives me an error:
SELECT field1
FROM table_1 a
LEFT JOIN table_2 b
ON a.field1=b.field2
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
However the following works:
SELECT field1
FROM table_1 a, table_2 b
WHERE a.field1=b.field2
Does anyone have any ideas why this is happening.
Thx for the help
There are two conditions that are not satisfied to make that materialized view refresh fast. First one is that you did not specify the rowid columns of every table involved. And the second one is an undocumented restriction: ANSI-joins are not supported.
Here is an example with DEPT being table_1, alias a and EMP being table_2, alias b:
SQL> create materialized view log on emp with rowid
2 /
Materialized view log created.
SQL> create materialized view log on dept with rowid
2 /
Materialized view log created.
SQL> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select a.deptno
5 from dept a
6 left join emp b on (a.deptno = b.deptno)
7 /
from dept a
*
ERROR at line 5:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
That mimics your situation. First add the rowid's:
SQL> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select a.rowid dept_rowid
5 , b.rowid emp_rowid
6 , a.deptno
7 from dept a
8 left join emp b on (a.deptno = b.deptno)
9 /
from dept a
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Still it cannot fast refresh, because of the ANSI joins. Converting to old-style outer join syntax:
SQL> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select a.rowid dept_rowid
5 , b.rowid emp_rowid
6 , a.deptno
7 from dept a
8 , emp b
9 where a.deptno = b.deptno (+)
10 /
Materialized view created.
And to prove that it works:
SQL> select * from empdept_mv
2 /
DEPT_ROWID EMP_ROWID DEPTNO
------------------ ------------------ ----------
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAA 20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAB 30
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAC 30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAD 20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAE 30
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAF 30
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAG 10
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAH 20
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAI 10
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAJ 30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAK 20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAL 30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAM 20
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAN 10
AAARhmAAEAAAAI/AAD 40
15 rows selected.
SQL> insert into dept values (50,'IT','UTRECHT')
2 /
1 row created.
SQL> commit
2 /
Commit complete.
SQL> select * from empdept_mv
2 /
DEPT_ROWID EMP_ROWID DEPTNO
------------------ ------------------ ----------
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAA 20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAB 30
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAC 30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAD 20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAE 30
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAF 30
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAG 10
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAH 20
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAI 10
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAJ 30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAK 20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAL 30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAM 20
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAN 10
AAARhmAAEAAAAI/AAD 40
AAARhmAAEAAAAI7AAA 50
16 rows selected.
The ANSI-join syntax restriction is mentioned in point 6 in this blogpost.
Regards, Rob.