Materialized views

 

A materialized view is a database object that contains the results of a query.

A materialized view can query tables, views, and other materialized views.

They are also known as snapshots.

 

Syntax:

Create materialized view < materialized view name >

[build [deferred|immediate]

[refresh [fast|complete|force]

[on demand | commit]

[start with date] [next date]

[with {primary key|rowid}]]

As <select statement>;

[ ] - Optional

 

Methods:

 

Oracle provides 2 methods to build the materialized views.

 

• Immediate
• Deferred

 

Immediate:

Create the materialized view and then populate it with data.

 

Eg:

Create materialized view emp_mat_view

build immediate

as select * from emp;

 

Deferred:

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

 

Eg:

Create materialized view emp_mat_view

build deferred

as select * from emp;

 

Types:

 

Oracle provides 3 methods to refresh the materialized views.

 

• Complete
• Fast
• Force

 

Complete:

         When complete refresh occurs in the materialized view, it is first truncated and

         all the data are reloaded.

         Complete refresh is a good technique to use when

 

•  The number of new rows to be inserted is more than 50% of the cardinality of the

           tables on which the materialized view is based.

 

•  The time required to perform a fast refresh is longer than a complete refresh.

 

Eg:

Create materialized view emp_mat_view

refresh complete

as select * from emp;

 

Fast:

          The new data loaded into any table in the warehouse is identified and

          any materialized view referencing that table is automatically updated with the new data.

          To identify the new data loaded into the master tables, you need to create the materialized

          view log on the master tables.

          Before creating the materialized views the log should be created first.

Syntax:

Create materialized view log on <table_name>;

Create materialized view log on emp;

The log should be created based up on the master table.

Eg:

Create materialized view emp_mat_view

refresh fast

         as select * from emp;

Force:

When force refresh occurs in the materialized view, will do a fast refresh

 if it possible (only when the data is changed) otherwise performs a complete refresh.

 Force refresh is the default.

 

Eg:

Create materialized view emp_mat_view

refresh force

         as select * from emp;

 

 

Operations:

 

There are two operations available to refresh the materialized view.

 

• Commit
• Demand

 

Commit:

          The materialized view will be automatically refreshed or updated or reflected

          the data once the commit is done.

 

Eg:

Create materialized view emp_mat_view

refresh fast on commit

         as select * from emp;

 

 

Demand:

          The materialized view will be refreshed manually by calling

          the following procedures which is listed below.

 

Eg:

Create materialized view emp_mat_view

refresh fast on demand

         as select * from emp;

 

 

DBMS_MVIEW.REFRESH: For specific materialized views.

 

DBMS_MVIEW.REFRESH_DEPENDENT: For those materialized view dependent on a table.

 

DBMS_MVIEW.REFRESH_ALL_MVIEWS: For all materialized views

 

Timing the Refresh:

 

          The start clause informs the database when to refresh the materialized view.

          The next clause specifies the interval when to refresh the materialized view again.

 

Eg:

     Create materialized view emp_mat_view

     refresh fast

     start with sysdate

     next sysdate + 2

     as select * from emp;

     In the above example materialized view will get refreshed once in two days.

     If it gets refreshed on today then the next refresh will occur after 2 days.

 

With Primary Key/Rowid:

 

          Materialized view cannot be created until and unless the table contains the primary key.

          If you need to create the materialized view on the tables which contains no primary key

          you need to specify the keyword with rowid in the create statement. Primary key is the default.

 

Eg:

 

With Primary key [optional]

Create materialized view emp_mat_view

with primary key

as select * from emp;

OR

Create materialized view emp_mat_view

as select * from emp;

With Rowid

Create materialized view emp_mat_view refresh

with rowid

as select * from emp;

 

Benefits of Materialized views

 

• Less physical reads
• Decrease the CPU consumption
• Faster response time
• Indexes can be created

Eg:

 

Create index mat_view_index

on emp_mat_view (empno);