Materialized views in Oracle

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 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:

Creation Method Build Methods ):The options include build immediate and build deferred. Build immediate generates data when you create a materialized view, while build deferred does not generate data when you create the view, and then generates data as needed. The default value is build immediate.

Query Rewrite ):This 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 ):When a DML operation is performed on the base table, the mode in which the materialized view is synchronized with 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 NEVE *. 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:To perform a quick refresh, you need to create 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.

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

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 a Materialized View

Create materialized view log on mv_lvy_levytaxbgtdiv Tablespace ZGMV_DATA -- logs are stored in specific tablespace With rowid; Create materialized view log on tb_lvy_levydetaildata Tablespace ZGMV_DATA -- logs are stored in specific tablespace With rowid, sequence (LEVYDETAILDATAID ); Create materialized view log on tb_lvy_levydata Tablespace ZGMV_DATA -- logs are stored in specific tablespace WITH rowid, sequence (LEVYDATAID ); Create a Materialized View -- Create a Materialized View Create materialized view MV_LVY_LEVYDETAILDATA TABLESPACE ZGMV_DATA -- save the TABLESPACE Build deferred -- delayed refresh does not refresh immediately Refresh force -- if you can refresh quickly, refresh quickly; otherwise, refresh completely. On demand -- refresh as specified Start with to_date ('24-11-2005 18:00:10 ', 'dd-mm-yyyy hh24: mi: ss') -- first refresh time Next TRUNC (SYSDATE + 1) + 18/24 -- refresh interval As SELECT levydetaildataid, detaildatano, taxtermbegin, taxtermend, ...... ROUND (taxdeduct * taxpercent1, 2)-ROUND (taxdeduct * taxpercent2, 2 )- ROUND (taxdeduct * taxpercent3, 2)-ROUND (taxdeduct * taxpercent4, 2 )- ROUND (taxdeduct * taxpercent5, 2) taxdeduct, ROUND (taxfinal * taxpercent1, 2 )- ROUND (taxfinal * taxpercent2, 2)-ROUND (taxfinal * taxpercent3, 2 )- ROUND (taxfinal * taxpercent4, 2)-ROUND (taxfinal * taxpercent5, 2) taxfinal, A. levydataid, a. budgetitemcode, taxtypecode, ...... FROM tb_lvy_levydetaildata a, tb_lvy_levydata c, MV_LVY_LEVYTAXBGTDIV B WHERE a. levydataid = c. levydataid AND a. budgetdistrscalecode = B. budgetdistrscalecode AND a. budgetitemcode = B. budgetitemcode AND c. incomeresidecode = B. rcvfisccode And c. TAXSTATUSCODE = '08' And c. NEGATIVEFLAG! = '9' Delete materialized view logs -- Delete the Materialized View: -- Delete log: DROP materialized view log on mv_lvy_levytaxbgtdiv; DROP materialized view log on tb_lvy_levydetaildata; DROP materialized view log on tb_lvy_levydata; -- Delete the materialized view drop materialized view MV_LVY_LEVYDETAILDATA; -- Basically the same as the operation on the table -- the materialized view can create indexes because it is physically real. The creation method is the same as that for creating a common table.

Create base table updates and update materialized views

create materialized view lgc
    build immediate
    refresh on commit
    enable query rewrite
as

You may encounter a situation where the materialized view needs to be refreshed when the base table is deleted for a long time. You can refresh the view half a minute after the base table is updated.

ALTER MATERIALIZED VIEW user_order_mavi
  DISABLE QUERY REWRITE
  REFRESH ON DEMAND;

1. 
   
   Detailed description of Oracle Database full-text index
2. Comparison between the spool commands in Oracle
3. In-depth exposure of restrictions on Oracle index usage