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

Materialized view is mainly 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. In the data warehouse, query rewriting (query rewrite) mechanism is often used, so that there is no need to modify the original query statement, and Oracle will automatically select the appropriate materialized view for query, which is completely transparent to the application.

Materialized views can be queried directly as well as tables. Materialized views themselves can also be partitioned.

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 be divided into the following three types: materialized views that contain aggregates; materialized views that contain only connections; and 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:

Creation method (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 it is created, and later generates data as needed. The default is BUILD IMMEDIATE.

Query rewriting (Query Rewrite): 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 DISABLE QUERY REWRITE.

Refresh: refers to when and how the materialized view synchronizes with the base table after the DML operation occurs. There are two modes of refresh: ON DEMAND and ON COMMIT. ON DEMAND means that the materialized view is refreshed when the user needs it. It can be refreshed manually by DBMS_MVIEW.REFRESH or regularly by JOB. ON COMMIT indicates that the materialized view is refreshed while the DML operation on the base table is submitted. There are four ways to refresh: FAST, COMPLETE, FORCE, and NEVER. FAST refresh uses an incremental refresh, refreshing only the modifications made since the last refresh. COMPLETE refresh completely refreshes the entire materialized view. If you choose FORCE mode, Oracle will determine whether it can be refreshed quickly when refreshing, and if so, use FAST mode, otherwise use COMPLETE mode. NEVER means that the materialized view does not do any refresh. The default value is FORCE ON DEMAND.

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.

Materialized view log: if you need to refresh quickly, you need to establish 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.

You can indicate that the ON PREBUILD TABLE statement builds 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.

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.

Create a materialized view

SQL > conn / as sysdbaConnected.SQL > grant create materialized view to scott;Grant succeeded.SQL > conn scott/tigerConnected.SQL > create materialized view mv_emp as select * from emp;Materialized view created.

The default complete statement for materialized views is as follows

CREATE MATERIALIZED VIEW SCOTT.MV_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM DEPTNO) TABLESPACE USERSPCTUSED 0PCTFREE 10INITRANS 2MAXTRANS 255STORAGE (INITIAL 64K NEXT 1m MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) NOCACHELOGGINGNOCOMPRESSNOPARALLELBUILD IMMEDIATEUSING INDEX TABLESPACE USERS PCTFREE 10 INITRANS 2 MAXTRANS 255STORAGE (INITIAL 64K NEXT 1m MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) REFRESH FORCE ON DEMANDWITH PRIMARY KEYAS / * Formatted on 15:28:44 on 2015-9-8 (QP5 v5.252.13127.32847) * / SELECT "EMP". "EMPNO"EMPNO" "EMP". "ENAME"ENAME", "EMP". "JOB"JOB", "EMP". "MGR"MGR", "EMP". "HIREDATE"HIREDATE", "EMP". "SAL"SAL", "EMP". "COMM"COMM", "EMP". "DEPTNO"DEPTNO" FROM "EMP"EMP" COMMENT ON MATERIALIZED VIEW SCOTT.MV_EMP IS 'snapshot table for snapshot SCOTT.MV_EMP' CREATE UNIQUE INDEX SCOTT.PK_EMP1 ON SCOTT.MV_EMP (EMPNO) LOGGINGTABLESPACE USERSPCTFREE 10INITRANS 2MAXTRANS 255STORAGE (INITIAL 64K NEXT 1m MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) NOPARALLEL

Insert data into the base

SQL > INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) SELECT EMPNO-1000, ENAME, JOB, MGR HIREDATE, SAL, COMM, DEPTNO FROM scott.emp 14 rows created.SQL > commit;Commit complete.

View materialized view

SQL > select count (*) from mv_emp; COUNT (*)-14

The data is not refreshed.

Save the rowid of materialized view data first

SQL > create table mv_rowid as select rowid rid from mv_emp;Table created.

We refresh the materialized view manually

SQL > exec DBMS_SNAPSHOT.REFRESH ('SCOTT.MV_EMP'); PL/SQL procedure successfully completed.

