A materialized view is a database object that contains the results of a query same like a table. They are local copies of data located on remote site. Also, used to create summary tables based on aggregations of a table's data, which store data based on remote tables are also, referred as Snapshots.
A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).
For replication purposes, materialized views allow you to maintain copies of remote data on your local server as read-only data.If we want to update the local copies of data, we have to use the Advanced Replication features in oracle. We can select data from a materialized view same as local table or view.
Materialized Views refer local data from Materialized View Logs (Snapshot Logs), which need to create on Local tables.
CREATE MATERIALIZED VIEW LOG ON emp;
Primary Key Materialized Views which are created with "WITH PRIMARY KEY" clause.
CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp@remote;
Rowid Materialized Views which are created with "ROWID" clause.
CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID
AS SELECT * FROM emp@remote;
Subquery Materialized Views which are created by using subquery.
CREATE MATERIALIZED VIEW mv_empdept
AS SELECT * FROM emp@remote e
WHERE EXISTS
(SELECT * FROM dept@remote d
WHERE e.deptid = d.deptid)
For data warehousing purposes, materialized views are the aggregate views, single-table aggregate views and join views.
CREATE MATERIALIZED VIEW mv_empdept_dwh
AS SELECT * FROM emp@remote a, dept@remote b
WHERE a.deptid = b.deptid
and a.deptid < 1000;
COMPLETE refreshes by recalculating the defining query of the materialized view.
FAST refreshes by incrementally applying changes to the materialized view as per materialized view logs. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient.
FORCE Attempts a FAST refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST and COMPLETE.
A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).
For replication purposes, materialized views allow you to maintain copies of remote data on your local server as read-only data.If we want to update the local copies of data, we have to use the Advanced Replication features in oracle. We can select data from a materialized view same as local table or view.
Materialized Views refer local data from Materialized View Logs (Snapshot Logs), which need to create on Local tables.
CREATE MATERIALIZED VIEW LOG ON emp;
Primary Key Materialized Views which are created with "WITH PRIMARY KEY" clause.
CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp@remote;
Rowid Materialized Views which are created with "ROWID" clause.
CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID
AS SELECT * FROM emp@remote;
Subquery Materialized Views which are created by using subquery.
CREATE MATERIALIZED VIEW mv_empdept
AS SELECT * FROM emp@remote e
WHERE EXISTS
(SELECT * FROM dept@remote d
WHERE e.deptid = d.deptid)
For data warehousing purposes, materialized views are the aggregate views, single-table aggregate views and join views.
CREATE MATERIALIZED VIEW mv_empdept_dwh
AS SELECT * FROM emp@remote a, dept@remote b
WHERE a.deptid = b.deptid
and a.deptid < 1000;
COMPLETE refreshes by recalculating the defining query of the materialized view.
FAST refreshes by incrementally applying changes to the materialized view as per materialized view logs. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient.
FORCE Attempts a FAST refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST and COMPLETE.
