Materialized view Basic
Materialized views in Oracle
Oracle materialized views were first
introduced in Oracle8.
Materialized views are schema objects that can
be used to summarize, precompute, replicate and distribute data.
In
mview, the query result is cached as a concrete table that may be updated from
the original base tables from time to time. This enables much more efficient
access, at the cost of some data being potentially out-of-date. It is most
useful in datawarehousing scenarios, where frequent queries of the actual base
tables can be extremely expensive.
Oracle uses materialized views (also
known as snapshots in prior releases) to replicate data to non-master
sites in a replication environment and to cache expensive queries in a
datawarehouse environment.
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.
A
materialized view is a replica of a target master from a single point in time.
We can define a materialized view on a base/master table (at a master site), partitioned
table, view, synonym or a master materialized view (at a materialized view
site). Whereas in multi master replication tables are continuously updated by
other master sites, materialized views are updated from one or more masters
through individual batch updates, known as a refreshes, from a single master
site or master materialized view site.
A
materialized view provides indirect access to table data by storing the results
of a query in a separate schema object. Unlike an ordinary view, which does not
take up any storage space or contain any data, Mview stores data, whereas view
stores only query.
The existence of a materialized view
is transparent to SQL, but when used for query rewrites will improve the
performance of SQL execution. An updatable materialized view lets you insert,
update, and delete.
We
can define indexes on a materialized view.
A
materialized view can be stored in the same database as it's base table(s) or in
a different database. Materialized views stored in the same database as their
base tables can improve query performance through query rewrites. Query rewrites
are particularly useful in a datawarehouse environment.
A materialized view can
query tables, views and other materialized views. Collectively these are called
master tables (a replication term) or detail tables (a datawarehouse
term).
For replication purposes,
materialized views allow us to maintain copies of remote data on local node.
These copies are read-only. If we want to update the local copies, we have to
use the Advanced Replication feature. We can select data from a materialized
view as we would from a table or view.
For datawarehousing purposes, mviews
commonly created are aggregate views, single-table aggregate views and join
views.
In
replication environments, mviews commonly created are primary key, rowid and subquery materialized
views.
Whenever you create a
materialized view, regardless of it's type, always specify the schema name of
the table owner in the query for the materialized view.
Prerequisites:
To create mviews, the
user should have any one of
CREATE MATERIALIZED VIEW
or CREATE ANY MATERIALIZED VIEW privileges.
SQL> GRANT CREATE
MATERIALIZED VIEW TO user-name;
And
SQL> GRANT QUERY
REWRITE TO user-name;
And following init
parameters should be set
query_rewrite_enabled =
true (default)
query_rewrite_integrity =
enforced|trusted|stale_tolerated
The background
processes responsible for these materialized view refreshes are the
coordinated job queue (CJQ) processes.
job_queue_processes=n
Syntax:
CREATE MATERIALIZED VIEW
mview-name
[partitioning-options]
[storage-parameters]
[TABLESPACE
tablespace-name]
[OF
object-type]
[FOR UPDATE]
[BUILD
IMMEDIATE|BUILD DEFFERED|ON PREBUILT TABLE]
[REFRESH
[FAST|COMPLETE|FORCE|NEVER]
[ON
DEMAND|COMMIT]
[START
WITH date]
[NEXT
date]
[WITH
PRIMARY KEY|ROWID]]
[DISABLE|ENABLE QUERY
REWRITE]
AS
select-query;
Refresh
Types
Oracle can refresh a materialized
view using either a fast, complete or force refresh.
The refresh option
specifies:
a. Refresh method used by Oracle to refresh data in
materialized view. FORCE is the default option.
b. Whether the view is primary key based or row-id based.
PRIMARY KEY is the default option.
c. Time and interval at which the view is to be
refreshed.
Complete
Refresh
To perform COMPLETE
refresh of a materialized view, the server that manages the materialized view
executes the materialized view's defining query, which essentially recreates the
materialized view. To refresh the materialized view, the result set of the query
replaces the existing materialized view data. Oracle can perform a complete
refresh for any materialized view. Depending on the amount of data that
satisfies the defining query, a complete refresh can take a substantially longer
amount of time to perform than a fast refresh.
Note: If a materialized view is
complete refreshed, then set it's PCTFREE to 0 and PCTUSED to 99 for maximum
efficiency.
The complete refresh re-creates the
entire materialized view. If we request a complete refresh, Oracle performs a
complete refresh even if a fast refresh is possible.
SQL> CREATE
MATERIALIZED VIEW mv_emp
REFRESH COMPLETE
START WITH SYSDATE NEXT SYSDATE + 1
WITH PRIMARY KEY
AS SELECT * FROM
emp@remote_db;
To refresh this mview,
SQL> EXEC
DBMS_MVIEW.REFRESH('mv_emp', 'C');
From Oracle
10g, complete refresh of single materialized view can do delete instead of
truncate. To force the refresh to do truncate instead of delete, parameter
ATOMIC_REFRESH must be set to false.
ATOMIC_REFRESH = FALSE,
mview will be truncated and whole data will be inserted. The refresh will go
faster, and no undo will be generated.
ATOMIC_REFRESH = TRUE
(default), mview will be deleted and whole data will be inserted. Undo will be
generated. We will have access at all times even while it is being
refreshed.
SQL> EXEC
DBMS_MVIEW.REFRESH('mv_emp', 'C', atomic_refresh=>FALSE);
If
we perform complete refresh of a master materialized view, then the next refresh
performed on any materialized views based on this master materialized view must
be a complete refresh. If a fast refresh is attempted for such a materialized
view after it's master materialized view has performed a complete refresh, then
Oracle returns the following error:
ORA-12034 mview log is younger
than last refresh
Fast
Refresh
To
perform FAST refresh, the master that manages the materialized view first
identifies the changes that occurred in the master since the most recent refresh
of the materialized view and then applies these changes to the materialized
view. Fast refreshes are more efficient than complete refreshes when there are
few changes to the master because the participating server and network replicate
a smaller amount of data.
We
can perform fast refreshes of materialized views only when the master table or
master materialized view has a materialized view log. Also, for fast refreshes
to be faster than complete refreshes, each join column in the CREATE
MATERIALIZED VIEW statement must have an index on it.
SQL> CREATE
MATERIALIZED VIEW mv_emp
BUILD IMMEDIATE
REFRESH
FAST
START WITH SYSDATE NEXT SYSDATE + 2
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS SELECT * FROM emp@remote_db;
A materialized view log
is a schema object that records changes to a master table's data so that a
materialized view defined on the master table can be refreshed
incrementally.
We
should create a materialized view log for the master tables if we specify the
REFRESH FAST clause.
SQL> CREATE MATERIALIZED VIEW LOG
ON emp;
To refresh this
mview,
SQL> EXEC
DBMS_MVIEW.REFRESH('mv_emp', 'F');
After a direct path load
on a master table or master materialized view using SQL*Loader, a fast refresh
does not apply the changes that occurred during the direct path load. Also, fast
refresh does not apply changes that result from other types of bulk load
operations on masters. Examples of these operations include some INSERT
statements with an APPEND hint and some INSERT ... SELECT * FROM
statements.
Note:
->> Fast refreshable
materialized views can be created based on master tables and master materialized
views only.
->> Materialized views based
on a synonym or a view must be complete refreshed.
->> Materialized
views are not eligible for fast refresh if the defined subquery contains an
analytic function.
Force
Refresh
To
perform FORCE refresh of a materialized view, the server that manages the
materialized view attempts to perform a fast refresh. If fast refresh is not
possible, then Oracle performs complete refresh. Use the force setting when you
want a materialized view to refresh if fast refresh is not
possible.
If
you do not specify a refresh method, FORCE is the default.
SQL> CREATE
MATERIALIZED VIEW mv_emp
REFRESH FORCE
START WITH SYSDATE NEXT SYSDATE + 3
WITH PRIMARY KEY
DISABLE
QUERY REWRITE
AS SELECT * FROM emp@remote_db;
To refresh this
mview,
SQL> EXEC
DBMS_MVIEW.REFRESH(LIST =>'mv_emp', METHOD =>'?');
(or)
SQL> EXEC
DBMS_MVIEW.REFRESH(LIST =>'mv_emp');
Partition Change
Tracking (PCT) Refresh
In Oracle9i,
a new mechanism called Partition Change Tracking (PCT) has been introduced. This
mechanism keeps track of the base table partitions
that have been updated since the materialized view was last refreshed. It tracks
partition maintenance operations (PMOPs), like add and truncate partition as
well as DML changes to the base data, on partition basis. This allows Oracle to
identify fresh data in the materialized view.
Partition Change Tracking
(PCT) refresh refers to MV refresh using only the changed partitions of the base
tables of an MV. This refresh method is possible only if the base tables are
partitioned and changes to base tables are tracked on a partition
basis.
From Oracle 9i, it was
supporting, range
and range-hash partitioning.
From Oracle 10g, it was
supporting, list
partitioning also.
Enhanced Partition Change
Tracking (EPCT) refresh refers to PCT based refresh applied to MVs containing
columns that are partition-join dependent on the partitioning column of the base
table.
Timing the
refresh
The START WITH clause tells the
database when to perform the first replication from the master table to the
local base table. It should evaluate to a future point in time. The NEXT clause
specifies the interval between refreshes
SQL> CREATE
MATERIALIZED VIEW mv_emp_pk
REFRESH FAST
START WITH SYSDATE NEXT SYSDATE + 2
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;
In
the above example, the first copy of the materialized view is made at SYSDATE
(immediately) and the interval at which the refresh has to be performed is every
two days.
SQL> CREATE
MATERIALIZED VIEW mv_emp_pk
REFRESH COMPLETE
START WITH SYSDATE NEXT SYSDATE +
2/(24*60)
WITH ROWID
AS SELECT * FROM emp@remote_db;
In this example, the
interval is two minutes. For every two minutes, fast refresh will
happen.
SQL> CREATE
MATERIALIZED VIEW mv_emp_pk
REFRESH FORCE
START WITH SYSDATE NEXT SYSDATE +
30/(24*60*60)
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;
In this example, the
interval is 30 seconds.
SQL> CREATE
MATERIALIZED VIEW mv_emp_f
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;
SQL> CREATE
MATERIALIZED VIEW mv_emp_c
REFRESH COMPLETE ON DEMAND
WITH ROWID
AS SELECT * FROM emp@remote_db;
SQL> EXECUTE
DBMS_MVIEW.REFRESH('mv_emp_c','C');
PRIMARY KEY and ROWID
Clause
WITH PRIMARY KEY is used to create a
primary key materialized view i.e. the materialized view is based on the primary
key of the master table instead of ROWID. PRIMARY KEY is the default option. To
use the PRIMARY KEY clause we should have defined PRIMARY KEY on the master
table or else you should use ROWID based materialized views.
How to know
when was the last refresh happened on materialized views:
SQL> select
MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from
dba_mviews;
(or)
SQL> select
MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from
dba_mview_analysis;
(or)
SQL> select NAME,
to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS')
from
dba_mview_refresh_times;
Read-Only,
Updatable and Writeable Materialized Views
A
materialized view can be either read-only, updatable or writeable. Users cannot
perform data manipulation language (DML) statements on read-only materialized
views, but they can perform DML on updatable and writeable materialized
views.
1.
Read only
- Cannot be updated and complex materialized views are supported.
2.
Updateable
- Can be updated even when disconnected from the master site.
- Are refreshed on demand.
- Consumes fewer resources.
- Requires Advanced Replication option to be installed.
3.
Writeable
- Created with the for update clause.
- Changes are lost when view is refreshed.
- Requires Advanced Replication option to be installed.
Note: For read-only, updatable, and
writeable materialized views, the defining query of the materialized view must
reference all of the primary key columns in the master.
Read-Only
Materialized Views
We
can make a materialized view read-only during creation by omitting the FOR
UPDATE clause or disabling the equivalent option in the Replication Management
tool. Read-only materialized views use many of the same mechanisms as updatable
materialized views, except that they do not need to belong to a materialized
view group.
In
addition, using read-only materialized views eliminates the possibility of a
materialized view introducing data conflicts at the master site or master
materialized view site, although this convenience means that updates cannot be
made at the remote materialized view site.
The following is an example of a
read-only materialized view:
SQL> CREATE
MATERIALIZED VIEW hr.emp
AS
SELECT * FROM hr.emp@orcl;
Updatable
Materialized Views
We
can make a materialized view updatable during creation by including the FOR
UPDATE clause or enabling the equivalent option in the Replication Management
tool. For changes made to an updatable materialized view to be pushed back to
the master during refresh, the updatable materialized view must belong to a
materialized view group.
Updatable materialized views enable
us to decrease the load on master sites because users can make changes to the
data at the materialized view site.
The following is an example of an
updatable materialized view:
SQL> CREATE MATERIALIZED VIEW
hr.dept
FOR UPDATE
AS
SELECT * FROM hr.departments@orcl;
The following statement creates a
materialized view group:
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPGROUP
(
gname
=> 'hr_repg',
master
=> 'orcl',
propagation_mode
=> 'ASYNCHRONOUS');
END;
/
The following statement
adds the hr.departments materialized view to the materialized view group, making
the materialized view updatable:
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT
(
gname
=> 'hr_repg',
sname
=> 'hr',
oname
=> 'departments',
type
=> 'SNAPSHOT',
min_communication
=> TRUE);
END;
/
We
can also use the Replication Management tool to create a materialized view group
and add a materialized view to it.
Note:
- Do not use column aliases when we are creating an updatable materialized view. Column aliases cause an error when we attempt to add the materialized view to a materialized view group using the CREATE_MVIEW_REPOBJECT procedure.
- An updatable materialized view based on a master table or master materialized view that has defined column default values does not automatically use the master's default values.
- Updatable materialized views do not support the DELETE CASCADE constraint.
The following types of materialized
views cannot be masters for updatable materialized views:
- ROWID materialized views
- Complex materialized views
- Read-only materialized views
However, these types of materialized
views can be masters for read-only materialized views.
Additional Restrictions for
Updatable Materialized Views Based on Materialized Views, those
must:
- Belong to a materialized view group that has the same name as the materialized view group at it's master materialized view site.
- Reside in a different database than the materialized view group at it's master materialized view site.
- Be based on another updatable materialized view or other updatable materialized views, not on a read-only materialized view.
- Be based on a materialized view in a materialized view group that is owned by PUBLIC at the master materialized view site.
Writeable Materialized Views
A writeable materialized view is
one that is created using the FOR UPDATE clause but is not part of a
materialized view group. Users can perform DML operations on a writeable
materialized view, but if we refresh the materialized view, then these changes
are not pushed back to the master and the changes are lost in the materialized
view itself. Writeable materialized views are typically allowed wherever
fast-refreshable read-only materialized views are allowed.
Note: writeable materialized views
are rarely used.
Uses of
Materialized Views
We
can use materialized views to achieve one or more of the following
goals:
- Less network loads
- Create mass deployment environment
- Enable data subsetting
- Enable disconnected computing
Less Network Loads
We
can use materialized views to reduce network loads & to distribute your
corporate database to regional sites. Instead of the entire company accessing a
single database server, user load is distributed across multiple database
servers. Through the use of multitier materialized views, we can create
materialized views based on other materialized views, which enables us to
distribute user load to an even greater extent because clients can access
materialized view sites instead of master sites. To decrease the amount of data
that is replicated, a materialized view can be a subset of a master table or
master materialized view.
While multimaster replication also
distributes a database among multiple sites, the networking requirements for
multimaster replication are greater than those for replicating with materialized
views because of the transaction by transaction nature of multimaster
replication. Further, the ability of multimaster replication to provide
real-time or near real-time replication may result in greater network traffic,
and might require a dedicated network link.
Materialized views are updated
through an efficient batch process from a single master site or master
materialized view site. They have lower network requirements and dependencies
than multimaster replication because of the point in time nature of materialized
view replication. Whereas multimaster replication requires constant
communication over the network, materialized view replication requires only
periodic refreshes.
In
addition to not requiring a dedicated network connection, replicating data with
materialized views increases data availability by providing local access to the
target data. These benefits, combined with mass deployment and data subsetting
(both of which also reduce network loads), greatly enhance the performance and
reliability of your replicated database.
Create mass deployment
environment
Deployment templates allow us to
precreate a materialized view environment locally. We can then use deployment
templates to quickly and easily deploy materialized view environments to support
sales force automation and other mass deployment environments. Parameters allow
us to create custom data sets for individual users without changing the
deployment template. This technology enables you to roll out a database
infrastructure to hundreds or thousands of users.
Enable data
subsetting
Materialized views allow us to
replicate data based on column and row-level subsetting, while multimaster
replication requires replication of the entire table. Data subsetting enables us
to replicate information that pertains only to a particular site. For example,
if we have a regional sales office, then we might replicate only the data that
is needed in that region, thereby cutting down on unnecessary network
traffic.
Enable disconnected
computing
Materialized views do not require a
dedicated network connection. Though we have the option of automating the
refresh process by scheduling a job, we can manually refresh your materialized
view on-demand, which is an ideal solution for sales applications running on a
laptop. For example, a developer can integrate the replication management API
for refresh on-demand into the sales application. When the salesperson has
completed the day's orders, the salesperson simply dials up the network and uses
the integrated mechanism to refresh the database, thus transferring the orders
to the main office.
Note:
- Both the master site and the materialized view site must have compatibility level (COMPATIBLE initialization parameter) 9.0.1 or higher to replicate user-defined types and any objects on which they are based.
- We cannot create refresh-on-commit materialized views based on a master with user-defined types. Refresh-on-commit materialized views are those created using the ON COMMIT REFRESH clause in the CREATE MATERIALIZED VIEW statement.
- Advanced Replication does not support type inheritance.
Materialized View
Groups
A
materialized view group in a replication system maintains a partial or complete
copy of the objects at the target replication group at it's master site or
master materialized view site. Materialized view groups cannot span the
boundaries of the replication group at the master site or master materialized
view site.
Group A at the materialized view
site contains only some of the objects in the corresponding Group A at the
master site. Group B at the materialized view site contains all objects in Group
B at the master site. Under no circumstances, however, could Group B at the
materialized view site contain objects from Group A at the master site. A
materialized view group has the same name as the master group on which the
materialized view group is based. For example, a materialized view group based
on a personnel master group is also named personnel.
In
addition to maintaining organizational consistency between materialized view
sites and their master sites or master materialized view sites, materialized
view groups are required for supporting updatable materialized views. If a
materialized view does not belong to a materialized view group, then it must be
a read-only or writeable materialized view.
Managing MVs is much
easier in Oracle
10g with the introduction
of the powerful new tuning advisors that can tell us a lot about the design of
the MVs. Tuning recommendations that can generate a complete script that can be
implemented quickly, saving significant time and effort. The ability to force
rewriting or abort the query can be very helpful in decision-support systems
where resources must be conserved, and where a query that is not rewritten
should not be allowed to run amuck inside the database.
Related
Views
DBA_MVIEWS
DBA_MVIEW_LOGS
DBA_MVIEW_KEYS
DBA_REGISTERED_MVIEWS
DBA_REGISTERED_MVIEW_GROUPS
DBA_MVIEW_REFRESH_TIMES
DBA_MVIEW_ANALYSIS
Related
Package/Procedures
DBMS_MVIEW
package
- REFRESH
- REFRESH_ALL
- REFRESH_ALL_MVIEWS
- REFRESH_DEPENDENT
- REGISTER_MVIEW
- UNREGISTER_MVIEW
- PURGE_LOG
DBMS_REPCAT
package
DBMS_REFRESH
package
Comments
Post a Comment