Click here to Skip to main content
13,139,496 members (53,444 online)
Click here to Skip to main content
Add your own
alternative version


17 bookmarked
Posted 11 Nov 2006

Materialized Views in Oracle

, 11 Nov 2006
Rate this:
Please Sign up or sign in to vote.
This article explains in short what Materialized Views are and how to create read-only 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
    3) select * from BOOKSHELF@REMOTE_CONNECT;
Explanation : In the above example some lines are numbered. These are different parts of create statement, also these will be used to reference exactly one part at a time.

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


This article, along with any associated source code and files, is licensed under The Common Public License Version 1.0 (CPL)


About the Author

Software Developer (Senior) CYB
India India
coolestCoder is working at ITCube Solutions Pvt. Ltd., India. He has total experience of 2+ years developing various types of .Net applications, viz. Windows Service, Web Application and at present working on Windows Forms applications. He likes to face challenges while coding and always ready to help others. He would like to see himself as a Technical Architect in near future. His main interests lies in understanding the internal of any program / component / technology.
He loves to read articles related to new topics, for example, Framework 3.5, LINQ, etc.....

You may also be interested in...

Comments and Discussions

QuestionView Pin
Member 1043123416-Dec-13 20:25
professionalMember 1043123416-Dec-13 20:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170915.1 | Last Updated 12 Nov 2006
Article Copyright 2006 by coolestCoder
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid