Oracle Materialized View

Materialized View, which is 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. Materialized views are similar to indexes in many aspects: materialized views are used to improve query performance. materialized views are transparent to applications. adding or deleting materialized views does not affect applications. Program The correctness and validity of the SQL statement in. The materialized view occupies storage space. When the base table changes, the materialized view should also be refreshed. Materialized views include clustered materialized views, connected materialized views, and nested materialized views. However, the restrictions for quick refresh of the three materialized views are quite different, while those for other aspects are not. You can specify multiple options when creating a materialized view. The main options are as follows: 1. Creation method (build methods): 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. 2. query rewrite: 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. 3. Refresh: Specifies the mode in which the materialized view is synchronized with 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. 4. materialized view log: If you need to refresh the log quickly, 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. 5. 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. Example 1: Create materialized view MV _ dop_test refresh force on demand as select dsso. dop_id, Soo. work_center_no, Soo. operation_description from shop_order_operation Soo, dop_supply_shop_ord dsso where Soo. order_no = dsso. order_no and Soo. release_no = dsso. release_no and Soo. sequence_no = dsso. sequence_no; --- the creation and deletion of materialized views are the same as those of tables or view DDL: Drop materialized view log on materialized_view_log_name; create a stored log space when creating a materialized view (storing changes to objects in the materialized view) Create materialized view log on dop_supply_shop_ord_tab -- (base table name) tablespace ifsapp_data -- logs are saved in the specified tablespace with rowid; drop materialized view mv_materialized_view_name; Example 2: Create materialized view mv_dop_test tablespace ifsapp_data -- save the tablespace build deferred -- delayed refresh does, this initial view usually has no data refresh force -- if it can be refreshed quickly, it will be refreshed quickly; otherwise, it will be completely refreshed with rowid -- refresh Based on rowid (primary key by default) on Demand -- refresh start with to_date ('2017-12-11 13:20:51 ', 'yyyy-MM-DD hh24: MI: ss') Next sysdate + 2008 as select dsso as specified. dop_id, Soo. work_center_no, Soo. operation_description from shop_order_operation Soo, dop_supply_shop_ord dsso where Soo. order_no = dsso. order_no and Soo. release_no = dsso. release_no and Soo. sequence_no = dsso. sequence_no

 

Bytes ------------------------------------------------------------------------------------------------------------------------

Create materialized view mv_test_1
Refresh force on commit -- synchronously refresh when submitting. If it is demand, it is manually refreshed.
As
Select t.int _ AGID, sum (t.int _ FYP)
From agicomphist t
Group by t.int _ AGID;

Bytes ------------------------------------------------------------------------------------------------------------------------