Refreshing a Materialized View

Historically, one of the problems of using summary tables has been the initial loading and subsequent updating of the summary. These issues are now addressed because summary management provides mechanisms to:

• Fully refresh the data.

• Perform a fast refresh, that is add/merge only the changes.

• Automatically update a materialized view whenever changes are made.

Therefore the DBA must consider how much time is required to create and maintain each materialized view and balance that against the performance gains achieved by using this materialized view.

When you define a materialized view, you can specify three refresh options: how to refresh, what type of refresh, and can trusted constraints be used. If unspecified, the defaults are assumed as ON DEMAND, FORCE, and ENFORCED constraints respectively.

The two refresh execution modes are ON COMMIT and ON DEMAND. 

Table 1 Refresh Modes

Refresh Mode	Description
ON COMMIT	Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMIT privilege is necessary to use this mode
ON DEMAND	Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT)

When a materialized view is maintained using the ON COMMIT method, the time required to complete the commit may be slightly longer than usual. This is because the refresh operation is performed as part of the commit process. Therefore this method may not be suitable if many users are concurrently changing the tables upon which the materialized view is based.

If you anticipate performing insert, update or delete operations on tables referenced by a materialized view concurrently with the refresh of that materialized view, and that materialized view includes joins and aggregation, Oracle recommends you use ON COMMIT fast refresh rather than ON DEMAND fast refresh.

If a materialized view fails during refresh at COMMIT time, you must explicitly invoke the refresh procedure using the DBMS_MVIEW package after addressing the errors specified in the trace files. Until this is done, the materialized view will no longer be refreshed automatically at commit time.

You can specify how you want your materialized views to be refreshed from the detail tables by selecting one of four options: COMPLETE, FAST, FORCE, and NEVER



Table 2 Refresh Options

Refresh Option	Description
COMPLETE	Refreshes by recalculating the materialized view's defining query.
FAST	Applies incremental changes to refresh the materialized view using the information logged in the materialized view logs, or from a SQL*Loader direct-path or a partition maintenance operation.
FORCE	Applies FAST refresh if possible; otherwise, it applies COMPLETE refresh.
NEVER	Indicates that the materialized view will not be refreshed with refresh mechanisms.

Whether the fast refresh option is available depends upon the type of materialized view. You can call the procedure DBMS_MVIEW.EXPLAIN_MVIEW to determine whether fast refresh is possible.

You can also specify if it is acceptable to use trusted constraints and REWRITE_INTEGRITY = TRUSTED during refresh. Any nonvalidated RELY constraint is a trusted constraint. For example, nonvalidated foreign key/primary key relationships, functional dependencies defined in dimensions or a materialized view in the UNKNOWN state. If query rewrite is enabled during refresh, these can improve the performance of refresh by enabling more performant query rewrites. Any materialized view that can uses TRUSTED constraints for refresh is left in a state of trusted freshness (the UNKNOWN state) after refresh.

This is reflected in the column STALENESS in the view USER_MVIEWS. The column UNKNOWN_TRUSTED_FD in the same view is also set to Y, which means yes.

You can define this property of the materialized either during create time by specifying REFRESH USING TRUSTED [ENFORCED] CONSTRAINTS or by using ALTER MATERIALIZED VIEW DDL.

Table 3 Constraints

Constraints to Use	Description
TRUSTED CONSTRAINTS	Refresh can use trusted constraints and REWRITE_INTEGRITY = TRUSTED during refresh.This allows use of non-validated RELY constraints and rewrite against materialized views in UNKNOWN orFRESH state during refresh.
ENFORCED CONSTRAINTS	Refresh can use validated constraints and REFRESH_INTEGRITY=ENFORCED during refresh. This allows use of only validated, enforced constraints and rewrite against materialized views inFRESH state during refresh.

These operations may be performed: 

• On demand refreshing by: 

 Specific materialized views (DBMS_MVIEW.REFRESH) 

 Those materialized views dependent on a table (DBMS_MVIEW.REFRESH_DEPENDENT) 

 All materialized views (DBMS_MVIEW.REFRESH_ALL_MVIEWS) 

• on commit, whenever the tables on which the materialized view is defined are changed 

