Materialized view concept: Why do we need materialized view?

Materialized views are nothing but views created on the base table and having data which is extracted from the base table.
How is materialized view different from the normal view.

Difference # 1:

Normal view does not contain data. It is just a transparent layer on the top of base

Materialized view contains data and additional space is required to create materialized view.
Difference # 2:

To use normal view, a user needs to provide the view name in the query

To use materialized view user does not need to provide materialized view name (although a user can, but its not required.)

Materialized views are required mainly for 2 reasons

1) Advanced replication of data from one location (database) to another location (database)

2) Summarizing the data in the table

When we are replicating the table from remote location to local location our queries can access the same data from local location which can lead to improved query performance.
Replication of data is possible using materialized views.

 

You can use materialized views to increase the speed of queries on very large databases. Queries to large databases often involve joins between tables, aggregations such as SUM, or both. These operations are expensive in terms of time and processing power. The type of materialized view you create determines how the materialized view is refreshed and used by query rewrite.

 

Materialized views improve query performance by precalculating expensive join and aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries go directly to the materialized view and not to the underlying detail tables. In general, rewriting queries to use materialized views rather than detail tables improves response time.

Based on the above definitions we have 3 situations where materialized views can be used:

1) Materialized Views for Data Warehouses

In data warehouses, you can use materialized views to precompute and store aggregated data such as the sum of sales. Materialized views in these environments are often referred to as summaries, because they store summarized data. They can also be used to precompute joins with or without aggregations. A materialized view eliminates the overhead associated with expensive joins and aggregations for a large or important class of queries.

2) Materialized Views for Distributed Computing

In distributed environments, you can use materialized views to replicate data at distributed sites and to synchronize updates done at those sites with conflict resolution methods. These replica materialized views provide local access to data that otherwise would have to be accessed from remote sites. Materialized views are also useful in remote data marts.

3) Materialized Views for Mobile Computing

You can also use materialized views to download a subset of data from central servers to mobile clients, with periodic refreshes and updates between clients and the central servers.

Its difficult to understand everything before we create a materialized view. So lets create a MView and understand every clause that can be given in creating MVIew.

Creating Materialized View:

Here goes the syntax of creating materialized view

CREATE MATERIALIZED VIEW SCHEMA.NAME 
(COL1, COL2, COL3 … )
ON PREBUILT TABLE / BUILD IMMEDIATE / BUILD DEFERRED
WITH / WITHOUT REDUCED PRECISION
USING INDEX / USING NO INDEX
TABLESPACE <TS NAME>
FOR UPDATE
REFRESH FAST / COMPLETE /  FORCE / ON DEMAND / ON COMMIT / START WITH .. NEXT / 
REFRESH WITH PRIMARY KEY / ROWID
ENABLE / DISABLE QUERY REWRITE
AS <SELECT QUERY ON MASTER TABLE>

This syntax is no way complete and there are many more clause related to storage, constraints, physical properties etc. But this is a basic DDL statement that is used most of the time to create materialized view.
If you want to see complete details of creating MView with all clauses, check reference section to get documentation link.

Lets discuss about each clause and what these values means to us.

ON PREBUILT TABLE

When you create a materialized view you can create the same on pre-built table. For example, before creating MView you were managing data replication using software and you were keeping a local table updated with latest data from some other master site. Now when you are implementing MView its a good option to use same table as prebuilt table and create MView on top of that. This way you can stop software replication and start using Mview.

If you don’t have any prebuilt table you can skip that clause along with “WITH REDUCED PRECISION” clause. If you are not using “ON PREBUILT TABLE” clause than you can use with “BUILD IMMEDIATE” or “BUILD DEFERRED” clause.
BUILD IMMEDIATE will immediately fetch the data info Mview. BUILD DEFERRED clause will defer fetching of data unless you do complete refresh manually.

Following things to be considered in case of PREBUILT Table

• Materialized view name should be same as prebuilt table name. Master table can have different name
• PREBUILT table can have extra column or less columns compared to master table, but Mview definition should include only those columns which are present in both master table and prebuilt table and corresponding columns must have matching datatypes.
• If you don’t have any prebuilt table, create materialized view statement will create a table but it won’t be a prebuilt table. Meaning that if you drop Mview, your table will also be dropped (unless you use “preserve table” clause). But if its a prebuilt table, table will not be dropped even if you drop MView. This way you can retain data. So its essential to have a table marked as prebuilt table.

If you created materialized view without prebuilt table, it will create a table and there is a way to change that table as prebuilt table. So that if you drop materialized view some time in future, table will stay. Following update statement will convert a table into prebuilt table

update sys.snap$ set flag = 2228321 where vname = '<Mview Name>';
commit;

Above statement will convert a table into prebuilt table.

After that if you run the statement “drop materialized view <MView Name>;” it won’t drop prebuilt table.

Alternatively if you don’t have prebuilt table and you want to drop snapshot preserving the table you can use following statement

drop materialized view <MView Name> preserve table;

WITH / WITHOUT REDUCED PRECISION

Specify WITH REDUCED PRECISION to authorize the loss of precision that will result if the precision of columns in materialized view does not match with precision of column result returned by subquery. Remember that subquery is build on table on master site.
So this clause makes sense if you are using different precision of columns on Mview site than on master site.
WITHOUT REDUCED PRECISION will require the precision of columns in Mview to be exactly same as that of columns in table on master site (precision retuned by subquery).

