Creating Materialized Views

A materialized view can be created with the CREATE MATERIALIZED VIEW statement
or using Enterprise Manager. Example illustrates creating an materialized view
called cust_sales_mv.

Example Creating a Materialized View

CREATE MATERIALIZED VIEW cust_sales_mv
PCTFREE 0 TABLESPACE demo
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
PARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE AS
SELECT c.cust_last_name, SUM(amount_sold) AS sum_amount_sold
FROM customers c, sales s WHERE s.cust_id = c.cust_id
GROUP BY c.cust_last_name;

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.

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

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

2. 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 columns ambiguously 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:

CREATE MATERIALIZED VIEW sales_mv
ENABLE QUERY REWRITE AS
SELECT s.time_id sales_tid, c.time_id costs_tid
FROM sales s, products p, costs c
WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND
p.prod_name IN (SELECT prod_name FROM products);

Input query statement Q1 is as follows:

SELECT s.time_id, c.time_id
FROM sales s, products p, costs c
WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND
p.prod_name IN (SELECT prod_name FROM products);

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:

CREATE MATERIALIZED VIEW sales_mv (sales_tid, costs_tid)
ENABLE QUERY REWRITE AS
SELECT s.time_id, c.time_id
FROM sales s, products p, costs c
WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND
p.prod_name IN (SELECT prod_name FROM products);

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


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

BUILD IMMEDIATE Create the materialized view and then populate it with data.
BUILD DEFERRED Create the materialized view definition but do not populate it with data.
using the DBMS_MVIEW.REFRESH package

Posted in: Materialized Views