The ON DEMAND refresh is achieved by calling one of the procedures listed above, thus giving the DBA total control over when a materialized view is update. 

If the ON COMMIT refresh method is chosen, whenever a materialized view is affected by changes made to the source data, the materialized view will automatically be updated to reflect this data. However, it should be remembered that this update to the materialized view occurs as part of the commit processing in the transaction where the changes to the base table are made. Therefore, the commit will take slightly longer, because changes are being made to both the original table and then any materialized view whose definition includes that table.

Complete Refresh 

When a complete refresh of a materialized view occurs, all data is removed and then it is reloaded. Depending on the size of the materialized view, this could be a time consuming operation. Complete refresh is a good technique to use when: 

• the number of new rows to be inserted is more than 50% of the cardinality of the tables on which the materialized view is based 

• there is no index on the materialized view that is usable for merging 

• the time required to perform a fast refresh is longer than a complete refresh

Fast Refresh 

Some materialized views could be very large and the time required to regularly perform a complete refresh may not be available. The alternative is a fast refresh where only the changes to the detail table are applied against the materialized view. New data loaded into any table in the warehouse is identified and any materialized view referencing that table is automatically updated with the new data. 

In order to perform a fast refresh operation, changes made to the data must be recorded and this is achieved in one of two ways. If your data is only ever inserted into the database using SQL*Loader direct path, then the refresh mechanism will detect this and identify the new data to be loaded. However, many data changes will occur via the SQL commands, INSERT, UPDATE and DELETE. In this case a MATERIALIZED VIEW LOG is required on each of the tables on which the materialized views are based. 

Only one log is required per table and the materialized view log is on the table, not the materialized view. Therefore if you only had 6 tables in your database that were changing, you would only need 6 materialized view logs. But you could have any number of materialized views using those logs. 

It should be noted that not all materialized views are fast refreshable and confirmation of whether it is possible can be obtained by calling the procedure DBMS_MVIEW.EXPLAIN_MVIEW. This procedure will also advise what needs to be done to the materialized view to make it fast refreshable. The procedure TUNE_MVIEW can then be called to generate a script, which will show, if it is possible, how to make the materialized view fast refreshable. 

Syntax for creating Materialized View:

CREATE MATERIALIZED VIEW

  [ schema. ]materialized_view
  [ OF [ schema. ]object_type ]
  [ (scoped_table_ref_constraint) ]
  { ON PREBUILT TABLE
    [ { WITH | WITHOUT } REDUCED PRECISION ]
  | physical_properties materialized_view_props
  }
  [ USING INDEX
    [ physical_attributes_clause
    | TABLESPACE tablespace
    ]
      [ physical_attributes_clause
      | TABLESPACE tablespace
      ]...
  | USING NO INDEX
  ]
  [ create_mv_refresh ]
  [ FOR UPDATE ]
  [ { DISABLE | ENABLE }
QUERY REWRITE
  ]
AS subquery ;

Syntax for REFRESH option:

{ REFRESH

  { { FAST | COMPLETE | FORCE }
  | ON { DEMAND | COMMIT }
  | { START WITH | NEXT } date
  | WITH { PRIMARY KEY | ROWID }
  | USING
       { DEFAULT [ MASTER | LOCAL ]
ROLLBACK SEGMENT
       | [ MASTER | LOCAL ]
ROLLBACK SEGMENT rollback_segment
       }
         [ DEFAULT [ MASTER | LOCAL ]
ROLLBACK SEGMENT
         | [ MASTER | LOCAL ]
ROLLBACK SEGMENT rollback_segment
         ]...
  | USING
       { ENFORCED | TRUSTED }
CONSTRAINTS
  }
    [ { FAST | COMPLETE | FORCE }
    | ON { DEMAND | COMMIT }
    | { START WITH | NEXT } date
    | WITH { PRIMARY KEY | ROWID }
    | USING
         { DEFAULT [ MASTER | LOCAL ]
ROLLBACK SEGMENT
         | [ MASTER | LOCAL ]
ROLLBACK SEGMENT rollback_segment
         }
           [ DEFAULT [ MASTER | LOCAL ]
ROLLBACK SEGMENT
           | [ MASTER | LOCAL ]
ROLLBACK SEGMENT rollback_segment
           ]...
    | USING
         { ENFORCED | TRUSTED }
CONSTRAINTS
    ]...
| NEVER REFRESH
}

