ORACLE : Materialized view not working when Using LEFT JOIN

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         20AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAB         30AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAC         30AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAD         20AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAE         30AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAF         30AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAG         10AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAH         20AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAI         10AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAJ         30AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAK         20AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAL         30AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAM         20AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAN         10AAARhmAAEAAAAI/AAD                            4015 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         20AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAB         30AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAC         30AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAD         20AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAE         30AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAF         30AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAG         10AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAH         20AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAI         10AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAJ         30AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAK         20AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAL         30AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAM         20AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAN         10AAARhmAAEAAAAI/AAD                            40AAARhmAAEAAAAI7AAA                            5016 rows selected.

The ANSI-join syntax restriction is mentioned in point 6 in this blogpost.

Regards,Rob.

Since this is an old post; no has mentioned complete solution.

1. The table that is outer joined should have a primary key as mentioned in Oracle doc.
2. The query should not have any other constraints i.e should not have any filter criteriain WHERE clause, just the joins; nor can have CASE/DECODE statements in SELECT clause; GROUP BY, SUM(), COUNT() and such are allowed, though.

In above sample example the query will work if a primary key is created on department table on dept id column.

Followed the following instructions to make DBMS_MVIEW.EXPLAIN_MVIEW work:http://www.sqlsnippets.com/en/topic-12884.html

Capable of:

REFRESH_COMPLETE

Not Capable of:

REFRESH_FAST

REFRESH_FAST_AFTER_INSERT
inline view or subquery in FROM list not supported for this type MV

REFRESH_FAST_AFTER_INSERT
inline view or subquery in FROM list not supported for this type MV

REFRESH_FAST_AFTER_INSERT
view or subquery in from list

REFRESH_FAST_AFTER_ONETAB_DML
see the reason why REFRESH_FAST_AFTER_INSERT is disabled

MV_REPORT

REFRESH_FAST_AFTER_ANY_DML
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled