Oracle view
The materialization view is a database object including a query result, which is a local copy of remote data, or a summary table for generating a data-based summary. The materialized view stores data based on the remote table, or it can be called snapshot.
The materialization view can look up the table, view, and other materialization views.
Typically, the materialized view is called a primary table (during replication) or a clear table (in the data warehouse).
For replication, the materialization view allows you to maintain a copy of the remote data locally, which is read-only. If you want to modify local copies, you must use advanced copies. When you want to extract data from a table or view, you can use it from the materialized view.
For data warehouses, the productive view created is usually aggregated view, a single table aggregation view, and connection view.
This article we will see how to create a materialized view and discuss its refresh options.
In the replication environment, the materialized view created is usually the primary key, RowID, and subquery view.
1. Principal Key View:
The following syntax creates a primary keyline view on the remote database table EMP
SQL> CREATE MATERIALIZED VIEW MV_EMP_PK
Refresh Fast Start with sysdate
Next sysdate 1/48
WITH PRIMARY Key
AS SELECT * from EMP @ remote_db;
Materialized view created.
Note: Create a materialized view with the FAST option, you must create a home-based view log, as follows:
SQL> CREATE MATERIALIZED View LOG ON EMP;
Materialized view log create.
2.RowID material view
The following syntax creates a RowID primrization view on the remote database table EMP
SQL> CREATE MATERIALIZED View MV_EMP_ROWID
Refresh with rowid
AS SELECT * from EMP @ remote_db;
Materialized view log create.
3. Subproof material view
The following syntax is created on the remote database table EMP.
SQL> CREATE MATERIALIZED View MV_EMPDEPT
As SELECT * from EMP @ remote_db e
WHERE EXISTS
(SELECT * from dept @ remote_db d
WHERE E.DEPT_NO = D.DEPT_NO)
Materialized view log create.
Refresh clause
[refresh [fast | completion | force]
[On Demand | Commit]
[start with date] [Next date]
[with {primary key | rowid}]]]]
Refresh option Description:
a. Oracle refreshed data in a materialized view with a refresh method.
b. Is the primary key or a ROWID-based materialized view
c. Refresh time and interval of materialization view
Refresh method - FAST clause
The incremental refresh is used to send the data line to the material line to the physicalized view of the primary table. If the Refresh Fast clause is specified, then you should create a physicalized view log for the primary table.
SQL> CREATE MATERIALIZED View LOG ON EMP;
Materialized view log create.
For incremental refresh options, if an analytical function is present in the subquery, the materialization view does not work.
Refresh method - Complete clause
Fully refresh Re-generate the entire view, if the request is completely refreshed, Oracle will complete the full refresh even if the increment is available. Refresh Method - Force clause
When the Force clause is specified, if the incremental refresh is available Oracle will complete the increment refresh, otherwise it will complete the full refresh, if you do not specify the refresh method (FAST, COMPLETE, or Force), the Force option is the default option.
Primary key and rowd clause
The WITH PRIMARY Key option generates a primary keying view, that is, the materialization view is based on the primary key of the primary table, not RowID (corresponding to the RowID clause). Primary key is the default option, in order to generate the primary key clause, should be on the main table Define the primary key, otherwise the ROWID-based material should be used.
The primary keyline view allows identification of the materialized view main table without affecting the availability of the materialized view increment.
The RowID material view only has a single primary table that cannot include any of the following:
n Distinct or aggregate function.
n group by, subquery, connection and set operation
refresh time
START WITH SMAN Notifies the database to complete the time from the primary table to the local table, and should estimate the time point of the next run in time, the Next clarification describes the interval of refresh.
SQL> CREATE MATERIALIZED VIEW MV_EMP_PK
Refresh Fast
START with sysdate
Next sysdate 2
WITH PRIMARY Key
AS SELECT * from EMP @ remote_db;
Materialized view created.
In the above example, the first copy of the materialized view data is generated when it is created, and it is refreshed every two days later.
to sum up
The materialization view provides a scalable primary key or RowID view that specifies the refresh method and the automatic refresh time.
