Oracle materialized View

First, materialized view overview

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 be used to pre-compute and save the results of more time-consuming operations such as table joins or aggregates, so that time-consuming operations can be avoided when queries are executed, 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.

Materialized views can query tables, views, and other materialized views.

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.

In a replication environment, the materialized views created are typically primary keys, ROWID, and subquery views.

Materialized views can create indexes because they are physically real.

1.1 Materialized views can be divided into the following three types

• Contains materialized views of aggregation;

• Contains only materialized views of the connection;

• Nested materialized views.

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:

(1) Creation method (Build Methods): Includes build immediate and build deferred two kinds.

• Build immediate generates data when a materialized view is created. The default is build IMMEDIATE.

• Build deferred does not generate data when it is created, and generates data as needed later.

(2) query rewriting (queryrewrite): Includes 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 Disablequery REWRITE.

(3) Refresh: Refers to when the materialized view is synchronized with the base table when a DML operation occurs on the base table. There are two modes of refresh: On demand and on COMMIT.

The difference between on demand and on commit materialized views is that the Refresh method is different, on demand refers to materialized view when the user needs to refresh, you can manually through the Dbms_mview. Refresh, etc., can also be refreshed through the job timing, that is, updating materialized views 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, immediately refresh, immediately update the materialized view, so that the data and the base table consistent. For the base table, the usual commit can be done within 0.01 seconds, but after the on Commit view, it takes 6 seconds. The speed has been reduced many times. The effect of the on Commit view on the base table is visible.

1.2 materialized views, depending on the focus can have a different classification:

1) According to the mode of refresh: Fast/complete/force

2) vary by refresh time: on Demand/on COMMIT

3) Press to update: Updatable/read only

4) Press whether query rewriting is supported: Enable Queries Rewrite/disablequery REWRITE

By default, if no Refresh method and refresh mode are specified, Oracle defaults to force and demand.

Note: The materialized view setting refresh on commit cannot access the remote object.

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.

1.3 Materialized views have three ways to refresh: Complete, fast, and force.

• Full Refresh (complete) deletes all records in the table (in the case of a single-table refresh, which may take the form of truncate), and then rebuilds the materialized view based on the definition of the query statement in the materialized view.

• Fast Refresh (FAST) takes the mechanism of incremental refresh, flushing only all operations that have been made to 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.

• With force, Oracle automatically determines whether a fast refresh condition is met, and a quick refresh if satisfied, or a full refresh.

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.

1.4 Additional descriptions and examples of materialized view refresh clauses

The REFRESH clause can contain the following sections:

