Creating Materialized Views - Data Warehousing

A materialized view can be created with the CREATE MATERIALIZED VIEW statement or using Enterprise Manager. The following example creates the materialized view cust_sales_mv.

Example Creating a Materialized View

It is not uncommon in a data warehouse to have already created summary or aggregation tables, and you might not wish to repeat this work by building a new materialized view. In this case,the table that already exists in the database can be registered as a prebuilt materialized view. This technique is described in "Registering Existing Materialized Views".

Once you have selected the materialized views you want to create, follow these steps for each materialized view.

  • Design the materialized view. Existing user-defined materialized views do not require this step. If the materialized view contains many rows, then, if appropriate, the materialized view should be partitioned (if possible) and should match the partitioning of the largest or most frequently updated detail or fact table (if possible). Refresh performance benefits from partitioning, because it can take advantage of parallel DML capabilities and possible PCT-based refresh.
  • Use the CREATE MATERIALIZED VIEW statement to create and, optionally, populate the materialized view. If a user-defined materialized view already exists, then use the ON PREBUILT TABLE clause in the CREATE MATERIALIZED VIEW statement. Otherwise, use the BUILD IMMEDIATE clause to populate the materialized view immediately, or the BUILD DEFERRED clause to populate the materialized view later. A BUILD DEFERRED materialized view is disabled for use by query rewrite until the first COMPLETE REFRESH, after which it will be automatically enabled, provided the ENABLE QUERY REWRITE clause has been specified.

Creating Materialized Views with Column Alias Lists
Currently, when a materialized view is created, if its defining query contains same-name columns in the SELECT list, the name conflicts need to be resolved by specifying unique aliases for those columns. Otherwise, the CREATE MATERIALIZED VIEW statement will fail with the error messages of columnsambiguously defined. However, the standard method of attaching aliases in the SELECT clause for name resolution restricts the use of the full text match query rewrite and it will occur only when the text of the materialized view's defining query and the text of user input query are identical. Thus, if the user specifies select aliases in the materialized view's defining query while there is no alias in the query, the full text match comparison will fail. This is particularly a problem for queries from Discoverer, which makes extensive use of column aliases.

The following is an example of the problem. sales_mv is created with column aliases in the SELECT clause but the input query Q1 does not have the aliases. The full text match rewrite will fail. The materialized view is as follows:

Input query statement Q1 is as follows:

Even though the materialized view's defining query is almost identical and logically equivalent to the user's input query, query rewrite does not happen because of the failure of full text match that is the only rewrite possibility for some queries (for example, a subquery in the WHERE clause).

You can add a column alias list to a CREATE MATERIALIZED VIEW statement. The column alias list explicitly resolves any column name conflict without attaching aliases in the SELECT clause of the materialized view. The syntax of the materialized view column alias list is illustrated in the following example:

In this example, the defining query of sales_mv now matches exactly with the user query Q1, so full text match rewrite will take place.

Note that when aliases are specified in both the SELECT clause and the new alias list clause, the alias list clause supersedes the ones in the SELECT clause.

Naming Materialized Views
The name of a materialized view must conform to standard Oracle naming conventions. However, if the materialized view is based on a user-defined prebuilt table, then the name of the materialized view must exactly match that table name.

If you already have a naming convention for tables and indexes, you might consider extending this naming scheme to the materialized views so that they are easily identifiable. For example, instead of naming the materialized view sum_of_sales, it could be called sum_of_sales_mv to denote that this is a materialized view and not a table or view.

Storage And Table Compression
Unless the materialized view is based on a user-defined prebuilt table, it requires and occupies storage space inside the database. Therefore, the storage needs for the materialized view should be specified in terms of the tablespace where it is to reside and the size of the extents.

If you do not know how much space the materialized view will require, then the DBMS_MVIEW.ESTIMATE_SIZE package can estimate the number of bytes required to store this uncompressed materialized view. This information can then assist the design team in determining the tablespace in which the materialized view should reside.

You should use table compression with highly redundant data, such as tables with many foreign keys. This is particularly useful for materialized views created with the ROLLUP clause. Table compression reduces disk use and memory use (specifically, the buffer cache), often leading to a better scaleup for read-only operations. Table compression can also speed up query execution at the expense of update cost.

