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.