Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to create and refresh the materialized view of oracle. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Create materialized view log on F_RPT_TASK WITH ROWID--

-F_RPT_TASK is the table name-Note: (F_RPT_TASK is the table name or view name. For more information about creating materialized views on views, see materialized views based on views-create materialized view statements:

DROP materialized view log on F_RPT_TASK

Create materialized view MV_F_RPT_TASK

-MVTEST is the materialized view name

Build immediate-data generated during creation corresponds to build deferred

Refresh fast-incremental refresh

On commit-submit when there is an update to the base table. This sentence is not valid for the view.

With PRIMARY KEY-here you create a materialized view based on primary key, which corresponds to primary key in the log. If the log is rowid, it must be rowid.

As

Select * from F_RPT_TASK where RPT_PERIOD in (2012-2011)-generate materialized view data statements

Change the materialized view to nologging mode

Alter table MV_VECAM01_2YEARS nologging

Permission is required: GRANT CREATE MATERIALIZED VIEW, and must be directly granted to GRANT QUERY REWRITE.

Create a specified refresh attempt:

1. Contains SYSDATE/ROWNUM or RAW/LONG RAW data types; unable to establish On commit refresh.

Remove comments when you create them.

Create materialized view log on tecam01

TABLESPACE CISA_GXPORTAL

WITH PRIMARY KEY

-tecam01 is the table name-Note: (tecam01 is the table name or view name. For more information about creating materialized views on views, see materialized views based on views-create materialized view statements:

/ * Delete logs and views * /

DROP materialized view log on tecam01

Drop materialized view MV_vecam01_2years

Create materialized view MV_vecam01_2years

TABLESPACE CISA_GXPORTAL-saves tablespaces

BUILD DEFERRED-delayed refresh does not refresh immediately

Refresh force-if you can refresh quickly, do a quick refresh, otherwise refresh completely

On demand-refreshes as specified

Start with to_date ('07-12-2013 1200 10 minutes, 'dd-mm-yyyy hh34:mi:ss') / * first refresh time * /

Next sysdate+5/ (24060)-refresh every 5 minutes

-- next TRUNC (SYSDATE+1) + 18Compact 24-- Refresh starts at 18:00 every other day

As

SELECT * FROM tecam01 WHERE display_time > to_char ((sysdate- 730), 'yyyyMMdd')

Related information:

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 NEVE*. * * ST refresh uses 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.

2. Description of relevant parameters

Description of the main options for creating materialized views

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

The name Build Clause creation method description includes two values: BUILD IMMEDIATE and BUILD DEFERRED. BUILD IMMEDIATE generates data when creating a materialized view. BUILD DEFERRED does not generate data when it is created. Later, the default BUILD IMMEDIATE name Query Rewrite query rewrite description includes ENABLE QUERY REWRITE and DISABLE QUERY REWRITE as needed. Point out the entity of the creation respectively

Whether the view supports query rewriting. Query rewriting means that when querying the base table of a materialized view, Oracle will automatically determine

Can the break get the result by querying the materialized view, and if so, the aggregation or join operation is avoided

While reading data values directly from the calculated materialized view ENABLE QUERY REWRITE supports query rewriting DISABLE QUERY REWRITE does not support query rewriting default DISABLE QUERY REWRITE

Description of the main options for creating materialized attempt logs

If you need a quick refresh, you need to establish a materialized view log. Materialized view log according to different materialization

The need for quick refresh of the view can be established as a ROWID or PRIMARY KEY type. There is also a choice.

Whether to include SEQUENCE, INCLUDING NEW VALUES, and a list of specified columns.

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.