Basics on Materialized View(MV) in Oracle

What is a Materialized View(MV) in Oracle?

A materialized view is a database object that contains the results of a query. When you create a materialized view, Oracle Database creates one internal table and at least one index.

Why to use MV?

It advised to use materialized views if query involves summaries, large or multiple joins or both.

Materialized views are defined or created by a query, are stored in a physical object, and follow certain refresh mechanisms. This refresh method keeps the materialized view up to date.

When creating a materialized view, you have the option of specifying whether the refresh occurs ON DEMAND or ON COMMIT. In case of an ON COMMIT scenario, when a transaction commits, the materialized view is refreshed and as a result, the data is always current in a materialized view. In case of an ON DEMAND type, calling the dbms_mview package procedures refreshes the materialized view.

Uses of MV:

The use of MV comes handy when using remote table objects, this has come into force from 10g and above release where in table joins is now permitted to contain references to remote tables.

With Oracle10g, the refresh mechanism is extended to support nested refresh. All the underlying materialized views of a nested materialized view are refreshed first based on dependency order, and then the nested materialized view itself is refreshed to ensure its freshness and consistency with underlying tables whenever possible.

SYNTAX:

CREATE MATERIALIZED VIEW <schema.name>
TABLESPACE <tablespace_name>
BUILD IMMEDIATE
REFRESH <FAST | COMPLETE | FORCE > ON <COMMIT | DEMAND>
<USING INDEX | USING NO INDEX>
INITRANS <integer>
STORAGE CLAUSE

AS (<SQL statement>);

Built Option

The <build option> determines when MV is built

BUILD IMMEDIATE: Create the materialized view and then populate it with data.

BUILD DEFFERED: Create the materialized view definition but do not populate it with data.

ON PREBUILT TABLE: use an existing table as view source

Materialized Views Refresh Types

Refresh is the operation that synchronizes the content of the materialized view with the data in base tables. Following are the types of refresh:

Complete: Involves truncating existing data & re-inserting all the data based on the detail tables by re-executing the query definition from the create command.

Fast: Apply the changes made since the last refresh.

a)Fast refresh using materialized view logs

b)Fast refresh using ROWID range: Can do the fast refresh after the direct load, based on the rowed of the new rows.

Force: First tries to refresh with fast mechanism if possible or else will use a complete refresh. This is default refresh type.

Never: Suppresses all refreshes on materialized views.

Materialized Views Refresh Modes

Manual Refresh:  Can be performed using DBMS_MVIEW package. (REFRESH, REFRESH_DEPENDENT, REFRESH_ALL_VIEWS)

Automatic Refresh: Can be performed in two ways:

a)ON COMMIT – Materialized view gets updated whenever changes to one of these tables are committed.

b)ON DEMMAND – At Specified Time – Refresh is scheduled to occur for specified time by using START WITH & NEXT clauses. For such refreshes, instance must initiate a process with JOB_QUEUE_PROCESSES.

USING INDEX Clause

The USING INDEX clause lets you establish the value of the INITRANS(INITRANS specifies the number of update transaction entries for which space is initially reserved in the data block header)  and STORAGE (Storage parameters determine space allocation for objects when their segments are created in a tablespace )parameters for the default index Oracle Database uses to maintain the materialized view data. If USING INDEX is not specified, then default values are used for the index. Oracle Database uses the default index to speed up incremental (fast) refresh of the materialized view.

USING NO INDEX Clause

Specify USING NO INDEX to suppress the creation of the default index.

Advertisements
Comments
One Response to “Basics on Materialized View(MV) in Oracle”
  1. RC_IBM says:

    Hi,
    Thanks for the explanation. Can you give an example of such a view in R12 ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: