Materialized
View Types
Oracle
offers several types of materialized views
to meet the needs of many different replication (and non-replication)
situations.
- Primary Key
Materialized Views
- Subquery
Materialized Views
- ROWID
Materialized Views
- Object
Materialized Views
- Complex
Materialized Views
Primary
Key Materialized Views
Primary
key materialized views are the default type of materialized views in Oracle.
They are updatable if the materialized view was created as part of a
materialized view group and FOR UPDATE was specified when defining the
materialized view. An updatable materialized view must belong to a materialized
view group that has the same name as the replication group at its master site
or master materialized view site. In addition, an updatable materialized view
must reside in a different database than the master replication group.
The
following statement creates the primary key materialized view on the table emp
located on a remote database.
SQL>
CREATE MATERIALIZED VIEW mv_emp_pk
BUILD
DEFFERED
REFRESH
FAST
START
WITH SYSDATE NEXT SYSDATE + 1/48
WITH
PRIMARY KEY
AS
SELECT * FROM emp@remote_db;
Changes
are propagated according to the row-level changes that have occurred, as
identified by the primary key value of the row (not the ROWID).
The
following is an example of a SQL statement for creating an updatable, primary
key materialized view:
SQL>
CREATE MATERIALIZED VIEW offshore.customers
FOR
UPDATE
AS
SELECT * FROM onsite.customers@orcl;
Primary
key M-views allow materialized view master tables to be reorganized without
affecting the eligibility of the materialized view for fast refresh.
Subquery
Materialized Views
Materialized views may contain a subquery so that we can create a subset of rows at the remote materialized view site. A subquery is a query imbedded within the primary query, so that we have more than one SELECT statement in the CREATE MATERIALIZED VIEW statement. This subquery may be as simple as a basic WHERE clause or as complex as a multilevel WHERE EXISTS clause. Primary key materialized views that contain a selected class of subqueries can still be incrementally (or fast) refreshed, if each master referenced has a materialized view log. A fast refresh uses materialized view logs to update only the rows that have changed since the last refresh.
The
following statement creates a subquery materialized view based on the emp and
dept tables located on the remote database:
SQL>
CREATE MATERIALIZED VIEW mv_empdept
DISABLE
QUERY REWRITE
AS
SELECT * FROM emp@remote_db e
WHERE
EXISTS
(SELECT
* FROM dept@remote_db d WHERE e.dept_no = d.dept_no);
ROWID
Materialized Views
For
backward compatibility, Oracle supports ROWID materialized views in addition to
the default primary key materialized views. A ROWID materialized view is based
on the physical row identifiers (rowids) of the rows in a master. ROWID
materialized views should be used only for materialized views based on master
tables from an Oracle7 database, and should not be used from Oracle8 or higher.
The
following statement creates the rowid materialized view on table emp located on
a remote database:
SQL>
CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH
WITH ROWID
ENABLE
QUERY REWRITE
AS
SELECT * FROM emp@remote_db;
ROWID
materialized views should have a single master table and cannot contain any of
the following:
- Distinct or
aggregate functions
- GROUP BY
subqueries, joins & set operations
Object
Materialized Views
Object
materialized view is based on an object table and is created using the OF type
clause. An object materialized view is structured in the same way as an object
table. That is, an object materialized view is composed of row objects, and
each row object is identified by an object identifier (OID) column.
SQL>
CREATE MATERIALIZED VIEW customer OF cust_objtype
AS
SELECT CustNo FROM scott.Customer@orcl;
SQL>
CREATE MATERIALIZED VIEW LOG ON categories_tab WITH OBJECT ID;
An
object materialized view inherits the object identifier (OID) specifications of
its master. If the master has a primary key-based OID, then the OIDs of row
objects in the materialized view are primary key-based. If the master has a
system generated OID, then the OIDs of row objects in the materialized view are
system generated. Also, the OID of each row in the object materialized view
matches the OID of the same row in the master, and the OIDs are preserved
during refresh of the materialized view. Consequently, REFs to the rows in the
object table remain valid at the materialized view site.
Complex
Materialized Views
To
be fast refreshed, the defining query for a materialized
view must observe certain restrictions. If we require a materialized
view whose defining query is more general and cannot observe the restrictions,
then the materialized view is complex and cannot be fast refreshed.
A materialized view is considered complex when the defining query of the materialized view contains:
i) A CONNECT BY clause
For example, the following statement creates a complex materialized view:
SQL> CREATE MATERIALIZED VIEW hr.emp_hierarchy
AS SELECT LPAD(' ', 4*(LEVEL-1))||email USERNAME
FROM hr.employees@orcl START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
ii) An INTERSECT, MINUS, or UNION ALL set operation
For example, the following statement creates a complex materialized view because it has a UNION ALL set operation:
SQL> CREATE MATERIALIZED VIEW hr.mview_employees AS
SELECT employees.employee_id, employees.email
FROM hr.employees@orcl
UNION ALL
SELECT new_employees.employee_id, new_employees.email
FROM hr.new_employees@orcl;
iii) In some cases, the DISTINCT or UNIQUE keyword, although it is possible to have the DISTINCT or UNIQUE keyword in the defining query and still have a simple materialized view
For example, the following statement creates a complex materialized view:
SQL> CREATE MATERIALIZED VIEW hr.employee_depts
AS SELECT DISTINCT department_id FROM hr.employees@orcl
ORDER BY department_id;
iv) An aggregate function
For example, the following statement creates a complex materialized view:
SQL> CREATE MATERIALIZED VIEW hr.average_sal
AS SELECT AVG(salary) "Average" FROM hr.employees@orcl;
v) Joins other than those in a subquery
For example, the following statement creates a complex materialized view:
SQL> CREATE MATERIALIZED VIEW hr.emp_join_dep AS
SELECT last_name
FROM hr.employees@orc1.world e, hr.departments@orcl d
WHERE e.department_id = d.department_id;
vi) In some cases, a UNION operation. Specifically, a materialized view with a UNION operation is complex if any one of these conditions is true:
A materialized view is considered complex when the defining query of the materialized view contains:
i) A CONNECT BY clause
For example, the following statement creates a complex materialized view:
SQL> CREATE MATERIALIZED VIEW hr.emp_hierarchy
AS SELECT LPAD(' ', 4*(LEVEL-1))||email USERNAME
FROM hr.employees@orcl START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
ii) An INTERSECT, MINUS, or UNION ALL set operation
For example, the following statement creates a complex materialized view because it has a UNION ALL set operation:
SQL> CREATE MATERIALIZED VIEW hr.mview_employees AS
SELECT employees.employee_id, employees.email
FROM hr.employees@orcl
UNION ALL
SELECT new_employees.employee_id, new_employees.email
FROM hr.new_employees@orcl;
iii) In some cases, the DISTINCT or UNIQUE keyword, although it is possible to have the DISTINCT or UNIQUE keyword in the defining query and still have a simple materialized view
For example, the following statement creates a complex materialized view:
SQL> CREATE MATERIALIZED VIEW hr.employee_depts
AS SELECT DISTINCT department_id FROM hr.employees@orcl
ORDER BY department_id;
iv) An aggregate function
For example, the following statement creates a complex materialized view:
SQL> CREATE MATERIALIZED VIEW hr.average_sal
AS SELECT AVG(salary) "Average" FROM hr.employees@orcl;
v) Joins other than those in a subquery
For example, the following statement creates a complex materialized view:
SQL> CREATE MATERIALIZED VIEW hr.emp_join_dep AS
SELECT last_name
FROM hr.employees@orc1.world e, hr.departments@orcl d
WHERE e.department_id = d.department_id;
vi) In some cases, a UNION operation. Specifically, a materialized view with a UNION operation is complex if any one of these conditions is true:
o
Any query within the UNION is complex. The previous bullet items specify when a
query makes a materialized view complex.
o
The outermost SELECT list columns do not match for the queries in the UNION. In
the following example, the first query only has order_total in the outermost
SELECT list while the second query has customer_id in the outermost SELECT
list. Therefore, the materialized view is complex.
SQL>
CREATE MATERIALIZED VIEW oe.orders AS
SELECT
order_total FROM oe.orders@orcl o
WHERE
EXISTS (SELECT cust_first_name, cust_last_name
FROM
oe.customers@orcl c
WHERE
o.customer_id = c.customer_id AND c.credit_limit > 50)
UNION
SELECT
customer_id FROM oe.orders@orcl o
WHERE
EXISTS (SELECT cust_first_name, cust_last_name
FROM
oe.customers@orcl c
WHERE
o.customer_id = c.customer_id AND c.account_mgr_id = 30);
o
The innermost SELECT list has no bearing on whether a materialized view is
complex. In the previous example, the innermost SELECT list is cust_first_name
and cust_last_name for both queries in the UNION.
Note:
If possible, we should avoid using complex materialized views because they
cannot be fast refreshed, which may degrade network performance.
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.
To
refresh these type of mviews (from Oracle 10g),
SQL>
EXEC DBMS_MVIEW.REFRESH('mv_emp', 'P');
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.
Materialized Views Types
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 Log
Updatable Materialized View Log
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.
Refresh Groups
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
Materialized
View Log
A materialized view log is required on a master if we want to fast refresh materialized views based on the master. When we create a materialized view log for a master table or master materialized view, Oracle creates an underlying table as the materialized view log. A Mview log can hold the primary keys, rowids, or object ids of rows, or both, that have been updated in the master table or master materialized view. A materialized view log can also contain other columns to support fast refreshes of materialized views with subqueries.
CREATE MATERIALIZED VIEW LOG ON [schema.]table_name
[... storage options ...]
[... table_partitioning_clause ...]
[WITH {OBJECT | PRIMARY KEY | ROWID | SEQUENCE | column_list}]
[{INCLUDING | EXCLUDING} NEW VALUES];
SQL>
CREATE MATERIALIZED VIEW LOG ON emp;
The
name of a materialized view log's table is MLOG$_master_name. The materialized
view log is created in the same schema as the target master. One materialized
view log can support multiple materialized views on its master table or master
materialized view.
When
changes are made to the master table or master materialized view using DML, an
internal trigger records information about the affected rows in the
materialized view log. This information includes the values of the primary key,
rowid, or object id, or both, as well as the values of the other columns logged
in the materialized view log. This is an internal AFTER ROW trigger that is
automatically activated when we create a materialized view log for the target
master table or master materialized view. It inserts a row into the
materialized view log whenever an INSERT, UPDATE, or DELETE statement modifies
the table's data. This trigger is always the last trigger to fire.
SQL>
CREATE MATERIALIZED VIEW LOG ON emp WITH SEQUENCE, ROWID INCLUDING NEW VALUES;
Following
are the types of materialized view logs:
- Primary Key: The
materialized view records changes to the master table or master
materialized view based on the primary key of the affected rows.
- Row ID: The
materialized view records changes to the master table or master
materialized view based on the ROWID of the affected rows.
- Object ID: The
materialized view records changes to the master object table or master
object materialized view based on the object identifier (ID) of the
affected row objects.
- Combination: The
materialized view records changes to the master table or master
materialized view based any combination of the three options. It is
possible to record changes based on the primary key, the ROWID and the
object ID of the affected rows. Such a materialized view log supports
primary key, ROWID and object materialized views, which is helpful for
environments that have all three types of materialized views based on a
master.
A
combination materialized view log works in the same manner as a materialized
view log that tracks only one type of value, except that more than one type of
value is recorded. For example, a combination materialized view log can track
both the primary key and the rowid of the affected row are recorded.
Though
the difference between materialized view logs based on primary keys and rowids
is small (one records affected rows using the primary key, while the other
records affected rows using the physical rowid), the practical impact is large.
Using rowid materialized views and materialized view logs makes reorganizing
and truncating your master tables difficult because it prevents your ROWID
materialized views from being fast refreshed. If we reorganize or truncate your
master table, then your rowid materialized view must be COMPLETE refreshed
because the rowids of the master table have changed.
SQL>
CREATE MATERIALIZED VIEW LOG ON cust
TABLESPACE
mv_logs STORAGE(INITIAL 1M NEXT 1M) WITH ROWID;
Updatable
Materialized View Log
An
updatable materialized view log
(USLOG$_materialized_view_name), in Oracle database, is used to determine which
rows must be overwritten or removed from a materialized view during a fast
refresh. A read-only materialized view does not create this log, and Oracle
does not use this log during a complete refresh because, the entire
materialized view is replaced.
If
there is a conflict between an updatable M-view and a master, then, during a
refresh, the conflict may result in an entry in the updatable materialized view
log that is not in the materialized view log at the master site or master
materialized view site. In this case, Oracle uses the updatable materialized
view log to remove or overwrite the row in the materialized view.
The
updatable materialized view log is also used when we fast refresh a writeable
materialized view, as illustrated in the following scenario:
- A user inserts a
row into a writeable materialized view that has a remote master. Because
the materialized view is writeable and not updatable, the transaction is
not stored in the deferred transaction queue at the materialized view
site.
- Oracle logs
information about this insert in the updatable materialized view log.
- The user fast
refreshes the materialized view.
- Oracle uses the
information in the updatable materialized view log and deletes the
inserted row. A materialized view must be an exact copy of the master when
the fast refresh is complete. Therefore, Oracle must delete the inserted
row.
Internal
Trigger for the Updatable Materialized View Log
Like
the internal trigger at the master site or master materialized view site, an
internal trigger at the materialized view site records DML changes applied to
an updatable materialized view in the USLOG$_materialized_view_name log. A
read-only materialized view does not create this trigger.
Restriction
on import of MViews and MView Logs to different Schema
Materialized
views and materialized view logs are exported with the schema name explicitly
given in the DDL statements. Therefore, materialized views and materialized
view logs cannot be imported into a schema that is different than the schema
from which they were exported. If we attempt to use the FROM USER and TO USER
import options to import an export dumpfile that contains materialized views or
materialized view logs, then an error will be written to the import log file
and the items will not be imported.
M-Views Refresh Groups
A refresh
group can contain materialized
views from more than one
materialized view group to maintain transactional (read)
consistency across replication group boundaries.
To
preserve referential integrity and
transactional (read) consistency among multiple materialized views, Oracle has
the ability to refresh individual materialized views as part of a refresh
group. After refreshing all of the materialized views in a refresh group, the data
of all materialized views in the group correspond to the same transactionally
consistent point in time.
While
you may want to define a single
refresh group for each materialized view group, it may be more efficient to use
one large refresh group that contains objects from multiple materialized view
groups. Such a configuration
reduces the amount of overhead needed to refresh your materialized views. A
refresh group can contain up to 400 materialized views.
One
configuration that we want to avoid is using multiple refresh groups to refresh
the contents of a single materialized view group. Using multiple refresh groups
to refresh the contents of a single materialized view group may introduce
inconsistencies in the materialized view data, which may cause referential
integrity problems at the materialized view site. Only use this type of
configuration when we have in-depth knowledge of the database environment and
can prevent any referential integrity problems.
Refresh Group Size
There
are a few trade-offs to consider when you are deciding on the size of your
refresh groups. Oracle is optimized for large refresh groups. So, large refresh
groups refresh faster than an equal number of materialized views in small
refresh groups, assuming that the materialized views in the groups are similar.
For example, refreshing a refresh group with 100 materialized views is faster
than refreshing five refresh groups with 20 materialized views each. Also,
large refresh groups enable you to refresh a greater number of materialized
views with only one call to the replication management API.
During
the refresh of a refresh group, each materialized view in the group is locked
at the materialized view site for the amount of time required to refresh all of
the materialized views in the refresh group. This locking is required to
prevent users from updating the materialized views during the refresh
operation, because updates may make the data inconsistent. Therefore, having
smaller refresh groups means that the materialized views are locked for less
time when you perform a refresh.
Network
connectivity must be maintained while performing a refresh. If the connectivity
is lost or interrupted during the refresh, then all changes are rolled back so
that the database remains consistent. Therefore, in cases where the network
connectivity is difficult to maintain, consider using smaller refresh groups.
Advanced
Replication includes an optimization for null refresh. That is, if there were
no changes to the master tables
or master materialized views since the last refresh for a particular
materialized view, then almost no extra time is required for the materialized
view during materialized view group refresh. However, for materialized views in
a database prior to release 8.1, consider separating materialized views of master
tables that are not updated often into a separate refresh group of their own.
Doing so shortens the refresh time required for other materialized view groups
that contain materialized views of master tables that are updated frequently.
On-Demand Refresh
Scheduled
materialized view refreshes may not always be the appropriate solution for your
environment. For example, immediately following a bulk data load into a master
table, dependent materialized views no longer represent the master table's
data. Rather than wait for the next scheduled automatic group refreshes, you
can manually refresh dependent materialized view groups to immediately
propagate the new rows of the master table to associated materialized views.
You may also want to refresh your materialized
views on-demand when your materialized views are integrated with a sales force
automation system located on a disconnected laptop.
The
following example illustrates an on-demand refresh of the hr_refg refresh
group: SQL> EXECUTE DBMS_REFRESH.REFRESH('hr_refg');
No comments :
Post a Comment