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
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
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:
 |
{ { 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.
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.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 viewWITH 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.
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
GROUPBYorCONNECTBYclauses- 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.
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: ExampleThe following statement creates a rowid materialized view on the sample tableoe.orders:CREATE MATERIALIZED VIEW order_data REFRESH WITH ROWID AS SELECT * FROM orders;emp_dataand populates it with data from the sample tablehr.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 aSTARTWITHparameter, so Oracle Database determines the first automatic refresh time by evaluating theNEXTvalue using the currentSYSDATE. 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: ExampleThe following statement creates the complex materialized viewall_customersthat queries the employee tables on theremoteandlocaldatabases: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 isFORCE.Thedefining query contains aUNIONoperator, 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
STORAGEclause establishes the sizes of the first and second extents of the materialized view as 50 kilobytes each. - The second
STORAGEclause, appearing with theUSINGINDEXclause, establishes the sizes of the first and second extents of the index as 25 kilobytes each.
