3
Materialized View Concepts and Architecture

This chapter explains the concepts and architecture of Oracle materialized views.

This chapter contains these topics:

• Materialized View Concepts
• Materialized View Architecture

Materialized View Concepts

Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment. This chapter, and th is Oracle Database Advanced Replication manual in general, discusses materialized views for use in a replicat ion environment.

What is a Materialized View?

A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more maste rs through individual batch updates, known as a refreshes, from a single master site or master materialized view site, as illustrated in Figure 3-1. The arrows in Figure 3-1 represent database links.

Figure 3-1 Materialized View Connected to a Single Master Site

Text description of the illustration repln07a.gif

When a materialized view is fast refreshed, Oracle must examine all of the changes to the maste r table or master materialized view since the last refresh to see if any apply to the materialized view. Therefore, if any changes wh ere made to the master since the last refresh, then a materialized view refresh takes some time to apply the changes to the materiali zed view. If, however, no changes at all were made to the master since the last refresh of a materialized view, then the materialized view refresh should be very quick.

Why Use Materialized Views?

You can use materia lized views to achieve one or more of the following goals:

• Ease Network Loads
• Crea te a Mass Deployment Environment
• Enable Data Su bsetting
• Enable Disconnected Computing

Ease Networ k Loads

If one of your goals is to reduce network loads, then you can use materialized views to distribute your corporate database to regional sites. Instead of the entire company accessing a single data base server, user load is distributed across multiple database servers. Through the use of multitier materialized views, you can crea te materialized views based on other materialized views, which enables you to distribute user load to an even greater extent because clients can access materialized view sites instead of master sites. To decrease the amount of data that is replicated, a materialized view can be a subset of a master table or master materialized view.

While multimaster replic ation also distributes a corporate database among multiple sites, the networking requirements for multimaster replication are greater than those for replicating with materialized views because of the transaction by transaction nature of multimaster replication. Furt her, the ability of multimaster replication to provide real-time or near real-time replication may result in greater network traffic, and might require a dedicated network link.

Materialized views are updated through an effici ent batch process from a single master site or master materialized view site. They have lower network requirements and dependencies t han multimaster replication because of the point in time nature of materialized view replication. Whereas multimaster replication req uires constant communication over the network, materialized view replication requires only periodic refreshes.

In addition to not requiring a dedicated network connection, replicating data with materialized views increases dat a availability by providing local access to the target data. These benefits, combined with mass deployment and data subsetting (both of which also reduce network loads), greatly enhance the performance and reliability of your replicated database.

Create a Mass Deployment Environ ment

Deployment templates allow you to precreate a materialized view e nvironment locally. You can then use deployment templates to quickly and easily deploy materialized view environments to support sale s force automation and other mass deployment environments. Parameters allow you to create custom data sets for individual users witho ut changing the deployment template. This technology enables you to roll out a database infrastructure to hundreds or thousands of us ers.

Enable D ata Subsetting

Materialized views allow you to replicate data based on column- and row-level subsetting, while multimaster replication requires replication of the entire table. Data subsetting enables yo u to replicate information that pertains only to a particular site. For example, if you have a regional sales office, then you might replicate only the data that is needed in that region, thereby cutting down on unnecessary network traffic.

Enable Disconnected Computing

Materialized views do not require a dedicated network connection. Though you h ave the option of automating the refresh process by scheduling a job, you can manually refresh your materialized view on-demand, whic h is an ideal solution for sales applications running on a laptop. For example, a developer can integrate the replication management API for refresh on-demand into the sales application. When the salesperson has completed the day's orders, the salesperson simply dia ls up the network and uses the integrated mechanism to refresh the database, thus transferring the orders to the main office.

Read-Only, Updatable , and Writeable Materialized Views

A materialized view can be either r ead-only, updatable, or writeable. Users cannot perform data manipulation language (DML) statements on read-only materialized views, but they can perform DML on updatable and writeable materialized views.

Note: For read-only, upda table, and writeable materialized views, the defining query of the materialized view must reference all of the primary key columns in the master.

See Also: "Materialized View Replication" for an introduction to read-only and updatable material ized views "Datatype Considerations" for information about datatype considerations for materialized views

Read-Only Materialized View s

You can make a materialized view read-only during creation by omitti ng the FOR UPDATE clause or disabling the equivalent option in the Replication Management tool. Read-only m aterialized views use many of the same mechanisms as updatable materialized views, except that they do not need to belong to a materi alized view group.

In addition, using read-only materialized views eliminates the possibility of a materialized view introducing data conflicts at the master site or master materialized view site, although this convenience mea ns that updates cannot be made at the remote materialized view site. The following is an example of a read-only materialized view:

CREATE MATERIALIZED VIEW hr.employees AS
  SELECT * FROM hr.employees@orc1.
world;

Upd atable Materialized Views

You can make a materialized view updatable d uring creation by including the FOR UPDATE clause or enabling the equivalent option in the Replication Mana gement tool. For changes made to an updatable materialized view to be pushed back to the master during refresh, the updatable materia lized view must belong to a materialized view group.

Updatable materialized views enable you to decrease the load on master sites because users can make changes to the data at the materialized view site. The following is an ex ample of an updatable materialized view:

CREATE MATERIALIZED VIEW hr.departments FOR UPDATE A
S
  SELECT * FROM hr.departments@orc1.world;

The following statement creates a materialized view group:

BEGIN
   DBMS_REPC
AT.CREATE_MVIEW_REPGROUP (
      gname => 'hr_repg',
      master => 'orc1.world',
      propagation_mode => 'ASYNCHRONOUS');
END;
/




The following statement adds the hr.departments materialized view to the materia
lized view group, making the materialized view updatable:
BEGIN
   DBMS_R
EPCAT.CREATE_MVIEW_REPOBJECT (
      gname => 'hr_repg',
      sname => 'hr',
      oname => 'departments',
      type => 'SNAPSHOT',
      min_communicat
ion => TRUE);
END;



/
 
You ca
n also use the Replication Management tool to create a materialized view group and add a materialized view to it.
 
In a single master site environment that has updatable materialized views, quiesce is not required when you perf
orm administration operations on the master site if you:

Propaga
te all of the deferred transactions at the databases containing updatable materialized views before you perform the administration op
erations to the master group.
Do not allow any database manipulation language (D
ML) changes on the updatable materialized views until you have finished the administration operation on the master site and regenerat
ed replication support for the materialized view.
 
If you do not perform these actions,
then quiesce the master group before you perform the administration operations on it.
 





Note:  

Do not use column aliases when you are creating an updatable materialized view. Column aliases cause an error when you
attempt to add the materialized view to a materialized view group using the CREATE_MVIEW_REPOBJECT procedure.
An updatable materialized view based on a master table or master materialized view that has
 defined column default values does not automatically use the master's default values.
A DELETE CASCADE constraint used with an updatable materialized view must be deferrable.

 



See Also:  

"Materiali
zed View Groups" for more information
Oracle Database SQL Reference for more informa
tion about column aliases
 
Writeable Materialized Views

 
A writeable materialized view is one that is created using the FOR UPDATE clause but is not part of a mat
erialized view group. Users can perform DML operations on a writeable materialized view, but if you refresh the materialized view, th
en these changes are not pushed back to the master and the changes are lost in the materialized view itself. Writeable materialized v
iews are typically allowed wherever fast-refreshable read-only materialized views are allowed.
 





Note:  
Most of the documentation
about materialized views only refers to read-only and updatable materialized views because writeable materialized views are rarely us
ed.

 
Available Materialized Views

 
Oracle offers several
 types of materialized views to meet the needs of many different replication (and non-replication) situations. The following sections
 describe each type of materialized view and also describe some environments for which they are best suited.

 
The following sections contain examples of creating different types of materialized views:

Primary Key Materialized Views
Object Materialized Views
ROWID Materialized Views
Complex Materialized Views
 
Whenever you create a materialized view, re
gardless of its type, always specify the schema name of the table owner in the query for the materialized view. For example, consider
 the following CREATE MATERIALIZED VIEW statement:
CRE
ATE MATERIALIZED VIEW hr.employees
  AS SELECT * FROM hr.employees@orc1.world;




Here, the schema hr is specified in the query.
 
Primary Key Materialized Views

Object Materialized Views

 
If a materialized view is based on an
object table and is created using the OF type clause, then the materialized view is called an object materialized view. An object materialized view is structured in the same way as an object table. T
hat is, an object materialized view is composed of row objects, and each row object is identified by an object identifier (OID) colum
n.

 



See Also:  
"Materialized Views Based on Object Tables"
 
