Shulou(Shulou.com)06/01 Report--

I. Overview of materialized views

The materialized view of Oracle is a database object 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 table. Materialized views store data based on remote tables, which can also be called snapshots.

Materialized view can be used to pre-calculate and save the results of more time-consuming operations such as table join or aggregation, so that when executing the query, we can avoid these time-consuming operations and get the results quickly. Materialized views are similar to indexes in many aspects: the purpose of using materialized views is to improve query performance; materialized views are transparent to the application, adding and deleting materialized views will not affect the correctness and validity of SQL statements in the application; materialized views need to take up storage space; when the base table changes, materialized views should also be refreshed.

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

Typically, materialized views are called master tables (during replication) or parts lists (in data warehouses).

For replication, materialized views allow you to maintain copies of remote data locally, which are 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 usually aggregate views, single table aggregate views, and join views.

In a replication environment, materialized views are usually created with primary keys, rowid, and subquery views.

Materialized views can be indexed because they are physically real.

1.1 materialized views can be divided into the following three types

Materialized views containing aggregations

Only materialized views that contain connections

Nested materialized views.

The restrictions on the rapid refresh of the three materialized views are very different, but there is little difference in other aspects.

You can specify a variety of options when creating a materialized view. Here are some of the main options:

(1) creation method (Build Methods): includes BUILD IMMEDIATE and BUILD DEFERRED.

BUILD IMMEDIATE generates data when you create a materialized view. The default is BUILD IMMEDIATE.

BUILD DEFERRED does not generate data at creation time, but later generates data as needed.

(2) query rewriting (QueryRewrite): includes ENABLE QUERY REWRITE and DISABLE QUERY REWRITE.

Indicates whether the created materialized view supports query rewriting, respectively. Query rewriting means that when querying the base table of the materialized view, oracle will automatically judge whether the result can be obtained by querying the materialized view. If so, it avoids the aggregation or join operation and reads the data directly from the calculated materialized view. The default is DISABLEQUERY REWRITE.

(3) Refresh: when and in which way the materialized view synchronizes with the base table after the DML operation occurs in the base table. There are two modes of refresh: ON DEMAND and ON COMMIT.

The difference between ON DEMAND and ON COMMIT materialized views lies in their refresh methods. ON DEMAND means that materialized views are refreshed when users need them. They can be refreshed manually by DBMS_MVIEW.REFRESH, or regularly through JOB, that is, update materialized views to ensure consistency with base table data. ON COMMIT means that once the base table has COMMIT, that is, the transaction commits, it will be refreshed immediately, and the materialized view will be updated immediately to make the data consistent with the base table. For the base table, the normal COMMIT can be completed in 0. 01 seconds, but with the ON COMMIT view, it takes 6 seconds. The speed has been reduced many times. The impact of the ON COMMIT view on the base table can be seen.

1.2 materialized views, which can be classified according to different priorities:

1) divided by refresh method: FAST/COMPLETE/FORCE

2) according to the refresh time: ON DEMAND/ON COMMIT

3) press whether it can be updated: UPDATABLE/READ ONLY

4) rewrite by whether the query is supported or not: ENABLE QUERY REWRITE/DISABLEQUERY REWRITE

By default, Oracle defaults to FORCE and DEMAND if you do not specify a refresh method and refresh mode.

Note: the materialized view that sets the REFRESH ON COMMIT cannot access the remote object.

When creating a materialized view, you can specify an ORDER BY statement so that the generated data is saved in a certain order. However, this statement is not written to the definition of the materialized view and has no effect on future refreshes.

There are three ways to refresh materialized views: COMPLETE, FAST, and FORCE.

Full refresh (COMPLETE) deletes all records in the table (if it is a single table refresh, it may be TRUNCATE), and then regenerates the materialized view according to the definition of the query statement in the materialized view.

Fast refresh (FAST) adopts the mechanism of incremental refresh, which only refreshes all operations on the base table to the materialized view since the last refresh. FAST must create a view log based on the primary table. For the incremental refresh option, the materialized view does not work if there is an analysis function in the subquery.

In FORCE mode, Oracle automatically determines whether the condition for fast refresh is met, and if so, it will refresh quickly, otherwise it will refresh completely.

The fast refresh mechanism of Oracle materialized view is accomplished through materialized view log. Oracle can also support rapid refresh of multiple materialized views through a materialized view log.

The materialized view log can be established as ROWID or PRIMARY KEY type according to the need for rapid 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 instructions and examples of the materialized view Refresh clause

The REFRESH clause can contain the following parts:

