Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use Oracle materialized view, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

The usage and Summary of Oracle materialized View

What is materialized View (material view)?

A materialized view is a database object that includes a query result, which is a local copy of the remote data, or is used to generate a summary table based on the sum of the data table.

Materialized views store data based on remote tables, which can also be called snapshots (similar to snapshot, static snapshots in MSSQL Server). 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 replication feature. When you want to extract data from a table or view, you can extract it from a materialized view.

For data warehouses, the materialized views created are usually aggregate views, single table aggregate views, and join views. This is based on an 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. Some large time-consuming table joins can be implemented with materialized view, which will improve the efficiency of query. Of course, turn on the query rewrite option.

The main role of Material View

1. To achieve data synchronization between two databases, there can be a time difference.

two。 If it is a remote link to the database scenario, improve the query speed.

(because the query logic is complex and the amount of data is relatively large, the query speed is slow and inefficient every time the view is queried.)

The way and method of refreshing materialized view

1. Refresh mode

Fast

Complete

Fource

two。 Refresh method

DBMS_REFRESH.Refresh

DBMS_MVIEW.Refresh

EXEC DBMS_MVIEW.refresh ('BXJ_OBJECTS_MV_T1','C'); EXEC DBMS_REFRESH.refresh (' REP_MVIEWGROUP'); the refresh method and method of materialized view (1). Create mview log log files in the source database

Create materialized view log on w_1

Note: (TEST is the name of the table or view. For more information about building materialized views on views, see materialized views based on views.

-create materialized view statement:

(2)。 Establish materializad view syntax in statistical data

Create materialized view MV_TEST

-MVTEST is the materialized view name

Build immediate

-data generated during creation corresponds to build deferred

Refresh fast

-incremental refresh

On commit

-submit when there is an update to the base table. This sentence is not valid for the view.

With rowid

-here you create a materialized view based on rowid, which corresponds to primary key

As

Select * from TEST

-generate materialized view data statements

Or

CREATE MATERIALIZED VIEW MV_TableName BUILD IMMEDIATE-refresh REFRESH FORCE immediately upon creation-refresh quickly if it can be refreshed 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 called

Dbms_refresh.refresh ('White1')

Create a materialized view CREATE MATERIALIZED VIEW bxj_objects_mv_t1 REFRESH FAST AS SELECT * FROM apps.bxj_objects_t1;CREATE MATERIALIZED VIEW bxj_objects_mv_t2 REFRESH FORCE AS SELECT * FROM bxj_objects_t2;CREATE MATERIALIZED VIEW bxj_objects_mv_t3 REFRESH COMPLETE AS SELECT * FROM bxj_objects_t3 The difference between ON DEMAND and ON COMMIT materialized views the difference between ON DEMAND and ON COMMIT materialized views lies in their refresh methods. ON DEMAND, as its name implies, refreshes (REFRESH) only when the materialized view "needs" to be refreshed, that is, to update the materialized view to ensure consistency with the base table data. ON COMMIT means that once the base table has COMMIT, that is, the transaction commits, it will be refreshed immediately, and the materialized view will be updated immediately to make the data consistent with the base table. Is the materialized view a physical table? 1) the materialized view is, in a sense, a physical table (but not just a physical table), which is supported by the fact that it can be queried by user_tables; 2) the materialized view is also a segment, so it has its own physical storage properties; and 3) the materialized view takes up the disk space of the database, which can be corroborated 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'; Thank you for reading this article carefully. I hope the article "how to use Oracle materialized View" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.