ROWID Materialized Views

 
Oracle supports ROWID materialized views in addition to the default primary key materialized views. A ROWID materialized view is based on t
he physical row identifiers (rowids) of the rows in a master. ROWID materialized views may be used for materialized view
s based on master tables that do not have a primary key, or for materialized views that do not include all primary key columns of the
 master tables.

 
The following is an example of a CREATE MATERIALIZED VIEW statement that creates a ROWID materialized view:
CREATE MAT
ERIALIZED VIEW oe.orders REFRESH WITH ROWID AS
 SELECT * FROM oe.orders@orc1.world;






See Also:  

"Materialized View Log" for more information on the differences between a ROWID and primary key materialized view
Oracle Database SQL Reference for more information about the WITH ROWID clause in the CRE
ATE MATERIALIZED VIEW statement
 
Complex Materialized Views

A Compariso
n of Simple and Complex Materialized Views

 
For certain applications, y
ou may want to consider using a complex materialized view. Figure 3-2 and the following text di
scuss some issues that you should consider.
 
F
igure 3-2 Comparison of Simple and Complex Materialized Views
 
Text description of the illustration repln023.gif
<
ul class="LB1">
Complex Materialized View: Method A in
Figure 3-2 shows a complex materialized view. The materialized view in Database II exhibits eff
icient query performance because the join operation was completed during the materialized view's refresh. However, complete refreshes
 must be performed because the materialized view is complex, and these refreshes will probably be slower than fast refreshes.
Simple Materialized Views with a Joined View: Method B in
 Figure 3-2 shows two simple materialized views in Database II, as well as a view that performs
 the join in the materialized view's database. Query performance against the view would not be as good as the query performance again
st the complex materialized view in Method A. However, the simple materialized views can be refreshed more efficiently using fast ref
resh and materialized view logs.
 
In summary, to decide which method to use:

If you refresh rarely and want faster query performance, then use Method A
(complex materialized view).
If you refresh regularly and can sacrifice query pe
rformance, then use Method B (simple materialized view).
 
Required Privileges for Materialized View Operations

Creator Is Owner

 
If the creator of a materialized view also owns the materialized view, this user must have the following privileges to create a
 materialized view, granted either explicitly or through a role:

CREATE MATERIALIZED VIEW or CREATE ANY MATERIALIZED VIEW
CREATE TABLE or CREATE ANY TABLE
CREATE VIEW or CREATE ANY VIEW if the compatibility level of the database is lower than 8.1.0
SELECT object privilege on the master and the master's materialized view log or S
ELECT ANY TABLE system privilege. If the master site or master materialized view site is remote, the
n the SELECT object privilege must be granted to the user at the master site or master materialized view site to which t
he user at the materialized view site connects through a database link. 

If the owner of m
aterialized view at the materialized view site has a private database link to the master site or master materialized view site, then
the database link connects to the owner of the master at the master site or master materialized view site. Otherwise, the normal rule
s for connections through database links apply.
 
Creator Is Not Owner

 
If t
he creator of a materialized view is not the owner, certain privileges must be granted to the creator and to the owner to create a ma
terialized view. The creator's privileges can be granted explicitly or through a role, but the owner's privileges must be granted exp
licitly. That is, the privileges granted to the owner cannot be granted through a role.
 
Table 3-1 shows the required privileges when the creator of the materialized view is not the owner.<
/p>
 
 <
em>Table 3-1 Required Privileges for Creating Materialized Views (Creator != Owner)



 Creator
 Owner


 
CREATE ANY MATERIALIZED VIEW
 
CREATE TABLE or CREATE ANY TABLE 
CREATE VIEW or CREATE <
code>ANY VIEW if the compatibility level of the database is lower than 8.1.0 
SELECT object privilege on the master and the master's materialized view log or SELECT ANY
TABLE system privilege. If the master site or master materialized view site is remote, then the SELECT obje
ct privilege must be granted to the user at the master site or master materialized view site to which the user at the materialized vi
ew site connects through a database link. 
If the owner of materialized view at the materialize
d view site has a private database link to the master site or master materialized view site, then the database link connects to the o
wner of the master at the master site or master materialized view site. Otherwise, the normal rules for connections through database
links apply. 
Note: These privileges for the owner must be grante
d to the user explicitly, not through a role.
 
Refresher Is Owner

 
If the refresher of a materialized view also owns the materialized view, this user must have SELECT object privi
lege on the master and the master's materialized view log or SELECT ANY TABLE system privilege
. If the master site or master materialized view site is remote, then the SELECT object privilege must be granted to the
 user at the master site or master materialized view site to which the user at the materialized view site connects through a database
 link. This privilege can be granted either explicitly or through a role.
 
If the owner of materialized view at the materialized view site has a private database link to the master site or master m
aterialized view site, then the database link connects to the owner of the master at the master site or master materialized view site
. Otherwise, the normal rules for connections through database links apply.
 
Refresher Is Not Owner


 
If the refresher of a materialized view is not the owner, certain privileges must be granted to the refresher and to
the owner. These privileges can be granted either explicitly or through a role.
 
Table 3-2 shows the required privileges when the refresher of the materialized view is not the owner.
 
 Tab
le 3-2 Required Privileges for Refreshing Materialized Views (Refresher != Owner)



 Refresher
 Owner


 
ALTER ANY MATERIALIZED VIEW
 
If the master site or master materialized view site is local, then
SELECT object privilege on the master and master's materialized view log or SELECT ANY T
ABLE system privilege. 
If the master site or master materialized view site is remote, t
hen the SELECT object privilege must be granted to the user at the master site or master materialized view site to which the user at
the materialized view site connects through a database link. If the owner of materialized view at the materialized view site has a pr
ivate database link to the master site or master materialized view site, then the database link connects to the owner of the master a
t the master site or master materialized view site. Otherwise, the normal rules for connections through database links apply.
 
Data Subsetting with Materialized Views

 
In certain situations, y
ou may want your materialized view to reflect a subset of the data in the master table or master materialized view. Row subsetting en
ables you to include only the rows that are needed from the master in the materialized views by using a WHERE clause. Co
lumn subsetting enables you to include only the columns that are needed from the master in the materialized views. You do this by spe
cifying certain select columns in the SELECT statement during materialized view creation. If you use deployment template
s to build your materialized views, then you can define column subsets on updatable materialized views.
 
Some reasons to use data subsetting are to:

Reduce Network Traffic: In a column-subsetted materialized view, only changes that satisfy the WHERE clause of the materialized view's defining query are propagated to the materialized view site, thereby reducing the amount of data
transferred and reducing network traffic.
Secure Sensitive
Data: Users can only view data that satisfies the defining query for the materialized view.
Reduce Resource Requirements: If the materialized view is located on a laptop, the
n hard disks are generally significantly smaller than the hard disks on a corporate server. Subsetted materialized views may require
significantly less storage space.
Improve Refresh Times: Because less data is propagated to the materialized view site, the refresh process is faster, which is essential for those who
 need to refresh materialized views using a dial up network connection from a laptop.

 
F
or example, the following statement creates a materialized view based on the oe.orders@orc1.world master table and inclu
des only the rows for the sales representative with a sales_rep_id number of 173:
CREATE MATERIALIZED VIEW oe.orders REFRESH FAST AS
 SELECT * FROM oe.orders@orc1.world
 WHERE sales_rep_id = 173;




Rows of the orders table with a <
code>sales_rep_id number other than 173 are excluded from this materialized view.
 





Note:  
The following secti
ons discuss row subsetting through the use of subqueries. For more information about column subsetting, see "Column Subsetting with Deployment Templates".

 
Materialized Views with Subqueries<
/font>


 
The previous example works well for individual materialized views that
 do not have any referential constraints to other materialized views. But, if you want to replicate data based on the information in
more than one table, then maintaining and defining these materialized views may be difficult. The following sections provide examples
 of situations where a subquery is useful.
 
Many to One Subqueries

 
Consider a scenar
io where you have the customers table and orders table in the oe schema, and you want to creat
e a materialized view of the orders table based on data in both the orders table and the customers table. For example, suppose a salesperson wants to see all of the orders for the customers with a credit limit greater than $10,
000. In this case, the CREATE MATERIALIZED VIEW statement that creates the orders
 materialized view has a subquery with a many to one relationship, because there can be many orders for each customer.

 
