Oracle Materialized View

I. theoretical part

The Materialized View of Oracle is a database object that includes query results. 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.

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.

Materialized views can be used to query tables, views, and Other materialized views.
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.
In the replication environment, the created materialized view usually contains the primary key, rowid, and subquery view.

 

Refresh clause
[Refresh [Fast | complete | force]
[On Demand | commit]
[Start with date] [next date]
[With {primary key | rowid}]

By default, if the refresh method and refresh mode are not specified, Oracle uses force and demand by default.

 

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.

Refresh: Specifies the mode in which the materialized view is synchronized with the base table after the DML operation is performed on the base table. There are two refresh modes: on demand and on commit.
The difference between the on demand and on commit materialized views is that the refresh method is different. On Demand refers to the refresh of the materialized view when the user needs it. You can refresh it by means of dbms_mview.refresh manually, you can also use the job to regularly refresh the materialized view to ensure data consistency between the base table and the base table. On commit means that once the base table has a commit, it is a transaction commit, refresh immediately and update the materialized view to make the data consistent with the base table.

For the base table, the common commit can be completed within 0.01 seconds, but it takes 6 seconds to have the on commit view. The speed is greatly reduced. The impact of the On commit view on the base table is evident.

 

There are four refresh Methods: fast, complete, force, and never.
1. fast: Incremental refresh uses the materialized view log to send the modified data rows of the master table to the materialized view. if you specify the refresh fast clause, you should create a materialized view log for the master table. Therefore, when you use the fast option to create a materialized view, you must create a view log based on the master table.
SQL> Create materialized view log on EMP;
Materialized view log created.
For the incremental refresh option, if an analysis function exists in the subquery, the materialized view does not work.

2. Complete: Completely refresh the entire materialized view.

3. force: when the force clause is specified, if incremental refresh is available to Oracle, incremental refresh will be completed; otherwise, full refresh will be completed. If the refresh method (fast, complete, or force) is not specified ), the force option is the default option.

4. Never: The materialized view is not refreshed. The default value is force on demand.

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.

Materialized view log: If you need to refresh the log quickly, you need to create a materialized view log. 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.

Materialized views can be partitioned. 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.

 

Primary Key and rowd clause: The with primary key option generates the primary key materialized view, that is, the materialized view is based on the primary key of the primary table, rather than the rowid (corresponding to the rowid clause ). primary Key is the default option. To generate a primary key clause, you should define a primary key in the primary table. Otherwise, you should use a rowid-based materialized view.
The materialized view of the primary key allows you to identify the primary table of the materialized view without affecting the availability of incremental refresh of the materialized view.
The rowid materialized view has only one primary table and cannot contain any of the following items:
1. Distinct or aggregate function.
2. Group by, subquery, connection, and set operations

 

1. Primary Key Materialized View: The following syntax creates a primary key Materialized View on the remote database table EMP.
SQL> Create materialized view mv_emp_pk
Refresh fast start with sysdate
Next sysdate more than 1/48
With primary key
As select * from EMP @ remote_db;
Materialized View created.
Note: When you use the fast option to create a materialized view, you must create a view log based on the master table, as shown below:
SQL> Create materialized view log on EMP;
Materialized view log created.

2. rowid Materialized View: The following syntax creates a rowid Materialized View on the remote database table EMP.

SQL> Create materialized view mv_emp_rowid refresh with rowid
As select * from EMP;
3. Sub-query Materialized View: The following syntax creates a sub-query materialized view based on the EMP and dept tables on the remote database table EMP.
SQL> Create materialized view mv_empdept
As select * from EMP e where exists
(Select * from Dept d Where E. dept_no = D. dept_no)

Refresh time: the start with clause instructs the database to complete the first copy time from the master table to the local table. The next clause specifies the refresh interval.
SQL> Create materialized view mv_emp_pk refresh fast
Start with sysdate
Next sysdate + 2
With primary key
As select * from EMP;
In the preceding example, the first copy of the Materialized View data is generated at creation and refreshed every two days.

 

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 * from EMP;

 

Materialized views can create indexes because they exist physically.

 

Ii. materialized view instance

The rapid refresh of materialized views adopts the incremental mechanism. During refresh, only the changed data on the base table is refreshed. Therefore, quick refresh is the first choice for Materialized View refresh.

However, quick Refresh has many constraints, especially for materialized views that use the on commit mode for quick refresh. The quick refresh mechanism for materialized views that contain aggregation and connection is not the same, and there are additional requirements for quick refresh of materialized views with multi-layer nesting. So many restrictions are hard to remember. When a materialized view fails to be created, the error message provided by Oracle is too simple, and sometimes you cannot locate the cause of the problem accurately.
The dbms_mview.explain_mview process provided by Oracle helps you quickly locate the cause of the problem. The following example shows how to solve the problem through this process.

