3 Materialized View Concepts and Architecture

Ease Network 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 database server, user load is distributed across multiple database servers. Through the use of multitier materialized views, you can create 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 replication 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. Further, the ability of multimaster replication to provide real-time or near real-time replication can result in greater network traffic, and might require a dedicated network link.

Materialized views are updated through an efficient batch process from a single master site or master materialized view site. They have lower network requirements and dependencies than multimaster replication because of the point in time nature of materialized view replication. Whereas multimaster replication requires 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 data 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 Environment

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

Enable Data 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 you 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 have the option of automating the refresh process by scheduling a job, you can manually refresh your materialized view on-demand, which 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 dials up the network and uses the integrated mechanism to refresh the database, thus transferring the orders to the main office.

Read-Only Materialized Views

You can make a materialized view read-only during creation by omitting the FOR UPDATE clause or disabling the equivalent option in the Replication Management tool. Read-only materialized views use many of the same mechanisms as updatable materialized views, except that they do not need to belong to a materialized 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 means 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;

Updatable Materialized Views

You can make a materialized view updatable during creation by including the FOR UPDATE clause or enabling the equivalent option in the Replication Management tool. For changes made to an updatable materialized view to be pushed back to the master during refresh, the updatable materialized 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 example of an updatable materialized view:

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

The following statement creates a materialized view group:

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

The following statement adds the hr.departments materialized view to the materialized view group, making the materialized view updatable:

BEGIN
   DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
      gname => 'hr_repg',
      sname => 'hr',
      oname => 'departments',
      type => 'SNAPSHOT',
      min_communication => TRUE);
END;

/

You can 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 perform administration operations on the master site if you:

• Propagate all of the deferred transactions at the databases containing updatable materialized views before you perform the administration operations to the master group.

• Do not allow any database manipulation language (DML) changes on the updatable materialized views until you have finished the administration operation on the master site and regenerated 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.

Writeable Materialized Views

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

Primary Key Materialized Views

Primary key materialized views are the default type of materialized view. They are updatable if the materialized view was created as part of a materialized view group and FOR UPDATE was specified when defining the materialized view. An updatable materialized view must belong to a materialized view group that has the same name as the replication group at its master site or master materialized view site. In addition, an updatable materialized view must reside in a different database than the master replication group.

Changes are propagated according to the row-level changes that have occurred, as identified by the primary key value of the row (not the ROWID). The following is an example of a SQL statement for creating an updatable, primary key materialized view:

CREATE MATERIALIZED VIEW oe.customers FOR UPDATE AS
  SELECT * FROM oe.customers@orc1.world;

Primary key materialized views can contain a subquery so that you can create a subset of rows at the remote materialized view site. A subquery is a query imbedded within the primary query, so that you have more than one SELECT statement 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.world o
 WHERE EXISTS
   (SELECT * FROM oe.customers@orc1.world c
    WHERE o.customer_id = c.customer_id AND c.credit_limit > 10000);

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

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. That is, an object materialized view is composed of row objects, and each row object is identified by an object identifier (OID) column.

ROWID Materialized Views

Oracle supports ROWID materialized views in addition to the default primary key materialized views. A ROWID materialized view is based on the physical row identifiers (rowids) of the rows in a master. 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.world;

Complex Materialized Views

To be fast refreshed, the defining query for a materialized view must observe certain restrictions. If you require a materialized view whose defining query is more general and cannot observe the restrictions, then the materialized view is complex and cannot be fast refreshed.

Specifically, a materialized view is considered complex when the defining query of the materialized view contains:

• A CONNECT BY clause.

  For example, the following statement creates a complex materialized view:

  CREATE MATERIALIZED VIEW hr.emp_hierarchy AS
    SELECT LPAD(' ', 4*(LEVEL-1))||email USERNAME 
      FROM hr.employees@orc1.world START WITH manager_id IS NULL 
      CONNECT BY PRIOR employee_id = manager_id;
  

• An INTERSECT, MINUS, or UNION ALL set operation.

  For example, the following statement creates a complex materialized view because it has a UNION ALL set operation:

  CREATE MATERIALIZED VIEW hr.mview_employees AS
    SELECT employees.employee_id, employees.email 
    FROM hr.employees@orc1.world
  UNION ALL
    SELECT new_employees.employee_id, new_employees.email 
    FROM hr.new_employees@orc1.world;
  

