ORACLE materialized view application

Materialized View is a special physical table. The Materialized view is relative to a common view. A common view is a virtual table, which has many application limitations. Oracle converts any View query to a view SQL statement query. This improves the overall query performance without substantial benefits. 1. materialized view type: the difference between on demand and on commit is that the refresh method is different. As the name suggests, on demand is refreshed only when the Materialized View "needs, REFRESH (update Materialized View) to ensure consistency with the base table data. on commit means that once the base table has a COMMIT, that is, the transaction is committed, it will be refreshed immediately, update the materialized view immediately to make the data consistent with the base table. 2. The creation of materialized views in the on demand materialized view is complex and requires parameter settings optimization, especially for large production database systems. However, Oracle allows you to do this in the simplest way, similar to a common view, so it will inevitably involve default values. That is to say, we need to pay special attention to the handling of the default values of important definition parameters for materialized views in Oracle. Materialized View features: (1) materialized view is a physical table in a sense (and not just a physical table), which can be queried by user_tables, (2) materialized views are also segments, so they have their own physical storage attributes. (3) materialized views occupy the disk space of the database, this is evidenced by the query results of user_segment. The creation statement is create materialized view mv_name as select * from table_name. By default, if the refresh method and refresh mode are not specified, oracle uses FORCE and DEMAND by default. How does the Materialized View data update with the base table? Oracle provides two methods: manual refresh and automatic refresh. Manual refresh is used by default. That is to say, we can manually execute a system-level stored procedure or package provided by an Oracle database to ensure data consistency between the Materialized View and the base table. This is the most basic refresh method. Auto-Refresh means that Oracle will create a job through which the same stored procedure or package is called for implementation. The characteristics of the on demand materialized view and its differences with the on commit materialized view, that is, the former does not update the materialized view without refreshing (manual or automatic), while the latter updates the materialized view without refreshing, -- as long as the base table has a COMMIT. 3. The on commit materialized view is not much different from the materialized view created on demand above. Because on demand is the default, you need to add another parameter to the on commit materialized view. It should be noted that you cannot specify only on commit when defining it, but you still need to include a parameter. Create on commit materialized view: create materialized view mv_name refresh force on commit as select * from table_name Note: during actual creation, the base table requires a primary key constraint; otherwise, an error is reported (ORA-12014
Advantages: 1. The biggest advantage of materialized views is that it can improve performance: the materialized views of Oracle provide powerful functions, it 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. 2. materialized views are similar to indexes in many aspects. 3. Storage of pre-computed answers can greatly reduce machine load A and less physical reads-scanning less data B, less write-do not sort and aggregate C frequently. Reduce CPU consumption-no need to perform aggregation computing on data and function call D, significantly accelerating response time-when querying data using materialized views (opposite to the primary table ), the query results will be returned soon.
Disadvantages: 1. materialized views are best used in a read-only or intensive environment and are not used in an online transaction processing system (OLTP) environment, updating a fact table may result in Materialized View row locks, affecting system concurrency. 2. materialized views may fail to be refreshed quickly, resulting in inaccurate query data. 3. Rowid materialized views (created materialized views usually have primary keys, rowids, and subquery views) there is only one single primary table, which cannot include any of the following items: A, Distinct, or aggregate function. b, Group by, subquery, connection, and SET operations 4. The materialized view adds the demand for disk resources, that is, the hard disk space to be permanently allocated to the materialized view to store data 5, the working principle of materialized views is subject to some possible constraints, such as primary keys and Foreign keys. Experiment: 1. Create a Materialized View:/* Formatted on 10:46:08 (QP5 v5.115.810.9015) */create materialized view Contract refresh force on demand as select "Contract_ID", "Contract_ProjectID", "Contract_TableID ", "Contract_NO" FROM "Comm_Contract" WHERE "Contract_TableID" IN (SELECT "AppTab_ID" FROM "Base_AppTableInfo" WHERE "AppTab_TableCode" = 'salecontractp _ base') 2, query ---- query FROM the base table: SELECT "Contract_ID" FROM "Comm_Contract" WHERE "Contract_NO" LIKE '% 100' A. The response time for A single user query is 2009011% ms B, 50 concurrent query response time: 1670 ms ----- query from the materialized view: select "Contract_ID" from "Contract" where "Contract_NO" like '% 100' A, single user query response time: 23 ms B, 50 concurrent query response time: 48 ms conclusion: currently, the project is used for more and more fields in the original table. As the data volume increases, the query efficiency and concurrency efficiency are reduced, the main fields extracted from a table are built into a materialized view. The performance and concurrency of associated queries, subqueries, and queries are significantly improved. Problem: It seems that the cross-Table materialized view cannot be created, similar to the general view, if you are interested in the experiment that we did a few years ago, you can experiment with the materialized view restrictions.
Author: wangting424