[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 the primary key materialized view, that is, the materialized view is based on the primary key of the primary table, not 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.

The materialized view based on ROWID has only a single main table and cannot include any of the following:

(1) Distinct or aggregate function.

(2) Group by, subquery, join and SET operation

1.4.2 Refresh time:

The START WITH clause informs the database of the time to complete the first replication from the main table to the local table, and the time of the next run should be estimated in time. The NEXT clause indicates the interval between refreshes.

1.5 ON PREBUILD TABLE description

When you specify the ON PREBUILD TABLE statement when you create a materialized view, you can build the materialized view on an existing table. 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.

This query rewriting of materialized views requires that the parameter QUERY_REWRITE_INTEGERITY must be set to trusted or stale_tolerated.

1.6 materialized View Partition

Materialized views can be partitioned. And partition-based materialized views can support partition change tracking (PCT). The materialized view with this characteristic can still be refreshed quickly after the partition maintenance operation of the base table. For aggregate materialized views, you can use CUBE or ROLLUP in the GROUP BY list to create different levels of aggregated materialized views.

2. examples of the use of materialized views

2.1 create a materialized view

Create materialized view mv_emp_pk refresh fast-- quickly refresh build immediate-- refresh on demand immediately-- refresh start with sysdate as specified-- 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 with rowidas-a subquery select employee_id from employees where department_id=10

You can see the error ORA-23413.

23413, 00000, "table\"% s\ ".% s\" does not have 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 see from the error description, the refresh mode of the materialized view to be created is FAST, but the materialized view log is not created on the table employees, so the above error is reported.

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

Zx@ORA11G > create materialized view log on employees;Materialized view log created.zx@ORA11G > 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.zx@ORA11G > select * from mv_emp_pk EMPLOYEE_ID- 200 zx@ORA11G > select employee_id from employees where department_id=10;EMPLOYEE_ID- 200

2.2 View the time when materialized views are refreshed in job

Zx@ORA11G > 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 modify the data in the employees table

Zx@ORA11G > insert into employees (EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,DEPARTMENT_ID) values; 1 row created.zx@ORA11G > commit;Commit complete.zx@ORA11G > select * from mv_emp_pk;EMPLOYEE_ID-

The newly inserted data is not refreshed to the materialized view

2.4 Refresh the materialized view

(1) manually refresh using dbms_mview.refresh

Zx@ORA11G > exec dbms_mview.refresh ('MV_EMP_PK'); PL/SQL procedure successfully completed.zx@ORA11G > select * from mv_emp_pk;EMPLOYEE_ID- 200 209zx@ORA11G > exec dbms_mview.refresh (' MV_EMP_PK','c');-- full refresh PL/SQL procedure successfully completed.zx@ORA11G > exec dbms_mview.refresh ('MV_EMP_PK','f');-- Fast refresh PL/SQL procedure successfully completed.

(2) use the dbms_refresh.refresh process to refresh MV in batches

If we specify the refresh time for start and next time during the process of creating the materialized view, then Oracle automatically creates a refreshed job and uses the dbms_refresh.refresh approach. As shown in 2.2

If you do not specify that next time refresh in this way, you need to make refresh group before refreshing.

Suppose there are materialized views MV_T1, MV_T2, MV_T3. The syntax for creating a refresh group is as follows:

SQL > EXEC DBMS_REFRESH.MAKE ('REP_TEST',' MV_T1,MV_T2,MV_T3', SYSDATE, 'SYSDATE+ 1');-- refresh the entire refresh group group: SQL > EXEC DBMS_REFRESH.REFRESH (' REP_TEST')

2.5 query materialized view state

-- user_mviews view zx@ORA11G > 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 zx@ORA11G > col owner for a10zx@ORA11G > col name for a10zx@ORA11G > col master_owner for a10zx@ORA11G > col master for a10zx@ORA11G > 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 Delete materialized views and logs

Zx@ORA11G > drop materialized view mv_emp_pk;Materialized view dropped.zx@ORA11G > 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 will be reported

Zx@ORA11G > 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 have 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 create a remote materialized view

Create a materialized view zx@ORA12C > 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@link_ora11g 11 where department_id=10;Materialized view created.zx@ORA12C > select * from mv_emp_pk at the remote end EMPLOYEE_ID- 200209Mel-delete the data in the employees table zx@ORA11G > delete from employees where employee_id=209;1 row deleted.zx@ORA11G > commit;Commit complete on the source side. Zx@ORA12C > select * from mv_emp_pk;EMPLOYEE_ID- 200 209-refresh materialized view zx@ORA12C > exec dbms_mview.refresh ('MV_EMP_PK'); PL/SQL procedure successfully completed.zx@ORA12C > select * from mv_emp_pk;EMPLOYEE_ID- 200

2.8Creating materialized views based on commit

Zx@ORA11G > 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.zx@ORA11G > select * from mv_emp_commit EMPLOYEE_ID- 200-insert new data and submit zx@ORA11G > insert into employees (EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,DEPARTMENT_ID) values (209 row created.zx@ORA11G > commit;Commit complete.--commit automatically refresh zx@ORA11G > select * from mv_emp_commit after materialized view EMPLOYEE_ID- 200 209zx@ORA11G > delete from employees where employee_id=209;1 row deleted.zx@ORA11G > select * from mv_emp_commit;EMPLOYEE_ID- 200 209zx@ORA11G > commit;Commit complete.zx@ORA11G > 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.