REFRESH is used to refresh a specified materialized view.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Syntax
DBMS_MVIEW.REFRESH (
{ list IN VARCHAR2,
| tab IN DBMS_UTILITY.UNCL_ARRAY,}
method IN VARCHAR2 := NULL,
rollback_seg IN VARCHAR2 := NULL,
push_deferred_rpc IN BOOLEAN := true,
refresh_after_errors IN BOOLEAN := false,
purge_option IN BINARY_INTEGER := 1,
parallelism IN BINARY_INTEGER := 0,
heap_size IN BINARY_INTEGER := 0,
atomic_refresh IN BOOLEAN := true,
nested IN BOOLEAN := false,
out_of_place IN BOOLEAN := false,
skip_ext_data IN BOOLEAN := false,
refresh_parallel IN BINARY_INTEGER := 0,
nested_refresh_mode IN VARCHAR2 := NULL
);
Parameters
Note
Non-nested materialized views do not support cascading refreshes. Specifying the nested and nested_refresh_mode parameters has no effect, and the default is to refresh independently.
| Parameter | Description |
|---|---|
| list | A comma-separated list of materialized view names to be refreshed. Currently, only a single materialized view can be refreshed at a time; refreshing multiple materialized views simultaneously is not supported. |
| tab | An array of materialized view names to be refreshed. Currently, only an array of single materialized view names can be refreshed at a time; refreshing multiple arrays of materialized view names simultaneously is not supported. |
| method | Defines the refresh strategy for the materialized view. This parameter can be specified when creating the materialized view to set a default strategy. Valid values:
|
| rollback_seg | This parameter has no effect and is retained for Oracle compatibility. |
| push_deferred_rpc | This parameter has no effect and is retained for Oracle compatibility. |
| refresh_after_errors | This parameter has no effect and is retained for Oracle compatibility. |
| purge_option | This parameter has no effect and is retained for Oracle compatibility. |
| parallelism | This parameter has no effect and is retained for Oracle compatibility. |
| heap_size | This parameter has no effect and is retained for Oracle compatibility. |
| atomic_refresh | This parameter has no effect and is retained for Oracle compatibility. |
| nested | Specifies the refresh mode for nested materialized views. Valid values:
NoteFor OceanBase Database V4.3.5, the |
| out_of_place | This parameter has no effect and is retained for Oracle compatibility. |
| skip_ext_data | This parameter has no effect and is retained for Oracle compatibility. |
| refresh_parallel | The concurrency level of the refresh operation, i.e., the number of threads that can execute refresh operations simultaneously.
NoteFor OceanBase Database V4.3.5, this parameter's default value was changed to 0 starting from V4.3.5 BP1. |
| nested_refresh_mode | Specifies whether the cascading refresh of nested materialized views is consistent or inconsistent. Valid values:
NoteFor OceanBase Database V4.3.5:
|
Examples
Independent refresh mode:
Specify only the
mv_nameparameter.CALL DBMS_MVIEW.REFRESH('mv1');Specify the
mv_nameparameter and set thenestedparameter tofalse.CALL DBMS_MVIEW.REFRESH('mv1', nested=> false);
Cascading non-consistent refresh mode:
Specify the
mv_nameparameter, set thenestedparameter totrue, and do not specify thenested_refresh_modeparameter.CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> true);Specify the
mv_nameparameter, set thenestedparameter totrue, and set thenested_refresh_modeparameter toinconsistent.CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> true, nested_refresh_mode => 'inconsistent');
Cascading consistent refresh mode:
Specify the
mv_nameparameter, set thenestedparameter totrue, and set thenested_refresh_modeparameter toconsistent.CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> true, nested_refresh_mode => 'consistent');The following examples show error scenarios:
If the
nestedparameter is set tofalseand thenested_refresh_modeparameter is also specified, an error occurs.CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> false, nested_refresh_mode => 'xxxx');If the
nestedparameter is not specified and thenested_refresh_modeparameter is specified, an error occurs.CALL DBMS_MVIEW.REFRESH('mv1', nested_refresh_mode => 'xxxx');If the
nestedparameter is set totrueand thenested_refresh_modeparameter is set to an invalid string, an error occurs.CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> true, nested_refresh_mode => 'invalid string');
References
For more information about refreshing materialized views, see Refresh a materialized view.