. It can look quite complex so I am presenting only the important elements here. In this post, I begin with only the basic elements.
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>