Build Methods
Two build methods are available for creating the materialized view, as shown in the following Table. If you select BUILD IMMEDIATE, the materialized view definition is added to the schema objects in the data dictionary, and then the fact or detail tables are scanned according to the SELECT expression and the results are stored in the materialized view. Depending on the size of the tables to be scanned, this build
process can take a considerable amount of time.

An alternative approach is to use the BUILD DEFERRED clause, which creates the materialized view without data, thereby enabling it to be populated at a later date using the DBMS_MVIEW.REFRESH package.

Build Methods

Build Methods

Enabling Query Rewrite
Before creating a materialized view, you can verify what types of query rewrite are possible by calling the procedure DBMS_MVIEW.EXPLAIN_MVIEW, or use DBMS_ ADVISOR.TUNE_MVIEW to optimize the materialized view so that a many types of query rewrite are possible. Once the materialized view has been created, you can use DBMS_MVIEW.EXPLAIN_REWRITE to find out if (or why not) it will rewrite a specific query.

Even though a materialized view is defined, it will not automatically be used by the query rewrite facility. Even though query rewrite is enabled by default, you also must specify the ENABLE QUERY REWRITE clause if the materialized view is to be considered available for rewriting queries.

If this clause is omitted or specified as DISABLE QUERY REWRITE when the materialized view is created, the materialized view can subsequently be enabled for query rewrite with the ALTER MATERIALIZED VIEW statement.

If you define a materialized view as BUILD DEFERRED, it is not eligible for query rewrite until it is populated with data.

Query Rewrite Restrictions
Query rewrite is not possible with all materialized views. If query rewrite is not occurring when expected, DBMS_MVIEW.EXPLAIN_REWRITE can help provide reasons why a specific query is not eligible for rewrite. If this shows that not all types of query rewrite are possible, use the procedure DBMS_ADVISOR.TUNE_ MVIEW to see if the materialized view can be defined differently so that queryrewrite is possible. Also, check to see if your materialized view satisfies all of the following conditions.

Materialized View Restrictions
You should keep in mind the following restrictions:

  • The defining query of the materialized view cannot contain any non-repeatable expressions (ROWNUM, SYSDATE, non-repeatable PL/SQL functions, and so on).
  • The query cannot contain any references to RAW or LONG RAW datatypes or object REFs.
  • If the materialized view was registered as PREBUILT, the precision of the columns must agree with the precision of the corresponding SELECT expressions unless overridden by the WITH REDUCED PRECISION clause.

General Query Rewrite Restrictions
You should keep in mind the following restrictions:

  • If a query has both local and remote tables, only local tables will be considered for potential rewrite.
  • Neither the detail tables nor the materialized view can be owned by SYS.
  • If a column or expression is present in the GROUP BY clause of the materialized view, it must also be present in the SELECT list.
  • Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.
  • CONNECT BY clauses are not allowed.

Refresh Options
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. Depending on the materialized view you create, some of the options may not be available. The following table describes the refresh modes.

Refresh Modes

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 you think the materialized view did not refresh, check the alert log or trace file. 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. The following table describes the refresh options.

Refresh Options

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


General Restrictions on Fast Refresh

The defining query of the materialized view is restricted as follows:

  • The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.
  • The materialized view must not contain references to RAW or LONG RAW data types.

Restrictions on Fast Refresh on Materialized Views with Joins Only

Defining queries for materialized views with joins only and no aggregates have the following restrictions on fast refresh:

  • All restrictions from "General Restrictions on Fast Refresh".
  • They cannot have GROUP BY clauses or aggregates.
  • If the WHERE clause of the query contains outer joins, then unique constraints must exist on the join columns of the inner join table.
  • If there are no outer joins, you can have arbitrary selections and joins in the WHERE clause. However, if there are outer joins, the WHERE clause cannot have any selections. Furthermore, if there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.
  • Rowids of all the tables in the FROM list must appear in the SELECT list of the query.
  • Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.

Restrictions on Fast Refresh on Materialized Views with Aggregates
Defining queries for materialized views with aggregates or joins have the following restrictions on fast refresh:

  • All restrictions from "General Restrictions on Fast Refresh".

