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.

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)."

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.

Logging Columns in the Materialized View Log

When you create a materialized view log, you can add columns to the log when necessary. To perform a fast refresh on a materialized view, the following types of columns must be added to the materialized view log:

• A column referenced in the WHERE clause of a subquery that is not part of an equi-join and is not a primary key column. These columns are called filter columns.
  
• A column in an equi-join that is not a primary key column, if the subquery is either many to many or one to many. If the subquery is many to one, then you do not need to add the join column to the materialized view log.
  

A collection column cannot be added to a materialized view log. Also, materialized view logs are not required for materialized views that use complete refresh.
For example, consider the following DDL:

1) CREATE MATERIALIZED VIEW oe.customers REFRESH FAST AS
2)  SELECT * FROM oe.customers@orc1.example.com c
3)  WHERE EXISTS
4)    (SELECT * FROM oe.orders@orc1.example.com o
5)     WHERE c.customer_id = o.customer_id AND o.order_total > 20000);

Notice in line 5 of the preceding DDL that three columns are referenced in the WHERE clause. Columns orders.customer_id and customers.customer_id are referenced as part of the equi-join clause. Because customers.customer_id is a primary key column, it is logged by default, but orders.customer_id is not a primary key column and so must be added to the materialized view log. Also, the column orders.order_total is an additional filter column and so must be logged.
Therefore, add orders.customer_id and orders.order_total the materialized view log for the oe.orders table.

To create the materialized view log with these columns added, issue the following statement:

CREATE MATERIALIZED VIEW LOG ON oe.orders 
  WITH PRIMARY KEY (customer_id,order_total);

If a materialized view log already exists on the oe.customers table, you can add these columns by issuing the following statement:

ALTER MATERIALIZED VIEW LOG ON oe.orders ADD (customer_id,order_total);

If you are using user-defined data types, then the attributes of column objects can be logged in the materialized view log. For example, the oe.customers table has the cust_address.postal_code attribute, which can be logged in the materialized view log by issuing the following statement:

ALTER MATERIALIZED VIEW LOG ON oe.customers ADD (cust_address.postal_code);

You are encouraged to analyze the defining queries of your planned materialized views and identify which columns must be added to your materialized view logs. If you try to create or refresh a materialized view that requires an added column without adding the column to the materialized view log, then your materialized view creation or refresh might fail.

Note:

To perform a fast refresh on a materialized view, you must add join columns in subqueries to the materialized view log if the join column is not a primary key and the subquery is either many to many or one to many. If the subquery is many to one, then you do not need to add the join column to the materialized view log.