A materialized view stores both definitions of view plus rows resulting from the execution of the view. It is more efficient to use materialized views if query involves summaries, large or multiple joins or both. It is a pre-computed table comprising aggregated or joined data from fact and possibly dimensions tables. Also known as a summary or aggregate table and mainly used for improving query performance or providing replicated data.

Oracle provides SQL Access Advisor, which is a set of advisory procedures in the DBMS_ADVISOR package to help in designing and evaluating materialized views for query rewrite

A materialized view stores both definitions of view plus rows resulting from the execution of the view. It is more efficient to use materialized views if query involves summaries, large or multiple joins or both. It is a pre-computed table comprising aggregated or joined data from fact and possibly dimensions tables. Also known as a summary or aggregate table and mainly used for improving query performance or providing replicated data.

Oracle provides SQL Access Advisor, which is a set of advisory procedures in the DBMS_ADVISOR package to help in designing and evaluating materialized views for query rewrite

Key Features

• Can define independent tablespace, storage parameters to Materialized views.
• Use Index & Partition
• Use query re-write feature – Process of modifying a query to use the view rather than the base table is called query rewrite.

Types of materialized views

• Materialized view with Aggregates – The valid aggregate functions are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX
• Materialized views containing only joins
• Nested Materialized views – materialized view whose definition is based on another materialized view

Materialized View Logs

Materialized view logs are required 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.

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.

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;

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

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;

Create Syntax

CREATE MATERIALIZED VIEW <name> TABLESPACE <tbs_name> (<storage parameter>).

build option

REFRESH <refresh option> <refresh mode> [ENABLE | DISABLE] QUERY REWRITE AS SELECT <select clause>;

Example

CREATE MATERIALIZED VIEW product_sales_mv

PCTFREE 0 TABLESPACE demo

STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)

BUILD DEFERRED

REFRESH COMPLETE ON DEMAND

ENABLE QUERY REWRITE AS

SELECT p.prod_name, SUM (s.amount_sold) AS dollar_sales

FROM sales s, products p WHERE s.prod_id = p.prod_id

GROUP BY p.prod_name;

Built Option

The <build option> determines when MV is built

BUILD IMMEDIATE: Create the materialized view and then populate it with data.

BUILD DEFFERED: Create the materialized view definition but do not populate it with data.

ON PREBUILT TABLE: use an existing table as view source

Materialized Views Refresh Types

Refresh is the operation that synchronizes the content of the materialized view with the data in base tables. Following are the types of refresh:

Complete: Involves truncating existing data & re-inserting all the data based on the detail tables by re-executing the query definition from the create command.

Fast: Apply the changes made since the last refresh.

a)Fast refresh using materialized view logs

b)Fast refresh using ROWID range: Can do the fast refresh after the direct load, based on the rowed of the new rows.

Force: First tries to refresh with fast mechanism if possible or else will use a complete refresh. This is default refresh type.

Never: Suppresses all refreshes on materialized views.

Materialized Views Refresh Modes

Manual Refresh:  Can be performed using DBMS_MVIEW package. (REFRESH, REFRESH_DEPENDENT, REFRESH_ALL_VIEWS)

Automatic Refresh: Can be performed in two ways:

a)ON COMMIT – Materialized view gets updated whenever changes to one of these tables are committed.

b)ON DEMMAND – At Specified Time – Refresh is scheduled to occur for specified time by using START WITH & NEXT clauses. For such refreshes, instance must initiate a process with JOB_QUEUE_PROCESSES.

Materialized Views restrictions

Underlying Query cannot contain 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

For PREBUILT, the precision of the columns must agree with the precision of the corresponding SELECT expressions

MV with FAST refresh cannot contain any subquery.

Adding Comments to Materialized Views

COMMENT ON MATERIALIZED VIEW sales_mv IS ’sales materialized view’;

SELECT MVIEW_NAME, COMMENTS

FROM USER_MVIEW_COMMENTS WHERE MVIEW_NAME = ‘SALES_MV’;

Dropping Materialized Views

DROP MATERIALIZED VIEW sales_sum_mv;

Analyzing Materialized View Capabilities

To analyze the potential / Details of MV can use following package. This will write details into MV_CAPABILITIES_TABLE.

EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW (‘SH.CAL_MONTH_SALES_MV’);

SELECT capability_name,  possible, SUBSTR(related_text,1,8)

AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt

FROM MV_CAPABILITIES_TABLE

ORDER BY seq;

Partition Change Tracking (PCT)

The ability to identify which rows in a materialized view are affected by a certain detail table partition is known as Partition Change Tracking. When one or more of the detail tables are partitioned, it may be possible to identify the specific rows in the materialized view that correspond to a modified detail partition(s); those rows become stale when a partition is modified while all other rows remain fresh.

Requirements for PCT

At least one of the detail tables referenced by the materialized view must be partitioned

Partitioned tables must use either range, list or composite partitioning

The top level partition key must consist of only a single column

PCT-based refresh is not supported for UNION ALL materialized views

PCT is not supported for a materialized view that refers to views, remote tables, or outer joins

QUERY REWRITE

Query rewrite is the process of modifying a query to use the view rather than the base table.

User do not explicitly need privileges on materialized views, permission on underlying base tables are required.

Optimizer rewrites the queries in order to utilize materialized views.

GLOBAL_QUERY_REWRITE & QUERY_REWRITE system privileges allows user to enable materialized views.