Materialized views can be used to cache the results from queries that would normally be very demanding on a database. This is much different than a regular view which doesn’t actually store any data. A regular view only stores a query that will be executed against the base table, while a materialized view will actually store the results from a query that was executed on the base table. Once the materialized is created, the data can be refreshed in two ways: complete or fast. In this blog, I will discuss the use of a complete refresh, and I will discuss the use of a fast refresh in the next blog.

Complete Refresh

A complete refresh will repopulate the materialized view regardless if any data has changed from the base table. 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 be created on this table:

CREATE MATERIALIZED VIEW mview_complete AS
   SELECT *
   FROM mview_table;

The procedure DBMS_MVIEW.REFRESH() will be used to refresh this table. To see how the complete refresh works, the ROWID can be used to see if the row has been changed. Below is the OBJECT_NAME, OBJECT_ID, and ROWID prior to the complete refresh:

SELECT object_name, object_id, rowid
  FROM mview_complete;

OBJECT_NAME                     OBJECT_ID ROWID
------------------------------ ---------- ------------------
ICOL$                                  20 AAATPaAABAAAV55AAA
I_USER1                                46 AAATPaAABAAAV55AAB
CON$                                   28 AAATPaAABAAAV55AAC
UNDO$                                  15 AAATPaAABAAAV55AAD
C_COBJ#                                29 AAATPaAABAAAV55AAE

Executing DBMS_MVIEW.REFRESH(‘mview_complete’) will do a complete refresh on the materialized view MVIEW_COMPLETE:

EXECUTE DBMS_MVIEW.REFRESH('mview_complete');

Notice that the ROWID has changed:

SELECT object_name, object_id, rowid
  FROM mview_complete; 

OBJECT_NAME                     OBJECT_ID ROWID
------------------------------ ---------- ------------------
ICOL$                                  20 AAATPaAABAAAV55AAF
I_USER1                                46 AAATPaAABAAAV55AAG
CON$                                   28 AAATPaAABAAAV55AAH
UNDO$                                  15 AAATPaAABAAAV55AAI
C_COBJ#                                29 AAATPaAABAAAV55AAJ

This shows that the complete refresh repopulated the materialized view. This is not very efficient and can be demanding on the database. In the next blog, I will discuss the use and benefits of refreshing a materialized view with a fast refresh instead of a complete refresh.

Link to script that contains the examples in this post.

 

 

 

 

 

 

 

Comment