Look at the relationships in Figure 3-3, and notice that the cu
stomers and orders tables are related through the customer_id column. The following statement satisf
ies the original goal of the salesperson. That is, the following statement creates a materialized view that contains orders for custo
mers whose credit limit is greater than $10,000:
CREATE MATERIALIZED VIEW oe.orders REFRESH F
AST FOR UPDATE AS
  SELECT * FROM oe.orders@orc1.world o
  WHERE EXISTS

   (SELECT * FROM oe.customers@orc1.world c
     WHERE o.customer_id = c.customer_id AND c.credit_limit > 1000
0);









Note:  
To create this oe.orders materialized view, credit_limit must be lo
gged in the master's materialized view log. See "Logging Columns in the Materialized View Log" for more information.

 
Figure 3-3 Row Subsetting with Many to One Subqueries
 
Text description of the illustratio
n repln088.gif
 
As you can see, the materialized view created by this statement is fast re
freshable and updatable. If new customers are identified that have a credit limit greater than $10,000, then the new data will be pro
pagated to the materialized view site during the subsequent refresh process. Similarly, if a customer's credit limit drops to less th
an $10,000, then the customer's data will be removed from the materialized view during the subsequent refresh process.
 
One to Many Subqueries


 
Consider a scenario where you have the customers table and orders table in the oe schema, and you want to create a materialized view of the customers table
 based on data in both the customers table and the orders table. For example, suppose a salesperson wants t
o see all of the customers who have an order with an order total greater than $20,000, then the most efficient method is to create a
materialized view with a one to many subquery in the defining query of a materialized view.
 
H
ere, the defining query in the CREATE MATERIALIZED VIEW statement on the customers table has a subquery with a one to many relationship. That is, one customer can have many orders.
 
Look at the relationships in Figure 3-4, and notice that the orders table a
nd customers table are related through the customer_id column. The following statement satisfies the origin
al goal of the salesperson. That is, this statement creates a materialized view that contains customers who have an order with an ord
er total greater than $20,000:
CREATE MATERIALIZED VIEW oe.customers REFRESH FAST FOR UPDATE
AS
  SELECT * FROM oe.customers@orc1.world c
  WHERE EXISTS
    (SELECT *
 FROM oe.orders@orc1.world o
     WHERE c.customer_id = o.customer_id AND o.order_total > 20000);









Note:  
To create this oe.customers materialized view, customer_id and order_total must be logged in the materialized view log for the orders table. See "Logging Columns
in the Materialized View Log" for more information.


 
Figure 3-4 Row Subsetting with One to Many Subqueries

 
Text description of the illustration repln087.gif
 
The materialized view created by t
his statement is fast refreshable and updatable. If new customers are identified that have an order total greater than $20,000, then
the new data will be propagated to the materialized view site during the subsequent refresh process. Similarly, if a customer cancels
 an order with an order total greater than $20,000 and has no other order totals greater than $20,000, then the customer's data will
be removed from the materialized view during the subsequent refresh process.
 

<
/tr>




Note:  
The materialized view site must have a compa
tibility level of 9.0.1 or higher because fast refresh of materialized views with one to many subqueries was not supported prior to r
elease 9.0.1 of Oracle. The compatibility level is controlled by the COMPATIBLE initialization parameter.

 
Many to Many Subqueries

 
Consider a scenario where you have the order_items table and inventories table in the oe schema, and you want to create a materialized vie
w of the inventories table based on data in both the inventories table and the order_items tab
le. For example, suppose a salesperson wants to see all of the inventories with a quantity on hand greater than 0 (zero) for each pro
duct whose product_id is in the order_items table. In other words, the salesperson wants to see the invento
ries that are greater than zero for all of the products that customers have ordered. Here, an inventory is a certain quantity of a pr
oduct at a particular warehouse. So, a certain product can be in many order items and in many inventories.
 
To accomplish the salesperson's goal, you can create a materialized view with a subquery on the many to many relationsh
ip between the order_items table and the inventories table.
 
When yo
u create the inventories materialized view, you want to retrieve the inventories with the quantity on hand greater than
zero for the products that appear in the order_items table. Look at the relationships in Fi
gure 3-5, and note that the inventories table and order_items table are related through the 
product_id column. The following statement creates the materialized view:
CREATE MATER
IALIZED VIEW oe.inventories REFRESH FAST FOR UPDATE AS
  SELECT * FROM oe.inventories@orc1.world i
  WHERE i.quantity_on_hand > 0 AND EXISTS
    (SELECT * FROM oe.order_items@orc1.world o
     WHERE i.product_id = o.product_id);









Note:  
To create this oe.inventories material
ized view, the product_id column in the order_items table must be logged in the master's materialized view
log. See "Logging Columns in the Materialized View Log" for more info
rmation.

 
Figure
3-5 Row Subsetting with Many to Many Subqueries
 
Text description of the illustration repln089.gif
 
The materialized view created by this statement is fast refreshable and updatable. If new inventories that are g
reater than zero are identified for products in the order_items table, then the new data will be propagated to the mater
ialized view site during the subsequent refresh process. Similarly, if a customer cancels an order for a product and there are no oth
er orders for the product in the order_items table, then the inventories for the product will be removed from the materi
alized view during the subsequent refresh process.
 





Note:  
The materialized view site must have a compatibility level of 9.0.1 or
 higher because fast refresh of materialized views with many to many subqueries was not supported prior to release 9.0.1 of Oracle. T
he compatibility level is controlled by the COMPATIBLE initialization parameter.

 
Materialized Views with
 Subqueries and Unions

 
In situations where you want a single materiali
zed view to contain data that matches the complete results of two or more different queries, you can use the UNION opera
tor. When you use the UNION operator to create a materialized view, you have two SELECT statements around e
ach UNION operator, one is above it and one is below it. The resulting materialized view contains rows selected by eithe
r query.
 
You can use the UNION operator as a way to create fast refreshable mate
rialized views that satisfy "or" conditions without using the OR expression in the WHERE clause of a subque
ry. Under some conditions, using an OR expression in the WHERE clause of a subquery causes the resulting ma
terialized view to be complex, and therefore not fast refreshable.
 



See Also:  
"Restrictions f
or Materialized Views with Subqueries" for more information about the OR expression
s in subqueries
 
For example, suppose a salesperson wants the product
information for the products in a particular category_id that are either in a warehouse in Calif
ornia or contain the word "Rouge" in their translated product descriptions (for the French translation). The
following statement uses the UNION operator and subqueries to capture this data in a materialized view for products in <
code>category_id 29:
CREATE MATERIALIZED VIEW oe.product_information REFRESH FAST FOR
UPDATE AS
 SELECT * FROM oe.product_information@orc1.world pi
 WHERE pi.category_id = 29 AND
EXISTS
  (SELECT * FROM oe.product_descriptions@orc1.world pd
  WHERE pi.product_id = pd.prod
uct_id AND 
        pd.translated_description LIKE '%Rouge%')  
UNION
 SE
LECT * FROM oe.product_information@orc1.world pi
 WHERE pi.category_id = 29 AND EXISTS
  (SEL
ECT * FROM oe.inventories@orc1.world i
  WHERE pi.product_id = i.product_id AND EXISTS
    (S
ELECT * FROM oe.warehouses@orc1.world w
    WHERE i.warehouse_id = w.warehouse_id AND EXISTS

      (SELECT * FROM hr.locations@orc1.world l
       WHERE w.location_id = l.location_id 

     AND l.state_province = 'California')));   
  








Note:  
To create the oe.product_information materialized view, translated_description in the product_descriptions table and state_province in the locations table must be logged in each master's materialized view log. See "Loggi
ng Columns in the Materialized View Log" for more information.


<
a name="52002"> 
Figure 3-6 shows the relationships of the master tables invo
lved in this statement.
 
Figure 3-6 Row Subset
ting with Subqueries and Unions
 
Text description of the illustration repln090.gif
 
In addition to the UNION operation, this statement contains the following subqueries:

A subquery referencing the product_information table and the product_descr
iptions table. This subquery is one to many because one product can have multiple product descriptions (for different language
s).
A subquery referencing the product_information table and the inventories table. This subquery is one to many because a product can be in many inventories.
A subquery referencing the inventories table and the warehouses table. This subque
ry is many to one because many inventories can be stored in one warehouse.
A sub
query referencing the warehouses table and the locations table. This subquery is many to one because many w
arehouses can be in one location.
 
The materialized view created by this statement is fa
st refreshable and updatable. If a new product is added that is stored in a warehouse in California or that has the string "Rouge" in
 the translated product description, then the new data will be propagated to the product_information materialized view d
uring the subsequent refresh process.
 





