General Use of Oracle materialized views

Oracle materialized view generally uses a materialized view because there are a large number of query summaries and the speed is taken into account. I simply sorted out what I used. First look at the simple creation statement: create materialized view mv_materialized_test refresh force on demand start with sysdate nextto_date (concat (to_char (sysdate + 1, 'dd-mm-yyyy'), '10: 25: 00 '), 'dd-mm-yyyy hh24: mi: ss') asselect * from user_info; -- the materialized view is refreshed at every day. The Materialized View of Oracle is a database object that includes query results. It is a local copy of remote data or is used to generate a summary table based on the sum of data tables. Materialized View stores data based on remote tables, also known as snapshots. Materialized views can be used to query tables, views, and Other materialized views. Features: (1) the materialized view is a physical table (and not just a physical table) in a sense, which can be queried by user_tables and confirmed. (2) materialized views are also segments, so they have their own physical storage properties. (3) materialized views occupy 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 because the materialized view is physically real, you can create an index. Data generated during creation: build immediate and build deferred. build immediate generates data when creating the materialized view. Build deferred does not generate data when it is created, and data will be generated later as needed. If this parameter is not specified, the default value is build immediate. Refresh mode: There are two refresh modes for materialized views: on demand or on commit when the refresh mode is created. As the name suggests, on demand REFRESH is performed only when the Materialized View "Needs" to be refreshed, that is, update the materialized view to ensure data consistency between the base table and the base table; on commit is triggered. Once the base table has a commit, that is, the transaction is committed, it is refreshed immediately, and the materialized view is updated immediately to make the data consistent with the base table. This method is generally used to perform operations on the base table at a slower speed. If the materialized view is not specified during creation, Oracle is created in on demand mode. The above is the refresh mode. For how to refresh, there are three refresh Methods: Completely refresh (COMPLETE): all records in the table will be deleted (if it is a single table refresh, may be TRUNCATE), and then regenerate the materialized view based on the definition of the query statement in the materialized view. FAST: uses the incremental refresh mechanism to refresh all operations on the base table after the last refresh to the materialized view. FAST must create view logs based on the master table. For the incremental refresh option, if an analysis function exists in the subquery, the materialized view does not work. FORCE Method: This is the default data refresh method. Oracle automatically determines whether the quick refresh condition is met. If yes, it performs a quick refresh. Otherwise, it performs a full refresh. Quick Refresh: the quick refresh mechanism of Oracle materialized view is completed by materialized view logs. Oracle also supports quick refresh of multiple materialized views through 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. QueryRewrite: 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. Syntax: create materialized view view_namerefresh [fast | complete | force] [on [commit | demand] | start with (start_time) next (next_time)] AS subquery; grant create materialized view to user_name; create a materialized view log in the source table: create materialized view log on test_table tablespace test_space -- log space with primary key; -- create materialized view: create materialized view mv_materialized_test refresh force on demand start with sysdate nextto_date (concat (to_char (sysdate + 1, 'dd-mm-yyyy'), '10: 25: 00'), 'dd-mm-yyyy hh24: mi: ss') asselect * from user_info; -- refresh and modify the materialized view at every day. refresh time: alter materialized view mv_materialized_test refresh force on demand start with sysdate next to_date (concat (to_char (sysdate + 1, 'dd-mm-yyyy'), '23:00:00 '), 'dd-mm-yyyy hh24: mi: ss '); or alter materialized view mv_materialized_test refresh force on demand start with sysdate next trunc (sysdate, 'dd') + 1 + 1/24; -- refresh index creation at every day: create index into mv_materialized_test (ID, UNAME) tablespace test_space; Delete materialized view and log: drop materialized view log on test_table; -- delete materialized view log: drop materialized view mv_materialized_test; -- delete materialized view