Oracle offers several types of read-only materialized views to meet the needs of many different replication (and nonreplication) situations.

Whenever you create a materialized view, regardless 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:

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

Here, the schema hr is specified in the query.

Primary key materialized views are the default type of materialized view.

The following is an example of a SQL statement for creating a primary key materialized view:

CREATE MATERIALIZED VIEW oe.customers WITH PRIMARY KEY 
  AS SELECT * FROM oe.customers@orc1.example.com;

Because primary key materialized views are the default, the following statement also results in a primary key materialized view:

CREATE MATERIALIZED VIEW oe.customers 
  AS SELECT * FROM oe.customers@orc1.example.com;

Primary key materialized views can contain a subquery so that you can create a subset of rows at the remote materialized view database. A subquery is a query imbedded within the primary query, so that you have multiple SELECT statements in the CREATE MATERIALIZED VIEW statement. This subquery can be as simple as a basic WHERE clause or as complex as a multilevel WHERE EXISTS clause. Primary key materialized views that contain a selected class of subqueries can still be incrementally (or fast) refreshed, if each master referenced has a materialized view log. A fast refresh uses materialized view logs to update only the rows that have changed since the last refresh.

The following materialized view is created with a WHERE clause containing a subquery:

CREATE MATERIALIZED VIEW oe.orders REFRESH FAST AS
 SELECT * FROM oe.orders@orc1.example.com o
 WHERE EXISTS
   (SELECT * FROM oe.customers@orc1.example.com c
    WHERE o.customer_id = c.customer_id AND c.credit_limit > 10000);

This type of materialized view is called a subquery materialized view.

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, and each row object is identified by an object identifier (OID) column.

A ROWID materialized view is based on the physical row identifiers (rowids) of the rows in a master table.

ROWID materialized views can be used for materialized views 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 MATERIALIZED VIEW oe.orders REFRESH WITH ROWID AS
 SELECT * FROM oe.orders@orc1.example.com;

Three distinct types of users perform operations on materialized views.

These users are:

• Creator: The user who creates the materialized view.

• Refresher: The user who refreshes the materialized view.

• Owner: The user who owns the materialized view. The materialized view resides in this user's schema.

One user can perform all of these operations on a particular materialized view. However, in some replication environments, different users perform these operations on a particular materialized view. The privileges required to perform these operations depend on whether the same user performs them or different users perform them.

If the owner of a materialized view at the materialized view database has a private database link to the master database, then the database link connects to the owner of the master table at the master database. Otherwise, the normal rules for connections through database links apply.

If the creator of a materialized view also owns the materialized view, then this user must have the required privileges to create a materialized view.

The following privileges must be granted explicitly rather than through a role:

• CREATE MATERIALIZED VIEW or CREATE ANY MATERIALIZED VIEW

• CREATE TABLE or CREATE ANY TABLE

• READ or SELECT object privilege on the master table and the master table's materialized view log or either READ ANY TABLE or SELECT ANY TABLE system privilege

  If the master database is remote, then the READ or SELECT object privilege must be granted to the user at the master database to which the user at the materialized view database connects through a database link.

If the creator of a materialized view is not the owner, then certain privileges must be granted to the creator and to the owner to create a materialized view.

Both the creator's privileges and the owner's privileges must be granted explicitly rather than through a role.

Table 36-1 shows the required privileges when the creator of the materialized view is not the owner.

If the refresher of a materialized view also owns the materialized view, then this user must have the required privileges to create the materialized view.

Specifically, this user must have READ or SELECT object privilege on the master table and the master table's materialized view log or either READ ANY TABLE or SELECT ANY TABLE system privilege. If the master database is remote, then the READ or SELECT object privilege must be granted to the user at the master database to which the user at the materialized view database connects through a database link. This privilege can be granted either explicitly or through a role.

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 36-2 shows the required privileges when the refresher of the materialized view is not the owner.

In certain situations, you might want your materialized view to reflect a subset of the data in the master table.

Row subsetting enables you to include only the rows that are needed from the master table in the materialized views by using a WHERE clause. Column subsetting enables you to include only the columns that are needed from the master table in the materialized views. You do this by specifying certain select columns in the SELECT statement during materialized view creation.

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 applied to the materialized view database, 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, then hard disks are generally significantly smaller than the hard disks on a corporate server. Subsetted materialized views might require significantly less storage space.

• Improve Refresh Times: Because less data is applied to the materialized view database, the refresh process is faster, which might be essential for those who need to refresh materialized views using a network connection from a laptop.

For example, the following statement creates a materialized view based on the oe.orders@orc1.example.com master table and includes 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.example.com
 WHERE sales_rep_id = 173;

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

• Many to One Subqueries
  You can create a materialized view with a subquery with a many to one relationship.
• One to Many Subqueries
  You can create a materialized view with a subquery with a one to many relationship.
• Many to Many Subqueries
  You can create a materialized view with a subquery with a many to many relationship.
• Materialized Views with Subqueries and Unions
  You can create a materialized view with subqueries and unions.

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 following are restrictions for fast refresh materialized views with subqueries:

• Materialized views must be primary key materialized views.

• The master table's materialized view log must include certain columns referenced in the subquery. For information about which columns must be included, see "Logging Columns in a Materialized View Log".

• If the subquery is many to many or one to many, then join columns that are not part of a primary key must be included in the materialized view log of the master table. This restriction does not apply to many to one subqueries.

• The subquery must be a positive subquery. For example, you can use the EXISTS condition, but not the NOT EXISTS condition.