• The DISTINCT or UNIQUE keyword.

  For example, the following statement creates a complex materialized view:

  CREATE MATERIALIZED VIEW hr.employee_depts AS
    SELECT DISTINCT department_id FROM hr.employees@orc1.world 
    ORDER BY department_id; 
  

• In some cases, an aggregate function, although it is possible to have an aggregate function in the defining query and still have a simple materialized view.

  For example, the following statement creates a complex materialized view:

  CREATE MATERIALIZED VIEW hr.average_sal AS
    SELECT AVG(salary) "Average" FROM hr.employees@orc1.world;
  

• In some cases, joins other than those in a subquery, although it is possible to have joins in the defining query and still have a simple materialized view.

  For example, the following statement creates a complex materialized view:

  CREATE MATERIALIZED VIEW hr.emp_join_dep AS
    SELECT last_name 
    FROM hr.employees@orc1.world e, hr.departments@orc1.world d
    WHERE e.department_id = d.department_id; 
  

• In some cases, a UNION operation. Specifically, a materialized view with a UNION operation is complex if any one of these conditions is true:

  • Any query within the UNION is complex. The previous bullet items specify when a query makes a materialized view complex.

  • The outermost SELECT list columns do not match for the queries in the UNION. In the following example, the first query only has order_total in the outermost SELECT list while the second query has customer_id in the outermost SELECT list. Therefore, the materialized view is complex.

    CREATE MATERIALIZED VIEW oe.orders AS
      SELECT order_total 
      FROM oe.orders@orc1.world o
      WHERE EXISTS
        (SELECT cust_first_name, cust_last_name
           FROM oe.customers@orc1.world c
           WHERE o.customer_id = c.customer_id 
           AND c.credit_limit > 50)
    UNION    
      SELECT customer_id 
      FROM oe.orders@orc1.world o
      WHERE EXISTS
        (SELECT cust_first_name, cust_last_name 
           FROM oe.customers@orc1.world c
           WHERE o.customer_id = c.customer_id 
           AND c.account_mgr_id = 30);  
    

    The innermost SELECT list has no bearing on whether a materialized view is complex. In the previous example, the innermost SELECT list is cust_first_name and cust_last_name for both queries in the UNION.

• Clauses that do not comply with the requirements detailed in "Restrictions for Materialized Views with Subqueries".

  See Also:

  • Oracle Database Data Warehousing Guide for information about materialized views with aggregate functions and joins

  • Oracle Database SQL Reference for more information about the CONNECT BY clause, set operations, the DISTINCT keyword, and aggregate functions

  Note:

  If possible, you should avoid using complex materialized views because they cannot be fast refreshed, which might degrade network performance (see "Refresh Process" for information).

A Comparison of Simple and Complex Materialized Views

For certain applications, you might want to consider using a complex materialized view. Figure 3-2 and the following text discuss some issues that you should consider.

Figure 3-2 Comparison of Simple and Complex Materialized Views

Description of "Figure 3-2 Comparison of Simple and Complex Materialized Views"

• Complex Materialized View: Method A in Figure 3-2 shows a complex materialized view. The materialized view in Database II exhibits efficient 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 against the complex materialized view in Method A. However, the simple materialized views can be refreshed more efficiently using fast refresh 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 performance, then use Method B (simple materialized view).

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

  If the owner of materialized 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 rules for connections through database links apply.

Creator Is Not Owner

If the creator of a materialized view is not the owner, certain privileges must be granted to the creator and to the owner to create a materialized view. The creator's privileges can be granted explicitly or through a role, but the owner's privileges must be granted explicitly. 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.

Refresher Is Owner

If the refresher of a materialized view also owns the materialized view, this user must have 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 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 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 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.

Materialized Views with Subqueries

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 can be difficult. The following sections provide examples of situations where a subquery is useful.

Many to One 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 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 customers and orders tables are related through the customer_id column. The following statement satisfies the original goal of the salesperson. That is, the following statement creates a materialized view that contains orders for customers whose credit limit is greater than $10,000:

CREATE MATERIALIZED VIEW oe.orders REFRESH FAST 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 > 10000);

Note:

To create this oe.orders materialized view, credit_limit must be logged 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

Description of "Figure 3-3 Row Subsetting with Many to One Subqueries"

As you can see, the materialized view created by this statement is fast refreshable and updatable. If new customers are identified that have a credit limit greater than $10,000, then the new data will be propagated to the materialized view site during the subsequent refresh process. Similarly, if a customer's credit limit drops to less than $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 to 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.

