Materialized Views : Part 1

Creating Materialized Views

-- Normal
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

-- Pre-Built
CREATE MATERIALIZED VIEW view-name
ON PREBUILT TABLE
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

CREATE MATERIALIZED VIEW cust_sales_mv

PCTFREE 0 TABLESPACE demo

STORAGE (INITIAL 8M)

PARALLEL

BUILD IMMEDIATE (or DEFERRED)

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;

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

Ø You also must specify the ENABLE QUERY REWRITE clause if the materialized view is to be considered available for rewriting queries.

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

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

Ø use the procedure DBMS_ADVISOR.TUNE_MVIEW to see if the materialized view can be defined differently so that query rewrite is possible.

Refresh Modes for Materialized Views

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

ON STATEMENT Refresh occurs automatically, without the need to commit the transaction, when a DML operation is

performed on any of the materialized view’s base tables. This method does not require the creation of materialized view logs on materialized view’s base tables.

Types (Option) of Materialized View Refresh

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.

Ø You can call the procedure DBMS_MVIEW.EXPLAIN_MVIEW to determine whether fast refresh is possible

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.

• It cannot contain a SELECT list subquery.

• It cannot contain analytic functions (for example, RANK) in the SELECT clause.

• It cannot reference a table on which an XMLIndex index is defined.

• It cannot contain a MODEL clause.

• It cannot contain a HAVING clause with a subquery.

• It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.

• It cannot contain a [START WITH …] CONNECT BY clause.

• It cannot contain multiple detail tables at different sites.

• ON COMMIT materialized views cannot have remote detail tables.

• Nested materialized views must have a join or aggregate.

• Materialized join views and materialized aggregate views with a GROUP BY clause cannot select from an index-organized table.

• They cannot have GROUP BY clauses or aggregates.

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

Nested Materialized Views

·       A nested materialized view is a materialized view whose definition is based on another materialized view.

·       You can create a nested materialized view on materialized views, but all parent and base materialized views must contain joins or aggregates.

·      All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log.

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;

/*create materialized view join_sales_cust_time as fast refreshable at

COMMIT time */

CREATE MATERIALIZED VIEW join_sales_cust_time

REFRESH FAST

ON COMMIT AS

SELECT c.cust_id, c.cust_last_name, s.amount_sold, t.time_id,

t.day_number_in_week, s.rowid srid, t.rowid trid, c.rowid crid

FROM sales s, customers c, times t

WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id;

Ø To create a nested materialized view on the table join_sales_cust_time, you would have to create a materialized view log on the table.

/* create materialized view log on join_sales_cust_time */

CREATE MATERIALIZED VIEW LOG ON join_sales_cust_time

WITH ROWID (cust_last_name, day_number_in_week, amount_sold)

INCLUDING NEW VALUES;

/* create the single-table aggregate materialized view sum_sales_cust_time

on join_sales_cust_time as fast refreshable at COMMIT time */

CREATE MATERIALIZED VIEW sum_sales_cust_time

REFRESH FAST

ON COMMIT AS

SELECT COUNT (*) cnt_all, SUM(amount_sold) sum_sales, COUNT(amount_sold)

cnt_sales, cust_last_name, day_number_in_week

FROM join_sales_cust_time

GROUP BY cust_last_name, day_number_in_week;

Ø  You can refresh a tree of nested materialized views in the appropriate dependency order by specifying the

nested = TRUE parameter with the DBMS_MVIEW.REFRESH parameter.

For example, if you call

DBMS_MVIEW.REFRESH ('SUM_SALES_CUST_TIME', nested => TRUE),

 the REFRESH procedure will first refresh the join_sales_cust_time materialized view, and then refresh the sum_sales_cust_time materialized view.

Ø If you want the highest level materialized view to be refreshed with respect to the detail tables, you must ensure that all materialized views in a tree are refreshed in the correct dependency order before refreshing the highest-level. You can automatically refresh intermediate materialized views in a nested hierarchy using the  nested = TRUE

Refreshing Nested Materialized Views

You can refresh nested materialized views in two ways:

Ø  DBMS_MVIEW.REFRESH with the nested flag set to TRUE and

Ø  DBMS_MVIEW.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 and the coverage starting from the specified materialized view in top-down fashion.

DBMS_MVIEW.REFRESH('SALES_MV,COST_MV', nested => TRUE);

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

With DBMS_MVIEW.REFRESH_DEPENDENT, the entire chain is refreshed from the bottom up. That is, all the parent materialized views in the dependency hierarchy starting from the specified table are refreshed in order.

Materialized View Logs

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID

(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)

INCLUDING NEW VALUES

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID

(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)

COMMIT SCN INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID

(prod_id, cust_id, time_id, channel_id, promo_id,quantity_sold, amount_sold)

INCLUDING NEW VALUES;

Ø DROP MATERIALIZED VIEW sales_sum_mv;

Ø use the DBMS_MVIEW.EXPLAIN_MVIEW procedure to learn what is possible with a materialized view or potential materialized view

• If a materialized view is fast refreshable

• What types of query rewrite you can perform with this materialized view

• Whether partition change tracking refresh is possible

Partitioning a Materialized View

CREATE MATERIALIZED VIEW part_sales_mv

PARALLEL PARTITION BY RANGE (time_id)

(PARTITION month1

VALUES LESS THAN (TO_DATE('31-12-1998', 'DD-MM-YYYY'))

PCTFREE 0

STORAGE (INITIAL 8M)

TABLESPACE sf1,

PARTITION month2

VALUES LESS THAN (TO_DATE('31-12-1999', 'DD-MM-YYYY'))

PCTFREE 0

STORAGE (INITIAL 8M)

TABLESPACE sf2,

PARTITION month3

VALUES LESS THAN (TO_DATE('31-12-2000', 'DD-MM-YYYY'))

PCTFREE 0

STORAGE (INITIAL 8M)

TABLESPACE sf3)

BUILD DEFERRED

REFRESH FAST

ENABLE QUERY REWRITE AS

SELECT s.cust_id, s.time_id,

SUM(s.amount_sold) AS sum_dol_sales, SUM(s.quantity_sold) AS sum_unit_sales

FROM sales s GROUP BY s.time_id, s.cust_id;