Teacher Fu class: Materialized view of Oracle advanced applications (materialized view) __oracle

Original address: http://hi.baidu.com/gukeming888/blog/item/2682f69481c8237154fb9662.html

Materialized views (materialized view), referred to as snapshots (Snapshot) in previous versions of Oracle. The materialized views of Oracle provide powerful features that can be used to anticipate and save the results of time-consuming operations such as table joins or clustering, so that when you execute a query, you can avoid these time-consuming operations and get results quickly.

There are many aspects of materialized views that are similar to indexes, the purpose of using materialized views is to improve query performance, materialized views are transparent to applications, adding and removing materialized views does not affect the correctness and validity of SQL statements in applications, materialized views need to occupy storage space, and materialized views should be refreshed when the base table is changed. Materialized views can be grouped into the following three types: materialized views that contain aggregates, materialized views that contain only connections, and nested materialized views.

Materialized views can be partitioned. and partitioned materialized views can support zoning change tracking (PCT). Materialized views with this feature can still be entered when the base table is partitioned for maintenance operations
Row Quick refresh operation. For a clustered materialized view, you can use cube or rollup in the group by list to establish different levels of aggregated materialized views.

First, take a look at how to create a materialized view: [SQL] view Plain copy create materialized view vi_emp Refresh Force on demand as Select D . Dname,e.ename,e.job,e.hiredate from Scott.dept d,scott.emp e where d.deptno=e.deptno;

There are four ways to refresh the materialized views (refresh): Fast, complete, force, and never,oracle by default in force mode, as shown above.
Fast: Refresh takes an incremental refresh and refreshes only the modifications made since the last refresh.
Complete: Refreshes the entire materialized view for a full refresh.
Force: Refresh will determine whether a quick refresh, if you can use the fast way, otherwise use the complete way.
Never: Refers to materialized views without any refreshes.

There are two types of materialized views: On demand and on commit
On demand: As the name suggests, only when the materialized view "needs" is refreshed is it refreshing (refresh), that is, updating materialized views to ensure consistency with the base table data.
On commit: Once the base table has a commit, that is, the transaction commits, immediately refreshes, updates the materialized view immediately, makes the data and the base table consistent.

Second, create a timed refresh materialized view (refresh every night 10 o'clock):
[SQL] view Plain copy create materialized view vi_emp Refresh Force on Demand start with to_date (' 03-02-2012 14:50:59 ', ' dd-mm-yyyy hh24:mi:ss ') Next to_date (concat (To_char + 1, ' sysdate '), ' yyyy-mm-dd '), ' 22:00:00 yyyy-mm-dd 4:mi:ss ') as select D.dname,e.ename,e.job,e.hiredate from Scott.dept d,scott.emp e where d.deptno=e.deptno;

Third, delete materialized views:
[SQL] view plain copy drop materialized view vi_emp;