Here, 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 and customers table are related through the customer_id column. The following statement satisfies the original goal of the salesperson. That is, this statement creates a materialized view that contains customers who have an order with an order 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

Description of "Figure 3-4 Row Subsetting with One to Many Subqueries"

The materialized view created by this 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.

Note:

The materialized view site must have a compatibility level of 9.0.1 or higher because fast refresh of materialized views with one to many subqueries was not supported prior to Oracle9i Database Release 1 (9.0.1). 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 view of the inventories table based on data in both the inventories table and the order_items table. For example, suppose a salesperson wants to see all of the inventories with a quantity on hand greater than 0 (zero) for each product whose product_id is in the order_items table. In other words, the salesperson wants to see the inventories that are greater than zero for all of the products that customers have ordered. Here, an inventory is a certain quantity of a product 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 relationship between the order_items table and the inventories table.

When you 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 Figure 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 MATERIALIZED 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 materialized 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 information.

Figure 3-5 Row Subsetting with Many to Many Subqueries

Description of "Figure 3-5 Row Subsetting with Many to Many Subqueries"

The materialized view created by this statement is fast refreshable and updatable. If new inventories that are greater than zero are identified for products in the order_items table, then the new data will be propagated to the materialized view site during the subsequent refresh process. Similarly, if a customer cancels an order for a product and there are no other orders for the product in the order_items table, then the inventories for the product will be removed from the materialized 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 Oracle9i Database Release 1 (9.0.1). The compatibility level is controlled by the COMPATIBLE initialization parameter.

Materialized Views with Subqueries and Unions

In situations where you want a single materialized view to contain data that matches the complete results of two or more different queries, you can use the UNION operator. When you use the UNION operator to create a materialized view, you have two SELECT statements around each UNION operator, one is above it and one is below it. The resulting materialized view contains rows selected by either query.

You can use the UNION operator as a way to create fast refreshable materialized views that satisfy "or" conditions without using the OR expression in the WHERE clause of a subquery. Under some conditions, using an OR expression in the WHERE clause of a subquery causes the resulting materialized view to be complex, and therefore not fast refreshable.

See Also:

"Restrictions for Materialized Views with Subqueries" for more information about the OR expressions 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 California 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 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.product_id AND 
        pd.translated_description LIKE '%Rouge%')  
UNION
 SELECT * FROM oe.product_information@orc1.world pi
 WHERE pi.category_id = 29 AND EXISTS
  (SELECT * FROM oe.inventories@orc1.world i
  WHERE pi.product_id = i.product_id AND EXISTS
    (SELECT * 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 oe.product_descriptions table, the state_province in the hr.locations table, and the location_id column in the oe.warehouses table must be logged in each master's materialized view log. See "Logging Columns in the Materialized View Log" for more information.

Figure 3-6 shows the relationships of the master tables involved in this statement.

Figure 3-6 Row Subsetting with Subqueries and Unions

Description of "Figure 3-6 Row Subsetting with Subqueries and Unions"

In addition to the UNION operation, this statement contains the following subqueries:

• A subquery referencing the product_information table and the product_descriptions table. This subquery is one to many because one product can have multiple product descriptions (for different languages).

• 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 subquery is many to one because many inventories can be stored in one warehouse.

• A subquery referencing the warehouses table and the locations table. This subquery is many to one because many warehouses can be in one location.

The materialized view created by this statement is fast 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 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 a UNION operator was not supported prior to Oracle9i Database Release 1 (9.0.1). Also, fast refresh of materialized views with many to one subqueries requires 9.0.1 or higher compatibility. The compatibility level is controlled 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 following 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 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 site or master materialized view site.

  Note:

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

  See Also:

  "Primary Key Materialized Views" for more information about primary key materialized views

Restrictions for 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 the previous 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.

CREATE MATERIALIZED VIEW oe.orders REFRESH FAST 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 > 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);  

CREATE MATERIALIZED VIEW oe.orders AS
  SELECT * FROM oe.orders@orc1.world o
  WHERE EXISTS
    (SELECT * FROM oe.customers@orc1.world c
     WHERE o.customer_id = c.customer_id  -- first reference to orders table
     AND c.credit_limit > 50
     AND EXISTS
        (SELECT * FROM oe.orders@orc1.world o
         WHERE order_total > 5000 
         AND o.customer_id = c.customer_id)) -- second reference to orders table
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);  