Note:  
The materialized view site must have a compatibility level of 9.0.1 or higher becau
se fast refresh of materialized views with a UNION operator was not supported prior to release 9.0.1 of Oracle. Also, fa
st refresh of materialized views with many to one subqueries requires 9.0.1 or higher compatibility. The compatibility level is contr
olled by the COMPATIBLE initialization parameter.

 
Restrictions for Materialized Views with Subqueries


 
The defining query of a materialized view with a subquery is subject to
several restrictions to preserve the materialized view's fast refresh capability.
 
The followi
ng are restrictions for fast refresh materialized views with subqueries:

Materialized views must be primary key materialized views.
The master's
 materialized view log must include certain columns referenced in the subquery. For information about which columns must be included,
 see "Logging Columns in the Materialized View Log".
If the subquery is many to many or one to many, join columns that are not part of a primary key
must be included in the materialized view log of the master. This restriction does not apply to many to one subqueries.
The subquery must be a positive subquery. For example, you can use the EXISTS co
ndition, but not the NOT EXISTS condition.
The subquer
y must use EXISTS to connect each nested level (IN is not allowed).
Each table can be in only one EXISTS expression.
The
join expression must use exact match or equality comparisons (that is, equi-joins).

Each table can be joined only once within the subquery.
A primary key must e
xist for each table at each nested level.
Each nested level can only reference t
he table in the level above it.
Subqueries can include AND conditio
ns, but each OR condition may only reference columns contained within one row. Multiple OR conditions withi
n a subquery can be connected with an AND condition.
All tables ref
erenced in a subquery must reside in the same master site or master materialized view site. 






Note:  
If the CREATE
 MATERIALIZED VIEW statement includes an ON PREBUILT TABLE clause an
d a subquery, then the subquery is treated as many to many. Therefore, in this case, the join columns must be recorded in the materia
lized view log. See the Oracle Database SQL Reference for more information about the ON PREBUILT TABLE cla
use in the CREATE MATERIALIZED VIEW statement.

 



See Also:  
"Primary Key Materialized Views" for more information about pri
mary key materialized views
 
Restrictions for Materialized Views with Unions Containing Subqueries

 
The following are restrictions for fast refresh materialized views with unions conta
ining subqueries:

All of the restrictions described in the previ
ous section, "Restrictions for Materialized Views with Subqueries",
 apply to the subqueries in each union block.
All join columns must be included
in the materialized view log of the master, even if the subquery is many to one.
All of the restrictions described in the previous section, "Complex Materialized Views", for clauses with UNIONS.
 
Examples of Materialized Views with Unions Containing Subqueries

 
The following statement creates the oe.orders materialized view. This m
aterialized view is fast refreshable because the subquery in each union block satisfies the restrictions for subqueries described in
"Restrictions for Materialized Views with Subqueries".
CREATE MATERIALIZED VIEW oe.orders REFRESH FAST AS
  SELECT * FROM oe.orders@orc1.wor
ld o
  WHERE EXISTS
    (SELECT * FROM oe.customers@orc1.world c
     WHE
RE o.customer_id = c.customer_id 
     AND c.credit_limit > 50)
UNION    

  SELECT * 
  FROM oe.orders@orc1.world o
  WHERE EXISTS
    (SELECT
* FROM oe.customers@orc1.world c
     WHERE o.customer_id = c.customer_id 
     AND c.account
_mgr_id = 30);  




Notice that one of the restrictions for subqueries s
tates that each table can be in only one EXISTS expression. Here, the customers table appears in two 
EXISTS expressions, but the EXISTS expressions are in separate UNION blocks. Because the restriction
s described in "Restrictions for Materialized Views with Subqueries" only apply to each UNION block, not to the entire CREATE MATERIALIZED VIEW stat
ement, the materialized view is fast refreshable.
 
In contrast, the materialized view created
with the following statement cannot be fast refreshed because the orders table is referenced in two different EXIS
TS expressions within the same UNION block:
CREATE MATERIALIZED VIEW oe.o
rders AS
  SELECT * FROM oe.orders@orc1.world o
  WHERE EXISTS
    (SELEC
T * FROM oe.customers@orc1.world c
     WHERE o.customer_id = c.customer_id  -- first reference to orders table
<
a name="47703">     AND c.credit_limit > 50
     AND EXISTS
        (SELECT * FROM oe.
orders@orc1.world o
         WHERE order_total > 5000 
         AND o.customer_id = c.cust
omer_id)) -- second reference to orders table
UNION    
  SELECT * 
  FRO
M oe.orders@orc1.world o
  WHERE EXISTS
    (SELECT * FROM oe.customers@orc1.world c
     WHERE o.customer_id = c.customer_id 
     AND c.account_mgr_id = 30);  




Determining the Fast Refresh Capab
ilities of a Materialized View

 
To determine whether a materialized vie
w's subquery satisfies the restrictions detailed in the previous section, create the materialized view with fast refresh. Oracle retu
rns errors if the materialized view violates any restrictions for subquery materialized views. If you specify force refresh, then you
 may not receive any errors because, when a force refresh is requested, Oracle automatically performs a complete refresh if it cannot
 perform a fast refresh.
 
You can also use the EXPLAIN_MVIEW procedure in the DBMS_MVIEW package to determine the following information about an existing materialized view or a proposed materialized vi
ew that does not yet exist:

The capabilities of a materialized v
iew
Whether each capability is possible
If a capability is not possible, why it is not possible
 
This information c
an be stored in a varray or in the MV_CAPABILITIES_TABLE. If you want to store the information in the table, then, befor
e you run the EXPLAIN_MVIEW procedure, you must build this table by running the utlxmv.sql script in the Oracle_home/rdbms/admin directory.
 
For example, to determi
ne the capabilities of the oe.orders materialized view, enter:
EXECUTE DBMS_MVIE
W.EXPLAIN_MVIEW ('oe.orders');




