If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

M. View Fast Refresh

All,

I am creating a Materialized view using joining of two tables. When trying for the FAST refresh, getting the message

Code:

SQL> exec dbms_mview.refresh('crew_log','F');
BEGIN dbms_mview.refresh('crew_log','F'); END;
*
ERROR at line 1:
ORA-12004: REFRESH FAST cannot be used for materialized view
"REPLDBA"."CREW_LOG"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

Originally my understanding was, FAST refresh is not possible on M. Views based complex queries ( M. Views with joins and functions). But please see the following link

Use the DBMS_MVIEW.EXPLAIN_MVIEW procedure to get a list of the capabilities of the MV -- it may point to a problem with the structure, or more likely to a bug in Oracle. Some of the fast refresh code seems to be a little ropy.

The use of primary key/rowid ought not to affect whether you can fast refresh or not, since it just controls whether the materialized view log stores changes against the rowid of the base table or the primary key. The use of primary key-based MV logs is more robust -- rowid-based logs get cleared if you move the table, and are really only there for backwards compatibility (although they're handy for DW fact tables that have no PK)