view materialized

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc

Poston Wed, 23 Jul 2008 8:46 am

Well folks ....
I wanted to know a few things about view materialized = D Let's start = D what is the difference of view and materialized view?
Why use materialized view?
When it is better to use materialized view?

If anyone knows answer will help me = D muito grateful ...
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Poston Wed, 23 Jul 2008 9:21 am

Brother, beleza?
I found this documentation here, however, in English, but I think that helps in doubt.
Code: Select all

This article gives a short description of materialized views (MVIEW) including
readonly and updatable MVIEWs. The information supplied applies to Oracle
releases from 7.x to 11.x.


The terms Materialized View (MVIEW) are synonymous and they will be used

The following terms are used in this article:
Master Site:       The site where the master table is located
Master Table:      The table(s) which are referenced by the mview query
(MVIEW) Base Table: The table that is created with the MVIEW create statement, and
                    that stores the rows that satisfy the MVIEW query
Master Log:        The log table that is used by the fast refresh mechanism

Examples are provided with the following assumptions:
  1- master_db is the master site.
  2- snap_db is the materialized view site
  3- there is a database link called master_db from SCOTT user of snap_db to
     the SCOTT schema of master_db

Note that the examples provided below do not supply all the details of the setup.
For a complete discussion of the MVIEW environment and configuration refer to
Note 256241.1



1. Introduction
2. Usage of Materialized Views
   2.1 Materialized Views for Data Warehouses
   2.2 Materialized Views for Distributed Computing
3. Components of the materialized view configuration
4. Refreshing Materialized Views
5. Refresh Groups
6. Materialized View Types
   6.1 Read-Only Materialized Views
   6.2 Updatable Materialized Views
   6.3 Subquery Materialized Views
   6.4 Rowid vs. Primary Key Materialized Views
   6.5 Multitier Materialized Views
   6.6 Simple vs. Complex Materialized Views
7. Datatype Support
8. References


A materialized view is a replica of a target master from a single point in time.
The concept was first introduced with Oracle7 termed as SNAPSHOT. In Oracle
release 7.1.6 snapshots were enhanced to enable DMLs along with a new
terminology, updatable snapshots. In Oracle 7.3 the primary key snapshots were
commenced. Subquery snapshots, support of LOB datatypes and offline instantiation
are some of the new features released with Oracle8. With Oracle 8.1.6 snapshots
started to be used in data warehouse environments so a new terminology
materialized view was introduced to address both distributed and data warehouse
materialized views. The most remarkable MVIEW enhancements in Oracle9 are the
multitier materialized views and support for user-defined types.

2. Usage of Materialized Views

Materialized views can be used both for:
   - creating summaries to be utilized in data warehouse environments
   - replicating data in distributed environments

2.1 Materialized Views for Data Warehouses
In data warehouses, you can use materialized views to precompute and store
aggregated data such as the sum of sales. Materialized views in these
environments are often referred to as summaries, because they store summarized
data. They can also be used to precompute joins with or without aggregations.
A materialized view eliminates the overhead associated with expensive joins
and aggregations for a large or important class of query. MVIEWs in this class
typically have the master table within the same database as the materialized view.

2.2 Materialized Views for Distributed Computing
In distributed environments, you can use materialized views to replicate data
from a master site to other distributed sites. This configuration has the
following benefits:

   - Ease Network Loads
   - Enable Disconnected Computing
   - Data Synchronization via Conflict Resolution, for Updatable MVIEWs

Many of the capabilities of data warehousing, or aggregate, materialized views
are not available for these types of materialized views, e.g. ON-COMMIT refresh,
QUERY REWRITE cannot be used with distributed MVIEWs.

The main focus of this article is distributed materialized views.

3.Components of the materialized view configuration

A typical create MVIEW statement has the following form:

  create materialized view snap_test
  refresh fast
  start with sysdate
  next sysdate+1 as
  select * from [email protected]_db;

This statement should be executed in snap_db. It will create

  - A MVIEW base table called SNAP_TEST which has the same structure as
  - A new object namely SNAP_TEST of type materialized view
  - A UNIQUE index on the PK columns or ROWID depending on the type of
    MVIEW log at master site

Since this is a fast refresh MVIEW the master table should have a log to record
the changes on it that can be created by running

    create materialized view log on master_table;

in master_db. This will create the following objects:

  - A table called MLOG$_master_table
  - An internal trigger on MASTER_TABLE that populates the log table

4. Refreshing Materialized Views