Use the refresh clause to specify the default methods, modes, and times for the database to refresh the materialized view. If the master tables of a materialized view are modified, then the data in the materialized view must be updated to make the materialized view accurately reflect the data currently in its master tables. This clause lets you schedule the times and specify the method and mode for the database to refresh the materialized view.

FAST Clause

Specify FAST to indicate the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. The changes for conventional DML changes are stored in the materialized view log associated with the master table.The changes for direct-path INSERT operations are stored in the direct loader log.

If you specify REFRESH FAST, then the CREATE statement will fail unless materialized view logs already exist for the materialized view master tables. Oracle Database creates the direct loader log automatically when a direct-path INSERT takes place. No user intervention is needed.

For both conventional DML changes and for direct-path INSERT operations, other conditions may restrict the eligibility of a materialized view for fast refresh.
Materialized views are not eligible for fast refresh if the defining query contains an analytic function.

COMPLETE Clause

Specify COMPLETE to indicate the complete refresh method, which is implemented by executing the defining query of the materialized view. If you request a complete refresh, then Oracle Database performs a complete refresh even if a fast refresh is possible.

FORCE Clause

Specify FORCE to indicate that when a refresh occurs, Oracle Database will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.

ON COMMIT Clause

Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.

Restriction on Refreshing ON COMMIT

This clause is not supported for materialized views containing object types.

ON DEMAND Clause

Specify ON DEMAND to indicate that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures. If you omit both ON COMMIT and ON DEMAND, ON DEMAND is the default.

If you specify ON COMMIT or ON DEMAND, then you cannot also specify START WITH or NEXT.

START WITH Clause
Specify a datetime expression for the first automatic refresh time.

NEXT Clause

Specify a datetime expression for calculating the interval between automatic refreshes.

Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, then the database determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a START WITH value but omit the NEXT value, then the database refreshes the materialized view only once. If you omit both the START WITH and NEXT values, or if you omit the create_mv_refresh entirely, then the database does not automatically refresh the materialized view

WITH PRIMARY KEY Clause 

Specify WITH PRIMARY KEY to create a primary key materialized view. This is the default and should be used in all cases except those described for WITH ROWID. Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh. The master table must contain an enabled primary key constraint.

WITH ROWID Clause

Specify WITH ROWID to create a rowid materialized view. Rowid materialized views are useful if the materialized view does not include all primary key columns of the master tables. Rowid materialized views must be based on a single table and cannot contain any of the following:

• Distinct or aggregate functions
• GROUP BY or CONNECT BY clauses
• Subqueries
• Joins
• Set operations

Rowid materialized views are not eligible for fast refresh after a master table reorganization until a complete refresh has been performed.

USING ROLLBACK SEGMENT Clause 

This clause is not valid if your database is in automatic undo mode, because in that mode Oracle Database uses undo tablespaces instead of rollback segments. Oracle strongly recommends that you use automatic undo mode. This clause is supported for backward compatibility with replication environments containing older versions of Oracle Database that still use rollback segments.

For rollback_segment, specify the remote rollback segment to be used during materialized view refresh.

MASTER

MASTER specifies the remote rollback segment to be used at the remote master site for the individual materialized view.

LOCAL

LOCAL specifies the remote rollback segment to be used for the local refresh group that contains the materialized view. This is the default.

If you omit rollback_segment, then the database automatically chooses the rollback segment to be used. One master rollback segment is stored for each materialized view and is validated during materialized view creation and refresh. If the materialized view is complex, then the database ignores any master rollback segment you specify.

USING ... CONSTRAINTS Clause 

The USING ... CONSTRAINTS clause lets Oracle Database choose more rewrite options during the refresh operation, resulting in more efficient refresh execution. The clause lets Oracle Database use unenforced constraints, such as dimension relationships or constraints in the RELY state, rather than relying only on enforced constraints during the refresh operation.

NEVER REFRESH Clause

Specify NEVER REFRESH to prevent the materialized view from being refreshed with any Oracle Database refresh mechanism or packaged procedure. Oracle Database will ignore any REFRESH statement on the materialized view issued from such a procedure. To reverse this clause, you must issue an ALTER MATERIALIZED VIEW ... REFRESH statement.

