[Terry Notes] materialized view basic learning

1. Summary of Materialized View definitions:
The materialized view is a database object that contains a query result (the system regularly refreshes the data). The materialized view is not read during use, it is used to pre-calculate and save the time-consuming operation results such as table join or aggregation, which greatly increases the reading speed during queries. It is especially suitable for extracting some information from the big data table and using the data link connection table. In addition, the rewrite mechanism is used in the data warehouse. for users, Oracle selects the appropriate materialized view for query and does not need to re-write the SQL.
 
Materialized views can also be used for local replication of remote data. In this case, materialized view storage can also be called snapshots. But it is used to implement inter-database synchronization. In general, materialized views are used as "primary tables" for replication, and are used in data warehouses as "detail tables ".

Its physical significance:

(3) The materialized view occupies the disk space of the database. This is evidenced by the query results of user_segment;

 

Parameters related to materialized views:

COMPATIBLE must be later than 8.1.0

QUERY_REWRITE_ENABLED = TRUE enable query rewriting (default

QUERY_REWRITE_INTEGRITY =

ENFORCED-only restrictions and rules ENFORCED and guaranteed by Oracle are used for query (default );

TRUSTED-in addition to Oracle's mandatory and guaranteed constraints and rules, the query can also be rewritten using any relationships between user-defined data;

STALE_TOLERATED-even if Oracle knows that the data in the materialized view has expired (not synchronized with fact tables, etc.), the query is overwritten.

JOB_QUEUE_PROCESSES, must be set to greater than 1.

OPTIMIZER_MODE, which must be set to a CBO method.

 
Ii. Create materialized views
1. Create a materialized view log first when creating a materialized view, mainly to make the materialized view refresh fast. It is best to put the log in a separate tablespace.

  t1      t1   pk_t1   ( materialized    materialized  mv1 refresh force         materialized      materialized  mv1;

 

----- Create a materialized view log syntax

 MATERIALIZED     rowid;

Primary key and rowed
The primary key option generates a primary key materialized view, that is, the materialized view is based on the primary key of the PRIMARY table.
Primary key is the default option. To generate a primary key clause, you should define a primary key in the PRIMARY table. Otherwise, you should use a ROWID-based materialized view.
The materialized view of the primary key allows you to identify the primary table of the materialized view without affecting the availability of incremental refresh of the materialized view.
The Rowid materialized view has only one primary table and cannot contain "Distinct, aggregate function, Group by, subquery, join, SET operation"


----- Create materialized view syntax

 MATERIAILZED deferred  disable QUERY REWRITE  fastcomplete   demand  to_date(, )  TRUNC(SYSDATE)     …

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.
1) on demand (default) indicates that the materialized view is refreshed when the user needs it. You can manually refresh the view by DBMS_MVIEW.REFRESH start with next or by regularly refreshing the JOB.
2) on commit indicates that the Materialized View refreshes the base table while submitting DML operations.
-- There are four refresh methods:FAST, COMPLETE, FORCE, and NEVER.
1) FAST refresh uses incremental refresh. Only modifications made since the last refresh are refreshed. You need to create a materialized view log of the primary key or rowid type.
2) The COMPLETE refresh completely refreshes the entire Materialized View (truncate first ).
3) FORCE (default): determines whether a quick Refresh can be performed during refresh. If yes, use the FAST method; otherwise, use the COMPLETE method.
4) NEVER indicates that the materialized view is not refreshed.
 
Refresh example:

SQL  materialized  mv_name refresh force  demand start  sysdate  sysdate  materialized  mv_name refresh force  demand start  sysdate  to_date( concat( to_char( sysdate,),),

 

Other advanced materialized views (to be continued ):
Materialized views based on physical tables: 
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 rewriting of this materialized view requires that the QUERY_REWRITE_INTEGRITY parameter must be set to trusted or stale_tolerated.
Contains the materialized view of aggregation;
Only the connected materialized views are included;
Nested materialized view.