What Is a Materialized View?

A materialized view is a database object that includes a query result. It is a local copy of remote data, or it is used to generate a summary table based on a sum of data tables . Materialized views store data based on remote tables and can also be called snapshots (similar to snapshots in MSSQL Server, static snapshots) ^[1] . For replication, materialized views allow you to maintain copies of remote data locally, which are read-only. If you want to modify the local copy, you must use the advanced copy function. When you want to extract data from a table or view, you can use to extract from the materialized view. For data warehouses, the materialized views created are usually aggregate views, single-table aggregate views, and join views. (This is an aggregation based on the local base table or view). Materialized view, to put it plainly, is a physical table , but this table can be regularly updated through Oracle's internal mechanism. Some large time-consuming tables are connected with materialized views to achieve query efficiency . Of course, to turn on the query rewrite option;

Materialized view

Materialized view and

Materialized view for data replication

One of the main functions of materialized views is for data replication. The advanced replication function introduced by Oracle is divided into two parts, multi-master replication and materialized view replication. And materialized view replication is to take advantage of the functionality of materialized views. ^[2]

1. Build methods: Build Immediate and Build Deferred. Build Immediate generates data when the materialized view is created, while Build Deferred does not generate data when it is created, and later generates data as needed. The default is Build Immediate.

2. Query Rewrite: Enable Query Rewrite and Disable Query Rewrite. Indicate whether the created materialized view supports query rewrite. Query rewriting is when

There are two orders for deleting materialized views. One is to delete the materialized view first and then the log; the other is to delete the log and then delete the materialized view.

If the original data table is only referenced by a materialized view, you can delete the log first, and then delete the materialized view, so the deletion is faster.

drop materialized view log on test_table;

drop materialized view mv_test_table;

If multiple materialized views are established on the log, you can delete only the materialized views, because after the log is deleted, other materialized views that reference the log cannot be refreshed.