Examples

Creating a Materialized View Log: Examples

The following statement creates a materialized view log on the oe.customers table that specifies physical and storage characteristics:

CREATE MATERIALIZED VIEW LOG ON customers 
   PCTFREE 5 
   TABLESPACE example 
   STORAGE (INITIAL 10K NEXT 10K); 

This materialized view log supports fast refresh for primary key materialized views only. 

The following statement creates another version of the materialized view log with the ROWID clause, which enables fast refresh for more types of materialized views:

CREATE MATERIALIZED VIEW LOG ON customers WITH PRIMARY KEY, ROWID; 

This materialized view log makes fast refresh possible for rowid materialized views and for materialized join views. To provide for fast refresh of materialized aggregate views, you must also specify the SEQUENCE and INCLUDING NEW VALUES clauses, as shown in the next statement.

Specifying Filter Columns for Materialized View Logs: Example

The following statement creates a materialized view log on the sh.sales table and is used in "Creating Materialized Aggregate Views: Example". It specifies as filter columns all of the columns of the table referenced in that materialized view.

CREATE MATERIALIZED VIEW LOG ON sales 
   WITH ROWID, SEQUENCE(amount_sold, time_id, prod_id)
   INCLUDNG NEW VALUES; 

Specifying Join Columns for Materialized View Logs: Example

The following statement creates a materialized view log on the order_items table of the sample oe schema. The log records primary keys and product_id, which is used as a join column in "Creating a Fast Refreshable Materialized View: Example".

CREATE MATERIALIZED VIEW LOG ON order_items WITH (product_id);

Creating a Fast Refreshable Materialized View: Example

The following statement creates a fast-refreshable materialized view that selects columns from the order_items table in the sample oe schema, using the UNION set operator to restrict the rows returned from the product_information and inventories tables using WHERE conditions. 

The materialized view logs for order_items and product_information were created in the "Examples " section of CREATE MATERIALIZEDVIEW LOG. This example also requires a materialized view log on oe.inventories.

CREATE MATERIALIZED VIEW LOG ON inventories
   WITH (quantity_on_hand);

CREATE MATERIALIZED VIEW warranty_orders REFRESH FAST AS
  SELECT order_id, line_item_id, product_id FROM order_items o
    WHERE EXISTS
    (SELECT * FROM inventories i WHERE o.product_id = i.product_id
      AND i.quantity_on_hand IS NOT NULL)
  UNION
    SELECT order_id, line_item_id, product_id FROM order_items
    WHERE quantity > 5; 

This materialized view requires that materialized view logs be defined on order_items (with product_id as a join column) and on inventories (with quantity_on_hand as a filter column).

Creating Materialized Join Views: Example

The following statement creates and populates the materialized aggregate view sales_by_month_by_state using tables in the sample sh schema. The materialized view will be populated with data as soon as the statement executes successfully. By default, subsequent refreshes will be accomplished by reexecuting the defining query of the materialized view:

CREATE MATERIALIZED VIEW sales_by_month_by_state
     TABLESPACE example
     PARALLEL 4
     BUILD IMMEDIATE
     REFRESH COMPLETE
     ENABLE QUERY REWRITE
     AS SELECT t.calendar_month_desc, c.cust_state_province,
        SUM(s.amount_sold) AS sum_sales
        FROM times t, sales s, customers c
        WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
        GROUP BY t.calendar_month_desc, c.cust_state_province;

Creating Rowid Materialized Views: Example

The following statement creates a rowid materialized view on the sample table oe.orders:
CREATE MATERIALIZED VIEW order_data REFRESH WITH ROWID 
   AS SELECT * FROM orders;


Periodic Refresh of Materialized Views: Example

The following statement creates the primary key materialized view emp_data and populates it with data from the sample table hr.employees:
CREATE MATERIALIZED VIEW LOG ON employees
   WITH PRIMARY KEY
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW emp_data 
   PCTFREE 5 PCTUSED 60 
   TABLESPACE example 
   STORAGE (INITIAL 50K NEXT 50K)
   REFRESH FAST NEXT sysdate + 7 
   AS SELECT * FROM employees; 



