General Usage of oracle Materialized View

 

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. Features of materialized views:

 

(1) materialized view is a physical table (and 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;

 

Create statement: create materialized view mv_name as select * from table_name by default, if the refresh method and refresh mode are not specified, the default values of Oracle are FORCE and DEMAND.

 

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.

 

Create a materialized view with timed refresh: 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 previously created materialized view is refreshed every day, however, the refresh time is not specified. If you want to specify the refresh time (for example, refresh regularly at every night ): create materialized view mv_name refresh force on demand start with sysdate next to_date (concat (to_char (sysdate + 1, 'dd-mm-yyyy '), '22:00:00 '), 'dd-mm-yyyy hh24: mi: ss ')

 

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)

 

4. 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. (As mentioned above) there are four refresh Methods: FAST, COMPLETE, FORCE, and NEVER. FAST refresh uses incremental refresh. Only the modifications made since the last refresh are refreshed. COMPLETE refresh completely refreshes the entire materialized view. If the FORCE method is selected, Oracle determines whether a quick Refresh can be performed during refresh. If yes, it adopts the FAST method; otherwise, it adopts the COMPLETE method. NEVER indicates that the materialized view is not refreshed. You can modify the refresh method of the created materialized view. For example, you can change the refresh method of the materialized view mv_name to refresh 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 '), '22:00:00 '), 'dd-mm-yyyy hh24: mi: ss ')

 

5. materialized views have the same features as tables. Therefore, we can create indexes for tables in the same way.

 

6. Delete materialized views: Although materialized views are managed together with tables, in PLSQL tools that are frequently used, you cannot delete a table by deleting it. (You cannot right-click the table and select 'drop' to delete the materialized view.) You can use the following statement to delete the table: drop materialized view mv_name.

 

From the blog of programmer YB