Oracle materialized view description

1. materialized view Overview The Materialized View of Oracle is a database image that contains a query result. It is a local copy of remote data or is used to generate a summary table based on the sum of data tables. Materialized View stores data based on remote tables, also known as snapshots. Materialized views can be used to pre-calculate and save the results of time-consuming operations such as table join or aggregation.

1. materialized view Overview The Materialized View of Oracle is a database image that contains a query result. It is a local copy of remote data or is used to generate a summary table based on the sum of data tables. Materialized View stores data based on remote tables, also known as snapshots. Materialized views can be used to pre-calculate and save the results of time-consuming operations such as table join or aggregation.

1. materialized view Overview

The Materialized View of Oracle is a database object that includes query results. It is a local copy of remote data or is used to generate a summary table based on the sum of data tables. Materialized View stores data based on remote tables, also known as snapshots.

Materialized views can be 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. Materialized views are similar to indexes in many aspects: materialized views are used to improve query performance, while materialized views are transparent to applications, adding or deleting materialized views does not affect the correctness and validity of SQL statements in applications. materialized views occupy storage space. materialized views should also be refreshed when the base table changes.

Materialized views can be used to query tables, views, and Other materialized views.

A materialized view is usually called a master table (during replication) or a schedule (in a data warehouse ).

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.

In the replication environment, the created materialized view usually contains the primary key, rowid, and subquery view.

Materialized views can create indexes because they exist physically.

1.1 materialized views can be divided into the following three types:

(1) materialized views that contain aggregation;

(2) Only the connected materialized views are included;

(3) nested materialized views.

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. The following describes the main options:

(1) creation method (BuildMethods): Includes build immediate and build deferred.

Build immediate generates data when creating a materialized view.

Build deferred does not generate data when it is created, and data will be generated later as needed. The default value is build immediate.

(2) QueryRewrite: 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 disablequery rewrite.

(3) Refresh: the method used to synchronize the materialized view with the base table after the DML operation is performed on the base table. There are two refresh modes: on demand and on commit.

The difference between the on demand and on commit materialized views is that the refresh method is different. on demand refers to the refresh of the materialized view when the user needs it. You can refresh it by means of DBMS_MVIEW.REFRESH manually, you can also use the JOB to regularly refresh 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, it is a transaction COMMIT, refresh immediately and update the materialized view to make the data consistent with the base table.

For the base table, the common COMMIT can be completed within 0.01 seconds, but it takes 6 seconds to have the on commit view. The speed is greatly reduced. The impact of the on commit view ON the base table is evident.

1.2 materialized views can be classified based on different points:

1) by refresh method: FAST/COMPLETE/FORCE

2) based ON the refresh time: on demand/ON COMMIT

3) UPDATABLE/READ ONLY

4) enable query rewrite/DISABLEQUERY REWRITE

By default, if the refresh method and refresh mode are not specified, Oracle uses FORCE and DEMAND by default.

Note: The materialized view that sets refresh on commit cannot access the remote object.

When creating a materialized view, you can specify the order by statement to save the generated data in a certain ORDER. However, this statement will not be written into the definition of the materialized view, and will not be effective for subsequent refreshes.

1.3 materialized views can be refreshed in three ways: COMPLETE, FAST, and FORCE.

1) a complete refresh operation deletes all records in the table (if a single table is refreshed, TRUNCATE may be used ), then, the materialized view is regenerated based on the definition of the query statement in the materialized view.

2) FAST refresh uses the incremental refresh mechanism to only refresh all operations on the base table since the last refresh to the materialized view. FAST must create view logs based on the master table.

For the incremental refresh option, if an analysis function exists in the subquery, the materialized view does not work.

3) when the FORCE method is adopted, Oracle automatically determines whether the quick refresh conditions are met. If the conditions are met, hybriddb for MySQL performs a quick refresh. Otherwise, the system performs a full refresh.