The statement does not include a START WITH parameter, so Oracle Database determines the first automatic refresh time by evaluating the NEXT value using the current SYSDATE. A materialized view log was created for the employee table, so Oracle Database performs a fast refresh of the materialized view every 7 days, beginning 7 days after the materialized view is created.

Because the materialized view conforms to the conditions for fast refresh, the database will perform a fast refresh. The preceding statement also establishes storage characteristics that the database uses to maintain the materialized view.



Automatic Refresh Times for Materialized Views: Example

The following statement creates the complex materialized view all_customers that queries the employee tables on the remote and local databases:
CREATE MATERIALIZED VIEW all_customers
   PCTFREE 5 PCTUSED 60 
   TABLESPACE example 
   STORAGE (INITIAL 50K NEXT 50K) 
   USING INDEX STORAGE (INITIAL 25K NEXT 25K)
   REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 
   NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 15/24 
   AS SELECT * FROM sh.customers@remote 
         UNION
      SELECT * FROM sh.customers@local; 



Oracle Database automatically refreshes this materialized view tomorrow at 11:00 a.m. and subsequently every Monday at 3:00 p.m. The default refresh method is FORCE. The defining query contains a UNION operator, which is not supported for fast refresh, so the database will automatically perform a complete refresh.

The preceding statement also establishes storage characteristics for both the materialized view and the index that the database uses to maintain it:

The first STORAGE clause establishes the sizes of the first and second extents of the materialized view as 50 kilobytes each.
The second STORAGE clause, appearing with the USING INDEX clause, establishes the sizes of the first and second extents of the index as 25 kilobytes each.

Materialized View REFRESH option

In this post I will be creating read only materialized view and giving different REFRESH option to get data updated in Master table into Materialized view.

Here, user SCOTT will be creating materialized view on HR.EMPLOYEES table. 

Manual Refresh using DBMS_MVIEW, DBMS_SNAPSHOT packages.

First, Grant needful privilege to SCOTT from SYS and HR schema.

---- As SYS ---

GRANT CREATE MATERIALIZED VIEW to SCOTT;

--- As HR ---

GRANT SELECT ON EMPLOYEES to SCOTT; 

-- As SCOTT --

SQL> CREATE MATERIALIZED VIEW scott.hremp
  2  AS SELECT * FROM HR.EMPLOYEES;

Materialized view created.

Querying Materialized view SCOTT.hremp

--- As Scott -- 

SQL> SELECT SALARY FROM HREMP
  2  WHERE EMPLOYEE_ID = 206;
    SALARY
----------
      8500

Now to refresh the materialized view:

Suppose you update one record in Employees Table:

--- As HR -- 

SQL> update employees set salary=8300 where employee_id =206;

1 row updated.

SQL> Commit;

SQL>  SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 206;
    SALARY
----------
      8300

-- As Scott

SQL> SELECT SALARY FROM HREMP WHERE EMPLOYEE_ID = 206;

    SALARY
----------

      8500

Now perform manual refresh using REFRESH method of DBMS_MVIEW package. 

SQL> exec DBMS_MVIEW.REFRESH('HREMP');
PL/SQL procedure successfully completed.

SQL>  SELECT SALARY FROM HREMP WHERE EMPLOYEE_ID = 206;

    SALARY

----------

      8300

Now we got refreshed data from HR.EMPLOYEES table into our materilized view SCOTT.HREMP.

Creating a Materialized View Refresh On commit on Master table:

We will be creating a materialized view again as above. Now in this case whenever commit on changes performed on master table (HR.EMPLOYEES) table is performed, our Materialized view (SCOTT.HREMP) should be refreshed quickly.

This is tricky and need some extra objects and privileges to come into picture.

We should have CREATE MATERIALIZED VIEW privilege, that can be verified as:

-- As SCOTT ---

SQL> SELECT * FROM SESSION_PRIVS;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE MATERIALIZED VIEW
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE 


11 rows selected.

Now from HR schema we also need to create MATERIALIZED VIEW LOG table and GRANT permission on it to SCOTT.

--- AS HR -- 

First drop any already existing MView Log

