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

For more information, please see:

Description of Oracle materialized view:

Https://www.cnblogs.com/xiaohuilong/p/5995596.html

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.

What is the difference between materialized view and normal VIEW:

Materialized view (MV) is refreshed automatically or manually. View can directly update without refreshing MV, but does not affect base table. The update of View reflects that MV is mainly used for remote data access on base table, data in mv needs disk space, and data is not saved in view.

Materialized views can be indexed because they are physically real.

1.1 materialized views can be divided into the following three types

(1) materialized views containing aggregations

(2) materialized views that contain only connections

(3) 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 (buildmethods): including build immediate and build deferred.

Build immediate generates data when you create a materialized view.

Build deferred does not generate data at creation time, but later generates data as needed.

The default is build immediate.

(2) query rewriting (queryrewrite): includes enable queryrewrite and disable queryrewrite.

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.

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

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.

1) complete deletes all records in the table (if it is a single table refresh, it may use truncate), and then regenerates the materialized view according to the definition of the query statement in the materialized view.

2) 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 materialized view log based on the main table. For the incremental refresh option, the materialized view does not work if there is an analysis function in the subquery.

3) in force mode, oracle will automatically determine whether the condition for fast refresh is met, and if so, perform a quick refresh, otherwise complete refresh.

The fast refresh mechanism of oracle materialized view is accomplished through materialized view log. O racle 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.

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

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.

Note: when creating a materialized view, it is specified by default that there is a primary key in the materialized view. If it is not specified, the base table of the created materialized view log file must have a primary key, otherwise an error will be reported.

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

-- example of primary key (primarykey) materialized view:

Create a primary key materialized view on the remote database table emp:

Create materialized view mv_emp_pk refresh fast start with sysdate next sysdate + 1pm 48 with primary key as select * from emp@scottlink

Source database: 192.168.56.12 wang

Target library: 192.168.56.20 slient

Source database:

Sql > show user

User is "scott"

Sql > create database link scottlink connect to scott identified by tiger using'(description =

2 (address = (protocol = tcp) (host = 192.168.56.20) (port = 1521))

3 (connect_data =

4 (server = dedicated)

5 (service_name = service3)

6)

7)'

Database link created.

Sql > select * from dba_db_links

Owner db_link username host created

-

Scott scottlink scott (description = 27-apr-18

(address = (protocol = tcp) (host = 192.168.56.20) (port = 1521))

(connect_data =

(server = dedicated)

(service_name = service3)

)

)

Sql > create materialized view MV_EMP_PK refresh fast start with sysdate next sysdate + 1 Compact 48 with primary key as select * from emp@scottlink

Create materialized view mv_emp_pk refresh fast start with sysdate next sysdate + 1pm 48 with primary key as select * from emp@scottlink

*

Error at line 1:

Ora-23413: table "scott". "emp" does not have a materialized view log

Error report, showing the log of materialized view to be created

SQL > create materialized view log on emp@SCOTTLINK

Create materialized view log on emp@SCOTTLINK

*

ERROR at line 1:

ORA-00949: illegal reference to remote database

Quick refresh cannot create the basic materialized view log of the target library, so change the refresh method from fast to force, as follows:

Sql > create materialized view mv_emp_pk refresh force start with sysdate next sysdate + 1 Compact 48 with primary key as select * from emp@scottlink

Materialized view created.

Method 2: show that you can create a basic materialized view log in the target database, and then create a materialized view in the source database, as follows:

Source database operation:

SQL > create materialized view mv_emp_pk_2 refresh fast start with sysdate next sysdate + 1 Compact 48 with primary key as select * from emp@scottlink

Create materialized view mv_emp_pk_2 refresh fast start with sysdate next sysdate + 1pm 48 with primary key as select * from emp@scottlink

*

ERROR at line 1:

ORA-23413: table "SCOTT". "EMP" does not have a materialized view log

Target library operation:

SQL > create materialized view log on emp

Materialized view log created.

Source library created again: successful

SQL > create materialized view mv_emp_pk_2 refresh fast start with sysdate next sysdate + 1 Compact 48 with primary key as select * from emp@scottlink

Materialized view created. -- when you create a materialized view with the fast option, you must create a view log based on the main table, as follows:

SQL > create materialized view MV_EMP refresh fast start with sysdate next sysdate + 1 Compact 48 with primary key as select * from emp

Create materialized view mv_emp refresh fast start with sysdate next sysdate + 1pm 48 with primary key as select * from emp

*

ERROR at line 1:

ORA-23413: table "SCOTT". "EMP" does not have a materialized view log

Error report shows that to create a local materialized view fast refresh mechanism, you must first create a materialized view log, as shown below:

SQL > create materialized view log on emp

Materialized view log created.

SQL > create materialized view mv_emp refresh fast start with sysdate next sysdate + 1 Compact 48 with primary key as select * from emp

Materialized view created.

Successfully created.

-- example of 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@scottlink

Materialized view created.

