web analytics
Sign In | Register
Welcome, Guest
1 2 3

Materialized Views in Oracle

 Options
Previous Topic Next Topic
@2018-07-31 23:23:47

Refresh Modes for Materialized Views

The two refresh execution modes are ON COMMIT and ON DEMAND. Depending on the materialized view you create, some options may not be available. The following table describes the refresh modes.

Refresh Mode Description

ON COMMIT

Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMIT privilege is necessary to use this mode.

ON DEMAND

Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT).

When a materialized view is maintained using the ON COMMIT method, the time required to complete the commit may be slightly longer than usual. This is because the refresh operation is performed as part of the commit process. Therefore, this method may not be suitable if many users are concurrently changing the tables upon which the materialized view is based.

If you anticipate performing insert, update or delete operations on tables referenced by a materialized view concurrently with the refresh of that materialized view, and that materialized view includes joins and aggregation, Oracle recommends you use ON COMMIT fast refresh rather than ON DEMAND fast refresh.

If you think the materialized view did not refresh, check the alert log or trace file.

If a materialized view fails during refresh at COMMIT time, you must explicitly invoke the refresh procedure using the DBMS_MVIEW package after addressing the errors specified in the trace files. Until this is done, the materialized view will no longer be refreshed automatically at commit time.
@2018-07-31 23:25:04

Types of Materialized View Refresh

You can specify how you want your materialized views to be refreshed from the detail tables by selecting one of four options: COMPLETE, FAST, FORCE, and NEVER.

Refresh Option Description

COMPLETE

Refreshes by recalculating the materialized view's defining query.

FAST

Applies incremental changes to refresh the materialized view using the information logged in the materialized view logs, or from a SQL*Loader direct-path or a partition maintenance operation.

FORCE

Applies FAST refresh if possible; otherwise, it applies COMPLETE refresh.

NEVER

Indicates that the materialized view will not be refreshed with refresh mechanisms.

Whether the fast refresh option is available depends upon the type of materialized view. You can call the procedure DBMS_MVIEW.EXPLAIN_MVIEW to determine whether fast refresh is possible.
@2018-08-03 22:02:08

Create Materialized View - Insufficient Privileges

You need to grant CREATE MATERIALIZED VIEW to our schema owner:

GRANT CREATE MATERIALIZED VIEW TO DEV
@2018-08-03 22:25:40

Dropping a Materialized View

Use the DROP MATERIALIZED VIEW statement to remove an existing materialized view from the database.

Examples 

The following statement drops the materialized view emp_data in the sample schema hr:

DROP MATERIALIZED VIEW emp_data;

The following statement drops the sales_by_month_by_state materialized view and the underlying table of the materialized view, unless the underlying table was registered in the CREATE MATERIALIZED VIEW statement with the ON PREBUILT TABLE clause:

DROP MATERIALIZED VIEW sales_by_month_by_state;
Previous Topic Next Topic
1 2 3

Comments
You must Sign In to comment on this topic.


© 2024 Digcode.com