In an Oracle database, a materialized view log is a table associated with the master table of a materialized view. When master table data undergoes DML changes (such as INSERT, UPDATE, or DELETE), the Oracle database stores rows describing those changes in the materialized view log. A materialized view log is similar to an AUDIT table and is the mechanism used to capture changes made to its related master table. Rows are automatically added to the Materialized View Log table when the master table changes. The Oracle database uses the materialized view log to refresh materialized views based on the master table. This process is called fast refresh and improves performance in the source database.
A materialized view log can capture the primary keys, row IDs, or object identifiers of rows that have been updated in the master table. The standard naming convention for a materialized view log table
Bug 14215010 ORA-942 on DMLs after an illegal drop of a MV log (if fix 13863326 present)
This bug is only relevant when using Query Rewrite (Including Materialized Views)
This problem is caused by the fix for bug 13863326.
If a materialized view log table is dropped by a different method than
"DROP MATERIALIZED VIEW LOG"
or
"DROP MATERIALIZED VIEW LOG ON T ;"
it can cause DMLs over the source table to raise ORA-942 errors.
Once this error is hit, the corruption already exists. The fix for this bug prevents the
illegal drop of a materialized view log in the future but will not correct existing
dictionary issues.
Workaround
To remove the dictionary inconsistency caused by this bug, connect as the schema owner
of the materialized view log, and issue the correct drop statement:
eg: drop materialized view log on <master_table>;
Note:
For interim patches please use this fix instead of 13863326 to address both issues.
Get all materialized views
select * from user_mviews
Get latest refresh times for all materialized views
select * from user_mview_refresh_times
Get information on a log
select count(*) from mlog$_MyTable;
Get the list of all materialized views on a view log
SELECT master, owner, NAME, snapshot_site,
TO_CHAR(current_snapshots,'mm/dd/yyyy hh24:mi') current_snapshots
FROM user_registered_snapshots, user_snapshot_logs
WHERE user_registered_snapshots.snapshot_id = user_snapshot_logs.snapshot_id (+)
complete refreshes of materialized views can be expensive operations. Fortunately there is a way to refresh only the changed rows in a materialized view's base table. This is called fast refreshing. Before a materialized view can perform a fast refresh however it needs a mechanism to capture any changes made to its base table. This mechanism is called a Materialized View Log. We can create a materialized view log on our test table, T, like this.
describe T
Name Null? Type
-------------------------------------------- -------- ------------------------------
KEY NOT NULL NUMBER
VAL VARCHAR2(5)
create materialized view log on t ;
Note how the materialized view log is not given a name. This is because a table can only ever have one materialized view log related to it at a time, so a name is not required.
To see what a materialized view log looks like we can examine the table used to implement it. In practice developers other than Dizwell never actually need to reference this table, but showing it here helps illustrate materialized view log behaviour.
describe MLOG$_T
Name Null? Type
-------------------------------------------- -------- ------------------------------
KEY NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
The MLOG$_T.KEY column mirrors the base table's primary key column T.KEY. The other MLOG$ columns are system generated
select * from MLOG$_T ;
no rows selected
The query above shows that a materialized view log is initially empty upon creation. Rows are automatically added to MLOG$_T when base table T is changed.
UPDATE t set val = upper( val ) where KEY = 1 ;
INSERT into t ( KEY, val ) values ( 5, 'e' );
column dmltype$$ format a10
select key, dmltype$$ from MLOG$_T ;
KEY DMLTYPE$$
---------- ----------
1 U
5 I
If the changes affecting T are rolled back, so are the changes to MLOG$_T.
rollback ;
Rollback complete.
select key, dmltype$$ from MLOG$_T ;
no rows selected
WITH PRIMARY KEY
To include the base table's primary key column in a materialized view log the WITH PRIMARY KEY clause can be specified.
drop materialized view log on t ;
create materialized view log on t WITH PRIMARY KEY ;
desc mlog$_t
Name Null? Type
-------------------------------------------- -------- ------------------------------
KEY NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
Note how MLOG$_T contains T's primary key column, T.KEY. This materialized view log is equivalent to the one created earlier in this topic, which did not have a WITH clause, because WITH PRIMARY KEY is the default option when no WITH clause is specified.
WITH ROWID
To include rowids instead of primary keys WITH ROWID can be specified.
drop materialized view log on t ;
create materialized view log on t WITH ROWID ;
desc mlog$_t
Name Null? Type
-------------------------------------------- -------- ------------------------------
M_ROW$$ VARCHAR2(255)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
Note how the KEY column was replaced by the M_ROW$$ column, which contains rowids from table T. A materialized view log can also be created with both a rowid and a primary key column.
drop materialized view log on t ;
create materialized view log on t WITH ROWID, PRIMARY KEY ;
desc mlog$_t
Name Null? Type
-------------------------------------------- -------- ------------------------------
KEY NUMBER
M_ROW$$ VARCHAR2(255)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
In this case both KEY and M_ROW$$ appear in the log table.
WITH SEQUENCE
A special SEQUENCE column can be include in the materialized view log to help Oracle apply updates to materialized view logs in the correct order when a mix of Data Manipulation (DML) commands, e.g. insert, update and delete, are performed on multiple base tables in a single transaction.
drop materialized view log on t ;
create materialized view log on t WITH SEQUENCE ;
create materialized view log on t2 WITH SEQUENCE ;
INSERT into T values ( 5, 'e' );
INSERT into T2 values ( 60, 3, 300 );
UPDATE T set val = upper(val) where key = 5 ;
UPDATE T2 set amt = 333 where key = 60 ;
commit;
select SEQUENCE$$, key, dmltype$$ from mlog$_T ;
SEQUENCE$$ KEY DMLTYPE$$
---------- ---------- ----------
60081 5 I
60083 5 U
select SEQUENCE$$, key, dmltype$$ from mlog$_T2 ;
SEQUENCE$$ KEY DMLTYPE$$
---------- ---------- ----------
60082 60 I
60084 60 U
Since mixed DML is a common occurrence SEQUENCE will be specified in most materialized view logs. In fact, Oracle recommends it.
"Oracle recommends that the keyword SEQUENCE be included in your materialized view log statement unless you are sure that you will never perform a mixed DML operation (a combination of INSERT, UPDATE, or DELETE operations on multiple tables)."
-- from Creating Materialized Views: Materialized View Logs"
WITH Column List
The WITH clause can also contain a list of specific base table columns. In the next snippet we include the VAL column.
drop materialized view log on t ;
create materialized view log on t WITH ( VAL );
desc mlog$_t
Name Null? Type
-------------------------------------------- -------- ------------------------------
KEY NUMBER
VAL VARCHAR2(5)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
select * from t ;
KEY VAL
---------- -----
1 a
2 b
3 c
4
5 E
UPDATE t set val = 'f' where key = 5 ;
column old_new$$ format a10
select key, val, old_new$$ from mlog$_t ;
KEY VAL OLD_NEW$$
---------- ----- ----------
5 E O
INCLUDING NEW VALUES Clause
In the last snippet we see that the VAL column contains values as they existed before the update operation, aka the "old" value. There is no need to store the new value for an update because it can be derived by applying the change vector (a RAW value stored in CHANGE_VECTOR$$, which Oracle uses internally during refreshes) to the old value. In some situations, which we will identify in later topics, it helps to have both the old value and the new value explicitly saved in the materialized view log. We can do that using the INCLUDING NEW VALUES clause, like this.
drop materialized view log on T ;
create materialized view log on t
with sequence ( VAL )
INCLUDING NEW VALUES
;
update t set val = 'g' where key = 5 ;
column old_new$$ format a9
select sequence$$, key, val, old_new$$
from mlog$_t
order by sequence$$ ;
SEQUENCE$$ KEY VAL OLD_NEW$$
---------- ---------- ----- ---------
60085 5 f O
60086 5 g N
Note how both the old and the new values are stored in the same column, VAL. The OLD_NEW$$ column identifies the value as either an old or a new value.
Gotcha - Commas
The syntax diagrams for the CREATE MATERIALIZED VIEW LOG command indicate a comma is required between each component of the WITH clause. However this does not appear to be the case when the component is a column list, e.g. "( VAL )".
drop materialized view log on t ;
create materialized view log on t with sequence, ( VAL ), primary key ;
create materialized view log on t with sequence, ( VAL ), primary key
*
ERROR at line 1:
ORA-00922: missing or invalid option
Omitting the comma before the column list works better.
create materialized view log on t with sequence ( VAL ), primary key;
Materialized view log created.
DBMS_REDEFINITION
The DBMS_REDEFINITION package has certain restrictions related to materialized view logs. In
Oracle 10g these restrictions are:
Tables with materialized view logs defined on them cannot be redefined online.
For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties. No horizontal or vertical subsetting is permitted, nor are any column transformations. The only valid value for the column mapping string is NULL.
-- f-rom Oracle® Database Administrator's Guide 10g Release 2 (10.2) - Restrictions for Online Redefinition of Tables
In Oracle 11g they are:
After redefining a table that has a materialized view log, the subsequent refresh of any dependent materialized view must be a complete refresh.
For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties. No horizontal or vertical subsetting is permitted, nor are any column transformations. The only valid value for the column mapping string is NULL.
-- from Oracle® Database Administrator's Guide 11g Release 1 (11.1) - Restrictions for Online
Redefinition of Tables
Cleanup
delete t2 ;
delete t ;
insert into t select * from t_backup ;
insert into t2 select * from t2_backup ;
commit;
drop materialized view log on t ;
drop materialized view log on t2 ;
