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
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
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:
That mimics your situation. First add the rowid's:
Still it cannot fast refresh, because of the ANSI joins. Converting to old-style outer join syntax:
And to prove that it works:
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.
WHERE
clause, just the joins; nor can haveCASE
/DECODE
statements inSELECT
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.