Shulou(Shulou.com)06/01 Report--

Materialized view is a special physical table, and "Materialized" view is relative to ordinary view. Ordinary view is a virtual table, which has great limitations in application. Any query to the view, Oracle, is actually transformed into the query of the view SQL statement. This is not substantially good for the improvement of overall query performance.

Materialized view: 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 remote table-based data, which can also be called snapshots!

1. Types of materialized views: ON DEMAND, ON COMMIT

The difference between the two lies in the refresh method. ON DEMAND, as the name implies, refreshes the materialized view only when it "needs" to be refreshed, that is, to update the materialized view to ensure consistency with the base table data; while ON COMMIT means that once the base table has COMMIT, that is, the transaction commits, it refreshes and updates the materialized view immediately to make the data consistent with the base table.

2. ON DEMAND materialized view: the creation of materialized view itself is very complex and needs to be optimized, especially for large-scale production database systems. But Oracle allows you to do it in the simplest way, similar to a normal view, so it inevitably involves default values. In other words, we should pay special attention to the default value handling of the important definition parameters of the materialized view by Oracle.

3. Characteristics of materialized views:

(1) materialized view is, in a sense, a physical table (and not just a physical table), which is supported by the fact that it can be queried by user_tables.

(2) materialized view is also a kind of segment, so it has its own physical storage properties.

(3) materialized views will occupy the disk space of the database, which can be proved by the query results of user_segment.

Create statement: create materialized view mv_name as select * from table_name

By default, Oracle defaults to FORCE and DEMAND if you do not specify a refresh method and refresh mode.

4. How does the data of the materialized view update with the base table?

Oracle provides two ways, manual refresh and automatic refresh. The default is manual refresh. In other words, we manually execute a system-level stored procedure or package provided by Oracle to ensure that the materialized view is consistent with the base table data. This is the most basic refresh method. Automatic refresh, that is, Oracle creates a job through which the same stored procedure or package is called and implemented.

The characteristics of ON DEMAND materialized view and the difference between ON COMMIT materialized view and ON COMMIT materialized view, that is, the former does not update the materialized view without refreshing (manually or automatically), while the latter will update the materialized view as long as COMMIT occurs in the base table.

Create a regularly refreshed materialized view: create materialized view mv_name refresh force on demand start with sysdate next sysdate+1 (specify that the materialized view is refreshed once a day)

The materialized view created above is refreshed every day, but no refresh time is specified. If you want to specify a refresh time (for example, a regular refresh at 10:00 every evening): create materialized view mv_name refresh force on demand start with sysdate next to_date (to_char (sysdate+1,'dd-mm-yyyy'),'22 to_char 00 to_char 00'), 'dd-mm-yyyy hh34:mi:ss')

5. ON COMMIT materialized view

The creation of the materialized view of ON COMMIT is not much different from the materialized view of ON DEMAND created above. Because ON DEMAND is the default, ON COMMIT materializes the view and needs to add another parameter.

It should be noted that in the actual creation process, the base table needs to have a primary key constraint, otherwise an error will be reported (ORA-12014).

6. Refresh of materialized view

Refresh: refers to when and how the materialized view synchronizes with the base table after the DML operation occurs.

There are two modes of refresh: ON DEMAND and ON COMMIT. (as mentioned above) there are four ways to refresh: FAST, COMPLETE, FORCE, and NEVER. FAST refresh uses an incremental refresh, refreshing only the modifications made since the last refresh. COMPLETE refresh completely refreshes the entire materialized view. If you choose FORCE mode, Oracle will determine whether it can be refreshed quickly when refreshing, and if so, use FAST mode, otherwise use COMPLETE mode. NEVER means that the materialized view does not do any refresh

For the materialized view that has been created, you can change its refresh method, such as changing the refresh method of the materialized view mv_name to refresh it at 10:00 every night: alter materialized view mv_name refresh force on demand start with sysdate next to_date (concat (to_char (sysdate+1,'dd-mm-yyyy'), '22concat (to_char)),' dd-mm-yyyy hh34:mi:ss').

7. Materialized views have the same characteristics as tables, so we can create indexes, create methods, and align tables just like tables.

8. Delete the materialized view:

Although the materialized view is managed with the table, in the frequently used PLSQL tools, it can not be deleted by deleting the table (right-clicking on the table and selecting 'drop' does not delete the materialized view). You can use the statement: drop materialized view mv_name

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.