Or, if the materialized view does not
yet exist, then you can supply the query that you want to use to create it:
BEGIN
  DBMS_MVIEW.EXPLAIN_MVIEW ('SELECT * FROM oe.orders@orc1.world o
    WHERE EXISTS (SELECT * FROM oe.cus
tomers@orc1.world c
    WHERE o.customer_id = c.customer_id AND c.credit_limit > 500)');
E
ND;
/




Query the MV_CAPABILITIES_TABLE to see the results.
 



See Also:
  
Oracl
e Data Warehousing Guide for more information about the EXPLAIN_MVIEW procedure
 
Multitier Materializ
ed Views

 
The ability to create materialized views that are based on ot
her materialized views enables you to create multitier materialized views. Materialized views that are
based on other materialized views can be read-only or updatable. The arrows in Figure 3-7 repre
sent database links.
 
Figure 3-7 Multitier Mate
rialized Views
 
Text description of the illustration repln085.gif
 
When you are
 using multitier materialized views, the materialized view based on a master table is called a level 1 materialized view. Then, a mat
erialized view based on the level 1 materialized view is called a level 2 materialized view. Next is level 3 and so on. Figure 3-8 shows these levels.
 
Figure 3-8 Levels of Materialized Views
 
Text description of the illustration repln095.gif
 
A materialized view that is acting as the master for another materialized view is called a master materialized view. A materialized view at any level can be a master materialized view, and, as you can see in Figure 3-8, a master materialized view can have more than one materialized view based on it. In Figure 3-8, two level 2 materialized views are based on one level 1 materialized view.
 
Figure 3-9 illustrates an example that shows a master materialized
 view at level 1 (orders_1) and level 2 (orders_2).
 
Figure 3-9 Master Materialized Views
 
Text description of the illustration repln097.
gif
 
The master for the level 1 materialized view orders_1 is the master tabl
e orders at the master site, but, starting with level 2, each materialized view has a master materialized view at the le
vel above it. For example, the master for the level 2 materialized view orders_2 is the level 1 materialized view 
orders_1.
 
A master materialized view functions the same way a master table does at a m
aster site. That is, changes pushed from a level 2 materialized view to a level 1 materialized view are handled in exactly the same w
ay that changes pushed from a level 1 materialized view to a master table are handled.
 
A rece
iver must be registered at a master materialized view site. The receiver is responsible for receiving and applying the deferred trans
actions from the propagator at multitier materialized view sites that are based on the master materialized view.

 



See Also:  
"Receiver"
 
Multitier materialized views offer greater flexibility in the design of a replication environment. Some materialized view s
ites may not need to replicate all of the data in master tables, and, in fact, these sites may not have the storage capacity for all
of the data. In addition, replicating less data means that there is less activity on the network.
 
Multitier materialized views are ideal for organizations that are structured on three or more levels or constrained by limited n
etwork resources. For example, consider a company with international, national, and local offices. This company has many computers at
 both the national and local level that replicate data. Here, the replication environment can be configured with the master site at t
he international headquarters and with materialized views at the national level. These materialized views at the national level only
replicate the subset of data from the master tables that apply to their respective countries. Now, using multitier materialized views
, another level of materialized views at the local level can be based on the materialized views at the national level. The materializ
ed views at the local level contain the subset of data from the level 1 materialized views that apply to their local customers.
<
a name="46249"> 
Scenario for Using
 Multitier Materialized Views

 
Consider a multinational company that ma
intains all employee information at headquarters, which is in the in the United States. The company uses the tables in the hr schema to maintain the employee information. This company has one main office in 14 countries and many regional offices for cit
ies in these countries.

 
For example, the company has one main office for all of the United Ki
ngdom, but it also has an office in the city of London. The United Kingdom office maintains employee information for all of the emplo
yees in the United Kingdom, while the London office only maintains employee information for the employees at the London office. In th
is scenario, the hr.employees master table is at headquarters in the United States and each regional office has a an hr.employees materialized view that only contains the necessary employee information.
 
The following statement creates the hr.employees materialized view for the United Kingdom office. The statement queri
es the master table in the database at headquarters, which is orc1.world. Notice that the statement uses subqueries so t
hat the materialized view only contains employees whose country_id is UK.
CREATE MATERIALIZED VIEW hr.employees REFRESH FAST FOR UPDATE AS
  SELECT * FROM hr.employees@orc1.world e

    WHERE EXISTS
      (SELECT * FROM hr.departments@orc1.world d

 WHERE e.department_id = d.department_id
       AND EXISTS
         (SELECT * FROM hr.locatio
ns@orc1.world l
          WHERE l.country_id = 'UK'
          AND d.location_id = l.location_
id));









Note:  
To create this hr.employees materialized view, the following columns must be l
ogged:

The department_id column must be logged in the
 materialized view log for the hr.employees master table at orc1.world.
The country_id must be logged in the materialized view log for the hr.locations master tab
le at orc1.world.
 
See "Logging Columns in the
Materialized View Log" for more information.

 
The following statement creates the hr.employees materialized view for the London office based on the l
evel 1 materialized view at the United Kingdom office. The statement queries the materialized view in the database at the United King
dom office, which is reg_uk.world. Notice that the statement uses subqueries so that the materialized view only contains
 employees whose city is London.
CREATE MATERIALIZED VIEW hr.employ
ees REFRESH FAST FOR UPDATE AS
  SELECT * FROM hr.employees@reg_uk.world e
    WHERE EXISTS
<
a name="57005">      (SELECT * FROM hr.departments@reg_uk.world d
       WHERE e.department_id = d.department
_id
       AND EXISTS
         (SELECT * FROM hr.locations@reg_uk.world l
          WHERE l.city = 'London'
          AND d.location_id = l.location_id));









<
a name="57129">Note:  
To create this hr.employees materialized view, the following columns must be logged:

The department_id column must be logged in the materialized view log for the 
hr.employees master materialized view at reg_uk.world.
The country_id must be logged in the materialized view log for the hr.locations master materialized view at reg_uk.world.
 
See "Logging Columns in the Materiali
zed View Log" for more information.

 
Restrictions for Using Multitier Materialized
 Views

 
Both master materialized views and materialized views based on
materialized views must:

Be primary key materialized views
Reside in a database that is at 9.0.1 or higher compatibility level 






Note:  
Th
e COMPATIBLE initialization parameter controls a database's compatibility level.

 
Additional Re
strictions for Master Materialized Views

 
The following types of materi
alized views cannot be masters for updatable materialized views:

ROWID materialized views
Complex materialized views
Read-only materialized views
 
However, these type
s of materialized views can be masters for read-only materialized views.
 
Additional Restrictions for Updatable Materialized Views Based on Materia
lized Views

 
Updatable materialized views based on materialized views m
ust:

Belong to a materialized view group that has the same name
as the materialized view group at its master materialized view site
Reside in a
different database than the materialized view group at its master materialized view site
Be based on another updatable materialized view or other updatable materialized views, not on a read-only materialized view<
/li>
Be based on a materialized view in a materialized view group that is owned by PUBLIC at the master materialized view site.
 
How Materialized Views Work with Object Types and Collections

 
Oracle object types are user-def
ined datatypes that make it possible to model complex real-world entities such as customers and orders as single entities, called objects, in the database. You create object types using the CREATE TYPE ..
. AS OBJECT statement. You can replicate object types and objects between master sites and materiali
zed view sites in a replication environment.
 
An Oracle object that occupies a single column i
n a table is called a column object. Typically, tables that contain column objects also contain other c
olumns, which may be built-in datatypes, such as VARCHAR2 and NUMBER. An object table<
/strong> is a special kind of table in which each row represents an object. Each row in an object table is a row
 object.

 
You can also replicate collections. Collectio
ns are user-defined datatypes that are based on VARRAY and nested table datatypes. You create varrays with the CRE
ATE TYPE ... AS VARRAY statement, and you create nested tables with the CREATE TYPE ... AS TABLE statement.
 





Note:  

Both the master site and the materialized view site must have a compatibility level of 9.0.1 or higher to
replicate user-defined types and any objects on which they are based. The compatibility level is controlled by the COMPATIBLE initialization parameter.

You cannot create refresh-on-commit materialized
views based on a master with user-defined types. Refresh-on-commit materialized views are those created using the ON COMMIT REFRESH clause in the CREATE MATERIALIZED VIEW statement.
<
li class="NL" type="disc">Advanced Replication does not support type inheritance.

 



See Also:  
Oracle Database Applica
tion Developer's Guide - Object-Relational Features for detailed information about user-defined types, Oracle objects, and c
ollections. This section assumes a basic understanding of the information in that book.
<
/a> 
Type Agreement at Replication Site
s

 
User-defined types include all types created using the CREATE<
/code> TYPE statement, including object, nested table, VARRAY, and indextype. To replicate schema objects b
ased on user-defined types, the user-defined types themselves must exist, and must be exactly the same, at all replication sites. In
addition, Oracle Corporation recommends that you add a user-defined type to the replication group in which it is used, but doing so i
s not required.

 
When replicating user-defined types and the schema objects on which they are
based, the following conditions apply:

The user-defined types re
plicated at the master site and materialized view site must be created at the materialized view site before you create any materializ
ed views that depend on these types.
All of the masters on which a materialized
view is based must be at the same master site to create a materialized view with user-defined types.
A user-defined type must be exactly the same at all replication sites: 

All replication sites must have the same object identifier (OID), schema owner, and type name for each rep
licated user-defined type.
If the user-defined type is an object type, then al
l replication sites must agree on the order and datatype of the attributes in the object type. You establish the order and datatypes
of the attributes when you create the object type. For example, consider the following object type: 
CREATE TYPE cust_address_typ AS OBJECT
     (street_address     VARCHAR2(40), 
      pos
tal_code        VARCHAR2(10), 
      city               VARCHAR2(30), 
      state_province
   VARCHAR2(10), 
      country_id         CHAR(2));
/




At all replication sites, street_address must be the first attribute for this type and must b
e VARCHAR2(40), postal_code must be the second attribute and must be VARCHAR2(10), city<
/code> must be the third attribute and must be VARCHAR2(30), and so on.

All replication sites must agree on the hashcode of the user-defined type. Oracle examines a user-defined type and assigns
 the hashcode. This examination includes the type attributes, order of attributes, and type name. When all of these items are the sam
e for two or more types, the types have the same hashcode. You can view the hashcode for a type by querying the DBA_TYPE_VERSIO
NS data dictionary view.

 
To ensure that a user-defined type is exactly
 the same at all replication sites, you must create the user-defined type at the materialized view site in one of the following ways:


Use the Replication Management API

Use a CREATE TYPE Statement
 
Use the Replication M
anagement API

 
Oracle Corporation recommends that you use the replicati
on management API to create, modify, or drop any replicated object at a materialized view site, including user-defined types. If you
do not use the replication management API for these actions, then replication errors may result.
 
Specifically, to create a user-defined type that is exactly the same at the master site and the materialized view site, use the <
code>CREATE_MVIEW_REPOBJECT procedure in the DBMS_REPCAT package. This procedure creates the type and adds it to
a materialized view group. To drop a user-defined type from the materialized view site, use the DROP_MVIEW_REPOBJECT pro
cedure in the DBMS_REPCAT package.
 



See Also:  
Oracle Database Advanced Replication Management API Reference
 
Use a CREATE
TYPE Statement

 
You can use a CREATE TYPE sta
tement at the materialized view site to create the type. It may be necessary to do this if you want to create a read-only materialize
d view that uses the type, and you do not want to add the read-only materialized view to a materialized view group.
 
If you choose this option, then you must ensure the following:

The type is in the same schema at both the materialized view site and the master site.
The type has exactly the same attributes in exactly the same order at both the materialized view site an
d the master site.
The type has exactly the same datatype for each attribute at
both the materialized view site and the master site.
The type has the same objec
t identifier at both the materialized view site and the master site.
 
You can find the o
bject identifier for a type by querying the DBA_TYPES data dictionary view. For example, to find the object identifier (
OID) for the cust_address_typ, enter the following query:
SELECT TYPE_OID FROM D
BA_TYPES WHERE TYPE_NAME = 'CUST_ADDRESS_TYP';

TYPE_OID
----------------
----------------
6F9BC33653681B7CE03400400B40A607




Now that you know the OID for the type at the master site, complete the following steps to create the type at the materialized view
site:

Log in to the materialized view site as th
e user who owns the type at the master site. If this user does not exist at the materialized view site, then create the user.
Issue the CREATE TYPE statement and specify the OID: 
CREATE TYPE oe.cust_address_typ OID '6F9BC33653681B7CE03400400B40A607' 

 AS OBJECT (
     street_address     VARCHAR2(40), 
     postal_code        VARCHAR2(10), 
     city               VARCHAR2(30), 
     state_province     VARCHAR2(10), 
<
/a>     country_id         CHAR(2));
/





The type is now ready for use at the materialized view site.
 
Column Subsetting of Masters with Column Objects

 
A read-only materialized view can replicate specific attributes of a column object without replicating oth
er attributes. For example, using the cust_address_typ user-defined datatype described in the previous section, suppose
a customers_sub master table is created at master site orc1.world:

CREATE TABLE oe.customers_sub (
      customer_id        NUMBER(6)  PRIMARY KEY, 
      cust_
first_name    VARCHAR2(20), 
      cust_last_name     VARCHAR2(20),
      cust_address
oe.cust_address_typ);




You can create the following read-only material
ized view at a remote materialized view site:
CREATE MATERIALIZED VIEW oe.customers_mv1 AS
   SELECT customer_id, cust_last_name, c.cust_address.postal_code
   FROM oe.customers_sub@orc1
.world c;




Notice that the postal_code attribute is speci
fied in the cust_address column object.
 
An updatable materialized view must repl
icate the entire column object. It cannot replicate some attributes of a column object but not others. The following statement is val
id because it specifies the entire cust_address column object:
CREATE MATERIALIZ
ED VIEW oe.customers_mv1 FOR UPDATE AS
   SELECT customer_id, cust_last_name, cust_address

 FROM oe.customers_sub@orc1.world;






Se
e Also:  
"Column Subsetting with Deployment Templates" for more information about column subsetting with deployment templates. Column subsetting is support
ed only through the use of deployment templates.
 
Materialized Views Based on Object Tables

 
If a materialized view is based on an object table and is created using the OF type clause, then the materialized view is called an object materialized view. An object
 materialized view is structured in the same way as an object table. That is, an object materialized view is composed of row objects.
 If a materialized view that is based on an object table is created without using the OF type cl
ause, then the materialized view is read-only and is not an object materialized view. That is, such a materialized view has regular r
ows, not row objects.
 
To create a materialized view based on an object table, the types on wh
ich the materialized view depends must exist at the materialized view site, and each type must have the same object identifier as it
does at the master site.
 
Creation of Object Materialized Views Using the OF type Clause

 
After the required types are created at the materialized view site, you can create an object materialize
d view by specifying the OF type clause.
 
For example, su
ppose the following SQL statements create the oe.categories_tab object table at the orc1.world master site:

CREATE TYPE oe.category_typ AS OBJECT
   (category_name           VARCHA
R2(50), 
    category_description    VARCHAR2(1000), 
    category_id             NUMBER(2))
NOT FINAL;
/

CREATE TABLE oe.categories_tab OF oe.ca
tegory_typ
    (category_id    PRIMARY KEY);




If y
ou want to create materialized views that can be fast refreshed based on the oe.categories_tab master table, then create
 a materialized view log for this table:
CREATE MATERIALIZED VIEW LOG ON oe.categories_tab WI
TH OBJECT ID;




The WITH OBJECT ID clause is required when you create a materialized view log on an object table.

 
After you c
reate the oe.category_typ type at the materialized view site with the same object identifier as the same type at the mas
ter site, you can create an object materialized view based on the oe.categories_tab object table using the OF type clause, as in the following SQL statement:

CREATE MATERIALIZE
D VIEW oe.categories_objmv OF oe.category_typ 
   REFRESH FAST FOR UPDATE
   AS SELECT * FROM
 oe.categories_tab@orc1.world;




Here, type is
oe.category_typ.
 





Note:  
The types must be exactly the same at the materialized view site and master site. See "Type Agreement at Replication Sites" for more information.

 
Materialized Views Based on Object Tables Created Without Using the OF type Clause

 
If you create a materialized view based on an object table without using the OF
 type clause, then the materialized view is read-only, and it loses the object properties of the object table
 on which it is based. That is, the resulting read-only materialized view contains one or more of the columns of the master, but each
 row functions as a row in a relational table. The rows are not row objects.
 
For example, you
 can create a materialized view base on the categories_tab master by using the following SQL statement:
CREATE MATERIALIZED VIEW oe.categories_relmv
   AS SELECT * FROM oe.categories_tab@orc1.w
orld;




In this case, the categories_relmv materialized vi
ew must be read-only, and the rows in this materialized view function in the same way as rows in a relational table.
 
OID Preservation in Object Ma
terialized Views

 
An object materialized view inherits the object ident
ifier (OID) specifications of its master. If the master has a primary key-based OID, then the OIDs of row objects in the materialized
 view are primary key-based. If the master has a system generated OID, then the OIDs of row objects in the materialized view are syst
em generated. Also, the OID of each row in the object materialized view matches the OID of the same row in the master, and the OIDs a
re preserved during refresh of the materialized view. Consequently, REFs to the rows in the object table remain valid at
 the materialized view site.
 
Materialized Views with Collection Columns

 
Collection
columns are columns based on varray and nested table datatypes. Oracle supports the creation of materialized views with collection co
lumns.
 
If the collection column is a nested table, then you can optionally specify the nested_table_storage_clause during materialized view creation. The nested_table_storage_clause lets you specify the name of the storage table for the nested table in the materialized view. For example, suppose you create the
 master table people_reltab at the master site orc1.world that contains the nested table phones_ntab<
/code>:

CREATE TYPE oe.phone_typ AS OBJECT (
   location    VARCHAR2(15),

   num         VARCHAR2(14));
/

CREATE TYPE oe.phon
e_ntabtyp AS TABLE OF oe.phone_typ;
/

CREATE TABLE oe.people_reltab (
   id               NUMBER(4) CONSTRAINT pk_people_reltab PRIMARY KEY,
   first_name       VARC
HAR2(20),
   last_name        VARCHAR2(20),
   phones_ntab      oe.phone_ntabtyp)
   NESTED TABLE phones_ntab STORE AS phone_store_ntab
   ((PRIMARY KEY (NESTED_TABLE_ID, location)));




Notice the PRIMARY KEY specification in the l
ast line of the preceding SQL statement. You must specify a primary key for the storage table if you plan to create materialized view
s based on its parent table. In this case, the storage table is phone_store_ntab and the parent table is people_re
ltab.
 
If you want to create materialized views that can be fast refreshed, then create
 a materialized view log on both the parent table and the storage table, specifying the nested table column as a filter column for th
e parent table's materialized view log:
CREATE MATERIALIZED VIEW LOG ON oe.people_reltab;

ALTER MATERIALIZED VIEW LOG ON oe.people_reltab ADD(phones_ntab);

CREATE MATERIALIZED VIEW LOG ON oe.phone_store_ntab WITH PRIMARY KEY;




<
p class="BP">At the materialized view site, create the required types, ensuring that the object identifier for each type is the same
as the object identifier at the master site. Then, you can create a materialized view based on people_reltab and specify
 its storage table using the following statement:

CREATE MATERIALIZED VIEW oe.people_reltab_m
v
   NESTED TABLE phones_ntab STORE AS phone_store_ntab_mv 
   REFRESH FAST AS SELECT * FROM
oe.people_reltab@orc1.world;




In this case, the nes
ted_table_storage_clause is the line that begins with "NESTED TABLE" in the previous example, and it s
pecifies that the storage table's name is phone_store_ntab_mv. The nested_table_storage_clause i
s optional. If you do not specify this clause, Oracle automatically names the storage table. To view the name of a storage table, que
ry the DBA_NESTED_TABLES data dictionary table.
 
The storage table:

Is a separate, secondary materialized view
<
a name="50102">Is refreshed automatically when you refresh the materialized view containing the nested table
Is dropped automatically when you drop the materialized view containing the nested table
Inherits the primary key constraint of the master's storage table
 
Because the storage table inherits the primary key constraint of the master's storage table, do not specify PRIMARY KEY in the STORE AS clause.
 
The follo
wing actions are not allowed directly on the storage table of a nested table in a materialized view:

Refreshing the storage table
Adding the sto
rage table to a replication group
Altering the storage table
Dropping the storage table
Generating replicat
ion support on the storage table
 
These actions can occur indirectly when they are perfo
rmed on the materialized view that contains the nested table. In addition, you cannot replicate a subset of the columns in a storage
table.
 

See Also:  
Oracle Database SQL Reference for more information about the nested_table_storage_clause, wh
ich is fully documented in the CREATE TABLE statement
 
Restrictions for Materialized Views with C
ollection Columns

 
The following restrictions apply to materialized vie
ws with collection columns:

Row subsetting of collection columns
 is not allowed. However, you can use row subsetting on the parent table of a nested table and doing so can result in a subset of the
 nested tables in the materialized view.
Column subsetting of collection columns
 is not allowed.
A nested table's storage table must have a primary key.
For the parent table of a nested table to be fast refreshed, both the parent table and
the nested table's storage table must have a materialized view log.
 
Materialized Views with REF Columns

 
You can create materialized views with REF columns. A REF is an Oracle built-in
datatype that is a logical "pointer" to a row object in an object table. A scoped REF is a REF that can con
tain references only to a specified object table, while an unscoped REF can contain references to any object table in th
e database that is based on the corresponding object type. A scoped REF requires less storage space and provides more ef
ficient access than an unscoped REF.
 
As you will see in the following secti
on, you can rescope a REF column to a local materialized view or table at the materialized view site during creation of
the materialized view. If you do not rescope the REF column, then they continue to point to the remote master. Unscoped
REF columns always continue to point to the master. When a REF column at a materialized view site points to
 a remote master, the REFs are considered dangling. In SQL, dereferencing a dangling REF returns a NU
LL. Also, PL/SQL only supports dereferencing REFs by using the UTL_OBJECT package and raises an exce
ption for dangling REFs.
 
Scoped REF Columns

 
If you are creating a mate
rialized view based on a master that has a scoped REF column, then you can rescope the REF to a different o
bject table or object materialized view at the materialized view site. Typically, you would rescope the REF column to th
e local object materialized view instead of the original remote object table. To rescope a materialized view, you can either use the
SCOPE FOR clause in the CREATE MATERIALIZED VIEW statement, or you c
an use the ALTER MATERIALIZED VIEW statement after creating the materialized view. If you do n
ot rescope the REF column, then the materialized view retains the REF scope of the master.
 
For example, suppose you create the customers_with_ref master table at the orc1.world master site using the following statements:

-- Create the user-defined datatype cust_addre
ss_typ.
CREATE TYPE oe.cust_address_typ AS OBJECT
   (street_address     VARCHAR2(40),
    postal_code        VARCHAR2(10),
    city               VARCHAR2(30),
    s
tate_province     VARCHAR2(10),
    country_id         CHAR(2));
/

-- Create the object table cust_address_objtab.
CREATE TABLE oe.cust_address_objtab OF oe.cust_add
ress_typ;

-- Create table with REF to cust_address_typ.
CREATE TABLE oe.
customers_with_ref (
             customer_id        NUMBER(6) PRIMARY KEY, 
     cust_first_
name    VARCHAR2(20), 
     cust_last_name     VARCHAR2(20),
     cust_address       REF oe.c
ust_address_typ 
                          SCOPE IS oe.cust_address_objtab);




Assuming the cust_address_typ exists at the materialized view site with the same object iden
tifier as the type at the master site, you can create a cust_address_objtab_mv object materialized view using the follow
ing statement:
CREATE MATERIALIZED VIEW oe.cust_address_objtab_mv OF oe.cust_address_typ AS 
   SELECT * FROM oe.cust_address_objtab@orc1.world;




Now, you can create a materialized view of the customers_with_ref master table and rescope the REF to th
e cust_address_objtab_mv materialized view using the following statement:
CREATE
 MATERIALIZED VIEW oe.customers_with_ref_mv
   (SCOPE FOR (cust_address) IS oe.cust_address_objtab_mv)
   AS SELECT * FROM oe.customers_with_ref@orc1.world;




If yo
u want to use the SCOPE FOR clause when you create a materialized view, then remember to create the materia
lized view or table specified in the SCOPE FOR clause first. Otherwise, you cannot specify the SCOPE<
/code> FOR clause during materialized view creation. For example, if you had created the customers_with_ref_mv materialized view before you created the cust_address_objtab_mv materialized view, then you could not use the 
SCOPE FOR clause when you created the customers_with_ref_mv materialized view. In this case, the REFs are considered dangling because they point back to the object table at the remote master site.

 
However, even if you do not use the SCOPE FOR clause when you are creating a materialized
view, you can alter the materialized view to specify a SCOPE FOR clause. For example, you can alter the customers_with_ref_mv materialized view with the following statement:
ALTER MATERIA
LIZED VIEW oe.customers_with_ref_mv
   MODIFY SCOPE FOR (cust_address) IS oe.cust_address_objtab_mv;



Unscoped REF Columns


 
If you create a materialized view based on a remote master with an unsc
oped REF column, then the REF column is created in the materialized view, but the REFs are con
sidered dangling because they point to a remote database.
 
Logging REF Columns in the Materialized View Log

 
If necessary, you can log REF columns in the materialized view log.
 



See Also:  
"Logging Columns in the Materialized View Log" for more information
 
REFs Crea
ted Using the WITH ROWID Clause

 
If the WITH ROWID clause is specified for a REF column, then Oracle maintains the rowid of the object referenced in the REF. Oracle can find the object referenced directly using the rowid contained in the REF, without the need to fetch the
 rowid from the OID index. Therefore, you use the WITH ROWID clause to specify a rowid hint. The WITH
 ROWID clause is not supported for scoped REFs.

 
Replicating
a REF created using the WITH ROWID clause results in an incorrect rowid hint at each replicati
on site except the site where the REF was first created or modified. The ROWID information in the REF
 is meaningless at the other sites, and Oracle does not correct the rowid hint automatically. Invalid rowid hints can cause pe
rformance problems. In this case, you must use the ANALYZE statement to correct rowid hints at each replication site whe
re they are incorrect.
 



See Also:  
Oracle Database SQL Reference for more information about the ANALYZE statement

 
Materialized View Registration at a Master Site or Master Materialized View Site

 
At the master site and master materialized view site, an Oracle database automatically registers information about a ma
terialized view based on its master table(s) or master materialized view(s). The following sections explain more about Oracle's mater
ialized view registration mechanism.
 
Viewing Information about Registered Materialized Views

 
A level 1 materialized view or materialized view group is registered at its master site. A level 2 or higher multitier mate
rialized view or materialized view group is registered at its master materialized view site, not at the master site. You can query th
e DBA_REGISTERED_MVIEWS data dictionary view at a master site or master materialized view site to list the following inf
ormation about a remote materialized view:

The owner, name, and
database that contains the materialized view
The materialized view's defining qu
ery
Other materialized view characteristics, such as its refresh method
 
You can also query the DBA_MVIEW_REFRESH_TIMES view at a master site or master mat
erialized view site to obtain the last refresh times for each materialized view. Administrators can use this information to monitor m
aterialized view activity and coordinate changes to materialized view sites if a master table or master materialized view needs to be
 dropped, altered, or relocated.
 
Internal Mechanisms

 
Oracle automatically registers
 a materialized view at its master site or master materialized view site when you create the materialized view, and unregisters the m
aterialized view when you drop it. The same applies to materialized view groups.
 
When you dro
p a master materialized view, Oracle does not automatically drop the materialized views based on it. You must drop these materialized
 views manually. If you do not drop such a materialized view and the materialized view tries to refresh to a master materialized view
 that has been dropped, Oracle returns an error.
 
For example, suppose a materialized view nam
ed orders_lev1 is based on the oe.orders master table, and a materialized view named orders_lev2 is based on orders_lev1. If you drop orders_lev1, orders_lev2 remains intact. However, if y
ou try to refresh orders_lev2, Oracle returns an error because orders_lev1 no longer exists.

 





Caution:  
Oracle cannot guarantee the registration or unregistration of a materialized view at its master site or master materialized view sit
e during the creation or drop of the materialized view, respectively. If Oracle cannot successfully register a materialized view duri
ng creation, then you must complete the registration manually using the REGISTER_MVIEW procedure in the DBMS_MVIEW
 package. If Oracle cannot successfully unregister a materialized view when you drop the materialized view, then the registrat
ion information for the materialized view persists in the master site or master materialized view site until it is manually unregiste
red. It is possible that complex materialized views may not be registered.

 
Manual Materialized View Registration


 
If necessary, you can maintain registration manually. Use the REGIST
ER_MVIEW and UNREGISTER_MVIEW procedures of the DBMS_MVIEW package at the master site or master mate
rialized view site to add, modify, or remove materialized view registration information.
 



See Also:  
The REGISTER_MVI
EW and UNREGISTER_MVIEW procedures are described in the PL/SQL Packages and Types Reference
 
Materialized View Architecture


 
The objects used in materialized view replication are depicted in Figure 3-10. Some of these objects are optional and are used only as needed to support the created m
aterialized view environment. For example, if you have a read-only materialized view, then you do not have an updatable materialized
view log nor an internal trigger at the materialized view site. Also, if you have a complex materialized view that cannot be fast ref
reshed, then you may not have a materialized view log at the master site.
 
Figure 3-10 Materialized View Replication Objects
 
Text description of the illustration repln0
96.gif
 
Notice that a master materialized view may have both a materialized view log and a
n updatable materialized view log. Make sure you account for the extra space required by these logs when you are planning for your ma
ster materialized view site.
 
Master Site and Master Materialized View Site Mechanisms

 
The three mechanisms displayed in Figure 3-11 are required at a master site and at a master
materialized view site to support fast refreshing of materialized views.
 





Note:  
Master materialized views contain the mechanisms
 described in "Materialized View Site Mechanisms" in addition to th
e mechanisms described in this section.

 
Figure 3-11 Master Site and Master Materialized View Site Objects
 
Text description of the illustr
ation repln071.gif
 
Master Table or Master Materialized View

 
The master table or
 master materialized view is the basis for the materialized view. A master table is located at the target master site while a master
materialized view is located at a master materialized view site. If the master is a master table, then this table may be involved in
both materialized view replication and multimaster replication. Remember that a materialized view points to only one master site or m
aster materialized view site. Changes made to the master table or master materialized view, as recorded by the materialized view log,
 are propagated to the materialized view during the refresh process.
 





Note:  
Fast refreshable materialized views can be created b
ased on master tables and master materialized views only. Materialized views based on a synonym or a view must be complete refreshed.


 
Internal Trigger for the Materialized View Log

 
When c
hanges are made to the master table or master materialized view using DML, an internal trigger records information about the affected
 rows in the materialized view log. This information includes the values of the primary key, rowid, or object id, or both, as well as
 the values of the other columns logged in the materialized view log. This is an internal AFTER ROW trigger
 that is automatically activated when you create a materialized view log for the target master table or master materialized view. It
inserts a row into the materialized view log whenever an INSERT, UPDATE, or DELETE statement m
odifies the table's data. This trigger is always the last trigger to fire.
 





Note:  
When the materialized view contains a subquery
, you may need to log columns referenced in a subquery. See "Data Subsetting with Materialized Views" for information on subquery materialized views and "Logging Columns in
 the Materialized View Log" for more information about the columns that must be logged.
<
/td>
 
Materialized View Log

 
A materialized vie
w log is required on a master if you want to fast refresh materialized views based on the master. When you create a materialized view
 log for a master table or master materialized view, Oracle creates an underlying table as the materialized view log. A materialized
view log can hold the primary keys, rowids, or object identifiers of rows, or both, that have been updated in the master table or mas
ter materialized view. A materialized view log can also contain other columns to support fast refreshes of materialized views with su
bqueries.
 
The name of a materialized view log's table is MLOG$_master_name. The materialized view log is created in the same schema as the target master. One materialized view log can suppo
rt multiple materialized views on its master table or master materialized view. As described in the previous section, the internal tr
igger adds change information to the materialized view log whenever a DML transaction has taken place on the target master.
 
Following are the types of materialized view logs:

Primary Key: The materialized view records changes to the master table or master mate
rialized view based on the primary key of the affected rows.
Row ID: The materialized view records changes to the master table or master materialized view based on the rowid of the af
fected rows.
Object ID: The materialized view reco
rds changes to the master object table or master object materialized view based on the object identifier of the affected row objects.

Combination: The materialized view records change
s to the master table or master materialized view based any combination of the three options. It is possible to record changes based
on the primary key, the ROWID, and the object identifier of the affected rows. Such a materialized view log supports pri
mary key, ROWID, and object materialized views, which is helpful for environments that have all three types of materiali
zed views based on a master.
 
A combination materialized view log works in the same mann
er as a materialized view log that tracks only one type of value, except that more than one type of value is recorded. For example, a
 combination materialized view log can track both the primary key and the rowid of the affected row are recorded.
 
Though the difference between materialized view logs based on primary keys and rowids is small (one records affe
cted rows using the primary key, while the other records affected rows using the physical rowid), the practical impact is large. Usin
g rowid materialized views and materialized view logs makes reorganizing and truncating your master tables difficult because it preve
nts your ROWID materialized views from being fast refreshed. If you reorganize or truncate your master table, then your
rowid materialized view must be COMPLETE refreshed because the rowids of the master table have changed.
 





Note:  

You use the BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION procedures in the DBMS_MVIEW package to reorganize a master table. See the PL/SQL Packages and Types Reference for more information.
Online redefinition of tables is another possible way to reorganize master tables, but
 online redefinition is not allowed on master tables with materialized view logs, master materialized views, and materialized views.
Online redefinition is allowed on master tables that do not have materialized view logs. See the Oracle Database Administrator's Guide for more informatio
n about online redefinition of tables.


 
Materialized View Logs on Object Tables

 
You can create materialized view logs on object tables. For example, the following SQL statement creat
es the categories_typ user-defined type:
CREATE TYPE oe.category_typ AS OBJECT
<
a name="50501">   (category_name           VARCHAR2(50), 
    category_description    VARCHAR2(1000), 
    category_id             NUMBER(2))
NOT FINAL;
/





When you create an object table based on this type, you can either specify that the object ident
ifier should be system-generated or primary key-based:
CREATE TABLE oe.categories_tab_sys OF
oe.category_typ 
    (category_id    PRIMARY KEY)
    OBJECT ID SYSTEM GENERATED;

CREATE TABLE oe.categories_tab_pkbased OF oe.category_typ 
    (category_id    PRIM
ARY KEY)
    OBJECT ID PRIMARY KEY;




When you crea
te a materialized view log on an object table, you must log the object identifier by specifying the WITH OBJECT ID clause, but you can also specify that the primary key is logged if the object identifier is primary key-based.
 
For example, the following statement creates a materialized view log for the categories_
tab_sys object table and specifies that the object identifier column be logged:

CREATE
 MATERIALIZED VIEW LOG ON oe.categories_tab_sys
   WITH OBJECT ID;



<
/a>
The following statement creates a materialized view log for the categories_tab_pkbased object table an
d specifies that the primary key column be logged along with the object identifier column:
CR
EATE MATERIALIZED VIEW LOG ON oe.categories_tab_pkbased
   WITH OBJECT ID, PRIMARY KEY;


<
/a>
Restriction on Import of Materializ
ed View Logs to a Different Schema

 
Materialized view logs are exported
 with the schema name explicitly given in the DDL statements. Therefore, materialized view logs cannot be imported into a schema that
 is different than the schema from which they were exported. An error will be written to the import log file and the items will not b
e imported if you attempt either of the following imports:

An im
port using the Data Pump Import utility that specifies the REMAP_SCHEMA import parameter to import an export dump file t
hat contains materialized view logs in the specified schema
An import using the
original Import utility that specifies the FROMUSER and TOUSER import parameters to import an export dump f
ile that contains materialized view logs
 
Materialized View Site Mechanisms

 
Wh
en a materialized view is created, additional mechanisms are created at the materialized view site to support the materialized view.
Specifically, at least one index is created. If you create an updatable materialized view, then an internal trigger and a local log (
the updatable materialized view log) are also created at the materialized view site.
 

<
table class="Note" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note"
 title="This is a layout table to format a note">