I. general usage of Materialized views are a special physical table. Materialized views are relative to normal views. A common view is a virtual table, which has many application limitations. Oracle converts any View query to a view SQL statement query. This improves the overall query performance without substantial benefits.
I. general usage of Materialized views are a special physical table. Materialized views are relative to normal views. A common view is a virtual table, which has many application limitations. Oracle converts any View query to a view SQL statement query. This improves the overall query performance without substantial benefits.
I. general usage of materialized views
Materialized View is a special physical table. The Materialized view is relative to a common view. A common view is a virtual table, which has many application limitations. Oracle converts any View query to a view SQL statement query. This improves the overall query performance without substantial benefits.
1. materialized view types: on demand and on commit. The difference between the two is that the REFRESH method is different. As the name suggests, the REFRESH is performed only when the materialized view is refreshed, that is, the materialized view is updated, to ensure data consistency between the base table and the base table. on commit indicates that once the base table has a COMMIT, that is, the transaction is committed, the data is refreshed immediately, and the materialized view is updated immediately, so that the data and the base table are consistent.
Materialized views can be divided into three types: materialized views that contain aggregation, materialized views that only contain connections, and nested materialized views. The restrictions for quick refresh of the three materialized views are very different, but not for other aspects. You can specify multiple options when creating a materialized view. The following describes the main options:
Build Methods: includes 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: 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.
When creating a materialized view, you can specify the order by statement to save the generated data in a certain ORDER. However, this statement will not be written into the definition of the materialized view, and will not be effective for subsequent refreshes.
2. The creation of materialized views in the on demand materialized view is complex and requires parameter settings optimization, especially for large production database systems. However, Oracle allows you to do this in the simplest way, similar to a common view, so it will inevitably involve default values. That is to say, we need to pay special attention to the handling of the default values of important definition parameters for materialized views in Oracle. Features of materialized views:
(1) materialized view is a physical table (and not just a physical table) in a sense, which can be queried by user_tables;
(2) materialized views are also segments, so they have their own physical storage attributes;
(3) The materialized view occupies the disk space of the database. This is evidenced by the query results of user_segment;
Create statement:
SQL> create materialized view mv_name as select * from table_name;
By default, if the refresh method and refresh mode are not specified, Oracle uses FORCE and DEMAND by default.
How does the Materialized View data update with the base table?
Oracle provides two methods: manual refresh and automatic refresh. Manual refresh is used by default. That is to say, we can manually execute a system-level stored procedure or package provided by an Oracle database to ensure data consistency between the Materialized View and the base table. This is the most basic refresh method. Auto-Refresh means that Oracle will create a job through which the same stored procedure or package is called for implementation.
The characteristics of the on demand materialized view and its differences with the on commit materialized view, that is, the former does not update the materialized view without refreshing (manual or automatic), while the latter updates the materialized view without refreshing, -- as long as the base table has a COMMIT.
Create a materialized view that is refreshed regularly (specify the materialized view to be refreshed once a day ):
SQL> create materialized view mv_name refresh force on demand start with sysdate next sysdate + 1;
The created materialized view refresh every day, but does not specify the refresh time. If you want to specify the refresh time (for example, refresh regularly at every night ):
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. The on commit materialized view is not much different from the materialized view created on demand above. Because on demand is the default, you need to add another parameter to the on commit materialized view.
It should be noted that you cannot specify only on commit when defining it, but you still need to include a parameter. Create a materialized view on commit:
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 a primary key constraint, otherwise an error (ORA-12014) will be reported ).
4. Refresh: the method used to synchronize the materialized view with the base table after the DML operation is performed on the base table.
There are two refresh modes: on demand and on commit.
There are four refresh Methods: FAST, COMPLETE, FORCE, and NEVER. FAST refresh uses incremental refresh. Only the modifications made since the last refresh are refreshed. COMPLETE refresh completely refreshes the entire materialized view. If the FORCE method is selected, Oracle determines whether a quick Refresh can be performed during refresh. If yes, it adopts the FAST method; otherwise, it adopts the COMPLETE method. NEVER indicates that the materialized view is not refreshed.
You can modify the refresh method of the created materialized view. For example, you can change the refresh method of the materialized view mv_name to refresh every night:
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. If you need to refresh materialized view logs quickly, you need to create materialized view logs. 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.
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_REWRITE_INTEGERITY parameter must be set to trusted or stale_tolerated.
6. materialized view partitions and partition-based materialized views Support partition change tracking (PCT ). In a materialized view with this feature, you can perform a quick refresh operation after the base table performs partition maintenance. For clustering materialized views, you can use CUBE or ROLLUP in the group by list to create clustering materialized views of different levels.
Ii. materialized views and data migration
The materialized views of Oracle provide powerful functions that can be used to pre-calculate and save the results of time-consuming operations such as table join or aggregation. In this way, when performing a query, you can avoid these time-consuming operations and get results quickly. Materialized views are similar to indexes in many aspects: materialized views are used to improve query performance, while materialized views are transparent to applications, adding or deleting materialized views does not affect the correctness and validity of SQL statements in applications. materialized views occupy storage space. materialized views should also be refreshed when the base table changes.
For example, how to create a table space is hardly described in other materialized views. I mainly use an example. If you have a clear understanding of the basic concepts of the materialized view, you will understand where to write a specific tablespace storage.
1. Create a table and a mview log on the master site.
SQL> create table stu (id varchar2 (10) 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 * from stu @ to_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
------------------------------------------------------------------------------------------------------------
21 SEAGULL 14:41:43 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', '123 ');
1 row created.
SQL> commit;
Wait for 1 minute and check on the mv site
SQL> select * from stu_mv;
ID NAME
------------------------------
56 555555555555
2. Cross-version data migration uses prebuilt mv for cross-platform and cross-version data migration. The implementation principle of this method is that a primary key is required for the table object to be migrated for music refresh. For tables that meet this requirement, create music video logs on the source table, create a table with the same structure on the target database, and then use the prebuilt method to create the mv on the target table. The first time the table is completely refreshed, then incremental refresh is used, you only need to refresh the incremental log, delete the mv, and keep the target table. Example of the basic idea:
Create tables and mview logs on the source database
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 a table with the same name as the table in the target database and create a prebuilt mv on the table:
SQL> create table big_t1 as select * from big_t1 @ to_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 big_t1 @ to_vm9;
Materialized view created.
Complete refresh and 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
In this case, the table of the source database changes again during the complete refresh process.
SQL> insert into big_t1 (object_id, owner) values (99991, 'test ');
1 row created.
SQL> commit;
Commit complete.
Then perform incremental refresh.
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, perform the last refresh, and then delete the mview log of the source database and the mview of the target database:
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 mview (big_t1) deleted here is the prebuilt mv. Therefore, deleting the mview does not delete the corresponding table. If mvnew (stu_mv) is deleted because it is a normal mv, The mview is deleted and no corresponding table exists.
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_levytaxbgtp
Tablespace ZGMV_DATA -- logs are stored in specific tablespace
With rowid;
SQL> CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata
Tablespace ZGMV_DATA -- logs are stored in specific tablespace
With rowid, sequence (LEVYDETAILDATAID );
SQL> CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydata
Tablespace ZGMV_DATA -- logs are stored in specific tablespace
WITH rowid, sequence (LEVYDATAID );
4. create a materialized view SQL> create materialized view MV_LVY_LEVYDETAILDATA
TABLESPACE ZGMV_DATA -- save the TABLESPACE
Build deferred -- delayed refresh does not refresh immediately
Refresh force -- if you can refresh quickly, refresh quickly; otherwise, refresh completely.
On demand -- refresh as specified
Start with to_date ('24-11-2005 18:00:10 ', 'dd-mm-yyyy hh24: mi: ss') -- first refresh time
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 logs often become large because the materialized view is not refreshed for a long time, or a batch data change to the base table may affect the refresh performance of the materialized view, therefore, the materialized view logs must be processed to reduce the high watermark line of the materialized view log table.
The materialized view log records all addition, deletion, and modification operations on the base table. After the materialized view performs a quick refresh operation, the record that the materialized view has been refreshed from the materialized view log and does not need to be refreshed from other materialized views is deleted. If one of the materialized views is not refreshed, the materialized view logs will become larger and larger.
Another scenario is that a table inserts a large amount of data, deletes a large amount of data, or updates a column in the table to a value, this operation will generate a large number of records in the materialized view log.
The increase in materialized view logs will inevitably affect the refresh speed of materialized views. On the one hand, materialized view logs must be scanned during refresh. On the other hand, after refresh the introduction, materialized view logs must also be cleared, and materialized view logs must still be scanned, therefore, the log size of the materialized view directly affects the refresh speed of the materialized view. More importantly, once the high level of Materialized View logs increases to a very high level, even if there are few or even no records in the materialized view logs in the future, materialized views still take a long time to refresh.
SQL> DROP materialized view log on mv_lvy_levytaxbgtp;
SQL> DROP materialized view log on tb_lvy_levydetaildata;
SQL> DROP materialized view log on tb_lvy_levydata;
6. Delete the materialized view SQL> drop materialized view MV_LVY_LEVYDETAILDATA;
The operations on the table are basically the same. Because the materialized view is physically real, you can create indexes in the same way as creating common tables.
Iii. Summary of ORACLE Materialized View
A materialized view is a database object that contains a query result. 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. It is mainly used in data warehouses and decision support systems.
A materialized view is usually called a master table (during replication) or a schedule (in a data warehouse ). For replication, materialized views allow you to locally maintain copies of remote data, which are read-only. To modify a local copy, you must use the advanced copy function. When you want to extract data from a table or view, you can extract data from the materialized view. For a data warehouse, the created materialized view is usually an aggregation view, a single table aggregation view, and a connection view.
Materialized View stores its physical structure in its own segment, which can be indexed and partitioned. The query does not have to completely match the SQL statement used to create the materialized view. The optimizer can dynamically rewrite a query similar to the original definition so that the materialized view can be used to replace the actual table. This query rewriting occurs automatically, it is transparent to users.
1. Several configuration steps before using the materialized view (1) determine the statements to create the materialized view.
(2) determine whether to maintain data synchronization between the view and the basic table.
If the data is not synchronized, you can select the following three refresh methods:
COMPLETE: When the refresh is started, truncate the materialized view first, and then re-insert the filled data from the base table.
FAST: only the data changed after the last refresh of the base table is refreshed. Use the view log data or ROWID.
FORCE: the default mode. Use FAST first. If not, use the COMPLETE method.
(3) set the init. ora parameter:
JOB_QUEUE_PROCESSES, must be set to greater than 1.
QUERY_REWRITE_ENABLED: when it is set to TRUE, the query can be rewritten dynamically.
QUERY_REWRITE_INTEGRITY to determine the degree of data consistency when accessing the materialized view.
OPTIMIZER_MODE, which must be set to a CBO method.
To use a materialized view, you only need to have permissions on the basic table.
2. create a 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 common syntaxes used to create materialized views in Oracle. The meanings of parameters are as follows:
1. refresh [fast | complete | force] view refresh Method
Fast: Incremental refresh. assume that the previous refresh time is t1. When you use the fast mode to refresh the materialized view, only the data that has changed from t1 to the current time period is added to the view. to record this change, a materialized view log table is also required to create an incremental refresh materialized view. Create materialized view log on (primary table name ).
Complete: refresh all. It is equivalent to executing a query statement to create a view again.
Force: This is the default data refresh method. When the fast mode is available, data is refreshed in the fast mode; otherwise, the complete mode is used.
2. MV data refresh time
On demand: refresh the data when the user needs to refresh. Here, the user is required to refresh the data manually (or use the job to refresh regularly)
On commit: when data is submitted in the master table, the data in the MV is refreshed immediately;
Start ...... : Refresh Every time (specified by next) from the specified time;
3. Build immediate has three options
(1) Build immediate: Create a materialized view and use the data executed by the current command to immediately fill in the View data.
(2) Build deferred: only the materialized view is created, and data is not filled during the first refresh.
(3) No prebuilt table: Use a table that already exists and contains existing data in the view definition, instead of creating a new structure to store data.
If it is created by refresh fast on commit or refresh complete on commit, the basic table will be refreshed when it is submitted. To enable or disable materialized views, you must have the query rewrite or global query rewrite permissions.
3. Automatically refresh the Materialized View:
(1) Use the commit option.
(2) Use dbms_mview to schedule automatic refresh time.
Manual Refresh:
SQL> execute dbms_mview.refresh ('emp_by_district '); -- refresh the specified Materialized View
SQL> execute dbms_mview.refresh_defresh_dependent ('employe');-Refresh all materialized views of the table.
SQL> execute dbms_mview.refresh_all_mviews;-refreshes all materialized views 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 set it to flase and restart the instance.
-Use alter system set query_rewrite_enabled = flase to dynamically modify the settings.
-Use alter session set query_rewrite_enabled = flash; to modify the session.
-Use the norewrite prompt.
5. Delete the materialized view SQL> drop materialized view emp_by_district;
