Materialized View

What is Materialized View

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data.

Materialized views, which store data based on remote tables are also known as snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables

Why Use Materialized View

Less physical reads

There is less data to scan through

Less writes

We will not be sorting/aggregating as frequently

Decreased CPU consumption

We will not be calculating aggregates and functions on the data, as we will have already done that

Markedly faster response times

Our queries will return incredibly quickly when a summary is used, as opposed to the details. This will be a function of the amount of work we can avoid by using the materialized view.

Types of Materialized Views

Read-only materialized view

You can not perform DML on materialized views in this category.

Can be refreshed by master tables (manually and automatically).

Updateable/Writeable materialized view

Can be updated even when disconnected from the master site.

They are created with the for update clause. The materialized view is updatable, but the changes are lost when the materialized view refreshes.

Updateable materialized views require the advanced replication option to be installed.

Materialized Views - Refresh

Refresh Clause

          [refresh [fast|complete|force][on demand | commit] [start with date] [next date] [with {primary key|rowid}]]

Refresh Methods

FAST Clause

The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.

You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.

Complete Clause

The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

Force Clause

When you specify a FORCE clause, Oracle performs a fast refresh if possible, otherwise complete refresh. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default

Materialized Views - Creation

CREATE MATERIALIZED VIEW LOG

      ON emp

      WITH PRIMARY KEY, ROWID (deptno)

Primary key materialized views:

CREATE MATERIALIZED VIEW mv_emp_pk

REFRESH FAST

START WITH SYSDATE

NEXT  SYSDATE + 1/24

WITH PRIMARY KEY 

AS SELECT * FROM emp

Clause NEXT SYSDATE+1/24 automatically refreshes the materialized view after every one hour

Update the table emp and the change is reflected to materialized view

UPDATE emp SET ename='JOHN' WHERE empno=7369

Example of Sub query materialized views

  CREATE MATERIALIZED VIEW  mv_empdept

   REFRESH FAST START WITH SYSDATE

          NEXT  SYSDATE + 1/86400

   AS SELECT * FROM emp e

        WHERE EXISTS(SELECT * FROM dept d WHERE    e.deptno = d.deptno)

Materialized Views – Manual Refresh

The 'refresh' stored procedure of the DBMS_MVIEWS  package can be used to manually refresh a materialized view

EXECUTE DBMS_MVIEWS.refresh (' [ schema.] name ' [, 'refresh_option'])

For example, to do a FAST refresh of the mv_emp_pk

EXECUTE DBMS_MVIEWS.refresh(' mv_emp_pk’,‘F')

    Refresh Options:

            'F' or 'f' = FAST refresh

            'C' or 'c' = COMPLETE refresh

            '?' = DEFAULT refresh option

                  

Materialized Views – Update / Drop

UPDATE mv_emp_pk SET ename='JOHN' WHERE empno=7369

Materialized Views can be updated directly but requires Oracle advanced replication option to be installed

DROP MATERIALIZED VIEW LOG ON emp

DROP MATERIALIZED VIEW mv_emp_pk