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

Creation and use of Oracle materialized View

Http://blog.csdn.net/tegwy/article/details/8935058

Let's first look at the simple creation statement:

Create materialized view mv_materialized_test refresh force on demand start with sysdate next

To_date (concat (to_char (sysdate + 1, 'dd-mm-yyyy'),' 10 to_char 25 sysdate 00'), 'dd-mm-yyyy hh34:mi:ss') as

Select * from user_info;-this materialized view is refreshed at 10:25 every day

Materialized view is also a kind of view. 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 views can query tables, views, and other materialized views.

Features:

(1) in a sense, a materialized view is a physical table (and not just a physical table), which is confirmed by the fact that it can be queried by user_tables.

(2) materialized view is also a kind of segment, so it has its own physical storage properties.

(3) materialized views will occupy the disk space of the database, which can be proved by the query results of user_segment.

Create statement: create materialized view mv_name as select * from table_name

Because materialized views are physically real, indexes can be created.

Generate data at creation time:

There are two types: 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.

If not specified, the default is build immediate.

Refresh mode:

There are two refresh modes for materialized views:

Whether refresh mode is on demand or on commit at the time of creation.

On demand as the name implies, only when the materialized view is "needed" is refreshed (REFRESH), that is, the materialized view is updated to ensure consistency with the base table data.

On commit commit trigger, once the base table has commit, that is, transaction commit, immediately refresh, immediately update the materialized view, so that the data is consistent with the base table. Generally speaking, the speed of operating the base table is slow in this method.

If the materialized view is not specified when it is created, Oracle is created in on demand mode.

The refresh mode is mentioned above, and there are three refresh methods for how to refresh:

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

Quick refresh (FAST): using the incremental refresh mechanism, only all operations on the base table since the last refresh are refreshed to the materialized view. 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.

FORCE mode: this is the default data refresh method. Oracle automatically determines whether the condition for fast refresh is met, and if so, a quick refresh is performed, otherwise a full refresh is performed.

About fast refresh: the fast refresh mechanism of Oracle materialized views is done through the 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.

Query rewriting (QueryRewrite):

Including 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.

Syntax:

Create materialized view view_name

Refresh [fast | complete | force]

[

On [commit | demand] |

Start with (start_time) next (next_time)

]

AS subquery

Concrete operation

The permissions required to create a materialized view:

Grant create materialized view to user_name

Create a materialized view log in the source table:

Create materialized view log on test_table

Tablespace test_space-log space

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

Create a MATERIALIZED VIEW on the target database:

Create materialized view mv_materialized_test refresh force on demand start with sysdate next

To_date (concat (to_char (sysdate + 1, 'dd-mm-yyyy'),' 10 to_char 25 sysdate 00'), 'dd-mm-yyyy hh34:mi:ss') as

Select * from user_info;-this materialized view is refreshed at 10:25 every day

Modify refresh time:

Alter materialized view mv_materialized_test refresh force on demand start with sysdate

Next to_date (concat (to_char (sysdate + 1, 'dd-mm-yyyy'),' 23lv 00lv 00'), 'dd-mm-yyyy hh34:mi:ss')

Or

Alter materialized view mv_materialized_test refresh force on demand start with sysdate

Next trunc (sysdate, 'dd') + 1 + 1 / 24;-- refresh at 1 o'clock every day

Index:

Create index IDX_MMT_IU_TEST

On mv_materialized_test (ID, UNAME)

Tablespace test_space

Delete materialized views and logs:

Drop materialized view log on test_table;-Delete the materialized view log:

Drop materialized view mv_materialized_test;-deletes materialized views

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.