Create a nested materialized view for quick Refresh:
SQL> Create Table B (ID number primary key, name varchar2 (30 ));
The table has been created.

SQL> Create Table C (ID number primary key, name varchar2 (30 ));
The table has been created.

SQL> Create Table A (ID number, bid number, CID number, num number,
2 constraint fk_a_ B _bid foreign key (BID) References B (ID ),
3 constraint fk_a_c_bid foreign key (CID) references C (ID ));
The table has been created.

SQL> insert into B select rownum, 'B' | rownum from user_tables where rownum <= 6;
Six rows have been created.

SQL> insert into C select rownum, 'C' | rownum from user_tables where rownum <= 4;
Four rows have been created.

SQL> insert into a select rownum, trunc (rownum-1)/2) + 1, trunc (rownum-1)/3) + 1, rownum
2 from user_tables
3 where rownum <= 12;
12 rows have been created.

SQL> commit;
Submitted.

The base table is created above, and the first materialized view is created below.
SQL> Create materialized view log on a with rowid;
The materialized view log has been created.

SQL> Create materialized view log on B with rowid;
The materialized view log has been created.

SQL> Create materialized view log on C with rowid;
The materialized view log has been created.

SQL> Create materialized view mv_abc refresh fast on commit enable query rewrite
2 select C. id cid, C. Name cname, B. ID bid, B. Name bname, A. Num,
3 A. rowid arowid, B. rowid browid, C. rowid crowid
4 From a, B, c Where a. Bid = B. ID and A. cid = C. ID;
The materialized view has been created.

The first materialized view has been created successfully. The nested materialized view is created below:

SQL> Create materialized view log on mv_abc with rowid (bname, cname, num) including new values;
The materialized view log has been created.

SQL> Create materialized view mv_mv_abc refresh fast on commit enable query rewrite
2 select cname, bname, count (*) count, sum (Num) sum_num from mv_abc
3 group by cname, bname;
Select cname, bname, count (*) count, sum (Num) sum_num from mv_abc
*
Error is located in row 2nd:
ORA-12053: This is not a valid nested Materialized View

The error occurs, but the error description contains a small amount of information. Let's take a look at how the Oracle document describes this error.

ORA-12053 this is not a valid nested Materialized View
Cause: the list of objects in the from clause of the definition of this materialized view had some dependencies upon each other.
Action: refer to the documentation to see which types of nesting are valid.

The description in this document is also very general and does not point out the specific problem.

Next, we can locate the error by using the dbms_mview.explain_mview process.

When using the explain_mview process, you must first create the mv_capabilities_table table. The step of table creation is $ ORACLE_HOME/rdbms/admin/utlxmv. SQL. (The explain_mview process is a heavy load of two processes. One is output to the mv_capabilities_table table, and the other is output in the varray format of PL/SQL. For simplicity, we create the mv_capabilities_table table ).

SQL> @? Rdbmsadminutlxmv. SQL
The table has been created.

The following describes the explain_mview process.

Dbms_mview.explain_mview (mV in varchar2, statement_id in varchar2: = NULL );

You can enter an existing materialized view name (or user_name.mv_name) or a query statement to create a materialized view. Enter a statement ID for the statement_id parameter to identify the corresponding records in the table.
SQL> begin
2 dbms_mview.explain_mview ('select cname, bname, count (*) count, sum (Num) sum_num from mv_abc
3 group by cname, bname', 'mv _ mv_abc ');
4 end;
5/
The PL/SQL process is successfully completed.

SQL> select capability_name, related_text, msgtxt from mv_capabilities_table
2 Where statement_id = 'mv _ mv_abc 'and possible = 'N' and capability_name not like' % PCT % ';

Capability_name related_text msgtxt
-----------------------------------------------------------------------------------------------
When sum (expr) is used in refresh_fast_after_onetab_dml sum_num, count (expr) is not provided)
Refresh_fast_after_any_dml yangtk. mv_abc MV log no serial number
Refresh_fast_after_any_dml view the reason why refresh_fast_after_onetab_dml is disabled

Based on the above information, we can determine the cause of the problem. sum (column) is used for clustering materialized views, but count (column) is not included ).

Modify the materialized view and recreate it:
SQL> Create materialized view mv_mv_abc refresh fast on commit enable query rewrite
2 select cname, bname, count (*) count, count (Num) num_count, sum (Num) sum_num from mv_abc
3 group by cname, bname;

 

 

Delete materialized view logs
-- Delete logs:

Drop materialized view log on;
-- Delete materialized views

Drop materialized view mv_abc;