Similarities between Oracle materialized views and Indexes

We all know that the Oracle materialized view provides very powerful functions in practical applications. Its main functions are mainly used for pre-calculation, table Storage, join, clustering, and other related operations, results of time-consuming operations. In this way, you can avoid these time-consuming operations during query execution and get results quickly.

Oracle materialized views are similar to indexes in many aspects:

Materialized views are used to improve query performance. Oracle 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. When the base table changes, the materialized views should also be refreshed.

Materialized views can be divided into three types: materialized views that contain aggregation, materialized views that only contain connections, and 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:

Build Methods): Includes build immediate and build deferred. Build immediate generates data when creating the v materialized view, while build deferred does not generate data when it is created, and data will be generated later as needed. The default value is build immediate.

Query Rewrite): 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 disable query rewrite.

Refresh): Specifies the method used to synchronize the materialized views of the Oracle database and the base table after the DML operation is performed on the base table. There are two refresh modes: on demand and on commit. On demand indicates that the materialized view is refreshed when the user needs it. You can manually refresh the materialized view by DBMS_MVIEW.REFRESH or by regularly refreshing the JOB.

On commit indicates that the Materialized View refreshes the base table while submitting the DML operation. 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. The default value is force on demand.

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.

Materialized view log: If you need to refresh the log quickly, you need to create an Oracle 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.

You can specify that the on prebuild table statement creates a 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.

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 a clustered materialized view, you can use CUBE or ROLLUP in the group by list to create a materialized view of clustered Oracle for different levels.

Materialized View basic operations and use can view web site: http://blog.itpub.net/post/468/13318 related stuff. I will mainly explain the basics of using materialized views. For example, how to create a table space is hardly described in other materialized views. I mainly use an example,

If you have a clear understanding of the basic concepts of materialized views, you will have a better understanding of writing specific tablespace storage there.

Create a storage log space before creating the Oracle Materialized View

 
 

  
  
CREATE MATERIALIZED VIEW LOG ON mv_lvy_levytaxbgtdiv  
  
  
tablespace ZGMV_DATA  
 
 

Logs are saved in a specific tablespace.

 
 

  
  
WITH ROWID ;  
  
  
CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata  
  
  
tablespace ZGMV_DATA   
 
 

Logs are saved in a specific tablespace.

 
 

  
  
WITH ROWID,sequence(LEVYDETAILDATAID);  
  
  
CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydata  
  
  
tablespace ZGMV_DATA   
 
 

Logs are stored in specific tablespaces. The above content is an introduction to the Oracle materialized view. I hope you can gain some benefits.