SQL> DROP  MATERIALIZED VIEW LOG ON HR.EMPLOYEES;
Materialized view log dropped. 

Now create Materialized View Log:

SQL> CREATE MATERIALIZED VIEW LOG ON HR.EMPLOYEES;

Materialized view log created.

This will create a table: MLOG$_. Here MLOG$_EMPLOYEES will be created.

SQL> SELECT * FROM MLOG$_EMPLOYEES;
no rows selected

SQL> DESC MLOG$_EMPLOYEES;
 Name
------------------------------------
 EMPLOYEE_ID
 SNAPTIME$$
 DMLTYPE$$
 OLD_NEW$$
 CHANGE_VECTOR$$
 XID$$

Now we need to GRANT permissions to HR. 

First revoke any already present privilege:

--- AS HR -- 
SQL> REVOKE ALL ON EMPLOYEES FROM SCOTT;
Revoke succeeded.

Now GRANT the needful permissions to SCOTT.

SQL> GRANT SELECT ON EMPLOYEES TO SCOTT;
Grant succeeded.

SQL> GRANT SELECT ON MLOG$_EMPLOYEES TO SCOTT;
Grant succeeded.

Now lets try to create materialized view: 

-- AS SCOTT

SQL> CREATE MATERIALIZED VIEW  scott.hremp
  2          REFRESH FAST ON COMMIT
  3    AS  SELECT * FROM HR.EMPLOYEES;
  AS  SELECT * FROM HR.EMPLOYEES
                       *
ERROR at line 3:
ORA-01031: insufficient privileges

SCOTT need more permission on HR.EMPLOYEES table.

-- As HR

SQL> GRANT ALL ON HR.EMPLOYEES TO SCOTT;

Grant succeeded.

-- As SCOTT, Trying to create M View again

SQL> CREATE MATERIALIZED VIEW  SCOTT.HREMP
  2  REFRESH FAST ON COMMIT
  3  AS  SELECT * FROM HR.EMPLOYEES;
Materialized view created.

Fetching data from M View (SCOTT.HREMP) :

SQL> SELECT SALARY FROM SCOTT.HREMP
  2  WHERE EMPLOYEE_ID = 206;
    SALARY
----------
      8300

Perform changes to some records in employees table followed by commit.

-- As HR

SQL> update employees set salary=salary+200 where employee_id =206;
1 row updated.

SQL> Commit;

SQL> SELECT SALARY FROM HR.EMPLOYEES

  2  WHERE EMPLOYEE_ID = 206;

    SALARY

----------

      8500

-- As SCOTT -- 

SQL> SELECT SALARY FROM SCOTT.HREMP  WHERE EMPLOYEE_ID = 206;

    SALARY

----------

      8500

Immediately data get refreshed.

Now scheduling Materialized View Refresh

Materialized View Concepts Explored

Materialized View Concepts

Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to nonmaster sites in a replication environment and to cache expensive queries in a data warehouse environment. This chapter, and this Oracle Database Advanced Replication manual in general, discusses materialized views for use in a replication 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 masters 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.

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

When a materialized view is fast refreshed, Oracle must examine all of the changes to the master table or master materialized view since the last refresh to see if any apply to the materialized view. Therefore, if any changes where made to the master since the last refresh, then a materialized view refresh takes some time to apply the changes to the materialized 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 materialized views to achieve one or more of the following goals:

• Ease Network Loads
• Create a Mass Deployment Environment
• Enable Data Subsetting
• Enable Disconnected Computing

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.

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, Updatable, and Writeable Materialized Views

A materialized view can be either read-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, updatable, and writeable materialized views, the defining query of the materialized view must reference all of the primary key columns in the master.
• Materialized views cannot be based on tables that contain any columns encrypted using transparent data encryption.

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:

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.

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.

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

Note:

Most of the documentation about materialized views only refers to read-only and updatable materialized views because writeable materialized views are rarely used.

	Read-only	Updatable	Writeable
created with “FOR UPDATE” clause?	N	Y	Y
perform DML operation?	N	Y	Y
placed in a materialized view group?	N	Y	N
changes be pushed back to master?	N	Y	N

 Available Materialized Views

Oracle offers several types of materialized views to meet the needs of many different replication (and nonreplication) 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

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.

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;