An important feature of materialized views is data replication. The Advanced Replication feature launched by Oracle is divided into two parts: Multi-master replication and materialized view replication.
Materialized View replication uses the materialized view function.
Materialized View replication includes read-only materialized view replication, updatable materialized view replication, and writable materialized view replication.
The read-only materialized view creates a read-only environment for the source database. Updatable materialized view replication creates a bidirectional replication Environment Based on materialized views.
The writable materialized view copy uses for update when the materialized view is created, but is not added to the materialized view group as the updatable materialized view. Therefore, the local materialized view can be modified, however, modifications cannot be sent to the source database, because modifications are lost after the materialized view is refreshed. This type of Materialized View replication is rarely used.
Example of creating a copy of a read-only materialized view environment:
Http://www.itpub.net/thread-204968-1-1.html
Http://www.itpub.net/viewthread.php? Tid = 234279
Maintenance of Materialized View replication Environment
The materialized view replication environment includes the master site and materialized view site. Determines whether to create a copy group and a materialized view group based on whether the materialized view needs to be updated.
For a read-only materialized view, you do not need to create a replication group or materialized view group. Therefore, the main site does not need to make any changes except for creating materialized view logs.
For updatable materialized views, the materialized views must be added to the materialized view group. Therefore, the master site establishes a replication group for comparison. If the primary site is not a member of Multi-master replication, you can create a single-master replication environment on the primary site.
For a master site that contains a replication group, the DDL operations on the tables in the replication group are different from those on normal tables. directly executing DDL operations on the tables may damage the replication objects.
Maintenance problems in the materialized view replication Environment
Exporting and importing materialized views may result in invalid materialized view logs: A Terrible bug can cause damage to the materialized view copy environment in unexpected situations.
Clearing Registration Information in Materialized View: Clearing Registration Information in materialized view.
Restrictions on rapid refresh of Materialized View replication (1): you cannot create a refresh Materialized View on the copied materialized view.
Restrictions on rapid refresh of Materialized View replication (2): A change solution that puts connected or clustered materialized views on the base table site in advance.
Database migration that includes the replication Environment (1): migrate the materialized view site through EXP/IMP.
Database migration that includes the replication Environment (2): migration of a single master site through EXP/IMP.
How to determine the materialized view that causes refresh group refresh failure (1): discusses how to locate the materialized view that failed refresh in the local materialized view.
How to determine the materialized view that causes refresh group refresh failure (2): This article describes a method applicable to various situations.
Materialized view log not cleared: This section describes how to manually clear registration information of materialized views deleted abnormally.
Constraints conflict during materialized view refreshing: a solution to constraints conflict during materialized view refreshing.
ORA-26500 errors: Create, delete function indexes, and other operations should be performed again to generate replication support for the table. |
Materialized View for pre-Calculation
This type of materialized object is used as a data warehouse system. It is mainly used to pre-calculate and save the results of time-consuming operations such as table join or aggregation. In this way, these time-consuming operations can be avoided during query execution, and get results quickly. This materialized view also often uses the query rewrite mechanism, so that you do not need to modify the original query statement. Oracle will automatically select the appropriate materialized view for query, completely transparent to the application.
This materialized view can be divided into three types: materialized view containing aggregation, materialized view containing only connection, and nested materialized view. The restrictions for quick refresh of the three materialized views are very different, but not for other aspects.
You can specify multiple options when creating a Materialized View:
Build Methods: includes build immediate and build deferred. Build immediate generates data when you create a materialized view, while build deferred does not generate data when you create the view, and then generates data as needed. The default value is build immediate.
Query Rewrite: Includes enable query rewrite and disable query rewrite. Specify whether the created materialized view supports query rewriting. Query Rewriting means that when you query the base table of the materialized view, Oracle automatically determines whether the result can be obtained by querying the materialized view. If yes, clustering or join operations are avoided, instead, read data directly from the computed materialized view. The default value is disable query rewrite.
Refresh: Specifies the mode in which the materialized view is synchronized with the base table after the DML operation is performed on the base table. There are two refresh modes: on demand and on commit. On demand indicates that the materialized view is refreshed when the user needs it. You can manually refresh the materialized view by DBMS_MVIEW.REFRESH or by regularly refreshing the JOB. On commit indicates that the Materialized View refreshes the base table while submitting the DML operation. There are four refresh Methods: FAST, COMPLETE, FORCE, and NEVER. FAST refresh uses incremental refresh. Only the modifications made since the last refresh are refreshed. COMPLETE refresh completely refreshes the entire materialized view. If the FORCE method is selected, Oracle determines whether a quick Refresh can be performed during refresh. If yes, it adopts the FAST method; otherwise, it adopts the COMPLETE method. NEVER indicates that the materialized view is not refreshed. The default value is force on demand.
You can specify that the on prebuild table statement creates a materialized view ON an existing TABLE. In this case, the materialized view and table must have the same name. When you delete a materialized view, tables with the same name are not deleted. The QUERY_REWRITE_INTEGERITY parameter must be set to trusted or stale_tolerated.
Materialized views can be partitioned. Partition-based materialized views Support partition change tracking (PCT ). In a materialized view with this feature, you can perform a quick refresh operation after the base table performs partition maintenance.
A brief description of features of the materialized view based on pre-calculation:
Http://yangtingkun.itpub.net/post/468/11356
|
Materialized View features
Materialized View creation:
Create a materialized view containing ROLLUP or CUBE: when to use a materialized view containing ROLLUP and CUBE.
Permissions required to create a materialized view (1): permissions required to create a general materialized view.
Permissions required to create a materialized view (2): permissions required to quickly refresh the materialized view.
Permissions required to create a materialized view (3): permissions required to create an on commit materialized view.
Permissions required to create a materialized view (4): permissions required to create a query and override a materialized view.
Create a materialized view on a table containing the LONG type: the materialized view can copy the LONG type.
How to create and refresh a Materialized View: how to create and refresh a materialized view.
Delete order of materialized views: Discusses the order of deleting materialized views and deleting logs of materialized views.
If you have insufficient permissions to create materialized views for other users, an error is returned when you create a materialized view for another user.
Materialized View quick Refresh:
Locate the cause that the materialized view cannot be refreshed quickly: how to find the cause that the materialized view cannot be refreshed quickly when a fast refreshed materialized view cannot be created.
Quick refresh of materialized views (1): Restrictions on quick refresh of connected materialized views.
Rapid refresh of materialized views (2): Restrictions on rapid refresh of materialized views based on aggregation.
Quick refresh of materialized views (3): The limit for quick refresh of materialized views that contain union all.
Two issues that make the materialized view unable to be refreshed quickly: synonym issues and append different table fields.
Non-atomic refresh of materialized views: Describes the atomic refresh features of materialized views.
Query the materialized view again:
If you fail to create a materialized view with the same name that supports query and rewriting, the original materialized view will be deleted: this bug may cause your original materialized view to be accidentally deleted.
Restrictions on Materialized View query rewriting (1): Limits on the establishment of materialized views for query rewriting.
Restrictions on Materialized View query rewriting (2): conditions for using the query rewriting function.
Materialized View PCT features:
PCT features of materialized views (1): Introduction to PCT features of materialized views based on partitioned tables.
PCT features of materialized views (2): Limitations on PCT features of materialized views based on partitioned tables.
Rapid refresh of 10 Gb materialized view PCT no longer requires materialized view logs (1): Oracle10g adds support for rapid refresh of Materialized View PCT, and quick refresh of Materialized View logs is no longer required by using PCT.
10g materialized view PCT quick refresh no longer requires materialized view logs (2): continue to study the mechanism of PCT quick refresh.
10g materialized view PCT quick refresh no longer requires materialized view logs (3): study the mechanism of PCT quick refresh. |
In-depth research on Materialized View
Research on the internal mechanism of materialized views:
Materialized view log structure: Describes the meaning of each field in the Oracle materialized view log.
How to refresh the materialized view based on the materialized view log: describes how Oracle uses the materialized view log to refresh the materialized view.
How to avoid the impact of system time changes in materialized view logs: This article describes how Oracle's materialized view log mechanism avoids the impact of system time adjustment.
Whether the materialized view is refreshed Based on fields (1): Description phenomenon.
Refresh the materialized view based on the field (2): speculate on the refresh behavior of Oracle.
Refresh the materialized view based on fields (3): Use SQL _TRACE to confirm the speculation in the previous article.
The elimination of inconsistency between materialized views and base table data (I): a solution for quickly refreshing the read-only materialized views is similar to that for base tables.
The elimination of inconsistent materialized views and base table data (2): Modify materialized view logs to synchronize INSERT and DELETE operations.
Discuss how to eliminate inconsistent materialized views and base table data (3): How to synchronize UPDATE operations by modifying materialized view logs.
Discussing how to eliminate inconsistent materialized views and base table data (4): How to synchronize UPDATE operations containing LOB columns by modifying materialized view logs.
A problem of fast refresh of materialized views: it does not affect the field precision method of the materialized view.
Maintenance of Materialized View logs: reduces the high watermark line of Materialized view log tables.
Use triggers to customize the materialized view refresh: You can create a trigger on the materialized view to customize the refresh operation.
Materialized View refresh is not entirely based on materialized view log records: materialized view refresh is not only dependent on materialized view logs. Materialized View of data.
Quick refresh of materialized views that contain LOB: On 9.2 and 10, quick refresh of materialized views that contain LOB is implemented with different processing mechanisms.
Rapid refresh of materialized views including LOB (2): Continue to study how LOB objects are refreshed after 10 Gb.
Rapid refresh of materialized views that contain LOB (3): This article studies how to refresh materialized views when the number of LOB objects exceeds 32 kb in 10 Gb or later versions.
Quick refresh of materialized views containing LOB (4): Compare the refresh performance of materialized views containing LOB 9i and 11g.
After the materialized view log table is DROP, an error is returned when the materialized view log table is directly dropped. If the log table is DROP, an error is returned when the materialized view is created.
Get the modified vector of the materialized view field (1): describes how to obtain the modified materialized view vector after a column is modified.
Get the modified vector of the materialized view field (2): Describes the modification of multiple fields.
Get modified vectors of Materialized View fields (3): encapsulate a function to complete this function.
|
Flexible Use of Materialized View Functions
Materialized views have powerful functions. The flexible use of materialized views can increase the cost of existing functions and implement more functions.
Materialized View for logical data migration: In addition to EXP/IMP, you can also use materialized views for database logical migration.
Key Points of Logical Data migration using materialized views: This section briefly introduces some important points that need to be paid attention to when using materialized views for logical data migration.
A problem of fast refresh of materialized views: it does not affect the field precision method of the materialized view.
Use triggers to customize the materialized view refresh: You can create a trigger on the materialized view to customize the refresh operation.
Modify DISTINCT Materialized View (1): Describes the DISTINCT operations in SELECT statements.
Modify DISTINCT Materialized View (2): Describes the DISTINCT operations in the COUNT or SUM aggregate function.
Modify DISTINCT Materialized View (3): describes an example of union all.
Transform A materialized view that contains MAX or MIN: how to transform a materialized view that contains MAX or MIN Aggregate functions.
Oracle's online redefinition function is actually implemented by using materialized view logs. Data Synchronization during the redefinition process also uses materialized views for rapid refresh.
However, because online redefinition also requires the support of Materialized View logs, there was no way to redefine the tables with materialized view logs before 11 GB, however, this function is added to Oracle in 11 GB:
New Feature of Oracle11-Online Operation Feature Enhancement (6): added support for Materialized View logs by online redefinition.
|
Issues and bugs related to materialized views
Some materialized view problems and BUG descriptions:
An error occurred while executing the oracle plan! : Query the rewriting bug in 9204.
If you fail to create a materialized view with the same name that supports query and rewriting, the original materialized view will be deleted: this bug may cause your original materialized view to be accidentally deleted.
Exporting and importing materialized views may result in invalid materialized view logs: A Terrible bug can cause damage to the materialized view copy environment in unexpected situations.
Two issues that make the materialized view unable to be refreshed quickly: synonym issues and append different table fields.
Clearing Registration Information in Materialized View: Clearing Registration Information in materialized view.
ORA-600 (999) error (1): materialized view refreshing problem.
ORA-600 (999) error (2): further research into this problem.
ORA-600 (999) error (III): further research into what information is missing in the database causes this problem.
Constraints conflict during materialized view refreshing: a solution to constraints conflict during materialized view refreshing.
Creating a materialized view causes the database instance to crash: creating a materialized view directly causes the instance to crash.
The elimination of inconsistency between materialized views and base table data (I): a solution for quickly refreshing the read-only materialized views is similar to that for base tables.
The elimination of inconsistent materialized views and base table data (2): Modify materialized view logs to synchronize INSERT and DELETE operations.
Discuss how to eliminate inconsistent materialized views and base table data (3): How to synchronize UPDATE operations by modifying materialized view logs.
Discussing how to eliminate inconsistent materialized views and base table data (4): How to synchronize UPDATE operations containing LOB columns by modifying materialized view logs.
10203 setting CURSOR_SHARING to SIMILAR causes refresh of materialized views: Setting CURSOR_SHARING to SIMILAR causes refresh of materialized views to fail.
A problem of fast refresh of materialized views: it does not affect the field precision method of the materialized view.
ORA-600 (ksmovrflow) error: an error is reported when the 8i or 9i Database creates a materialized view with a base table of 10 Gb.
How to determine the materialized view that causes refresh group refresh failure (1): discusses how to locate the materialized view that failed refresh in the local materialized view.
How to determine the materialized view that causes refresh group refresh failure (2): This article describes a method applicable to various situations.
Materialized View base table site upgrade causes materialized view refresh error ORA-4062: materialized view quick refresh error, ORA-4062, ORA-4068 error.
ORA-600 (kkzgavupd_agg.2) error: refresh a materialized view on 9204 with this error.
Delete order of materialized views: Discusses the order of deleting materialized views and deleting logs of materialized views.
Maintenance of Materialized View logs: reduces the high watermark line of Materialized view log tables.
If you have insufficient permissions to create materialized views for other users, an error is returned when you create a materialized view for another user.
ORA-6544 (55916) error: Create Materialized View on 11g access source table 10.1 error.
ORA-12028 error: this error was encountered in creating materialized views.
ORA-23515 error: tablespaces containing materialized views cannot be deleted directly.
ORA-26500 errors: Create, delete function indexes, and other operations should be performed again to generate replication support for the table. |
|
