The REFRESH procedure refreshes the 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. The nested and nested_refresh_mode parameters are invalid and will be ignored. By default, they are independently refreshed.
| Parameter | Description |
|---|---|
| list | The list of names of materialized views to be refreshed, which are separated with commas (,). You can specify only one materialized view at a time in the current version. |
| tab | The array of names of materialized views to be refreshed. You can specify only one materialized view at a time in the current version. |
| method | The refresh strategy for the materialized view. You can specify a default strategy when you create a materialized view. Valid values:
|
| rollback_seg | This parameter is reserved only for compatibility with Oracle. |
| push_deferred_rpc | This parameter is reserved only for compatibility with Oracle. |
| refresh_after_errors | This parameter is reserved only for compatibility with Oracle. |
| purge_option | This parameter is reserved only for compatibility with Oracle. |
| parallelism | This parameter is reserved only for compatibility with Oracle. |
| heap_size | This parameter is reserved only for compatibility with Oracle. |
| atomic_refresh | This parameter is reserved only for compatibility with Oracle. |
| nested | The refresh mode of the nested materialized view. Valid values:
NoteFor OceanBase Database V4.3.5, this parameter takes effect starting from V4.3.5 BP3. |
| out_of_place | This parameter is reserved only for compatibility with Oracle. |
| skip_ext_data | This parameter is reserved only for compatibility with Oracle. |
| refresh_parallel | The number of threads that execute the refresh operation at the same time.
NoteFor OceanBase Database V4.3.5, the default value for this parameter is changed to 0 starting from version V4.3.5 BP1. |
| nested_refresh_mode | Specifies whether to perform consistent refreshes or inconsistent refreshes for a nested materialized view in cascade. Valid values:
NoteFor OceanBase Database V4.3.5, this parameter takes effect starting from V4.3.5 BP3. |
Examples
Independent refresh mode:
Only specify the
mv_nameparameter.CALL DBMS_MVIEW.REFRESH('mv1');Specify the
mv_nameparameter and set thenestedparameter tofalse.CALL DBMS_MVIEW.REFRESH('mv1', nested=> false);
Inconsistent cascade refresh mode:
Specify the
mv_name,nestedparameters, and do not specify thenested_refresh_modeparameter.CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> true);Specify the
mv_name,nestedparameters, and set thenested_refresh_modeparameter toinconsistent.CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> true, nested_refresh_mode => 'inconsistent');
Consistent cascade refresh mode:
Specify the
mv_name,nested, andnested_refresh_modeparameters.CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> true, nested_refresh_mode => 'consistent');Error scenarios:
The
nestedparameter is set tofalse, and thenested_refresh_modeparameter is also specified. An error occurs.CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> false, nested_refresh_mode => 'xxxx');The
nested_refresh_modeparameter is specified, but thenestedparameter is not specified. An error occurs.CALL DBMS_MVIEW.REFRESH('mv1', nested_refresh_mode => 'xxxx');The
nestedparameter is set totrue, and 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 a materialized view, see Refresh a materialized view.