Oracle materialized View

/*
Oracle materialized View
*/
/*
Why use materialized views?
Materialized views are widely used as a means of SQL tuning in a data warehouse environment.
A large number of long query statements, poor processing, will cause serious I/O problems.
*/
/*
What is a materialized view?
Materialized views correspond to normal views, and all statements that create a view are repeated when Oracle uses normal views.
A materialized view is simply a special view with physical storage that occupies the physical space like a table.
Materialized views are created based on tables, materialized views, etc., and need to be synchronized with the source table to constantly refresh the data in the new materialized view.
Two important concepts: query rewriting and materialized view synchronization
*/
/*
What are the characteristics of materialized views?
1, materialized view can be used to pre-calculate and save the table connection or aggregation of more expensive operation results, so that when the implementation of 2 queries can avoid these time-consuming operations, quickly get results.
2. Use materialized views to improve query performance.
3. Materialized views are transparent to the application, that is, adding and deleting materialized views does not affect the SQL statements that are executed in the application.
4, materialized views need to occupy storage space, when the base table changes, materialized view also need to refresh.
5, materialized view also supports data replication through database chain;
*/
/*
Materialized views can be divided into three types:
Contains materialized views of aggregation
Materialized views that contain only connections
Nesting materialized views
*/
/*
How materialized views are created
Build IMMEDIATE data when creating materialized views
Build DEFERRED generate data as needed when creating materialized views without generating data
The default way to create this is: BUILD IMMEDIATE
*/
/*
Query rewriting for materialized views
What is a query rewrite?
Rewrite the SQL statement. When a user queries a base table using an SQL statement, if a materialized view based on these base tables has been established, Oracle automatically calculates and uses materialized views to complete the query. In some cases, it is possible to save query time and reduce the i/o,oracle of this kind of query optimization technique into query rewriting.
ENABLE QUERY REWRITE
DISABLE QUERY REWRITE
Whether query rewriting is supported
To view a command:
Show parameter query_rewrite_enabled;
*/
/*
Refresh
When the base table changes, the materialized view needs to synchronize the data to update the data in the materialized view when the base table has a DML operation.
On DEMAND: Materialized view can be refreshed when the user needs it, can be manually passed through Dbms_mview.refresh, can be refreshed by job timing.
On commit: Materialized views are refreshed at the same time that the DML operation transaction commits the base table.
After selecting the refresh mode, you also need to select a refresh type with four types of refreshes:
FAST "Incremental refresh, refresh only last modified",
Complete "Refresh materialized view",
Force "Oracle in the refresh will determine whether it can be quickly refreshed, you can use fast, you can not use complete",
NEVE "No Refresh on View"
The default value is force on demand, which is a quick flush when needed
*/
/*
Materialized View Log
If you need to refresh quickly, you need to establish a materialized view log, materialized view log according to the needs of the rapid refresh of different materialized views, can be established as ROWID or primary key type, you can also choose whether
Includes sequence,including NEW value and a list of specified columns.
The materialized view log can record the primary key, ROWID, or identity object or all of the records that the primary table is updated.
Materialized views also support a quick refresh of materialized views of a tape query.
The materialized view log indicates the name of the mlog$_ followed by the principal object.
Although the difference between the primary key materialized view log and the rowID materialized view log is small, these differences have a significant impact on the actual use.
*/
/*
When you create a materialized view, you can specify an order BY statement so that the generated data is saved in a certain order, but this statement is not written to the materialized view definition and is not valid for subsequent refreshes.
*/
/*
The onprebuildtable statement builds the materialized view on a table that already exists.
At this time The materialized view must have the same name as the table. Tables with the same name are not deleted when materialized views are deleted.
Query rewriting for this materialized view requires parameters
Query_rewrite_integrity must be set to trusted or stale_tolerated
*/
/* Use of materialized views */
--Example 1
/* Query the EMP table of the Scott user mv1 the user, and get real-time updates to scott.emp under MV1 */
Sqlplus/as SYSDBA
Create user Mv1 identified by oracle;--creating MV1 users
Grant Connect,resource to mv1;--Grant MV1 authorization
Grant SELECT on Scott.emp to mv1;--will query scott.emp
Grant create materialized view to mv1;--grants permissions to materialized views MV1
Grant execute on Dbms_mview to MV1;
Conn Mv1/oracle
SELECT * FROM scott.emp;--Test if you can query Scott's EMP table under MV1
Create materialized view EMP as SELECT * from scott.emp;--creating materialized views under MV1
The materialized attempt created by the SELECT * from emp;--test
--Open a new terminal to update Scott's EMP table
Update EMP set SAL=SAL-2;
Commit
--At this point, the EMP table Sal field in Scott has been updated, but materialized views in MV1 are not synchronized
--because there are no synchronization methods defined
Conn Mv1/oracle
--Manual synchronization
Exect Dbms_mview.refresh (' emp ', ' C ');--c indicates full refresh with complete
--Full refresh is equivalent to completely deleting the data from the original table.
--can be quickly refreshed with fast,
exec dbms_mview.refresh (' emp ', ' f ');
/*
Prompt for the following error
BEGIN Dbms_mview.refresh (' emp ', ' f '); END;
*
An error occurred on line 1th:
ORA-23413: Table "SCOTT". " EMP "With no manifested view log
ORA-06512: In "SYS." Dbms_snapshot ", Line 2558
ORA-06512: In "SYS." Dbms_snapshot ", Line 2771
ORA-06512: In "SYS." Dbms_snapshot ", Line 2740
ORA-06512: On line 1
*/
--No materialized view log created
--materialized view logs are used when the user chooses the fast refresh type to incrementally synchronize the changes of the base table
Conn/as SYSDBA
Grant create materialized view to Scott;
Conn scott/123
Create materialized view Log on EMP;
Grant on commit refresh in scott.emp to Mv1;
Conn Mv1/oracle
Drop materialized view emp;
Conn/as SYSDBA
Grant Select any table to mv1;--does not do this authorization to create a refreshed view under MV1 may prompt the table or view does not exist
Create materialized view EMP Refresh fast on Commit as SELECT * from Scott.emp;
--Test success! Modify the EMP table under the Scott user, query the View EMP Synchronization update under the MV1 user
--Create a materialized view that refreshes every minute
Create materialized view Empf refresh fast start with Sysdate next sysdate+1/1440 as SELECT * from Scott.emp;
--Create a materialized view that can be updated
Create materialized view Empu refresh fast for update as SELECT * from Scott.emp;
--Example 2
/*
Synchronizing data from a table in a remote database
Remote Database name: username
Password: password
Sid:cpees
Tables to be synchronized: table1
*/
--Create Db_link
Create DATABASE LINK to_cpees
CONNECT TP "username" indentified by "password"
Using "Cpees"
--Create a quick refresh log on the table on the remote database
Create materialized view Log on Table1 with primary key including new values;
--Create a materialized view locally
Create materialized View table1
Build immediate--generate data when creating materialized views
Refresh fast with primary key--quickly refreshes based on primary table primary key increment
On demand--refreshed by user when required by user
Enable query rewrite-readable and writable
As
SELECT * FROM [email protected]_cpees;--query statement
---refresh every month at irregular intervals
Create or replace Procdure P_mview_refresh as
Begin
Dbms_mview.refresh (' table1 ', ' f ');
End P_mview_refresh;
--Note: 1. If you need to refresh multiple materialized views at the same time, you must concatenate each materialized view name with a comma and indicate the Refresh method (F, incremental refresh, C, full refresh, or forced refresh) for each view. For example: Dbms_mview.refresh (' table1,table2 ', ' FF ');
---2, the log and materialized views are created, delete the logs, you need to recreate the new materialized view, or you cannot use the incremental refresh
--3, PRIMARY key update, table must have primary key
--About Delete
--Delete is log and materialized view to be deleted separately
DROP materialized VIEW LOG on [email protected];
DROP materialized VIEW table1;
/*
Other Concepts:
Materialized views can be partitioned.
A materialized view is a database object that includes a query result, is a local copy of 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 and can also be called snapshots.
Materialized views can be queried based on tables, views, and other materialized views.
About replication: Materialized views allow you to maintain a copy of the remote data locally, which is read-only. You must use the Advanced replication feature if you want to modify the local copy.
When you are extracting data like from a table or view, you can extract it from a materialized view. For data warehouses, creating materialized views is typically an aggregated view, a single table aggregation, and a connection view.
*/

Oracle materialized View