Fast refresh is supported for both ON COMMIT and ON DEMAND materialized views, however the following restrictions apply:

  • All tables in the materialized view must have materialized view logs, and the materialized view logs must:
  • Contain all columns from the table referenced in the materialized view.
  • Specify with ROWID and INCLUDING NEW VALUES.
  • Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.
  • Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.
  • COUNT(*) must be specified.
  • For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present.
  • If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified.
  • The SELECT list must contain all GROUP BY columns.
  • If the materialized view has one of the following, then fast refresh is supported only on conventional DML inserts and direct loads.
  • Materialized views with MIN or MAX aggregates
  • Materialized views which have SUM(expr) but no COUNT(expr)
  • Materialized views without COUNT(*)

Such a materialized view is called an insert-only materialized view.

  • A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause.
  • Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged.
  • If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause.
  • Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified. Also, unique constraints must exist on the join columns of the inner join table.If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.
  • For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the following restrictions apply:
  • The SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable.
  • GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".

Restrictions on Fast Refresh on Materialized Views with UNION ALL
Materialized views with the UNION ALL set operator support the REFRESH FAST option if the following conditions are satisfied:

  • The defining query must have the UNION ALL operator at the top level.

The UNION ALL operator cannot be embedded inside a subquery, with one exception: The UNION ALL can be in a subquery in the FROM clause provided the defining query is of the form SELECT * FROM (view or subquery with UNION ALL) as in the following example:

Note that the view view_with_unionall_mv satisfies all requirements for fast refresh.

  • Each query block in the UNION ALL query must satisfy the requirements of a fast refreshable materialized view with aggregates or a fast refreshable materialized view with joins.

The appropriate materialized view logs must be created on the tables as required for the corresponding type of fast refreshable materialized view.

Note that the Oracle Database also allows the special case of a single table materialized view with joins only provided the ROWID column has been included in the SELECT list and in the materialized view log. This is shown in the defining query of the view view_with_unionall_mv.

  • The SELECT list of each query must include a maintenance column, called a UNION ALL marker. The UNION ALL column must have a distinct constant numeric or string value in each UNION ALL branch. Further, the marker column must appear in the same ordinal position in the SELECT list of each query block.
  • Some features such as outer joins, insert-only aggregate materialized view queries and remote tables are not supported for materialized views with UNION ALL.
  • PCT-based refresh is not supported for UNION ALL materialized views.
  • The compatibility initialization parameter must be set to 9.2.0 or higher to create a fast refreshable materialized view with UNION ALL.

Achieving Refresh Goals
In addition tothe EXPLAIN_MVIEW procedure, which is discussed throughout this chapter, you can use the DBMS_ADVISOR.TUNE_MVIEW procedure to optimize a CREATE MATERIALIZED VIEW statement to achieve REFRESH FAST and ENABLE QUERY REWRITE goals. The procedure is described in Tuning Materialized Views for Fast Refresh and Query Rewrite.

Refreshing Nested Materialized Views
A nested materialized view is considered to be fresh as long as its data is synchronized with the data in its detail tables, even if some of its detail tables could be stale materialized views.

You can refresh nested materialized views in two ways: DBMS_MVIEW.REFRESH with the nested flag set to TRUE and REFRESH_DEPENDENT with the nested flag set to TRUE on the base tables. If you use DBMS_MVIEW.REFRESH, the entire materialized view chain is refreshed from the top down. With DBMS_MVIEW.REFRESH_DEPENDENT, the entire chain is refreshed from the bottom up.

Example 8–7 Example of Refreshing a Nested Materialized View

The following statement shows an example of refreshing a nested materialized view:

This statement will first refresh all child materialized views of sales_mv and cost_mv based on the dependency analysis and then refresh the two specified materialized views.

You can query the STALE_SINCE column in the *_MVIEWS views to find out when a materialized view became stale.

An ORDER BY clause is allowed in the CREATE MATERIALIZED VIEW statement. It is used only during the initial creation of the materialized view. Itis not used during a full refresh or a fast refresh.

To improve the performance of queries against large materialized views, store the rows in the materialized view in the order specified in the ORDER BY clause. This initial ordering provides physical clustering of the data. If indexes are built on the columns by which the materialized view is ordered, accessing the rows of the materialized view using the index often reduces the time for disk I/O due to thephysical clustering.

