view materializada

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

Mensagemem Qua, 23 Jul 2008 8:46 am

Bom pessoal....
Eu queria saber algumas coisas sobre view materializada =D
vamo começar =D

Qual é a diferença de view materializada e view ??
porque usar view materializada ??
Quando é melhor usar view materializada ??

se alguém souber responder vai me ajudar muito =D

Localização: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Mensagemem Qua, 23 Jul 2008 9:21 am

Brother, beleza?
Achei essa documentação aqui, porém, em Inglês, mas, acho que lhe ajuda nas dúvidas.
Código: Selecionar todos

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 master_table@master_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 master_table@master_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 o
        (SELECT * FROM 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:


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
      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
      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

Qualquer coisa, manda pra gente.
Localização: Araraquara - SP


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

Mensagemem Qua, 23 Jul 2008 9:45 am

olha la... muito obrigado pela documentação...
o fo*a é o meu inglês mesmo :=D

vou tentar intender :=D

Localização: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Mensagemem Qua, 23 Jul 2008 9:46 am

beleza brother...
O meu Inglês não é aquela coisa, mas, precisando, conta com a gente aqui.
Localização: Araraquara - SP


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

Mensagemem Qua, 23 Jul 2008 10:07 am

até o que eu intendi lendo no pai dos burros(google)
a diferença é mais ou menos isso

uma view normal... não armazena dados nenhum, ela apenas lê o que foi passado no seu select de criação.
Já uma view materializada, ela é um objeto do banco... que armazena os dados dentro dela.....
ai vem mais uma duvida.... tem vários parametros para criação dessa view...
ai é o seguinte... a VM guarda dentro dela os valores... mais se o valor é alterado na tabela principal... o valor da VM é alterado também ???
VM aceita comando DML direto para ela ??
Quando é dado um update na tabela principal, e a VM tenha um LOG, isso é computado no LOG também ??

PS: tabela principal, é a tabela que está no select da MV
PS2: Erri o nome do titulo, se for possivel alterar é bom pro pessoal encontras mais fácil quando pesquisar aqui no forum....
Localização: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Mensagemem Sex, 25 Jul 2008 9:01 am

então Cara....

Tenta Imaginar o seguinte Cenario...

Temos Duas Empresas, Matriz(SP) e Filiar(RJ)
Imagina que a empresa se SP precisa saber.. quantos pessoal foram cadastradas no RJ...
Com a VM você consegue realizar essa consulta e armazenar esses dades em uma tabela identica ao select que realizou a consulta...

Código: Selecionar todos
   NEXT SYSDATE+1/(1440/2)

Toda MV Cria uam Tabela Implicita....

Eu tenho um Passo a passo de como Criar e Utilizar uma....
No Exemplo acima estou utilizando um DB Link

passa seu e-mail.. que eu te mando... ai tiramos as duvidas por aqui...

Localização: SP


DBA Oracle Jr

Mensagemem Sex, 25 Jul 2008 9:38 am

Brother, beleza?

Manda no meu e-mail que posto aqui no fórum pra que todos compartilhem, pode ser?
Localização: Araraquara - SP


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

Mensagemem Sex, 25 Jul 2008 10:40 am

a VM guarda dentro dela os valores... mais se o valor é alterado na tabela principal... o valor da VM é alterado também ???

Depende da criação da view materializada. O REFRESH dela pode ser automático OU não. É claro que se for automático, isso provoca uma queda na performance. Tem casos que não é necessário ser tão "online" assim, e fazendo um refresh diário é o suficiente...

As views materializadas tem uma série de restrições.
Vou colocar alguns links pra você aqui do forum:

Localização: Portland, OR USA

Thomas F. G

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

Mensagemem Ter, 29 Jul 2008 4:14 pm

hmmmmm.... cara, só essa sua explicação ai já me exclareceu muita coisa...
valeww ai...
passa esse documento ai pro Trevisoli mesmo que ele posta no forum... mais pode mandar para meu e-mail também...

Localização: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

  • Veja também
    Última mensagem