stores aggregates of local tables in DWH or Remote copies.
Use of Materialized Views : This is relatively straightforward and is answered in a single word – performance.
Params:
COMPATIBLE should be set to 8.1.0 or above
QUERY_REWRITE_ENABLED – TRUE to enable. The default value is FALSE.
QUERY_REWRITE_INTEGRITY - ENFORCED – only when constraints and rules are enforced and guaranteed by Oracle. no chance of getting incorrect or stale data.| TRUSTED - even if some constraints are not enforced | STALE_TOLERATED - when data is out-of-sync with the details.
create materialized view mv
build immediate
refresh on commit
enable query rewrite
as select ...
When you create a materialized view using the FAST option you will need to create a view log on the master tables(s). A master table can have only one materialized view log defined on it.
create materialized view log on t WITH ROWID ; -- Columns : M_ROW$$, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$, CHANGE_VECTOR$$
REFRESH CLAUSE:
[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]
The FAST refreshes use the materialized view logs to send the rows that have changed from master tables to the materialized view.
The COMPLETE refresh re-creates the entire materialized view.
With FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. FORCE is the default.
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
WITH PRIMARY KEY 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 you should have defined
PRIMARY KEY on the master table or else you should use ROWID based materialized views.
Use of Materialized Views : This is relatively straightforward and is answered in a single word – performance.
Params:
COMPATIBLE should be set to 8.1.0 or above
QUERY_REWRITE_ENABLED – TRUE to enable. The default value is FALSE.
QUERY_REWRITE_INTEGRITY - ENFORCED – only when constraints and rules are enforced and guaranteed by Oracle. no chance of getting incorrect or stale data.| TRUSTED - even if some constraints are not enforced | STALE_TOLERATED - when data is out-of-sync with the details.
create materialized view mv
build immediate
refresh on commit
enable query rewrite
as select ...
When you create a materialized view using the FAST option you will need to create a view log on the master tables(s). A master table can have only one materialized view log defined on it.
create materialized view log on t WITH ROWID ; -- Columns : M_ROW$$, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$, CHANGE_VECTOR$$
REFRESH CLAUSE:
[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]
The FAST refreshes use the materialized view logs to send the rows that have changed from master tables to the materialized view.
The COMPLETE refresh re-creates the entire materialized view.
With FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. FORCE is the default.
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
WITH PRIMARY KEY 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 you should have defined
PRIMARY KEY on the master table or else you should use ROWID based materialized views.