USING INDEX

Using index clause can be used to specify the storage clause for index that will be created on materialized view. You can also specify the tablespace that should be used for all indexes on materialized view.
If you are creating ROWID based materialized view then “USING INDEX” clause will create default index like I_SNAP$_<table_name> which will be useful in internal MView management.
Using “NO INDEX VLAUSE” will prevent creating this default index and also you cannot specify storage clause and tablespace clause.

TABLESPACE <TS NAME>

This specifies the tablespace in which to create MVIEW.

ENABLE / DISABLE QUERY REWRITE

This clause will specify whether to enable or disable query rewrite on this MView.
One of the advantages of having materialized view is that our queries will automatically start using it (just like index)without doing any changes to the SQL. This capability is provided by query rewrite functionality.
The optimizer uses two different methods to recognize when to rewrite a query in terms of a materialized view. The first method is based on matching the SQL text of the query with the SQL text of the materialized view definition. If the first method fails, the optimizer uses the more general method in which it compares joins, selections, data columns, grouping columns, and aggregate functions between the query and materialized views.

You can find more details about query rewrite feature in link http://docs.oracle.com/cd/B19306_01/server.102/b14223/qrbasic.htm

REFRESH FAST / COMPLETE /  FORCE / ON DEMAND / ON COMMIT / START WITH .. NEXT /

Fast refresh can be based on primary key or it could be based on ROWID.
If fast refresh if based on primary key then, master table should have primary key defined. MView log will have primary key included. For fast refresh to work, you must have MLOG created on master table on master site using following commands

create materialized view log on <Master table name>;

Fast refresh is most efficient way of maintaining data as it brings only the changed data from master site to local MView. It maintains the changes in MLOG.
Not all Mviews can be fast refreshed. You can check the restrictions on creating fast refreshable materialized view at http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1007028

Complete refresh does not need any MLOG. When we do complete refresh, complete data of master table is brought into MView. This method can be used for small tables.

Refresh Force will first try to refresh the Mview using fast method and if it doesn’t work, it will go for complete refresh.

Refresh On Demand will need DBAs to schedule a job which will do the refresh of Mview on periodic basis. If you don’t want to setup a job, you have to decide the schedule and refresh Mview yourself.

Refresh On commit will refresh Mview if a transaction gets committed on master site. We have to be careful with this option, if master site is located far away and commit rate is high, this is not a good option. Everytime a commit happens, it will try to refresh Mview, which is going to take time and eventually your transactions on master site will slow down.

Refresh Start with .. Next is used when we want to define the schedule in Mview definition while creating MView itself. This way Mview gets refreshed as per defined schedule

I tried to give basic overview of Materialized view and creating materialized view. Below are some examples of creating materialized view using above clause

 

Examples of Mview Creation

Example 1:

create materialized view T_REP
 ( OWNER,
   SUBOBJECT_NAME ,
   OBJECT_ID,
   DATA_OBJECT_ID ,
   OBJECT_TYPE,
   CREATED,
   LAST_DDL_TIME,
   TIMESTAMP,
   STATUS ,
   TEMPORARY,
   GENERATED,
   SECONDARY,
   NAMESPACE,
   EDITION_NAME)
 BUILD IMMEDIATE
 REFRESH ON DEMAND
 as 
 select OWNER,
        SUBOBJECT_NAME ,
        OBJECT_ID,
        DATA_OBJECT_ID ,
        OBJECT_TYPE,
        CREATED,
        LAST_DDL_TIME,
        TIMESTAMP,
        STATUS ,
        TEMPORARY,
        GENERATED,
        SECONDARY,
        NAMESPACE,
        EDITION_NAME
 from t@DB1;

Example 2:

Assuming we have primary key on master table and materialized view log on master site

create materialized view T_REP
 ( OWNER,
   SUBOBJECT_NAME ,
   OBJECT_ID,
   DATA_OBJECT_ID ,
   OBJECT_TYPE,
   CREATED,
   LAST_DDL_TIME,
   TIMESTAMP,
   STATUS ,
   TEMPORARY,
   GENERATED,
   SECONDARY,
   NAMESPACE,
   EDITION_NAME)
 ON PREBUILT TABLE
 WITH REDUCED PRECISION
 USING INDEX TABLESPACE OPS_IDX
 REFRESH FAST ON DEMAND
 ENABLE QUERY REWRITE
 as 
 select OWNER,
        SUBOBJECT_NAME ,
        OBJECT_ID,
        DATA_OBJECT_ID ,
        OBJECT_TYPE,
        CREATED,
        LAST_DDL_TIME,
        TIMESTAMP,
        STATUS ,
        TEMPORARY,
        GENERATED,
        SECONDARY,
        NAMESPACE,
        EDITION_NAME
 from t@DB1;

I hope this clears few things. This article is no way a replacement of Oracle documentation. I just tried to provide a concise information on creating materialized views.
My reference has links to Oracle Docs which provides detailed information.

In my next article of materialized view, I am going to provide some backend DBA tables which can be used to view information about materialized views.

Hope this helps !!

References:

Basic Mview creation – http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm
Basics of Query rewrite – http://docs.oracle.com/cd/B19306_01/server.102/b14223/qrbasic.htm
Oracle By Example. Creating Mview – http://st-curriculum.oracle.com/obe/db/10g/r2/prod/bidw/mv/mv_otn.htm