Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

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
Share this article :

0 comments:

Post a Comment