Basic Replication -- 2a : Elements for creating a Materialized View

The CREATE MATERIALIZED VIEW statement is documented here.  It can look quite complex so I am presenting only the important elements here.  In this post, I begin with only the basic elements.

(EDIT: These SQL operations, queries and results were in a 19c Database)

First, I recreate the SOURCE_TABLE properly, with a Primary Key :

SQL> drop table source_table;

Table dropped.

SQL> create table source_table
  2  (id  number not null,
  3   data_element_1 varchar2(15),
  4   data_element_2 varchar2(15),
  5   date_col date)
  6  /

Table created.

SQL> create unique index source_table_pk
  2  on source_table(id);

Index created.

SQL> alter table source_table
  2  add constraint source_table_pk
  3  primary key (id)
  4  /

Table altered.

SQL>

Then I create a Materialized View Log on SOURCE_TABLE.  This will capture all DML against this table and will be read by the target Materialized View to identify "changed" rows at every refresh.

SQL> create materialized view log on source_table;

Materialized view log created.

SQL>

I then identify the objects that were created.

SQL> select object_id, object_name, object_type
  2  from user_objects
  3  where created > sysdate-1
  4  order by object_id
  5  /

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -----------------------
     73055 SOURCE_TABLE                   TABLE
     73056 SOURCE_TABLE_PK                INDEX
     73057 MLOG$_SOURCE_TABLE             TABLE
     73058 RUPD$_SOURCE_TABLE             TABLE
     73059 I_MLOG$_SOURCE_TABLE           INDEX

SQL>
SQL> desc mlog$_source_table;
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ID                                                                                NUMBER
 SNAPTIME$$                                                                        DATE
 DMLTYPE$$                                                                         VARCHAR2(1)
 OLD_NEW$$                                                                         VARCHAR2(1)
 CHANGE_VECTOR$$                                                                   RAW(255)
 XID$$                                                                             NUMBER

SQL> desc rupd$_source_table;
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ID                                                                                NUMBER
 DMLTYPE$$                                                                         VARCHAR2(1)
 SNAPID                                                                            NUMBER(38)
 CHANGE_VECTOR$$                                                                   RAW(255)

SQL>

Interesting that the "CREATE MATERIAIZED VIEW LOG" statement created 3 database objects.

What happens after I perform DML on the SOURCE_TABLE ?

SQL> insert into source_table
  2  values (1,'First','One',sysdate);

1 row created.

SQL> insert into source_table
  2  values (2,'Second','Two',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> delete source_table
  2  where id=2
  3  /

1 row deleted.

SQL>
SQL> commit;

Commit complete.

SQL> select * from mlog$_source_table;

        ID SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
------------------------------------------------------------------------------------------------------------------------------------
     XID$$
----------
         1 01-JAN-00 I N
FE
2.8158E+14

         2 01-JAN-00 I N
FE
2.8158E+14

         2 01-JAN-00 D O
00
2.5334E+15


SQL>
SQL> select * from rupd$_source_table;

no rows selected

SQL>

So the MLOG$_SOURCE_TABLE is the log that captures 2 INSERT statements and 1 DELETE statement.  (OR is it 2 INSERT *rows* and 1 DELETE *row* ??)
We don't know what the RUPD$_SOURCE_TABLE captures yet.

Let me create a Materialized View and then query MLOG$_SOURCE_TABLE (which is the "MV Log")

SQL> create materialized view
  2  mv_of_source
  3  refresh fast on demand
  4  as select * from source_table
  5  /

Materialized view created.

SQL> select * from mv_of_source
  2  /

        ID DATA_ELEMENT_1  DATA_ELEMENT_2  DATE_COL
---------- --------------- --------------- ---------
         1 First           One             18-AUG-19

SQL>
SQL> select * from mlog$_source_table;

no rows selected

SQL>

So, the CREATE MATERIALIZED VIEW statement has also done a cleanup of the MV Log entries with a SNAPTIME$ older than when it was created.

Let me insert two new rows and then refresh the Materialized View and check the MV Log again.

SQL> insert into source_table
  2  values (3,'Third','Three',sysdate);

1 row created.

SQL> insert into source_table
  2  values (4,'Fourth','Four',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mlog$_source_table;

        ID SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
------------------------------------------------------------------------------------------------------------------------------------
     XID$$
----------
         3 01-JAN-00 I N
FE
1.6889E+15

         4 01-JAN-00 I N
FE
1.6889E+15


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

PL/SQL procedure successfully completed.

SQL> select * from mlog$_source_table;

no rows selected

SQL> select * from mv_of_source;

        ID DATA_ELEMENT_1  DATA_ELEMENT_2  DATE_COL
---------- --------------- --------------- ---------
         1 First           One             18-AUG-19
         3 Third           Three           18-AUG-19
         4 Fourth          Four            18-AUG-19

SQL>

So, the 2 single-row INSERTs did create two entries in the MV Log and the REFRESH of the Materialized View did a cleanup of those two entries.

I haven't yet explored :
a.  UPDATEs
b. Multi-Row Operations