• The subquery 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 exist for each table at each nested level.

• Each nested level can only reference the table in the level above it.

• Subqueries can include AND conditions, but each OR condition can only reference columns contained within one row. Multiple OR conditions within a subquery can be connected with an AND condition.

• All tables referenced in a subquery must reside in the same master database.

There are restrictions for fast refresh materialized views with unions containing subqueries.

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

• All of the restrictions described in "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 table, even if the subquery is many to one.

• All of the restrictions described in "Complex Materialized Views" apply for clauses with UNIONS.

• Examples of Materialized Views with Unions Containing Subqueries
  Examples illustrate creating materialized views with unions containing subqueries.

You can replicate object types and objects between master databases and materialized view databases in a replication environment.

Oracle object types are user-defined data types 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.

An Oracle object that occupies a single column in a table is called a column object. Typically, tables that contain column objects also contain other columns, which can be built-in data types, such as VARCHAR2 and NUMBER. An object table 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. Collections are user-defined data types that are based on VARRAY and nested table data types. You create varrays with the CREATE TYPE ... AS VARRAY statement, and you create nested tables with the CREATE TYPE ... AS TABLE statement.

User-defined types include all types created using the CREATE TYPE statement, including object, nested table, VARRAY, and indextype. To replicate schema objects based on user-defined types, the user-defined types themselves must exist, and must be the same, at the master database and the materialized view database.

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

• The user-defined types replicated at the master database and materialized view database must be created at the materialized view database before you create any materialized views that depend on these types.

• All of the master tables on which a materialized view is based must be at the same master database to create a materialized view with user-defined types.

• A user-defined type must be the same at all databases:

  • All replication databases must have the same object identifier (OID), schema owner, and type name for each replicated user-defined type.

  • If the user-defined type is an object type, then all databases must agree on the order and data type of the attributes in the object type. You establish the order and data types 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), 
          postal_code        VARCHAR2(10), 
          city               VARCHAR2(30), 
          state_province     VARCHAR2(10), 
          country_id         CHAR(2));
    /
    

    At all databases, street_address must be the first attribute for this type and must be VARCHAR2(40), postal_code must be the second attribute and must be VARCHAR2(10), city must be the third attribute and must be VARCHAR2(30), and so on.

  • All databases 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 same for two or more types, the types have the same hashcode. You can view the hashcode for a type by querying the DBA_TYPE_VERSIONS data dictionary view.

You can use a CREATE TYPE statement at the materialized view database to create the type. It might be necessary to do this to create a read-only materialized view that uses the type.

If you choose this option, then you must ensure the following:

• The type is in the same schema at both the materialized view database and the master database.

• The type has the same attributes in the same order at both the materialized view database and the master database.

• The type has the same data type for each attribute at both the materialized view database and the master database.

• The type has the same object identifier at both the materialized view database and the master database.

You can find the object 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 DBA_TYPES WHERE TYPE_NAME = 'CUST_ADDRESS_TYP';

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

For example, now that you know the OID for the type at the master database, you can complete the following steps to create the type at the materialized view database:

1. Log in to the materialized view database as the user who owns the type at the master database. If this user does not exist at the materialized view database, then create the user.

2. 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), 
        country_id         CHAR(2));
   /
   

The type is now ready for use at the materialized view database.

A read-only materialized view can replicate specific attributes of a column object without replicating other attributes.

For example, using the cust_address_typ user-defined data type described in the previous section, suppose a customers_sub master table is created at master database orc1.example.com:

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 materialized view at a remote materialized view database:

CREATE MATERIALIZED VIEW oe.customers_mv1 AS
   SELECT customer_id, cust_last_name, c.cust_address.postal_code
   FROM oe.customers_sub@orc1.example.com c;

Notice that the postal_code attribute is specified in the cust_address column object.

Collection columns are columns based on varray and nested table data types. Oracle supports the creation of materialized views with collection columns.

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 database orc1.example.com that contains the nested table phones_ntab:

CREATE TYPE oe.phone_typ AS OBJECT (
   location    VARCHAR2(15),
   num         VARCHAR2(14));
/

CREATE TYPE oe.phone_ntabtyp AS TABLE OF oe.phone_typ;
/

CREATE TABLE oe.people_reltab (
   id               NUMBER(4) CONSTRAINT pk_people_reltab PRIMARY KEY,
   first_name       VARCHAR2(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 last line of the preceding SQL statement. You must specify a primary key for the storage table if you plan to create materialized views based on its parent table. In this case, the storage table is phone_store_ntab and the parent table is people_reltab.

To create materialized views that can be fast refreshed, create a materialized view log on both the parent table and the storage table, specifying the nested table column as a filter column for the 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;

At the materialized view database, create the required types, ensuring that the object identifier for each type is the same as the object identifier at the master database. 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_mv
   NESTED TABLE phones_ntab STORE AS phone_store_ntab_mv 
   REFRESH FAST AS SELECT * FROM oe.people_reltab@orc1.example.com;

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

The storage table:

• Is a separate, secondary materialized view

• 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 table's storage table, do not specify PRIMARY KEY in the STORE AS clause.

The following actions are not allowed directly on the storage table of a nested table in a materialized view:

• Refreshing the storage table

• Altering the storage table

• Dropping the storage table

• Generating replication support on the storage table

These actions can occur indirectly when they are performed on the materialized view that contains the nested table. In addition, you cannot replicate a subset of the columns in a storage table.

• Restrictions for Materialized Views with Collection Columns
  Restrictions apply to materialized views with collection columns.