PolarDB:DBMS_MVIEW

Syntax

PROCEDURE REFRESH_ALL_MVIEWS (
    number_of_failures     OUT   BINARY_INTEGER,
    method                 IN    VARCHAR2         := NULL,
    rollback_seg           IN    VARCHAR2         := NULL,
    refresh_after_errors   IN    BOOLEAN          := FALSE,
    atomic_refresh         IN    BOOLEAN          := TRUE,
    out_of_place           IN    BOOLEAN          := FALSE);

Parameters

Example

The following example creates a table and a materialized view, inserts a new row, and then calls REFRESH_ALL_MVIEWS to sync the view.

CREATE TABLE test(a int, b int);

INSERT INTO test VALUES (1, 2);
CREATE MATERIALIZED VIEW mv0 AS SELECT a, b FROM test;

INSERT INTO test VALUES (2, 3);

-- Before refresh: the view shows only the original row
SELECT * FROM mv0 ORDER BY 1, 2;
 a | b
---+---
 1 | 2
(1 row)

DECLARE
    a BINARY_INTEGER;
BEGIN
    DBMS_MVIEW.REFRESH_ALL_MVIEWS(a);
    -- The num of refresh failures is:0
    DBMS_OUTPUT.PUT_LINE('The num of refresh failures is:' || a);
END;

-- After refresh: the view includes the new row
SELECT * FROM mv0 ORDER BY 1, 2;
 a | b
---+---
 1 | 2
 2 | 3
(2 rows)

Syntax

-- Overload 1: pass view names as a comma-separated string
PROCEDURE REFRESH (
    list                   IN     VARCHAR2,
    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);

-- Overload 2: pass view names as an array
PROCEDURE REFRESH (
    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);

Parameters

Example

The following example refreshes a specific materialized view using the array overload.

CREATE TABLE test(a int, b int);

INSERT INTO test VALUES (1, 2);
CREATE MATERIALIZED VIEW mv0 AS SELECT a, b FROM test;

INSERT INTO test VALUES (2, 3);

-- Before refresh: the view shows only the original row
SELECT * FROM mv0 ORDER BY 1, 2;
 a | b
---+---
 1 | 2
(1 row)

DECLARE
    la DBMS_UTILITY.UNCL_ARRAY;
BEGIN
    la := DBMS_UTILITY.UNCL_ARRAY('mv0');
    DBMS_MVIEW.REFRESH(tab => la,
                       method => 'A',
                       rollback_seg => NULL,
                       push_deferred_rpc => true,
                       refresh_after_errors => false,
                       purge_option => 1,
                       nested => false,
                       out_of_place => true,
                       skip_ext_data => true);
END;

-- After refresh: the view includes the new row
SELECT * FROM mv0 ORDER BY 1, 2;
 a | b
---+---
 1 | 2
 2 | 3
(2 rows)

Syntax

-- Overload 1: pass object names as a comma-separated string
PROCEDURE REFRESH_DEPENDENT (
    number_of_failures     OUT    BINARY_INTEGER,
    list                   IN     VARCHAR2,
    method                 IN     VARCHAR2    := NULL,
    rollback_seg           IN     VARCHAR2    := NULL,
    refresh_after_errors   IN     BOOLEAN     := false,
    atomic_refresh         IN     BOOLEAN     := true,
    nested                 IN     BOOLEAN     := false,
    out_of_place           IN     BOOLEAN     := false);

-- Overload 2: pass object names as an array
PROCEDURE REFRESH_DEPENDENT (
    number_of_failures     OUT    BINARY_INTEGER,
    tab                    IN     DBMS_UTILITY.UNCL_ARRAY,
    method                 IN     VARCHAR2    := NULL,
    rollback_seg           IN     VARCHAR2    := NULL,
    refresh_after_errors   IN     BOOLEAN     := false,
    atomic_refresh         IN     BOOLEAN     := true,
    nested                 IN     BOOLEAN     := false,
    out_of_place           IN     BOOLEAN     := false);

Parameters

Example

The following example uses DBMS_UTILITY.COMMA_TO_TABLE to convert a table name into an array, then refreshes all materialized views that depend on that table.

CREATE TABLE test(a int, b int);

INSERT INTO test VALUES (1, 2);
CREATE MATERIALIZED VIEW mv0 AS SELECT a, b FROM test;

INSERT INTO test VALUES (2, 3);

-- Before refresh: the view shows only the original row
SELECT * FROM mv0 ORDER BY 1, 2;
 a | b
---+---
 1 | 2
(1 row)

DECLARE
    la  DBMS_UTILITY.UNCL_ARRAY;
    len INTEGER;
    nof BINARY_INTEGER;
BEGIN
    DBMS_UTILITY.COMMA_TO_TABLE('test', len, la);
    DBMS_MVIEW.REFRESH_DEPENDENT(number_of_failures => nof,
                                 tab => la,
                                 method => 'A',
                                 rollback_seg => NULL,
                                 refresh_after_errors => true,
                                 atomic_refresh => false,
                                 nested => false,
                                 out_of_place => true);
    -- The num of refresh failures is:0
    DBMS_OUTPUT.PUT_LINE('The num of refresh failures is:' || nof);
END;

-- After refresh: the view includes the new row
SELECT * FROM mv0 ORDER BY 1, 2;
 a | b
---+---
 1 | 2
 2 | 3
(2 rows)

Syntax

PROCEDURE GET_MV_DEPENDENCIES (
    mvlist  IN   VARCHAR2,
    deplist OUT  VARCHAR2);

Parameters

Example

The following example creates two materialized views on the same base table, then calls GET_MV_DEPENDENCIES to list them.

CREATE TABLE test(a int, b int);

CREATE MATERIALIZED VIEW mv0 AS SELECT a, b FROM test;
CREATE MATERIALIZED VIEW mv1 AS SELECT a, b FROM test;

DECLARE
    deplist VARCHAR2;
BEGIN
    DBMS_MVIEW.GET_MV_DEPENDENCIES('test', deplist);
    -- The dependencies are: "PUBLIC"."MV0", "PUBLIC"."MV1"
    DBMS_OUTPUT.PUT_LINE('The dependencies are: ' || deplist);
END;