Note that the default refresh condition is force, and by default, if no refresh method and refresh mode are specified, Oracle defaults to force and demand.

-- example of subquery materialized view:

Create a subquery materialized view based on emp and dept tables on the remote database table emp

SQL > CREATE MATERIALIZED VIEW MV_EMPDEPT AS SELECT * FROM emp@scottlink e WHERE EXISTS (SELECT * FROM dept@scottlink d WHERE e.DEPTNO = d.DEPTNO)

Materialized view created.

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.

SQL > create materialized view MV_EMP_PK_1 refresh complete start with sysdate next sysdate + 2 with primary key as select * from emp@scottlink

Materialized view created.

In the above example, the first copy of the materialized view data is created and refreshed every two days.

Create materialized view mv_lvy_levydetaildata

Tablespace users-saves tablespaces

Build deferred-- delay refresh, do not refresh immediately

If refresh force-- can refresh quickly, refresh quickly, otherwise refresh completely.

On demand-- refreshes as specified

Start with to_date ('24-11-200518 0010, 'dd-mm-yyyyhh34:mi:ss')-first refresh time

Next trunc (sysdate+1) + 18amp 24muri-refresh interval

As

Select * from emp@scottlink; 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. Example of materialized view operation

1. The permissions required to create a materialized view:

Grant create materialized view to user_name

two。 Create a materialized view log in the source table

Create materialized view log on dave

Tablespace&bisoncu_space-log space

With primary key;-- specified as the primary key type

3. Authorization to intermediate users

Grant select on dave to anqing

Grant select on mlog$_dave to anqing

4. Create a materialized view on the target database

Create materialized view aics_dave

Tablespace & bisoncs_space

Refresh fast on demand-first refresh time

-- start with to_date ('2012-01-01 20 0012,' yyyy-mm-dd hh34:mi:ss')

Start with sysdate-refresh interval. Refresh every day at 2: 00 a. M.

-- next trunc (sysdate,'dd') + 1, 2, 2, 24

Next sysdate+1/24/20

With primary key

-- using default local rollbacksegment

Disable query rewrite as

Select model_id, status,model_name, manu_id, description, create_time, update_time, sw_version

From aics_dave@link_dave

5. Create an index on the target materialized view

Create index idx_t_dv_ct

On aics_dev_info (create_time, update_time)

Tablespace & bison_idx

Create index idx_t_dv_ut

On aics_dev_info (update_time)

Tablespace & bison_idx

Create index i_t_dv_msisdn

On aics_dev_info (msisdn)

Tablespace & bison_idx

6. Materialized view refresh instructions

(1) manually refresh using dbms_mview.refresh

Such as:

Exec dbms_mview.refresh ('mv_dave')

-- full refresh

Exec dbms_mview.refresh (list = > 'mv_dave',method = >' c')

Exec dbms_mview.refresh ('mv_dave','c')

-- Fast refresh

Exec dbms_mview.refresh (list = > 'mv_dave',method = >' f')

Exec dbms_mview.refresh ('mv_dave','f'); (2) use the dbms_refresh.refresh process to batch refresh the mv

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.

You need to make refresh group before refreshing in this way.

The syntax of refreshmake can be referred to:

Http://docs.oracle.com/cd/b19306_01/server.102/b14227/rarrefreshpac.htm#i94057

Example:

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') 7. Delete materialized views and logs

-- Delete materialized view log:

Drop materialized view log on dave

-- Delete materialized view

Drop materialized view mv_dave

The order in which materialized views are deleted:

Http://blog.itpub.net/4227/viewspace-242683/ 8. View materialized view refresh status information

-- query materialized view information:

Set lines 200

Col OWNER for a10

Col MVIEW_NAME a10

Col QUERY for a30

Col MASTER_LINK for a15

Col eligible_for_fast_refree for a25

Col REWRITE_ENABLED for a15

Col REFRESH_MODE for a15

Col REFRESH_METHOD for a15

Select a.OWNERrea.MVIEWABLE name eligible_for_fast_refree,a.LAST_REFRESH_TYPE Method_for_recent_refresh,a.LAST_REFRESH_DATE,a.STALENESS Relationship,a.COMPILE_STATE Validity from dba_mviews a. QUERYrea.MASTER.REWITERESHABLE eligible_for_fast_refree,a.LAST_REFRESH_TYPE Method_for_recent_refresh,a.LAST_REFRESH_DATE,a.STALENESS Relationship,a.COMPILE_STATE Validity from dba_mviews a where a.MVIEW_NAME in ('MV_EMP_PK','MV_EMP','MV_EMP_ROWID') 'MV_EMPDEPT','MV_EMP_PK_1','MV_EMP_PK_2') Select * from dba_mview_refresh_times

-- query materialized view log information:

Select * from dba_mview_logs

9. Query materialized view log:

Select * from mlog$_dave

Oracle materialized view log structure:

Http://blog.itpub.net/31397003/viewspace-2146803/

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.