General use of Oracle materialized views

Normal view and materialized view is not a thing at all, say the difference is recklessly together, first understand the basic concept, the normal view is not to store any data, he only defined, in the query is converted to the corresponding definition of SQL to query, and materialized view is to convert the data into a table, the actual storage of data, so query data , you do not have to associate a large list of tables, and if the table is large, you do a lot of work in the temporary table space.

There is a project because there are more query summaries, considering the speed, so materialized views are used. Simple to use to tidy up the next. First look at the simple creation statement: Create materialized view mv_materialized_test Refresh Force on demand start with Sysdate nextto_date (concat (To_c Har (sysdate+1, ' dd-mm-yyyy '), ' 10:25:00 '), ' dd-mm-yyyy hh24:mi:ss ') asselect * from User_info; --This materialized view refreshes the materialized view at 10:25 every day and is also a kind of view. The materialized view of Oracle is a database pair image 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 based on remote tables and can also be referred to as snapshots. Materialized views can query tables, views, and other materialized views. Characteristics:

(1) Materialized view in a sense is a physical table (and not just a physical table), which can be user_tables query out, and be confirmed; (2) Materialized view is also a segment (segment), so it has its own physical storage properties; (3) Materialized views consume database disk space, which can be supported from user_segment query results; Create statement: Creation materialized view Mv_name as SELECT * from Table_ Name because the materialized view is physically real, you can create an index.

Generate data at creation time:

There

are two types: build immediate and build deferred,build immediate generate data when you create materialized views. Build deferred does not generate data when it is created, and generates data as needed later. If not specified, the default is build immediate.

Refresh mode:

materialized views have two refresh modes: Refresh mode on demand or on commit when it is created. On demand as the name implies, only when the materialized view "need" is refreshed, refresh (refresh), that is, to update the materialized view 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 materialized views immediately, making the data consistent with the base table. This method is generally used to manipulate the base table at a slower speed. When you create a materialized view without specifying it, Oracle is created in the On Demand mode. Above is the mode of refresh, for how to refresh, there are three ways to refresh: Full refresh: Delete all records in the table (if it is a single table refresh, may take the form of truncate), and then rebuild the materialized view based on the definition of the query statement in the materialized view. Fast refresh: An incremental refresh mechanism that refreshes only all operations on the base table since the last refresh to materialized views. Fast must create a view log based on the primary table. For the incremental refresh option, materialized views do not work if there are analytic functions in the subquery. Force mode: This is the default data refresh method. Oracle automatically determines whether a fast refresh condition is met, and a quick refresh if satisfied, or a full refresh. About fast refreshes: The fast refresh mechanism for Oracle materialized views is done through materialized view logs. Oracle can also support a fast refresh of multiple materialized views through a materialized view log. Materialized view logs can be built as ROWID or primary key types, depending on the need for a fast refresh of different materialized views.  You can also choose whether to include sequence, including NEW values, and a list of specified columns.

Query Rewriting (queryrewrite):

includes two types of enable query rewrite and disable query rewrite. Indicates whether the materialized view that was created supports query rewriting, respectively. Query rewriting means that when querying a materialized view's base table, Oracle automatically determines whether the query materialized views can be used to get results, and if so, avoids aggregation or join operations and reads data directly from the materialized views that have been computed.  The default is disable query rewrite.

Grammar:

Create materialized View view_name
Refresh [Fast|complete|force]
[
On [Commit|demand] |
Start with (start_time) next (next_time)]as subquery;

Specific operation

  Permissions required to create a materialized view:grant create materialized view to user_name;   Creating materialized view logs in 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 nextto_date (Concat (To_char ( Sysdate+1, ' dd-mm-yyyy '), ' 10:25:00 '), ' dd-mm-yyyy hh24:mi:ss ')  asselect * from user_ info; --This materialized view is refreshed at 10:25 every day    modified 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;  --  1 points per day refresh    Build index: Create index idx_mmt_iu_teston mv_materialized_test (ID, UNAME)   tablespace test_space;   Delete materialized views and logs:  drop materialized view  log on test_table;    --deleting materialized view logs:  drop materialized view  mv_materialized_test; --Deleting materialized views

General use of Oracle materialized views