“U” entries in MLOG$

Recently in a discussion with colleague, on what entries do MLOG$ have, the following was agreed to :-

1. “I” for insert
2. “D” for delete
3. “D” + “I” for update.

Along with the above entries, you might notice “U” also. The “U” entry is for update. But then, on what condition do we get “U” and when do we get “D” and “I” entries.

I have used “EMP” table as master table in the example

DBA@:1> CREATE MATERIALIZED VIEW "MV_EMP"
  ON PREBUILT TABLE WITHOUT REDUCED PRECISION
  USING INDEX
  REFRESH FAST ON DEMAND NEXT null
  WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS SELECT * from EMP;
 
Materialized view created.

DBA@:1> insert into emp values (0001,'ANAND','DBA',7839,sysdate,3500,null,20);
 
1 row created.
 
DBA@:1> select * from emp where empno=0001;
 
     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL	COMM	 DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
	 1 ANAND      DBA	      7839 2016.12.03 21:45:16	     3500		     20
 
col CHANGE_VECTOR$$ for a20
 
DBA@:1> select * from mlog$_emp;
 
     EMPNO SNAPTIME$$	       D O CHANGE_VECTOR$$	     XID$$
---------- ------------------- - - -------------------- ----------
	 1 4000.01.01 00:00:00 I N FEFF 		1.1259E+15

DBA@:1> exec dbms_mview.refresh('MV_EMP');
 
PL/SQL procedure successfully completed.

DBA@:1> select * from mlog$_emp;
 
no rows selected
 
DBA@:1> select * from RUPD$_EMP;
 
no rows selected

DBA@:1> update emp set empno=2 where empno=1;
 
1 row updated.
 
DBA@:1> commit;
 
Commit complete.
 
DBA@:1> select * from mlog$_emp;
 
     EMPNO SNAPTIME$$	       D O CHANGE_VECTOR$$	     XID$$
---------- ------------------- - - -------------------- ----------
	 1 4000.01.01 00:00:00 D O 0000 		4.2222E+15
	 2 4000.01.01 00:00:00 I N FFFF 		4.2222E+15

DBA@:1> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
 
Session altered.
 
DBA@:1> exec dbms_mview.refresh('MV_EMP');
 
PL/SQL procedure successfully completed.

bdj59v5mx6tx9
/* MV_REFRESH (MRG) */ MERGE INTO "PRAKANAN_DBA"."MV_EMP" "SNA$" USING (SELECT CURRENT$."EMPNO",CURRENT$."ENAME",CURRENT$."JOB",CURRENT$."MGR",CURRENT$."HIREDATE",CURRENT$."SAL",CURRENT$."COMM",CURREN
T$."DEPTNO" FROM (SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO" FROM
"EMP" "EMP") CURRENT$, (SELECT DISTINCT MLOG$."EMPNO" FROM "PRAKANAN_DBA"."MLOG$_EMP" MLOG$ WHERE "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."EMPNO" = LOG$."EMPNO")"AV$" ON ("SNA$
"."EMPNO" = "AV$"."EMPNO") WHEN MATCHED THEN UPDATE  SET "SNA$"."EMPNO" = "AV$"."EMPNO","SNA$"."ENAME" = "AV$"."ENAME","SNA$"."JOB" = "AV$"."JOB","SNA$"."MGR" = "AV$"."MGR","SNA$"."HIREDATE" = "AV$"."
HIREDATE","SNA$"."SAL" = "AV$"."SAL","SNA$"."COMM" = "AV$"."COMM","SNA$"."DEPTNO" = "AV$"."DEPTNO" WHEN NOT MATCHED THEN INSERT  (SNA$."EMPNO",SNA$."ENAME",SNA$."JOB",SNA$."MGR",SNA$."HIREDATE",SNA$."
SAL",SNA$."COMM",SNA$."DEPTNO") VALUES (AV$."EMPNO",AV$."ENAME",AV$."JOB",AV$."MGR",AV$."HIREDATE",AV$."SAL",AV$."COMM",AV$."DEPTNO")

In the above example, with an update, “D” and “I” entries are created.

Now, in which scenario, is “U” entry created :-

With the same table example, lets update a row

DBA@:1> update emp set job='DataEng' where JOB='DBA';

1 row updated.

DBA@:1> commit;

Commit complete.

DBA@:1> select * from MLOG$_EMP;

     EMPNO    SNAPTIME$$          D O CHANGE_VECTOR$$      XID$$
   ---------- ------------------- - - -------------------- ----------
            3 4000.01.01 00:00:00 U U 0800                 2.8148E+15

To conclude –

If you are updating a Primary Key column/Rowid column, you will see “D”(delete)+ “I”(Insert) on update(U). For rest of the cases, you will see just “U”.

Advertisements