2012-06 – Materialized Views: Fast Refresh

The last blog, found here, discussed the use of complete refreshes on materialized views. This blog will go over the use of a fast refresh. Fast refresh is a way to refresh materialized except, unlike complete refreshes, a fast refresh will only refresh the data that has changed. This allows fast refreshes to generally be much quicker than complete refreshes. To show this, a table needs to be created:

CREATE TABLE mview_table AS
   SELECT object_name, object_id, object_type
     FROM all_objects
     WHERE rownum <= 5;

A materialized view can now be created on this table:

CREATE MATERIALIZED VIEW mview_fast AS
   SELECT *
   FROM mview_table;

However, this materialized view cannot have a fast refresh used on it. Running the DBMS_MVIEW.EXPLAIN_MVIEW() procedure will show what is needed in order to enable fast refresh. But, in order to execute the DBMS_MVIEW.EXPLAIN_MVIEW() procedure, the script utlxmv.sql has to be executed which will create a table that is needed for the procedure. The script is located in your admin directory:

@?/rdbms/admin/utlxmv;

EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW('SYS.MVIEW_FAST');

Once the procedure completes, the table mv_capabilities_table (which was created when the utlxmv.sql script was executed) can be queried to see whether fast refresh is enabled:

SELECT capability_name, possible 
  FROM mv_capabilities_table 
 WHERE mvname = 'MVIEW_FAST' 
   AND capability_name = 'REFRESH_FAST';

CAPABILITY_NAME                P
------------------------------ -
REFRESH_FAST                   N

REFRESH_FAST capability has N (No) under pass. This means this view is not capable of using a fast refresh. In order to enable fast refresh, a materialized view log must be created on the base table. This log will keep track of all the changes made to the base table which will allow a fast refresh that will only update the changed values. A materialize view log can exist on the base table even if the base table does not have a primary key, however a fast refresh cannot be enabled unless the base table has a primary key:

ALTER TABLE mview_table
  ADD CONSTRAINT mview_table_pk
  PRIMARY KEY (object_id);

Now, a materialized view log can be created on MVIEW_TABLE using the PRIMARY KEY command. PRIMARY KEY is used to specify that the primary key of the changed rows needs to be recorded as well, SEQUENCE is used to store the ordering information and is needed for certain updates, and INCLUDE NEW VALUES will store the old and new records in the log:

CREATE MATERIALIZED VIEW LOG ON mview_table
WITH PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;

The materialized view log will store all the data that has changed on the base table. The changes will be applied to the materialized view during fast refresh. Now, the old materialized view can be dropped and a new fast refresh materialized view can be created:

DROP MATERIALIZED VIEW mview_fast;

CREATE MATERIALIZED VIEW mview_fast
   REFRESH FAST
   AS
   SELECT *
   FROM mview_table;

Notice that fast refresh is now enabled for the materialized view:

SELECT capability_name, possible 
  FROM mv_capabilities_table 
 WHERE mvname = 'MVIEW_FAST' 
   AND capability_name = 'REFRESH_FAST';

CAPABILITY_NAME                P
------------------------------ -
REFRESH_FAST                   Y

To see how the fast refresh works, OBJECT_NAME, OBJECT_ID, and ROWID can be selected to check if they are changed after a refresh:

SELECT object_name, object_id, rowid
  FROM mview_fast;

OBJECT_NAME                     OBJECT_ID ROWID
------------------------------ ---------- ------------------
ICOL$                                  20 AAATPhAABAAAV8RAAA
I_USER1                                46 AAATPhAABAAAV8RAAB
CON$                                   28 AAATPhAABAAAV8RAAC
UNDO$                                  15 AAATPhAABAAAV8RAAD
C_COBJ#                                29 AAATPhAABAAAV8RAAE

Executing DBMS_MVIEW.REFRESH() will refresh the materialized view:

/* the parameter ‘F’ indicates a fast refresh */
EXECUTE DBMS_MVIEW.REFRESH('mview_fast', 'F');

Selecting the OBJECT_NAME, OBJECT_ID, and ROWID again will show that nothing has been changed:

SELECT object_name, object_id, rowid
  FROM mview_fast;

OBJECT_NAME                     OBJECT_ID ROWID
------------------------------ ---------- ------------------
ICOL$                                  20 AAATPhAABAAAV8RAAA
I_USER1                                46 AAATPhAABAAAV8RAAB
CON$                                   28 AAATPhAABAAAV8RAAC
UNDO$                                  15 AAATPhAABAAAV8RAAD
C_COBJ#                                29 AAATPhAABAAAV8RAAE

If a record is changed on the base table, only its ROWID will change after a fast refresh:

UPDATE mview_table
 SET object_id = 1
 WHERE rownum = 1;

EXECUTE DBMS_MVIEW.REFRESH('mview_fast', 'F');

SELECT object_name, object_id, rowid
  FROM mview_fast;

OBJECT_NAME                     OBJECT_ID ROWID
------------------------------ ---------- ------------------
ICOL$                                  20 AAATPhAABAAAV8RAAA
I_USER1                                46 AAATPhAABAAAV8RAAB
CON$                                   28 AAATPhAABAAAV8RAAC
C_COBJ#                                29 AAATPhAABAAAV8RAAE
UNDO$                                   1 AAATPhAABAAAV8RAAF

Notice how the UNDO$’s OBJECT_ID is now 1 and the ROWID has changed while the rest of the ROWIDs have not. This indicates that the fast refresh only updated the record that was changed on the base table and no other row.

Since fast refresh only updated the materialized view with the changed records, it is much quicker than a complete refresh. This is much more efficient in certain environments than using a complete refresh. If a complete refresh materialized view were to be put on a table that had 10 million records, the refresh could take hours. Utilizing a materialized view log and a fast refresh materialized view, refreshing a 10 million record table could take just minutes (depending on the amount of changes made to the base table).

Link to script that contains the examples in this post.