Usage and summary of Oracle Materialized View

Usage and summary of Oracle Materialized View

What is a materialized view?

Materialized View is a database object that contains query results. It is a local copy of remote data or used to generate a summary table based on the sum of data tables.

Materialized views Store Data Based on Remote tables, also known as snapshots (similar to snapshots and static snapshots in MSSQL Server ). For replication, materialized views allow you to locally maintain copies of remote data, which are read-only.

To modify a local copy, you must use the advanced copy function. When you want to extract data from a table or view, you can extract data from the materialized view.

For a data warehouse, the created materialized view is usually an aggregation view, a single table aggregation view, and a connection view. (This is based on the aggregation of local base tables or views ).

Materialized View, to put it bluntly, is a physical table, but this table can be updated regularly through the internal mechanism of Oracle, and some large time-consuming tables are connected to materialized views, the query efficiency is improved. Of course, you must enable the query rewrite option;

Material View

1. There is a time difference between two databases for data synchronization.

2. If the database is remotely connected, the query speed is improved.

(Because the query logic is complex and the data volume is large, the query speed is slow and the efficiency is low each time you query a view)

 

Refresh methods and methods of materialized views

1. Refresh Method

Fast

Complete

Fource

2. Refresh Method

DBMS_REFRESH.Refresh

DBMS_MVIEW.Refresh

EXEC DBMS_MVIEW.refresh ('bxj _ OBJECTS_MV_T1 ', 'C'); EXEC DBMS_REFRESH.refresh ('rep _ mviewgroup ');

Refresh methods and methods of materialized views
(1) Create an mview log file in the source database

Create materialized view log on w_1;

---- Note: (TEST indicates the table name or view name. For details about how to create a materialized view on a view, see view-based materialized view.

---- Create materialized view statement:

(2). Create materializad view syntax in statistical data

Create materialized view MV_TEST

---- MVTEST indicates the materialized view name.

Build immediate

---- Build deferred

Refresh fast

---- Incremental refresh

On commit

---- Submit when the base table is updated. The view is invalid here.

With rowid

---- Create a rowid-based materialized view, which corresponds to the primary key.

As

Select * from TEST;

---- Generate Materialized View data statements

Or

Create materialized view MV_TableName build immediate -- refresh force immediately upon creation -- If REFRESH is enabled, REFRESH quickly, otherwise, completely refresh on demand -- refresh method start with sysdate -- first refresh time next sysdate + 1/12 -- refresh interval as select 1 id 'A' name FROM dual;

(3). Refresh when calling

Dbms_refresh.refresh ('W _ 1 ')

Create Materialized View
Create materialized view partition refresh fast as select * FROM apps. bxj_objects_t1; create materialized view partition refresh force as select * FROM bxj_objects_t2; create materialized view partition refresh complete as select * FROM region;


Differences between on demand and on commit materialized views
The difference between the on demand and on commit materialized views is that their refreshing methods are different,
As the name suggests, on demand refresh is performed only when the Materialized View "Needs" to be refreshed, that is, update the materialized view to ensure data consistency between the base table and the base table;
On commit means that once the base table has a COMMIT, that is, the transaction is committed, it is refreshed immediately and the materialized view is updated immediately to make the data consistent with the base table.

Are materialized views physical tables?
1) materialized view is a physical table (but not just a physical table) in a sense, which can be queried by user_tables;
2) materialized views are also segments, so they have their own physical storage attributes;
3) The materialized view occupies the disk space of the database. This is evidenced by the query results of user_segment.

Query the last refresh time of the Materialized View

SELECT last_refresh_date      FROM user_mviews     WHERE mview_name = 'MV_TABLENAME';