REFRESHE 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-compatible 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 refresh. Specifying the nested and nested_refresh_mode parameters is meaningless. By default, the refresh is independent.
| Parameter | Description |
|---|---|
| list | A comma-separated list of names of the materialized views to be refreshed. At present, only a single materialized view can be refreshed at a time. Multiple materialized views cannot be refreshed simultaneously. |
| tab | An array of names of the materialized views to be refreshed. At present, only an array of names of a single materialized view can be refreshed at a time. Multiple arrays of names of materialized views cannot be refreshed simultaneously. |
| method | The refresh strategy of the materialized view. You can specify a default strategy when you create a materialized view. Valid values:
|
| rollback_seg | This parameter is reserved for Oracle compatibility. |
| push_deferred_rpc | This parameter is reserved for Oracle compatibility. |
| refresh_after_errors | This parameter is reserved for Oracle compatibility. |
| purge_option | This parameter is reserved for Oracle compatibility. |
| parallelism | This parameter is reserved for Oracle compatibility. |
| heap_size | This parameter is reserved for Oracle compatibility. |
| atomic_refresh | This parameter is reserved for Oracle compatibility. |
| nested | The refresh mode of a nested materialized view. Valid values:
|
| out_of_place | This parameter is reserved for Oracle compatibility. |
| skip_ext_data | This parameter is reserved for Oracle compatibility. |
| refresh_parallel | The concurrency level of the refresh operation, that is, the number of threads that can be executed simultaneously. |
| nested_refresh_mode | The refresh mode of a nested materialized view. Valid values:
|
Examples
Independent refresh:
Specify only the
mv_nameparameter.obclient> CALL DBMS_MVIEW.REFRESH('mv1');Specify the
mv_nameparameter and set the value of thenestedparameter tofalse.obclient> CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> false);
Non-consistent cascading refresh:
Specify the
mv_nameparameter, set the value of thenestedparameter totrue, and do not specify thenested_refresh_modeparameter.obclient> CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> true);Specify the
mv_nameparameter, set the value of thenestedparameter totrue, and set the value of thenested_refresh_modeparameter toinconsistent.obclient> CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> true, nested_refresh_mode => 'inconsistent');
Consistent cascading refresh:
Specify the
mv_nameparameter, set the value of thenestedparameter totrue, and set the value of thenested_refresh_modeparameter toconsistent.obclient> CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> true, nested_refresh_mode => 'consistent');The following examples show error scenarios:
If the value of the
nestedparameter isfalseand the value of thenested_refresh_modeparameter is specified, an error is returned.obclient> CALL DBMS_MVIEW.REFRESH( 'mv1', nested=> false, nested_refresh_mode => 'xxxx');If the value of the
nestedparameter is not specified and the value of thenested_refresh_modeparameter is specified, an error is returned.obclient> CALL DBMS_MVIEW.REFRESH( 'mv1', nested_refresh_mode => 'xxxx');If the value of the
nestedparameter istrueand the value of thenested_refresh_modeparameter is an invalid string, an error is returned.obclient> 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.