View materialized view

SQL > select count (*) from mv_emp; COUNT (*)-28

View the intersection of rowid

SQL > select rowid rid from mv_emp intersect select rid from mv_rowid;no rows selected

It can be seen that the refresh of the materialized view is to delete the previous data, and then perform a full refresh.

Full refresh is more resource-intensive, and if only a small part of the basic data changes, you should use fast fast refresh. To support fast refresh, Oracle must know which rows of data in the base table have changed.

This leads to the introduction of Materialized log, which records changes in the base table.

We do the following tests:

Create materialized view log

SQL > CREATE MATERIALIZED VIEW LOG ON EMP;Materialized view log created.SQL > select * from tab TNAME TABTYPE CLUSTERID---BONUS TABLEDEPT TABLEEMP TABLEMLOG$_EMP TABLERUPD$_EMP TABLESALGRADE TABLE

After creating the materialized view log, there are two more tables, MLOG$_EMP,RUPD$_EMP. These two tables are used to record changes in emp data.

Next, let's create a quickly refreshed materialized view.

SQL > create materialized view fmv_emp refresh fast as select * from emp;Materialized view created.SQL > select count (*) from fmv_emp; COUNT (*)-28

We delete a portion of the data from the emp table.

SQL > delete from emp where empno col CHANGE_VECTOR$$ for a10SQL > select * from MLOG$_EMP EMPNO SNAPTIME$$ D O CHANGE_VEC XID$$--6369 4000-01-01 00:00:00 D O 0000 2.2519E+15 6499 4000-01-01 00:00:00 D O 0000 2.2519E+15 6521 4000-01-01 00:00:00 D O 0000 2.2519E+15 6566 4000-01-01 00:00:00 D O 0000 2.2519E+15 6654 4000-01-01 00:00:00 D O 0000 2.2519E+15 6698 4000-01-01 00:00:00 D O 0000 2.2519E+15 6782 4000-01-01 00:00:00 D O 0000 2.2519E+15 6788 4000-01-01 00: 00:00 D O 0000 2.2519E+15 6839 4000-01-01 00:00:00 D O 0000 2.2519E+15 6844 4000-01-01 00:00:00 D O 0000 2.2519E+15 6876 4000-01-01 00:00:00 D O 0000 2.2519E+15 EMPNO SNAPTIME$$ D O CHANGE_VEC XID$$-- -6900 4000-01-01 00:00:00 D O 0000 2.2519E+15 6902 4000-01-01 00:00:00 D O 0000 2.2519E+15 6934 4000-01-01 00:00:00 D O 0000 2.2519E+1514 rows selected.

The primary key of the deleted data is recorded in the log table, and the operation type is also recorded.

At this point, the materialized view is not actively refreshed because it is refreshed manually by default.

SQL > select count (*) from fmv_emp; COUNT (*)-28

Refresh the view

SQL > exec DBMS_SNAPSHOT.REFRESH ('FMV_EMP'); PL/SQL procedure successfully completed.SQL > select count (*) from fmv_emp; COUNT (*)-14SQL >

The data has been synchronized, and the records in the materialized view log have been deleted. As follows

SQL > select * from MLOG$_EMP;no rows selected

Let's take a look at how to make materialized views refresh automatically.

Create a materialized view and refresh it every 1 minute.

SQL > create materialized view auto_refresh_mv_emp refresh fast on demand start with sysdate next sysdate+1/24/60 as select * from emp;Materialized view created.SQL > select count (*) from auto_refresh_mv_emp; COUNT (*)-14SQL >

Modify the contents of the base table

SQL > INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) SELECT EMPNO + 1000, ENAME, JOB, MGR HIREDATE, SAL, COMM, DEPTNO FROM scott.emp 14 rows created.SQL > commit;Commit complete.SQL > select count (*) from auto_refresh_mv_emp; COUNT (*)-14

Wait a minute.

SQL > select count (*) from auto_refresh_mv_emp; COUNT (*)-28SQL >

Take a look at another view.