The ORDER BY clause is not considered part of the materialized view definition. As a result, there is no difference in the manner in which Oracle Database detects the various types of materialized views (for example, materialized join views with no aggregates). For the same reason, query rewrite is not affected by the ORDER BY clause. This feature is similar to the CREATE TABLE ... ORDER BY capability.

Materialized View Logs
Materialized view logs are required if you want to use fast refresh, with the exception of PCT refresh,where there a few situations where they are not necessary. As a general rule, though, you should create materialized view logs if you want to use fast refresh. Materialized view logs are defined using a CREATE MATERIALIZED VIEW LOG statement on the base table that is to be changed. They Creating are not created on the materialized view. For fast refresh of materialized views, the definition of the materialized view logs must normally specify the ROWID clause. In addition, for aggregate materialized views, it must also contain every column in the table referenced in the materialized view, the INCLUDING NEW VALUES clause and the SEQUENCE clause.

An example of a materialized view log is shown as follows where one is created on the table sales.

Alternatively, a materialized view log can be amended to include the rowid, as in the following:

Oracle recommends that the keyword SEQUENCE be included in your materialized view log statement unless you are sure that you will never perform a mixed DML operation (a combination of INSERT, UPDATE, or DELETE operations on multiple tables).The SEQUENCE column is required in the materialized view log to support fast refresh with a combination of INSERT, UPDATE, or DELETE statements on multiple tables. You can, however, add the SEQUENCE number to the materializedview log after it has been created.

The boundary of a mixed DML operation is determined by whether the materialized view is ON COMMIT or ON DEMAND.

  • For ON COMMIT, the mixed DML statements occur within the same transaction because the refresh of the materialized view will occur upon commit of this transaction.
  • For ON DEMAND, the mixed DML statements occur between refreshes. The following example of a materialized view log illustrates where one is created on the table sales that includes the SEQUENCE keyword:

Using the FORCE Option with Materialized View Logs
If you specify FORCE and any items specified with the ADD clause have already been specified for the materialized view log, Oracle does not return an error, but silently ignores the existing elements and adds to the materialized view log any items that do not already exist in the log. For example, if you used a filter column such as cust_id and this column already existed, Oracle Database ignores the redundancyand does not return an error.

Using Oracle Enterprise Manager
A materialized view can also be created using Enterprise Manager by selecting the materialized view object type. There is no difference in the information required if this approach is used.

Using Materialized Views with NLS Parameters
When using certain materialized views, you must ensure that your NLS parameters are the same as when you created the materialized view. Materialized views with this restriction are as follows:

  • Expressions that may return different values, depending on NLS parameter settings. For example, (date > "01/02/03") or (rate <= "2.150") are NLS parameter dependent expressions.
  • Equijoins where one side of the join is character data. The result of this equijoin depends on collation and this can change on a session basis, giving an incorrect result in the case of query rewrite or an inconsistent materialized view after a refresh operation.
  • Expressions that generate internal conversion to character data in the SELECT list of a materialized view, or inside an aggregate of a materialized aggregate view. This restriction does not apply to expressions that involve only numeric data, for example, a+b where a and b are numeric fields.

Adding Comments to Materialized Views
You can add a comment to a materialized view. For example, the following statement adds a comment to data dictionary views for the existing materialized view:

COMMENT ON MATERIALIZED VIEW sales_mv IS 'sales materialized view';To view the comment after the preceding statement execution, the user can query the catalog views, {USER, DBA} ALL_MVIEW_COMMENTS. For example:

The output will resemble the following

------------------- -------------------------------
SALES_MV sales materialized view

Note: If the compatibility is set to 10.0.1 or higher, COMMENT ON TABLE will not be allowed for the materialized view container table. The following error message will be thrown if it is issued.

ORA-12098: cannot comment on the materialized view.

In the case of a prebuilt table, if it has an existing comment, the comment will be inherited by the materialized view after it has been created. The existing comment will be prefixed with '(from table)'. For example, table sales_summary was created to contain sales summary information. An existing comment 'Sales summary data' was associated with the table. A materialized view of the same name is created to use the prebuilt table as its container table. After the materialized view creation, the comment becomes '(from table) Sales summary data'.

However, if the prebuilt table, sales_summary, does not have any comment, the following comment is added: 'Sales summary data'. Then, if we drop the materialized view, the comment will be passed to the prebuilt table with the comment: '(from materialized view) Sales summary data'.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Data Warehousing Topics