Initially, a materialized view contains the same data as in the master table.
After the materialized view is created, changes can be made to the master table,
and possibly also to the materialized view. To keep a materialized view's data
relatively current with the data in the master table, the materialized view must
be periodically refreshed. Refresh can be accomplished by one of the following

   dbms_mview.refresh( '<mview list>', '<Refresh Type>' )

   dbms_refresh.refresh( '<refresh group>' )

You can choose between Complete, Fast, and Force refresh types. Complete refresh
is performed by deleting (or truncating) the rows from the snapshot and inserting
the rows satisfying the mview query. Depending on the size of the materialized
view this can be a costly operation. This cost is addressed with fast refresh
where only rows updated since the last refresh are pulled from the master site.
This requires a log table called as materialized view log to be created on the
master table. Force refresh first tries to run a fast refresh if possible. If
fast refresh is not possible it accomplishes a complete refresh.
See Note 236233.1 for a complete discussion of the refresh mechanism.

5. Refresh Groups

Sometimes it is required to refresh multiple snapshots in a transactionally
consistent manner. Refresh groups server this purpose. When a refresh group is
refreshed all mviews in that group are populated with data from a consistent
point in time.

A refresh groups are managed using the procedures in the package DBMS_REFRESH.
MAKE and ADD API in that package are used to create a refresh group and add
new snapshots to an existing refgroup respectively.

6. Materialized View Types

In this section a classification of materialized views is provided. Note that
any of the refresh options described in the previous section can be used by
each snapshot in any of these categories.

6.1 Read-Only Materialized Views
As the name implies it is not possible to perform DML on snapshots in this
category. Applications can query data from read-only materialized views to
avoid network access to the master site, regardless of network availability.
However, master table should be accessed for DMLs. These changes are transfered
to the snapshot with the next refresh.

6.2 Updatable Materialized Views
Updatable materialized views eliminate the restriction of DMLs on snapshots.
Users are allowed to insert, update and delete rows of the updatable
materialized view. These changes are propagated to the master through the
PUSH mechanism of advanced replication. Changes committed on the master table
are pulled using the REFRESH mechanism. Permitting simultaneous DMLs on both
master and materialized view brings the possibility of conflicts.

Updatable Materialized View Restrictions:
   - They are always based on a single table, although multiple tables can be
     referenced in a subquery.
   - They should allow fast refreshing.
   - Master table should be registered to a replication group
   - Must belong to a materialized view group that has the same name as the
     replication group at its master site
   - Must reside in a different database than the master replication group
   - The name of the materialized view should be the same as its master table

See Note 120094.1 for an initial setup of an updatable snapshot environment.

For a snapshot to be updatable its associated create statement should include
"FOR UPDATE" clause.

  create materialized view snap_test
  refresh fast
  start with sysdate
  next sysdate+1
  select * from [email protected]_db;

For the details of setup and configuration of an updatable mview environment
refer to the Advanced Replication manual.

6.3 Subquery Materialized Views
Materialized views that are created with subqueries in the WHERE clause of the
mview query are referred to as subquery materialized views. Fast refresh
restrictions on these kind of mviews depend on the version, where the number of
restrictions decreases in newer versions.

     SELECT * FROM [email protected] o
        (SELECT * FROM [email protected] c
          WHERE o.customer_id = c.customer_id AND c.credit_limit > 10000);

6.4 Rowid vs. Primary Key Materialized Views
Fast refresh requires association between rows at snapshot and master sites. In
Oracle7 ROWIDs were employed to do this mapping. Given the maintenance problems
(e.g. ROWIDs change when a table is rebuild with EXP/IMP) fast refresh has been
enhanced in Oracle8 to use primary keys. Snapshots that use ROWIDs to refresh
are called ROWID snapshots while those that use primary keys are called primary
key snapshots. See Note 254593.1 for a complete discussion on rowid and primary
key mviews.

The following is an example of a CREATE MATERIALIZED VIEW statement that
creates a ROWID materialized view:

     SELECT * FROM [email protected];

Starting with Oracle8 primary key snapshots are the default. You should use
rowid mviews:
  - if there are Oracle7 databases involved or
  - if the master table has no primary key and there is a need for fast refresh

6.5 Multitier Materialized Views
A multitier materialized view is a materialized view whose master table is itself
a materialized view. This feature enables fast refresh of materialized views that
have materialized views as their masters. This also makes it possible to define
hierarchies of updatable mviews.

Many companies are structured on at least three levels: international, national,
and local. Many nodes at both the national and local levels are required. The
best possible solution in such cases is to use multitier materialized views. 