SQL > select count (*) from fmv_emp; COUNT (*)-14

There is no refresh, if you refresh the materialized view now.

SQL > select count (*) from mlog$_emp; COUNT (*)-14SQL > exec DBMS_SNAPSHOT.REFRESH ('FMV_EMP'); PL/SQL procedure successfully completed.SQL > select count (*) from mlog$_emp; COUNT (*)-0SQL > select count (*) from fmv_emp; COUNT (*)-28

Why is the mlog$_emp table not emptied when auto_refresh_mv_emp refreshes automatically? When Oracle refreshes the materialized view, if it finds that there are other materialized views that depend on the same base table that have not been refreshed, it will not empty the mlog$_emp table, but will only change the value of the SNAPTIME$$ field.

What if the materialized view commit is refreshed?

SQL > create materialized view refresh_at_commit_emp refresh fast on commit as select * from emp;Materialized view created.SQL > select count (*) from refresh_at_commit_emp; COUNT (*)-14

Modify the contents of the base table

SQL > INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) SELECT EMPNO + 1000, ENAME, JOB, MGR HIREDATE, SAL, COMM, DEPTNO FROM scott.emp 14 rows created.SQL > select count (*) from refresh_at_commit_emp; COUNT (*)-14SQL > commit;Commit complete.SQL > select count (*) from refresh_at_commit_emp; COUNT (*)-28SQL >

It can be seen that the materialized view is refreshed with the commit of the transaction.

The fast refresh of materialized views has the following limitations

The defining query of the materialized view is restricted as follows:

The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.

The materialized view must not contain references to RAW or LONG RAW data types.

It cannot contain a SELECT list subquery.

It cannot contain analytic functions (for example, RANK) in the SELECT clause.

It cannot contain a MODEL clause.

It cannot contain a HAVING clause with a subquery.

It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.

It cannot contain a [START WITH...] CONNECT BY clause.

It cannot contain multiple detail tables at different sites.

ON COMMIT materialized views cannot have remote detail tables.

Nested materialized views must have a join or aggregate.

Materialized join views and materialized aggregate views with a GROUP BY clause cannot select from an index-organized table.

Quick refresh, there are also restrictions on the log of materialized views, and the log we created earlier is only the simplest. Can not meet a lot of fast refresh requirements.

The following materialized view log is the most complete log

SQL > CREATE MATERIALIZED VIEW LOG ON emp WITH SEQUENCE,ROWID (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) INCLUDING NEW VALUES; Materialized view log created.SQL > desc mlog$_emp; Name Null? Type-EMPNO NUMBER (4) ENAME VARCHAR2 (10) JOB VARCHAR2 (9) MGR NUMBER (4) HIREDATE DATE SAL NUMBER (7) COMM NUMBER (7) DEPTNO NUMBER (2) Maurerowski $VARCHAR2 (255) SEQUENCE$$ NUMBER SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2 (1) OLD_NEW$$ VARCHAR2 (1) CHANGE_VECTOR$$ RAW (255) XID$$ NUMBERSQL >

Query rewriting

If a sql query, query the base table directly. However, the query can quickly get the results through the materialized view. Then Oracle will rewrite the query so that the query goes to the materialized view to fetch data.

SQL > CREATE MATERIALIZED VIEW agg_emp REFRESH FAST ON DEMAND ENABLE QUERY REWRITEAS SELECT deptno, SUM (sal) sum_sal,-- AVG (sal) avg_sal, COUNT (*) dept_cnt FROM emp GROUP BY deptno;Materialized view created.SQL > select deptno,sum (sal) from emp group by deptno DEPTNO SUM (SAL)-30 18800 20 21750 10 17500Execution Plan---Plan hash value: 2367 329769- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 3 | 78 | 3 (0) | 00:00:01 | | 1 | | MAT_VIEW REWRITE ACCESS FULL | AGG_EMP | 3 | 78 | 3 (0) | 00:00:01 |-|

And you can also build indexes on materialized views.

SQL > create unique index agg_emp_pk on agg_emp (deptno); Index created.

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.