The general usage of materialization materialized views are a special physical table, and the materialized view is relative to the normal view. The normal view is a virtual table, the limitations of the application are large, any query on the view, Oracle is actually converted into a query of the views SQL statement. This has no substantial benefit in improving overall query performance.
1. The type of materialized view on DEMAND, on COMMIT. The difference between the two is that the Refresh method is different, on demand as the name implies, only in the materialized view "need" is refreshed, only to refresh (refresh), that is, to update the materialized view to ensure consistency with the base table data, and on commit is that once the base table has a commit, that is, the transaction commits , the materialized view is immediately refreshed to make the data and the base table consistent.
Materialized views can be divided into the following three types: a materialized view containing aggregates, a materialized view that contains only connections, and a nested materialized view. The limitations of the fast refresh of three materialized views are very different, but not the other way. You can specify several options when you create a materialized view, following a brief description of several major choices:
Creation mode (Build Methods): Includes two types of build immediate and build deferred. Build immediate generates data when a materialized view is created, and build deferred does not generate data when it is created, and generates data as needed later. The default is build IMMEDIATE.
Query Rewrite: Includes the Enable query Rewrite and disable query Rewrite two kinds. 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.
When you create a materialized view, you can specify an order BY statement so that the resulting data is saved in a certain order. However, this statement is not written into the definition of materialized views and is not valid for subsequent refreshes.
2, on demand materialized views the creation of materialized views is inherently complex and requires optimization of parameter settings, especially for large production database systems. But Oracle allows this to be done in the simplest way, similar to a normal view, so it inevitably involves a default value problem. This means that the default value of Oracle's important definition parameters for materialized views is something we need to pay special attention to. Features of materialized views:
(1) Materialized view in a sense is a physical table (and not just a physical table), which can be user_tables query out, and get support;
(2) Materialized view is also a kind of segment (segment), so it has its own physical storage properties;
(3) Materialized views Occupy database disk space, which can be supported by user_segment query results;
To create a statement:
Sql> create materialized View mv_name as SELECT * FROM table_name;
By default, if no Refresh method and refresh mode are specified, Oracle defaults to force and demand.
How does the materialized view's data update with the base table?
Oracle provides two ways to manually refresh and refresh automatically, by default, by manual refresh. That is to say, by manually executing a system-level stored procedure or package provided by Oracle, the materialized view is consistent with the base table data. This is the most basic way to refresh. Automatic refresh, in fact, Oracle will create a job, through the job to invoke the same stored procedure or package, to implement.
The characteristics of On demand materialized view and its difference from on commit materialized view, that is, the former does not refresh (manual or automatic) does not update materialized view, the latter does not refresh will also update materialized view, as long as the base table has a commit.
Create a materialized view of a timed refresh (specifies that materialized views are refreshed once a day):
Sql> Create materialized view mv_name Refresh Force on demand start with sysdate next sysdate+1;
The materialized views created above are refreshed daily, but no refresh time is specified, if you want to specify a refresh time (such as a timed refresh once every night 10:00):
Sql> Create materialized view mv_name Refresh Force on demand start with Sysdate next To_date (Concat (To_char (sysdate +1, ' dd-mm-yyyy '), ' 22:00:00 '), ' dd-mm-yyyy hh24:mi:ss ');
3, on commit materialized view on commit materialized view is created, and the materialized view created on demand above is not very different. Because on demand is the default, on commit materialized view, you need to add another parameter.
It is important to note that only on COMMIT cannot be specified at the time of definition, and that a parameter is included. To create an on commit materialized view:
Sql> Create materialized view mv_name Refresh force on commit as SELECT * FROM table_name;
Note: In the actual creation process, the base table needs to have the primary key constraint, otherwise it will error (ORA-12014).
4, materialized view refresh refresh: When the base table has a DML operation, the materialized view is in which way and the base table to synchronize.
There are two modes of refresh: On demand and on COMMIT.
There are four ways to refresh: FAST, complete, force, and never. Fast refresh takes an incremental refresh and refreshes only the changes made since the last refresh. Complete refreshes the entire materialized view with a full refresh. If you choose force Mode, Oracle will determine whether it can be refreshed quickly, or if it can be fast, or else complete. Never refers to materialized views without any refreshes.
For a materialized view that has already been created, you can modify its refresh mode by modifying the refresh mode of materialized view Mv_name to refresh once every night 10 o'clock:
Sql> alter materialized view Mv_name refresh Force on demand start with Sysdate next To_date (Concat (To_char (sysdate+1, ' Dd-mm-yyyy '), ' 22:00:00 '), ' dd-mm-yyyy hh24:mi:ss ');
5. Materialized view log if you need to refresh quickly, you need to establish 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.
You can indicate that the on Prebuild table statement establishes a materialized view on an existing sheet. In this case, the materialized view and the table must have the same name. When you delete a materialized view, a table with the same name is not deleted. Query rewriting for this materialized view requires that the parameter query_rewrite_integerity must be set to trusted or stale_tolerated.
6. Materialized view partitioning and partition-based materialized views can support partition change tracking (PCT). Materialized views with this feature can still be quickly refreshed when the base table is partitioned for maintenance operations. For clustered materialized views, you can use cube or rollup in the group by list to create aggregated materialized views of different levels.
Ii. materialized views and data migrations Oracle's materialized views provide powerful capabilities to pre-compute and save the results of more time-consuming operations such as table joins or aggregates, so that when queries are executed, these time-consuming operations are avoided and results are obtained quickly. Materialized views are similar in many respects to indexes: the purpose of using materialized views is to improve query performance; Materialized views are transparent to the application, adding and removing materialized views does not affect the correctness and validity of the SQL statements in the application; Materialized views need to occupy storage space, and materialized views should be refreshed when the base table changes.
such as how to build on a particular table space, these in other materialized views are almost none of the introduction. This is mostly done with an example of what I do, and if the basic concepts of materialized views are clear, it is more obvious that there is a specific tablespace stored there.
1. Simple test CREATE table and Mview log on master site
Sql> CREATE TABLE Stu (ID varchar2 (TEN) primary key, name VARCHAR2 (20));
Table created.
Sql> create materialized view Log on Stu;
Materialized view log created.
Create Mview on MV site
Sql> Create materialized view STU_MV refresh fast start with Sysdate next sysdate+1/1440 with primary key as SELECT * F Rom [email protected]_vm9;
Materialized view created.
Sql> select Job,log_user,last_date,last_sec,next_date,next_sec,interval,what from User_jobs;
JOB log_user last_date last_sec next_date next_sec INTERVAL What
--- --------- ----------- --------- ----------- ---------- -------------- -----------------------------------------
SEAGULL 2008-2-18 1 14:41:43 2008-2-18 1 14:42:43 sysdate+1/1440 dbms_refresh.refresh (' SEAGULL '. " STU_MV "');
Sql> select * from tab;
Tname Tabtype Clusterid
------------------------------ ------- ----------
STU_MV TABLE
Update the master table on the master site:
Sql> INSERT into STU (id,name) VALUES (' 56 ', ' 555555555555 ');
1 row created.
Sql> commit;
Wait 1 minutes to check the MV site.
Sql> select * from STU_MV;
ID NAME
---------- --------------------
56 555555555555
2. Cross-version Data migration utilizes prebuilt MV for cross-platform, cross-version data migrations. This method is implemented for the Table object to be migrated, need to have a primary key, for the MV Refresh, for the table that meets this requirement, create a MV Log on the source table, create a structure-like table on the target database, and then create the MV in prebuilt mode on the target table, with a full refresh for the first time, After the use of incremental refresh, and so on when the real switch, only need to refresh the increment of the log, delete mv, keep the target table. Examples of basic ideas:
Create a table and Mview log on the source library
Sql> CREATE TABLE Big_t1 as SELECT * from Dba_objects;
Table created.
Sql> Select COUNT (1) from BIG_T1;
COUNT (1)
----------
6170
Sql> create materialized view Log on BIG_T1;
Materialized view log created.
Create the same table as the table on the target database, and create the prebuilt MV on the table:
Sql> CREATE TABLE Big_t1 as SELECT * FROM [email protected]_vm9 where 1=2;
Table created.
Sql> Select COUNT (1) from BIG_T1;
COUNT (1)
----------
0
Sql> create materialized view big_t1 on prebuilt table Refresh fast as SELECT * from [email protected]_vm9;
Materialized view created.
Do a full refresh and an incremental refresh
sql> exec Dbms_mview.refresh (' big_t1 ', ' complete ');
PL/SQL procedure successfully completed.
Sql> Select COUNT (1) from BIG_T1;
COUNT (1)
----------
6170
At this point, the table of the source library has changed during the full refresh process.
sql> INSERT INTO BIG_T1 (Object_id,owner) VALUES (99991, ' test ');
1 row created.
Sql> commit;
Commit complete.
Do incremental refreshes again
Sql> Select COUNT (1) from BIG_T1;
COUNT (1)
----------
6170
sql> exec Dbms_mview.refresh (' big_t1 ');
PL/SQL procedure successfully completed.
Sql> Select COUNT (1) from BIG_T1;
COUNT (1)
----------
6171
Stop switching, do the last refresh, and then delete the source Library's Mview log and Target library's Mview:
sql> exec Dbms_mview.refresh (' big_t1 ');
PL/SQL procedure successfully completed.
Sql> drop materialized view big_t1;
Materialized view dropped.
Sql> Select COUNT (1) from BIG_T1;
COUNT (1)
----------
6171
The deleted Mview (BIG_T1) is prebuilt mv, so the Mview is removed and the corresponding table is not deleted. If Mvnew (STU_MV) is removed, the Mview is deleted because it is a normal MV, there is no corresponding table.
Sql> drop materialized view stu_mv;
Materialized view dropped.
Sql> select * from tab;
Tname Tabtype Clusterid
------------------------------ ------- ----------
Big_t1 TABLE
3. Create a stored log space sql> create materialized VIEW log on Mv_lvy_levytaxbgtdiv
Tablespace Zgmv_data--logs are saved in a specific table space
With ROWID;
Sql> CREATE materialized VIEW LOG on Tb_lvy_levydetaildata
Tablespace Zgmv_data--logs are saved in a specific table space
With Rowid,sequence (Levydetaildataid);
Sql> CREATE materialized VIEW LOG on Tb_lvy_levydata
Tablespace Zgmv_data--logs are saved in a specific table space
With Rowid,sequence (Levydataid);
4. Then create materialized view sql> create materialized view Mv_lvy_levydetaildata
Tablespace zgmv_data--Save table space
BUILD DEFERRED--Deferred refresh does not refresh immediately
Refresh Force--refreshes quickly if you can quickly refresh, otherwise full refresh
On demand--refresh in the specified manner
Start with To_date (' 24-11-2005 18:00:10 ', ' dd-mm-yyyy hh24:mi:ss ')--first time refresh
Next TRUNC (sysdate+1) +18/24--Refresh Interval
As
SELECT Levydetaildataid, Detaildatano, Taxtermbegin, Taxtermend,
......
ROUND (Taxdeduct * taxpercent1, 2)-ROUND (Taxdeduct * taxpercent2, 2)-
ROUND (Taxdeduct * taxpercent3, 2)-ROUND (Taxdeduct * taxpercent4, 2)-
ROUND (Taxdeduct * taxpercent5, 2) taxdeduct, ROUND (taxfinal * taxpercent1, 2)-
ROUND (Taxfinal * taxpercent2, 2)-ROUND (Taxfinal * taxpercent3, 2)-
ROUND (Taxfinal * taxpercent4, 2)-ROUND (Taxfinal * taxpercent5, 2) taxfinal,
A.levydataid, A.budgetitemcode, Taxtypecode,
......
From Tb_lvy_levydetaildata A, tb_lvy_levydata C, Mv_lvy_levytaxbgtdiv b
WHERE A.levydataid = C.levydataid
and A.budgetdistrscalecode = B.budgetdistrscalecode
and A.budgetitemcode = B.budgetitemcode
and C.incomeresidecode = B.rcvfisccode
and c.taxstatuscode= ' 08 '
and c.negativeflag!= ' 9 '
5, delete materialized view log materialized view log often because the materialized view has not been refreshed for a long time, or a bulk data change of the base table becomes very large, this will affect the refresh performance of materialized view, so for this situation need to process the materialized view log, reduce the high watermark of materialized view Log table.
The materialized view log records all the increment, delete, and change operations of the base table, and when the materialized view finishes performing a quick flush operation, the materialized view is refreshed from the materialized view log and the records of other materialized views do not need to be refreshed. If one of the materialized views is not refreshed, the materialized view log becomes larger and bigger.
There is also a situation where a large amount of data is inserted into a table, a large amount of data is deleted, or a column in a table is uniformly updated to a value, which results in a large number of records in the materialized view log.
However, the increase of materialized view log will inevitably affect the refresh speed of materialized view. On the one hand, materialized views in the refresh to scan the materialized view log, on the other hand, materialized view after the introduction, also to clear the materialized view log records, still want to scan the materialized view log, so the size of the materialized view log directly affects the speed of materialized view fast refresh. More importantly, when the high water level of the materialized view log grows to a high position, the materialized view still takes a long time to refresh, even if there is little or no record in the materialized view log.
sql> DROP materialized view Log on Mv_lvy_levytaxbgtdiv;
sql> DROP materialized view Log on Tb_lvy_levydetaildata;
sql> DROP materialized view Log on Tb_lvy_levydata;
6, delete materialized view sql> drop materialized view mv_lvy_levydetaildata;
The basic and the operation of the table is consistent, materialized view because it is physical real existence, so you can create an index, created in the same way as the ordinary table creation.
Oracle materialized view summary materialized view 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. It is mainly used in data warehouse and decision support system.
Typically, materialized views are referred to as primary tables (during replication) or schedules (in the Data Warehouse). For replication, materialized views allow you to maintain a copy of the remote data locally, which is read-only. If you want to modify the local copy, you must use the Advanced Replication feature. When you want to extract data from a table or view, you can extract it from a materialized view. For data warehouses, the materialized views created are typically aggregated views, single-table aggregation views, and connection views.
The materialized view stores his physical structure in his own segment, which can be indexed and partitioned. The query does not have to match exactly the SQL statement used to create the materialized view, and the optimizer can dynamically override a query that is similar to the original definition so that the materialized view is used instead of the actual table, which automatically occurs and is transparent to the user.
1. Use several configuration steps before materialized views (1) To determine those statements to create materialized views.
(2) Decide whether to keep the view synchronized with the underlying table data.
If you do not sync, you can choose the following three ways to refresh:
Complete: When the refresh starts, truncate the materialized view and then reinsert the fill data from the underlying table.
FAST: Refreshes only the data that changed after the underlying table was last refreshed. Use the view's log data or ROWID to complete.
Force: The default way. Use fast first, not the complete method.
(3) Set the parameters of the Init.ora:
Job_queue_processes, must be set greater than 1.
Query_rewrite_enabled, when set to True, allows the query to be rewritten dynamically.
Query_rewrite_integrity, determines how much data consistency should be observed when accessing materialized views.
Optimizer_mode, must be set into a CBO in some way.
With a materialized view, users only have permissions on the underlying table.
2. Create materialized view sql>create materialized view Emp_by_district
Tablespace Mview_data
Build Immediate
Refresh Fast
Enable Query Rewrite
As
Select D.id,count (e.last_name) from distributor Dist,district D,employee E
Where e.id = dist.manager_id
and D.id dist.district_id
Group by D.id;
The following are the common syntax for Oracle to create materialized views, with the meanings of each parameter as follows:
1, refresh [Fast|complete|force] view of the way
Fast: Incremental refresh. Assuming that the time of the previous refresh is T1, when you refresh the materialized view using fast mode, only the data that is changed by the primary table is added to the view T1 to the current time period. In order to record this change, a materialized view Log table is also required to establish an incremental refresh materialized view. Create materialized view Log on (primary table name).
Complete: Refresh all. Equivalent to re-executing the query statement that created the view.
Force: This is the default data refresh method. When you can use fast mode, the data refresh will be in fast mode, otherwise use the complete method.
2, MV Data refresh time
On demand: Refresh when the user needs to refresh, this requires the user to manually refresh the data (you can also use the job timed refresh)
On commit: Refreshes the data in the MV immediately when data is submitted in the main table;
Start ... : Starts at a specified time and is refreshed once at intervals (specified by next);
3, Build immediate there are altogether three options
(1) Build immediate: Create materialized views and populate the view data immediately with the data executed by the current command.
(2) Build deferred: Only materialized views are created and no data is populated between the first refreshes.
(3) No prebuilt table, using pre-existing, contains a table with existing data in the view definition, rather than creating a new structure to hold the data.
If refresh fast on commit or refresh complete on commit is created, it will be refreshed when the underlying table is submitted. To enable or disable materialized views, you need to have query rewrite or global query rewrite permissions.
3, refresh materialized view automatic refresh:
(1) Use the commit option.
(2) Use Dbms_mview to schedule automatic refresh time.
Manual refresh:
Sql>execute Dbms_mview.refresh (' emp_by_district '); --Refreshes the specified materialized view
Sql>execute dbms_mview.refresh_defresh_dependent (' EMPLOYEE '); --refreshes all materialized views that take advantage of the table
Sql>execute dbms_mview.refresh_all_mviews; -Refreshes all materialized views in this mode that have not been refreshed since the last refresh.
4. Disable materialized view-Modify the query_rewrite_enabled parameter of the Init.ora parameter to Flase and restart the instance.
-use alter system set query_rewrite_enabled = Flase;
-use alter session set query_rewrite_enabled = Flash; in a modified session.
-Use norewrite hints.
5, delete materialized views sql>drop materialized view emp_by_district;
