What is a Materialized View?

More on Materialized Views
  Materialized Views

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query

What is a Materialized View?

Let’s start by looking at the differences between a View and a Materialize View.

A View is a logical table based on one or more tables or views.

Logical table meaning that the query is run at the time you select from the view.

A Materialized View is like a regular view except that the initial query has already been run and the results are actually stored in a physical table. When you select from a Materialized View you are only querying the underlying table where the results are already stored.

View – SQL query executed when you query the view.

Materialized View – Select directly from a base table where the SQL query results are already stored.

Materialized Views VS. Row/Result Cache

• Materialized View – The results stored in a table. Refreshed completely or only the DML transactions to the base table on commit or at periodic intervals.
• Result Cache – The results stored in memory.  Refreshed anytime the underlying data is changed.

 Complete Refresh VS. Fast Refresh

• Complete Refresh simply reloads the entire Materialized View from scratch.
• Fast Refresh, once created, only applies the DML changes to the Materialized View.

 Materialized View Log

• Fast Refresh requires a Materialized View Log be created prior to creating the Materialized View. The Materialized View Log is the table that stores all the DML changes that take place on the base table for the Materialized View. The Materialized View Log is self maintained and once the records are applied to the Materialized View they are automatically purged from the Materialized View log.

Refresh Mode

On Demand, On Commit, or Never.

• On Demand:  Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package REFRESH.

• On Commit: Refresh occurs automatically when a transaction that modified one of the Materialized View's base tables commits.  

For example, let’s say you have query that provides a sum of minutes and totals by user and experiment. You have an application that is running this query and because of the sum and aggregate it continues to take longer as time goes by.

select USERNAME, EXP_NAME, sum(MINUTES) AS MINUTES, count(*) AS TOTAL

from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B

where A.EXPID = B.EXPID

group by USERNAME,EXP_NAME

order by USERNAME,EXP_NAME;

Let’s transform this query into a table that can be queried.

CREATE TABLE EXPERIMENTS

AS

select USERNAME, EXP_NAME, sum(MINUTES) AS MINUTES, count(*) AS TOTAL

from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B

where A.EXPID = B.EXPID

group by USERNAME,EXP_NAME

order by USERNAME,EXP_NAME;

You put into place a nightly process to truncate and refresh it each day.

TRUNCATE TABLE EXPERIMENTS;

INSERT INTO EXPERIMENTS...

Now each day the users have sub-second query results by selecting from EXPERIMENTS.  The only problem is that the data is only up to date through the prior day.

This is similar to a Materialized View, but a Materialized View can not only be automated, it can greatly enhance this process.  A Materialized View has additional features, many which we will discuss, that a regular View does not have. 



Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query