Oracle

"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

[email protected]: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.
[email protected]:1> insert into emp values (0001,'ANAND','DBA',7839,sysdate,3500,null,20);
1 row created.
[email protected]: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
[email protected]: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
[email protected]:1> exec dbms_mview.refresh('MV_EMP');
PL/SQL procedure successfully completed.
[email protected]:1> select * from mlog$_emp;
no rows selected
[email protected]:1> select * from RUPD$_EMP;
no rows selected
[email protected]:1> update emp set empno=2 where empno=1;
1 row updated.
[email protected]:1> commit;
Commit complete.
[email protected]: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
[email protected]:1> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.
[email protected]: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

[email protected]:1> update emp set job='DataEng' where JOB='DBA';
1 row updated.
[email protected]:1> commit;
Commit complete.
[email protected]: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”.

Leave a Reply