General use of Oracle materialized views ____oracle

There is a project because there are more query summaries, considering the speed, so the materialized view is used.   Simply put the used to the next to tidy up. First look at the simple Create statement: Create materialized view mv_materialized_test Refresh Force on demand start with Sysdate next to_date (concat char (sysdate+1, ' dd-mm-yyyy '), ' 10:25:00 '), ' dd-mm-yyyy hh24:mi:ss ') as SELECT * from User_info; This materialized view is also a view of the refresh materialized view at 10:25 every day. The materialized view of Oracle is a database pair that includes a query result, which is a local copy of the remote data, or is used to generate a summary table based on the sum of the data tables. Materialized views store data that is based on remote tables, or it can be called snapshots. Materialized views can query tables, views, and other materialized views. Features:(1) Materialized view in a sense is a physical table (and not just a physical table), which can be user_tables query, and be confirmed; (2) Materialized view is also a segment (segment), so it has its own physical storage properties; (3) Materialized views can take up database disk space, which is supported by user_segment query results; Create materialized view mv_name as SELECT * FROM table_name Because materialized views are physically real, you can create indexes. generate data at creation time:There are two types of builds immediate and build deferred, building immediate generating data when creating materialized views. Build deferred does not generate data at creation time, and then generates data as needed. If not specified, the build immediate is the default. Refresh mode:Materialized views have two refresh modes: The refresh mode is on demand or on commit at the time of creation. On demand as the name suggests, only when the materialized view "needs" has been refreshed is it refreshing (refresh), that is, updating materialized views to ensure consistency with the base table data; On commit commit trigger, once the base table has a commit, that is, the transaction commits, immediately refresh, update the materialized view immediately, make the data and base table consistent. Generally, this method can be used to manipulate the base table at a slower rate.   When you create a materialized view without specifying it, Oracle presses on Demand mode to create it. It says the refresh mode, for how to refresh, there are three refresh methods: Full Refresh (COMPLETE): Deletes all records in the table (if it is a single table refresh, may take a truncate), and then regenerates the materialized view based on the definition of the query statement in the materialized view. Quick Refresh (FAST): An incremental refresh mechanism that refreshes all operations on the base table from the last refresh into the materialized view. Fast must create a view log based on the primary table. For the incremental refresh option, the materialized view does not work if the parse function exists in the subquery. Force: This is the default method of data refresh.   Oracle automatically determines whether the conditions for quick refreshes are met, and if they are satisfied, they are quickly refreshed, otherwise they are completely refreshed. About Quick refreshes: The quick refresh mechanism for the Oracle materialized view is accomplished through materialized view logs. Oracle can also support a quick refresh of multiple materialized views through a materialized view log. Materialized view logs can be established as ROWID or primary key types, depending on the need for rapid refresh of different materialized views. You can also choose whether to include sequence, including NEW values, and a list of the specified columns. query Rewrite (queryrewrite):These include the Enable query rewrite and disable query rewrite two. Indicates whether the materialized view you created supports query rewriting. Query rewriting means that when querying the base table of materialized views, Oracle automatically determines whether the results can be obtained by querying materialized views and, if so, avoids aggregation or join operations and reads data directly from the materialized view that has already been computed. The default is disable query rewrite. Syntax:Create materialized View view_name
Refresh [Fast|complete|force]
[
On [Commit|demand] |
Start with (start_time) next (Next_time)] assubquery; Specific Operations   Permissions required to create materialized views: grant create materialized view to user_name;    To establish a materialized view log on the source table: create materialized view log on test_table   tablespace  test_space --  Log Space    with primary key;     --  Specify the primary key type   create materialized on the target database view:create materialized view mv_materialized_test  Refresh force on demand start with sysdate next to_date (concat to_char ( Sysdate+1, ' dd-mm-yyyy '), ' 10:25:00 '), ' Dd-mm-yyyy hh24:mi:ss '  as select * from user_ info; --This materialized view refreshes     Modifies refresh time every 10:25: 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; --  refreshed at 1 o ' Day  
Build index: CREATE index idx_mmt_iu_test on mv_materialized_test (id,uname) tablespace test_space;
Delete materialized views and logs: Drop materialized view Log on test_table; --Delete materialized view log: Drop materialized view mv_materialized_test; --Delete materialized views