[Refresh [Fast|complete|force]

[On Demand | commit]

[Start with date] [Next Date]

[With {primary KEY|ROWID}]

1.4.1 PRIMARY KEY and ROWD clause:

The WITH PRIMARY key option generates a primary key materialized view, which means that the materialized view is based on the primary key of the main table, not the ROWID (corresponding to the ROWID clause). PRIMARY key is the default option, in order to generate the PRIMARY key clause, the primary key should be defined on the primary table, otherwise the materialized view based on ROWID should be used.

Based on the ROWID materialized view there is only one single primary table and cannot include any of the following:

(1) Distinct or aggregate function.

(2) Group by, subquery, connect and set operations

1.4.2 Refresh Time:

The START with clause notifies the database of the time it takes to complete the first replication from the primary table to the local surface, which should be estimated in time for the next run, and the next clause describes the time interval for the refresh.

1.5 on Prebuild TABLE description

When you create a materialized view, you specify the on Prebuild table statement, which you can build on an already 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.

1.6 Materialized View Partitioning

Materialized views can be partitioned. 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. examples of materialized view usage

2.1 Creating materialized views

Create materialized view MV_EMP_PK Refresh fast--Fast refresh build immediate--immediately flush on demand-refresh the St in the specified way Art with Sysdate-the first refresh time, sysdate represents the current time, or you can use To_date () to specify the time next sysdate+1-refresh interval with primary key-create a primary key materialized view, You can also use the WITH Rowidas-subquery select employee_id from Employees where department_id=10;

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9E/0A/wKioL1mKiC7gnc8iAAAs9AK60js707.png "title=" 1.png "alt=" Wkiol1mkic7gnc8iaaas9ak60js707.png "/>

Can see the error ORA-23413

23413, 00000, "Table \"%s\ ". \"%s\ "does not has a materialized view log"

*cause:the Fast refresh can not be performed because the master table

Does not contain a materialized view log.

*action:use the Create materialized VIEW LOG command to create a

Materialized view log on the master table.

As you can tell from the error description, the materialized view to be created is refreshed in fast, but the materialized view log is not created on table employees, so the above error is reported.

Create the materialized view Log on the table employees and then create the materialized view.

[email protected]>create materialized view log on employees; Materialized view log created. [email protected]>create materialized view mv_emp_pk   2   build immediate   3  refresh fast                 4  on demand               5  start with sysdate      6  next sysdate+1          7  with primary key       8  as                     9   select employee_id  10  from employees  11  where department_id=10; Materialized view created. [email protected]>select * from mv_emp_pk; employee_id-----------        200         [email protected]>select employee_id from employees where  department_id=10; employee_id-----------        200

2.2 Viewing the materialized view refresh time in the job

[Email protected]>select job,what,last_date,next_date from dba_jobs where log_user= ' ZX '; JOB what last_date next_date------------------------------------------------------------------------------------- ---------3 Dbms_refresh.refresh (' ZX ').      MV_EMP_PK "'); 20170809 12:02:41 20170810 12:02:41

2.3 Modifying data in the Employees table

[Email Protected]>insert into employees (EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,DEPARTMENT_ID) VALUES (209, ' ZX ', ' [email protected] ', sysdate,10,10); 1 row created. [Email protected]>commit; Commit complete. [Email Protected]>select * from MV_EMP_PK; EMPLOYEE_ID-----------200

The newly inserted data is not refreshed into the materialized view

2.4 Refreshing materialized views

(1) Manual refresh with Dbms_mview.refresh

[Email protected]>exec dbms_mview.refresh (' mv_emp_pk ');P L/sql procedure successfully completed. [Email Protected]>select * from MV_EMP_PK; employee_id-----------200209[email protected]>exec dbms_mview.refresh (' mv_emp_pk ', ' C ');--Full Volume refresh PL/SQL procedure Successfully completed. [Email protected]>exec dbms_mview.refresh (' mv_emp_pk ', ' f ');--Fast Refresh PL/SQL procedure successfully completed.

(2) Use Dbms_refresh.refresh process to batch refresh MV

If we specify the refresh time for start and next when we create the materialized view, Oracle automatically creates the refreshed job and takes the Dbms_refresh.refresh approach. As shown in 2.2

If you do not specify next time, you need to make the refresh group before you can refresh by using this method.

Suppose there is materialized view mv_t1, Mv_t2, Mv_t3. The syntax for creating the refresh group is as follows:

Sql> EXEC Dbms_refresh. Make (' Rep_test ', ' mv_t1,mv_t2,mv_t3 ', Sysdate, ' sysdate+ 1 ');--Refresh the whole refresh group: sql> EXEC Dbms_refresh. REFRESH (' rep_test ');

2.5 Querying materialized view state

--user_mviews View [Email protected]>select owner,mview_name,refresh_mode,last_refresh_type,last_ Refresh_date,refresh_mode from user_mviews;owner       mview_name       REFRES LAST_REF LAST_REFRESH_DATE  Refres------------------------------ ------------------------------ ------ -------- ------ ----------- ------zx       mv_emp_pk       demand fast     20170809 12:26:40 demand--user_mview_refresh_ Times View [email protected]>col owner for a10[email protected]>col name  For a10[email protected]>col master_owner for a10[email protected]>col  master for a10[email protected]>select * from user_mview_refresh_times ; owner   name        master_own master     last_refresh---------- ----------  ---------- ---------- -----------------zx   mv_emp_pk  zx  Employees  20170809 12:26:40

2.6 Deleting materialized views and logs

[Email protected]>drop materialized view mv_emp_pk; Materialized view dropped. [Email protected]>drop materialized view Log on employees; Materialized view Log dropped.

If you delete the materialized view log and then refresh the materialized view in Fash, the following error is reported

[Email protected]>exec dbms_mview.refresh (' mv_emp_pk ', ' f '); BEGIN Dbms_mview.refresh (' mv_emp_pk ', ' f '); End;*error at line 1:ora-23413:table "ZX". " EMPLOYEES "does not has a materialized view Logora-06512:at" SYS. Dbms_snapshot ", line 2809ora-06512:at" SYS. Dbms_snapshot ", line 3025ora-06512:at" SYS. Dbms_snapshot ", line 2994ora-06512:at Line 1

2.7 Creating a remote materialized view

--Create a materialized view at the far end [email protected]>create materialized view mv_emp_pk   2   build immediate   3  refresh fast                 4  on demand               5  start with  sysdate     6  next sysdate+1          7  with primary key       8   as                     9  select employee_id  10  from [email  protected]_ora11g  11  where department_id=10; Materialized view created. [EMAIL PROTECTED]>SELECT *&NBsp;from mv_emp_pk; employee_id-----------        200         209--Delete data from the Employees table on the source side [email protected]>delete from employees where  Employee_id=209;1 row deleted. [email protected]>commit; commit complete.        [email protected]>select *  from mv_emp_pk; employee_id-----------        200         209--refresh materialized view [Email protected]>exec dbms_mview.refresh (' mv_emp_pk ');P l/sql  Procedure successfully completed. [email protected]>select * from mv_emp_pk; employee_id-----------        200

2.8 Creating a materialized view based on commit

[email protected]>create materialized view mv_emp_commit  2   refresh fast   3  on commit  4  with primary  key  5  as   6  select employee_id   7   from employees   8  where department_id=10; Materialized view created. [email protected]>select * from mv_emp_commit; employee_id-----------        200--Insert new data and submit [email protected]> insert into employees  (employee_id,last_name,email,hire_date,job_id,department_id)  values ( 209, ' ZX ', ' [email protected] ', sysdate,10,10); 1 row created. [email protected]>commit; Commit complete.--commit materialized view Auto Refresh [email protected]>select * from mv_emp_commit; employee_id-----------     &nbsP;  200        209[email protected]>delete from  employees where employee_id=209;1 row deleted. [email protected]>select * from mv_emp_commit; employee_id-----------        200         209[email protected]>commit; Commit complete. [email protected]>select * from mv_emp_commit; employee_id-----------        200

Reference: http://blog.csdn.net/tianlesoftware/article/details/4713553

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6002.htm#SQLRF01302

http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT411

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1954739

Oracle materialized View