Basic Replication -- 5 : REFRESH_METHOD : FAST or FORCE ?

In the previous blog post, I had a remark "We'll explore the implications of "REFRESH FAST" and just "REFRESH" alone in a subsequent blog post."

This is in the context of whether it is a FORCE or FAST that shows up as the REFRESH_METHOD.  A FORCE attempts a FAST and, if it can't do so (e.g. the Materialized View Log is not accessible), attempts a COMPLETE Refresh from all the rows of the Source Table.

Other than a MV Log being a requirement, there are constraints on which types of Materialized Views can do a FAST Refresh.

SQL> create materialized view mv_fast_not_possible
  2  refresh fast on demand
  3  as select id, data_element_2, sysdate
  4  from source_table
  5  /
as select id, data_element_2, sysdate
                              *
ERROR at line 3:
ORA-12015: cannot create a fast refresh materialized view from a complex query


SQL> !oerr ora 12015
12015, 00000, "cannot create a fast refresh materialized view from a complex query"
// *Cause:  Neither ROWIDs and nor primary key constraints are supported for
//          complex queries.
// *Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE
//          option or create a simple materialized view.

SQL>

Thus, a "complex" query -- here one that adds a SYSDATE column -- cannot use a FAST Refresh.
(For all the restrictions, see Paragraph "5.3.8.4 General Restrictions on Fast Refresh" in the 19c documentation. )

SQL> create materialized view mv_fast_not_possible
  2  refresh force on demand
  3  as select id, data_element_2, sysdate
  4  from source_table
  5  /

Materialized view created.

SQL> select refresh_mode, refresh_method, last_refresh_type
  2  from user_mviews
  3  where mview_name  = 'MV_FAST_NOT_POSSIBLE'
  4  /

REFRESH_M REFRESH_ LAST_REF
--------- -------- --------
DEMAND    FORCE    COMPLETE

SQL>
SQL> insert into source_table
  2  values (2000,'TwoThousand','NewTwoTh',sysdate);

1 row created.

SQL> select * from source_table order by date_col ;

        ID DATA_ELEMENT_1  DATA_ELEMENT_2  DATE_COL
---------- --------------- --------------- ---------
       101 First           One             18-AUG-19
       103 Third           Three           18-AUG-19
       104 Fourth          Updated         09-SEP-19
         5 Fifth           Five            16-SEP-19
         6 Sixth           TwoHundred      19-SEP-19
         7 Seventh         ThreeHundred    19-SEP-19
      2000 TwoThousand     NewTwoTh        25-SEP-19

7 rows selected.

SQL>
SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('MV_OF_SOURCE');

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('MV_2');

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('MV_FAST_NOT_POSSIBLE');

PL/SQL procedure successfully completed.

SQL>
SQL> select mview_name, refresh_mode,refresh_method,last_refresh_type, last_refresh_date
  2  from user_mviews
  3  order by last_refresh_date
  4  /

MVIEW_NAME            REFRESH_M REFRESH_ LAST_REF LAST_REFR
--------------------- --------- -------- -------- ---------
MV_OF_SOURCE          DEMAND    FAST     FAST     25-SEP-19
MV_2                  DEMAND    FORCE    FAST     25-SEP-19
MV_FAST_NOT_POSSIBLE  DEMAND    FORCE    COMPLETE 25-SEP-19

SQL>

MV_FAST_NOT_POSSIBLE will always undergo a COMPLETE Refresh using REFRESH_METHOD='FORCE'.

MV_2 has REFRESH_METHOD='FORCE' because it was created with "refresh on demand" with the "fast" keyword missing.  Nevertheless, it is a "simple" Materialized View so does a FAST Refresh.

MV_OF_SOURCE was created with "refresh fast on demand", so it is already configured as REFRESH_METHOD='FAST'