Scenario for Using Multitier Materialized Views

Consider a multinational company that maintains 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 cities in these countries.

For example, the company has one main office for all of the United Kingdom, but it also has an office in the city of London. The United Kingdom office maintains employee information for all of the employees in the United Kingdom, while the London office only maintains employee information for the employees at the London office. In this 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 queries the master table in the database at headquarters, which is orc1.world. Notice that the statement uses subqueries so that 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.locations@orc1.world l
          WHERE l.country_id = 'UK'
          AND d.location_id = l.location_id));

The following statement creates the hr.employees materialized view for the London office based on the level 1 materialized view at the United Kingdom office. The statement queries the materialized view in the database at the United Kingdom 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.employees REFRESH FAST FOR UPDATE AS
  SELECT * FROM hr.employees@reg_uk.world e
    WHERE EXISTS
      (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));

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:

  The COMPATIBLE initialization parameter controls a database's compatibility level.

Type Agreement at Replication Sites

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 exactly the same, at all replication sites. In addition, Oracle recommends that you add a user-defined type to the replication group in which it is used, but doing so is not required.

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 site and materialized view site must be created at the materialized view site before you create any materialized 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 replicated user-defined type.

  • If the user-defined type is an object type, then all 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), 
          postal_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 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 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 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.

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

SELECT TYPE_OID FROM DBA_TYPES WHERE TYPE_NAME = 'CUST_ADDRESS_TYP';

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

Column Subsetting of Masters with Column Objects

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 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 materialized 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 specified in the cust_address column object.

An updatable materialized view must replicate the entire column object. It cannot replicate some attributes of a column object but not others. The following statement is valid because it specifies the entire cust_address column object:

CREATE MATERIALIZED VIEW oe.customers_mv1 FOR UPDATE AS
   SELECT customer_id, cust_last_name, cust_address
   FROM oe.customers_sub@orc1.world;

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 clause, then the materialized view is read-only and is not an object materialized view. That is, such a materialized view has regular rows, not row objects.

To create a materialized view based on an object table, the types on which 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.

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

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

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

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

CREATE MATERIALIZED VIEW oe.categories_relmv
   AS SELECT * FROM oe.categories_tab@orc1.world;

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 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 site orc1.world 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.

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 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 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_mv
   NESTED TABLE phones_ntab STORE AS phone_store_ntab_mv 
   REFRESH FAST AS SELECT * FROM oe.people_reltab@orc1.world;

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, Oracle 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'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

• Adding the storage table to a replication group

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

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 contain references only to a specified object table, while an unscoped REF can contain references to any object table in the database that is based on the corresponding object type. A scoped REF requires less storage space and provides more efficient access than an unscoped REF.

As you will see in the following section, 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 NULL. Also, PL/SQL only supports dereferencing REFs by using the UTL_OBJECT package and raises an exception for dangling REFs.

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

-- Create the object table cust_address_objtab.
CREATE TABLE oe.cust_address_objtab OF oe.cust_address_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.cust_address_typ 
                          SCOPE IS oe.cust_address_objtab);

CREATE MATERIALIZED VIEW oe.cust_address_objtab_mv OF oe.cust_address_typ AS 
   SELECT * FROM oe.cust_address_objtab@orc1.world;

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;

ALTER MATERIALIZED VIEW oe.customers_with_ref_mv
   MODIFY SCOPE FOR (cust_address) IS oe.cust_address_objtab_mv;

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 materialized view or materialized view group is registered at its master materialized view site, not at the master site. You can query the DBA_REGISTERED_MVIEWS data dictionary view at a master site or master materialized view site to list the following information about a remote materialized view:

• The owner, name, and database that contains the materialized view

• The materialized view's defining query

• 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 materialized view site to obtain the last refresh times for each materialized view. Administrators can use this information to monitor materialized 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 materialized view when you drop it. The same applies to materialized view groups.

When you drop 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 named 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 you try to refresh orders_lev2, Oracle returns an error because orders_lev1 no longer exists.

Manual Materialized View Registration

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

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 can be involved in both materialized view replication and multimaster replication. Remember that a materialized view points to only one master site or master 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.

Internal Trigger for the Materialized View Log

When changes 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 modifies the table's data. This trigger is always the last trigger to fire.

Materialized View Log

A materialized view 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 master materialized view. A materialized view log can also contain other columns to support fast refreshes of materialized views with subqueries.

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 support multiple materialized views o