Materialized Views in Oracle

What are Materialized Views ?

Materialized views are local copies of remote tables. Seems quite confusing. In today's distributed world, data resides on a central database server. The data is used by local users. In this kind of scenario, replicating data on local server can be helpful for gaining performance. Materialized views serve the same purpose. Materialized views contain data, same as if they were a table. These Materialized Views ( MV ) reside on local machines. Local users can query these MVs to get desired results. MVs can be complete replica of a table, or it can be a result of a query fired on multiple tables.

What is the difference between Tables / Views and Materialized Views ?

There must be some difference between Views and MVs. One can point a difference from the above discussion that views do not contain any data, but MVs do contain data. This difference can be easily pointed out from the definition of MVs itself. But then, what is the difference between Tables and MVs? The difference is that, MVs can refresh data from the Master tables after a specified time interval. I used a term Master tables. What are these Master tables? Master tables are the base for MV. As specified above MV can be an exact replica or result of a query. These tables on which query is fired on the server side are Master tables.

Types of Materialized Views

1) Read-Only : This type of MVs cannot send data back to the server Master tables. These server only one way communication i.e. from server to the client.

2) Updatable : This type of MVs can send the data, changed locally, back to the server.

Syntax ( Oracle ): -

1) create materialized view LOCAL_BOOKSHELF
    2) refresh force
       start with SysDate next SysDate + 7
       as
    3) select * from BOOKSHELF@REMOTE_CONNECT;

1) The first part is the create statement itself with the MV name to be created. In this case MV name is "LOCAL_BOOKSHELF". Note that some parts are purposefully ignored here to avoid complexity. If no tablespace is provided then the MV is created in the current tablespace.

2) The 'refresh' part has some options. a. Fast b. Complete c. Force Fast refreshes are only available if Oracle can match rows in the MV directly to rows in the base tables. They use tables called Materialized View Logs to send specific rows from the master table to the MV. Complete refreshes completely re-create the MV. The force option for refreshes tells Oracle to use a fast refresh if it is available; otherwise, a complete refresh will be used.

3) This part is the query. Note that the MV created in the above example is a Read-Only MV. If you want to create an updatable query, then 'for update' can be specified. Then syntax will be -

create materialized view LOCAL_BOOKSHELF
             refresh force
             start with SysDate next SysDate + 7
             for update as
             select * from BOOKSHELF@REMOTE_CONNECT;

All the examples used in this article are taken from the Oracle 9i Complete Reference