Materialized Views

Materialized View Logs

As mentioned earlier, 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.

 

Gotcha - 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.

-- from 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 ;
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12878.html]SQL Snippets: Materialized Views - Materialized View Logs[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12878.html">SQL Snippets: Materialized Views - Materialized View Logs</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Materialized Views - Materialized View Logs
  • URL (href): http://www.sqlsnippets.com/en/topic-12878.html

Revision Notes

Date Category Note
2008-04-26 Revision added "Gotcha - DBMS_REDEFINITION"
2009-07-06 Revision added link to Dizwell's blog