ITPub博客

首页 > 数据库 > Oracle > 物化视图刷新的几个问题

物化视图刷新的几个问题

原创 Oracle 作者:houxp666 时间:2014-06-05 17:58:02 0 删除 编辑

Refreshing Materialized Views - An Overview (文档 ID 889342.1)
异常之后能不能继续刷新
Refresh after errors (TRUE or FALSE)
A Boolean parameter. If set to TRUE, the number_of_failures output parameter will be set to the number of refreshes that failed, and a generic error message will indicate that failures occurred. The alert log for the instance will give details of refresh errors. If set to FALSE, the default, then refresh will stop after it encounters the first error, and any remaining materialized views in the list will not be refreshed.
The following four parameters are used by the replication process. For warehouse refresh, set them to FALSE, 0,0,0.

Atomic refresh (TRUE or FALSE)
If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then the refresh of each specified materialized view is done in a separate transaction. If set to FALSE, Oracle can optimize refresh by using parallel DML and truncate DDL on a materialized views.
For example, to perform a fast refresh on the materialized view cal_month_sales_mv, the DBMS_MVIEW package would be called as follows:

DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV', 'F', '', TRUE, FALSE, 0,0,0, FALSE);

同时刷新
Multiple materialized views can be refreshed at the same time, and they do not all have to use the same refresh method. To give them different refresh methods, specify multiple method codes in the same order as the list of materialized views (without commas).
For example, the following specifies that cal_month_sales_mv be completely refreshed and fweek_pscat_sales_mv receive a fast refresh:

DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV, FWEEK_PSCAT_SALES_MV', 'CF', '',TRUE, FALSE, 0,0,0, FALSE);

If the refresh method is not specified, the default refresh method as specified in the materialized view definition will be used.
同时刷新的意义在于性能,同时多个刷新,如果资源够用,性能会好,相当于同时并发了。

测试是否能快速刷新:
When Fast Refresh is Possible
Not all materialized views may be fast refreshable. Therefore, use the package DBMS_MVIEW.EXPLAIN_MVIEW to determine what refresh methods are available for a materialized view. If you are not sure how to make a materialized view fast refreshable, you can use the DBMS_ADVISOR.TUNE_MVIEW procedure, which will provide a script containing the statements required to create a fast refreshable materialized view.

刷新的方法:注意FORCE这种是?,各种尝试,先尝试快速,否则就完全刷新
Refresh Option Parameter Description
COMPLETE  C  Refreshes by recalculating the defining query of the materialized view.
FAST  F  Refreshes by incrementally applying changes to the materialized view.For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT.
FAST_PCT  P  Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.
FORCE  ?  Attempts a fast refresh. If that is not possible, it does a complete refresh.For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/263455/viewspace-1176465/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-11-24

  • 博文量
    46
  • 访问量
    77966