In this example
   - the site at the top of the hierarchy is assumed to be
   - regions are considered to be at the next level,
   - countries are located at the third level,

Create a materialized view of employees at EMEA region

   create materialized view employees
   refresh fast as
      select * from [email protected]
      where region='EMEA';

   -- create a materialized view log on the materialized view so that
   -- lower level mviews refresh fast
   create materialized view log on employees;

Create a materialized view of employees at UK

   create materialized view employees
   refresh fast as
      select * from [email protected]
      where country='UK';

6.6 Simple vs. Complex Materialized Views
Snapshot's being simple or complex determines whether it can be fast refreshed.
Specifically, a snapshot is fast refreshable if it is simple (not complex). A
snapshot is considered complex if its defining query does not meet certain
criteria, e.g. it should not contain a CONNECT BY, INTERSECT, MINUS or UNION ALL
clause. Restrictions that apply vary with version. See Note 179466.1 for a
detailed description of complex materialized views.

7. Datatype Support

The following datatypes are supported in snapshot replication:
- User-defined datatypes
- Binary LOB (BLOB)
- Character LOB (CLOB)
- National character LOB (NCLOB)
- UROWID (supported only for readonly materialized views)

The following types are not supported in snapshot replication:
- UROWID (not supported for updatable snapshots)

8. References

Note 179466.1         Diagnosing ORA-12015 fast refresh mview/complex queries
                      (for the definition of complex mviews by release)
Note 254593.1         Differences Between Rowid & Primary Key Materialized Views
Note 120093.1         Initial steps required to create Snapshot Replication
                      environment v8.1
Note 256241.1         Initial steps required to a create read only Mview
                      Replication environment v9.2
Oracle Documentation  Advanced Replication Manual

anything, send it to us.
Location: Araraquara - SP


OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional

Poston Wed, 23 Jul 2008 9:45 am

look it ... thank you very much for documentation ... the fuck is my English anyway: = D I'm trying to understand: = D thanks ...
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Poston Wed, 23 Jul 2008 9:46 am

beleza brother ...
My English is not that thing, but, needing, account with us here.
Location: Araraquara - SP


OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional

Poston Wed, 23 Jul 2008 10:07 am

even what I intend reading on the parent of the donkeys (google) the difference is more or less a normal view ... no stores data, it only reads what was passed in your select.
A view already materialized, she is an object of Bank ... that stores the data within it ... here comes another question .... has several parameters for creating this view ... There is the following ... the VM keeps inside her the values ... more if the value is changed in the main table ... the VM value is changed também???
VM direct DML command accepts her??
When is given an update on the main table, and the VM has a LOG, it is computed in the também LOG??

PS: main table is the table that is in the select of the MV PS2: Erri title name, if possible change is good for people find easier when searching here on the forum ....
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Poston Fri, 25 Jul 2008 9:01 am

So Man. ..

Try to Imagine the following Scenario.

We have two companies, matrix (SP) and Affiliate (RJ) Imagine that the company if SP needs to know How many people were registered in Rio de Janeiro.
With the VM you can perform this query and store these data in a table identical to select which held the query ...

Code: Select all
   NEXT SYSDATE+1/(1440/2)
   SELECT * FROM [email protected]_CLONE;
the whole MV Creates a Table Implicitly. ...

I have a step by step how to Create and Use a. ..
In the above example I am using a DB Link passes your email ... I send you ... Oh we all doubt by aqui ... falou ...
Location: SP


DBA Oracle Jr

Poston Fri, 25 Jul 2008 9:38 am

Brother, beleza?

Send on my email I post here in the forum for everyone to share.

[email protected]
Location: Araraquara - SP


OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional

Poston Fri, 25 Jul 2008 10:40 am

the VM holds within it the values ... more if the value is changed in the main table ... the VM value is changed too???

Depends on the creation of the view materialized. The REFRESH it can be automatic or not. Of course if it's automatic, this causes a drop in performance. Have cases that it is not necessary to be so \"online\" as well, and doing a daily refresh is enough ...

The materialized views has a number of restrictions.
I'll put some links for you here on the forum: viewtopic.php?t=312 viewtopic.php?t=735 viewtopic.php?t=316 viewtopic.php?t=1720:-
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?

Poston Tue, 29 Jul 2008 4:14 pm

hmmmmm .... dude, just that your explanation that exclareceu me a lot ... valeww ... passes this document ai pro Trevisoli even if it posts to the forum ... more can send to my email [email protected] vlwww também ... ... ....
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

  • See also
    Last Post