The quick refresh mechanism of Oracle materialized view is completed by materialized view logs. Oracle also supports quick refresh of multiple materialized views through a materialized view log.

Materialized View logs can be set to ROWID or primary key based on the needs of rapid refresh of different materialized views. You can also choose whether to include SEQUENCE, including new values, and the list of specified columns.

1.4 other descriptions and examples of Refresh clauses in Materialized View

The REFRESH clause can contain the following parts:

[Refresh [fast | complete | force]

[On demand | commit]

[Start with date] [next date]

[With {primary key | rowid}]

1.4.1 primary key and ROWD clause:

The with primary key option generates a primary key materialized view, that is, the materialized view is based on the primary key of the PRIMARY table, rather than the ROWID (corresponding to the ROWID clause ). primary key is the default option. To generate a primary key clause, you should define a primary key in the PRIMARY table. Otherwise, you should use a ROWID-based materialized view.

The ROWID-based materialized view has only one primary table and cannot contain any of the following items:

(1). Distinct or aggregate function.

(2). Group by, subquery, connection, and SET operations

-- Primary key Materialized View Example:

Create a materialized view of the primary key on the remote database table emp:

CREATEMATERIALIZEDVIEW mv_emp_pkREFRESHFASTSTARTWITHSYSDATENEXT  SYSDATE + 1/48WITHPRIMARYKEYASSELECT * FROM emp@remote_db

-- When you use the FAST option to create a materialized view, you must create a view log based on the master table, as shown below:

CREATEMATERIALIZEDVIEWLOGON emp;

-- Rowid Materialized View Example:

The following syntax creates a Rowid Materialized View on the remote database table emp.

CREATEMATERIALIZEDVIEW mv_emp_rowidREFRESHWITHROWIDASSELECT * FROM emp@remote_db;Materializedviewlog created.

-- Subquery Materialized View Example:

Create a materialized view for sub-queries based on the emp and dept tables on the remote database table emp

CREATEMATERIALIZEDVIEW  mv_empdeptASSELECT * FROM emp@remote_db eWHEREEXISTS(SELECT * FROM dept@remote_db dWHEREe.dept_no = d.dept_no)

1.4.2 refresh time

The start with clause instructs the database to complete the first copy time from the master table to the local table. The NEXT clause describes the interval of refreshing.

CREATEMATERIALIZEDVIEW mv_emp_pkREFRESHFASTSTARTWITHSYSDATENEXT  SYSDATE + 2WITHPRIMARYKEYASSELECT * FROM emp@remote_db;

In the preceding example, the first copy of the Materialized View data is generated at creation and refreshed every two days.

CreatematerializedviewMV_LVY_LEVYDETAILDATATABLESPACE ZGMV_DATA -- save the tablespace BUILDDEFERRED -- Do not refresh the refreshforce immediately after delayed refresh -- perform a quick refresh if refresh is enabled, otherwise, refresh ondemand completely -- refresh startwith to_date ('24-11-200518: 00: 10', 'dd-mm-yyyyhh24: mi: ss') as specified ') -- nextTRUNC (SYSDATE + 1) + 18/24 -- refresh interval asSELECT * FROM emp @ remote_db;

1.5 on prebuild table description

Specify the on prebuild table statement when creating the materialized view. You can create the 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.

1.6 materialized view Partition

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. For clustering materialized views, you can use CUBE or ROLLUP in the group by list to create clustering materialized views of different levels.

For more information, see:

Description of Oracle materialized view logs and quick refresh

Http://blog.csdn.net/tianlesoftware/article/details/7720580

Detailed error description of Oracle Materialized View

Http://blog.csdn.net/tianlesoftware/article/details/7719789

Restrictions on quick refresh of Oracle Materialized View

Http://blog.csdn.net/tianlesoftware/article/details/7719679

Ii. materialized view operation example

1. permissions required to create a Materialized View:

GRANT CREATE MATERIALIZED VIEW TO USER_NAME;

2. Create materialized view logs in the source table

Create materialized view log on davetablespace & BISONCU_SPACE -- LOG space with primary key; -- specified as the primary key type

3. grant permissions to intermediate users

GRANT SELECT ON DAVE TO ANQING;GRANT SELECT ON MLOG$_DAVE TO ANQING;

4. Create a materialized view on the target database

Create materialized view AICS_DAVETABLESPACE & BISONCS_SPACEREFRESH fast on demand -- first refresh time -- start with to_date ('2017-01-01 20:00:00 ', 'yyyy-mm-dd hh24: mi: ss ') start with sysdate -- refresh interval. Refresh once every day at -- next trunc (SYSDATE, 'dd ') + 1 + 2/24 next sysdate + 1/24/20 with primary key--using default local rollbacksegmentdisable query rewrite asselect MODEL_ID, STATUS, MODEL_NAME, MANU_ID, DESCRIPTION, CREATE_TIME, UPDATE_TIME, SW_VERSIONFROM AICS_DAVE @ LINK_DAVE;

5. Create an index on the target Materialized View

CREATE INDEX IDX_T_DV_CT   ON AICS_DEV_INFO (CREATE_TIME, UPDATE_TIME)   TABLESPACE &BISON_IDX; CREATE INDEX IDX_T_DV_UT   ON AICS_DEV_INFO (UPDATE_TIME)   TABLESPACE &BISON_IDX; CREATE INDEX I_T_DV_MSISDN   ON AICS_DEV_INFO (MSISDN)   TABLESPACE &BISON_IDX;

6. materialized view refresh description

(1) Use dbms_mview.refresh to manually refresh

For example:

EXEC DBMS_MVIEW.REFRESH ('mv _ DAVE '); -- completely refresh EXEC DBMS_MVIEW.REFRESH (LIST => 'mv _ DAVE', METHOD => 'C '); EXEC DBMS_MVIEW.REFRESH ('mv _ DAVE ', 'C'); -- refresh EXEC DBMS_MVIEW.REFRESH (LIST => 'mv _ DAVE', METHOD => 'F') quickly '); EXEC DBMS_MVIEW.REFRESH ('mv _ DAVE ', 'F ');

(2) Use the dbms_refresh.refresh process to refresh music videos in batches

If we specify the refresh time of start and next time during the process of creating the materialized view, Oracle will automatically create the refresh job and adopt dbms_refresh.refresh.

Make refresh group before refreshing in this way.

For the Refreshmake syntax, refer:

Http://docs.oracle.com/cd/B19306_01/server.102/b14227/rarrefreshpac.htm#i94057

Example:

Suppose there is a materialized view MV_T1, MV_T2, MV_T3. The syntax for creating a refresh group is as follows:

SQL> EXEC DBMS_REFRESH.MAKE ('rep _ test', 'mv _ T1, MV_T2, MV_T3 ', SYSDATE, 'sysdate + 1') -- refresh the entire refresh group: SQL> EXEC DBMS_REFRESH.REFRESH ('rep _ test ')

7. Delete materialized views and logs

-- Delete materialized view logs: drop materialized view log on dave; -- delete materialized view drop materialized view MV_DAVE;

8. view the refresh status information of the Materialized View

SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROMUSER_MVIEWS;SQL> SELECT NAME, LAST_REFRESH FROM USER_MVIEW_REFRESH_TIMES;

9. query materialized view logs:

SELECT * FROM MLOG$_DAVE;

Note:

Organized from Network

Bytes -------------------------------------------------------------------------------------------------------

!

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

DBA1 group: 62697716 (full); DBA2 group: 62697977 (full) DBA3 group: 62697850 (full)

Super DBA group: 63306533 (full); DBA4 group: 83829929 DBA5 group: 142216823

DBA6 group: 158654907 DBA7 group: 172855474 DBA group: 104207940