8 Synchronous Refresh

What Is Synchronous Refresh?

Synchronous refresh is a new approach for maintaining tables and materialized views in a data warehouse where tables and materialized views are refreshed at the same time. In traditional refresh methods, the changes are applied to the base tables and the materialized views are refreshed separately with one of the following refresh methods:

• Log-based incremental (fast) refresh using materialized view logs if such logs are available

• PCT refresh if it is applicable

• Complete refresh

Synchronous refresh combines some elements of log-based incremental (fast) refresh and PCT refresh methods, but it is applicable only to ON DEMAND materialized views, unlike the other two methods. There are three major differences between it and the other refresh methods:

• Synchronous refresh requires you to register the tables and materialized views.

• Synchronous refresh requires you to specify changes to the data according to some formally specified rules.

• Synchronous refresh works by dividing the refresh operation into two steps: preparation and execution. This approach provides some important advantages over the other methods, such as better performance and more control.

Synchronous refresh APIs are defined in a new package called DBMS_SYNC_REFRESH. For more information about this package, see Oracle Database PL/SQL Packages and Types Reference.

Why Use Synchronous Refresh?

Synchronous refresh offers the following advantages over traditional types of refresh methods:

• It coordinates the loading of the changes into the base tables with the extremely efficient refresh of the dependent materialized views themselves.

• It decreases the time materialized views are not available to be used by the Optimizer to rewrite queries.

• It is well-suited for a wide class of materialized views (materialized aggregate views and materialized join views) commonly used in data warehouses. It does require the materialized views be partitioned as well as the fact tables, and if materialized views are not currently partitioned, they can be efficiently partitioned to take advantage of synchronous refresh.

• It fully exploits partitioning and the nature of the data warehouse load cycle to guarantee synchronization between the materialized view and the base table throughout the refresh procedure.

• In a typical data warehouse, data preparation consists of extracting the data from one or more sources, cleansing, and formatting it for consistency, and transforming into the data warehouse schema. The data preparation area is called the staging area and the base tables in a data warehouse are loaded from the tables in the staging area. The synchronous refresh method fits into this model because it allows you to load change data into the staging logs.

• The staging logs play the same role as materialized view logs in the conventional fast refresh method. There is, however, an important difference. In the conventional fast refresh method, the base table is first updated and the changes are then applied from the materialized view log to the materialized views. But in the synchronous refresh method, the changes from the staging log are applied to refresh the materialized views while also being applied to the base tables.

• Most materialized views in a data warehouse typically employ a star or snowflake schema with fact and dimension tables joined in a foreign key to primary key relationship. The synchronous refresh method can handle both schemas in all possible change data load scenarios, ranging from rows being added to only the fact table, to arbitrary changes to the fact and dimension tables.

• Instead of providing the change load data in the staging logs, you have a choice of directly providing the change data in the form of outside tables containing the data to be exchanged with the affected partition in the base table. This capability is provided by the REGISTER_PARTITION_OPERATION procedure in the DBMS_SYNC_REFRESH package.

Registering Tables and Materialized Views for Synchronous Refresh

Before actually performing synchronous refresh, you must register the appropriate tables and materialized views. Synchronous refresh provides these methods to register tables and materialized views:

• Tables are registered with synchronous refresh by creating a staging log on them. A staging log is created with the CREATE MATERIALIZED VIEW LOG statement whose syntax has been extended in this release to create staging logs as well as the familiar materialized view logs used for the traditional incremental refresh. After you create a staging log on a table, it is deemed to be registered with synchronous refresh and can be modified only by using the synchronous refresh procedures. In other words, a table with a staging log defined on it is registered with synchronous refresh and cannot be modified directly by the user.

  For more information about the CREATE MATERIALIZED VIEW LOG statement, see Oracle Database SQL Language Reference.

• Materialized views are registered with synchronous refresh using the REGISTER_MVIEWS procedure in the DBMS_SYNC_REFRESH package. The REGISTER_MVIEWS procedure implicitly creates groups of related objects called sync refresh groups. A sync refresh group consists of all related materialized views and tables that must be refreshed together as a single entity because they are dependent on one another.

  For more information about the DBMS_SYNC_REFRESH package, see Oracle Database PL/SQL Packages and Types Reference.

Specifying Change Data for Refresh

In the other refresh methods, you can directly modify the base tables of the materialized view, and the issue of specifying change data does not arise. But with synchronous refresh, you are required to specify and prepare the change data according to certain formally specified rules and using APIs provided by the DBMS_SYNC_REFRESH package.

There are two ways to specify the change data:

• Provide the change data in an outside table and register it with the REGISTER_PARTITION_OPERATION procedure.

  See "Working with Partition Operations" for more details.

• Provide the change data by in staging logs and process them with the PREPARE_STAGING_LOG procedure. The format of the staging logs and rules for populating are described in "Working with Staging Logs". You are required to run the PREPARE_STAGING_LOG procedure for every table before performing the refresh operation on that table.

Synchronous Refresh Preparation and Execution

After preparing the change data, you can perform the actual refresh operation. Synchronous refresh takes a new approach to refresh execution. It works by dividing the refresh operation into two steps: preparation and execution. This is one of the main differences between it and the other refresh methods and provides some important benefits.

The preparation step determines the mapping between the fact table partitions and the materialized view partitions. This step computes the new tables corresponding only to the partitions of the fact table that have been changed by the incremental change data load. After these tables, called outside tables, have been computed, the actual execution of the refresh operation takes place in the execution step, which consists of just exchanging the outside tables with the corresponding partitions in the fact table or materialized view.

By dividing the refresh execution step into two phases and providing separate procedures for them, synchronous refresh not only provides you control over the refresh execution process, but also improves overall system performance. It does this by minimizing the time the materialized views are not available for use by direct access or the Optimizer because they are modified by the refresh process. During the preparation phase, the materialized view and its tables are not modified because at this time all the refresh changes are recorded in the outside table. Consequently, the materialized view is available to any query that needs to read them. It is only during execution that the tables and materialized views are modified. Execution performance is mainly affected by the number of changes to the dimension tables; if this number is small, then the performance should be very good because the exchange partition operations are themselves very fast.

The DBMS_SYNC_REFRESH package provides the PREPARE_REFRESH and EXECUTE_REFRESH procedures to perform these two steps.

Materialized View Eligibility Rules and Restrictions for Synchronous Refresh

The primary requirement for a materialized view to be eligible for synchronous refresh is that the materialized view must be partitioned with a key that can be derived from the partition key of its fact table. The following sections describe the other requirements for eligibility for synchronous refresh.

This section contains the following topics:

• Synchronous Refresh Restrictions: Partitioning

• Synchronous Refresh Restrictions: Refresh Options

• Synchronous Refresh Restrictions: Constraints

• Synchronous Refresh Restrictions: Tables

• Synchronous Refresh Restrictions: Materialized Views

• Synchronous Refresh Restrictions: Materialized Views with Aggregates

The Registration Phase

In this phase (Figure 8-1), you register the objects for use with synchronous refresh. The two steps in this phase are registration of tables first and then materialized views. You register the tables (by creating staging logs) and materialized views (with the REGISTER_MVIEWS procedure). The staging logs are created with the CREATE MATERIALIZED LOG … FOR SYNCHRONOUS REFRESH statement. If a table already has a regular materialized view log, the ALTER MATERIALIZED LOG … FOR SYNCHRONOUS REFRESH statement can be used to convert it to a staging log.

Figure 8-1 Registration Phase

Description of "Figure 8-1 Registration Phase"

You can create a staging log with a statement, as show in Example 8-1.

CREATE MATERIALIZED VIEW LOG ON fact
FOR SYNCHRONOUS REFRESH USING st_fact;

ALTER MATERIALIZED VIEW LOG ON fact
FOR SYNCHRONOUS REFRESH USING st_fact;

You can register a materialized view with a statement, as shown in Example 8-2.

EXECUTE DBMS_SYNC_REFRESH.REGISTER_MVIEWS('MV1');

EXECUTE DBMS_SYNC_REFRESH.REGISTER_MVIEWS('mv2, mv2_year, mv1_halfmonth');

The Synchronous Refresh Phase

Figure 8-2 shows the synchronous refresh phase. This phase can be used repeatedly to perform synchronous refresh. The three main steps in this phase are:

1. Prepare the change data for the refresh operation. You can provide the change data in a table and register it with the REGISTER_PARTITION_OPERATION procedure or provide the data by populating the staging logs. The staging logs must be processed with the PREPARE_STAGING_LOG procedure before proceeding to the next step.

   An example is Example 8-12.

2. Perform the first step of the refresh operation (PREPARE_REFRESH). This can potentially be a long-running operation because it prepares and loads the outside tables.

   An example is Example 8-16.

3. Perform the second and last step of the refresh operation (EXECUTE_REFRESH). This usually runs very fast because it usually consists of a series of partition-exchange operations.

   An example is Example 8-20.

In Figure 8-2, solid arrows show the standard control flow and dashed arrows are used for error-handling cases. If either of the refresh operations (PREPARE_REFRESH or EXECUTE_REFRESH) raises user errors, you use an ABORT_REFRESH procedure to restore tables and materialized views to the state that existed before the refresh operation, fix the problem, and retry the refresh operation starting from the beginning.

Figure 8-2 Refresh Phase

Description of "Figure 8-2 Refresh Phase"

The Unregistration Phase

If you choose to stop using synchronous refresh, then you must unregister the materialized views as shown in Figure 8-3. The materialized views are first unregistered with the UNREGISTER_MVIEWS procedure. The tables are then unregistered by either dropping their staging logs or altering the staging logs to ordinary logs. Note that if the staging logs are converted to be ordinary materialized view logs with an ALTER MATERIALIZED LOG … FOR FAST REFRESH statement, then the materialized views can be maintained with standard fast-refresh methods.

Figure 8-3 Unregistration Phase

Description of "Figure 8-3 Unregistration Phase"

Example 8-3 illustrates how to unregister the single materialized view MV1.

EXECUTE DBMS_SYNC_REFRESH.UNREGISTER_MVIEWS('MV1');

EXECUTE DBMS_SYNC_REFRESH.UNREGISTER_MVIEWS('mv2, mv2_year, mv1_halfmonth');

Example 8-4 illustrates how to drop the staging log.

DROP MATERIALIZED VIEW LOG ON fact;

ALTER MATERIALIZED VIEW LOG ON fact
FOR FAST REFRESH;

Examples of Common Actions with Synchronous Refresh Groups

The synchronous refresh demo scripts in the rdbms/demo directory enable you to view typical operations that you are likely to perform. The main script is syncref_run.sql, and its log is syncref_run.log. Example 8-5, Example 8-6, and Example 8-7 below illustrate the different contexts in which the GET_GROUP_ID function can be used.

Example 8-5 illustrates how to display the objects registered in a group after registering them.

EXECUTE DBMS_SYNC_REFRESH.REGISTER_MVIEWS('MV1');
SELECT NAME, TYPE, STAGING_LOG_NAME FROM USER_SR_OBJ
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1')
ORDER BY TYPE, NAME;

NAME       TYPE       STAGING_LOG_NAME
---------- ---------- ----------------
MV1        MVIEW
FACT       TABLE      ST_FACT
STORE      TABLE      ST_STORE
TIME       TABLE      ST_TIME

Example 8-6 illustrates how to invoke refresh operations.

EXECUTE DBMS_SYNC_REFRESH.PREPARE_REFRESH( -
        DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1'));
EXECUTE DBMS_SYNC_REFRESH.EXECUTE_REFRESH( -
        DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1'));
SELECT NAME, TYPE, STATUS FROM USER_SR_OBJ_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1')
ORDER BY TYPE, NAME;

Example 8-7 illustrates how to verify the status of objects registered in a group after an EXECUTE_REFRESH operation.

SELECT NAME, TYPE, STATUS FROM USER_SR_OBJ_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1')
ORDER BY TYPE, NAME;

NAME             TYPE       STATUS
---------------- ---------- ----------------
MV1              MVIEW      COMPLETE
FACT             TABLE      COMPLETE
STORE            TABLE      COMPLETE
TIME             TABLE      COMPLETE

Examples of Working with Multiple Synchronous Refresh Groups

You can work with multiple refresh groups at one time with the following APIs:

• GET_GROUP_ID_LIST

  Takes a list of materialized views as input and returns their group IDs in a list.

• GET_ALL_GROUP_IDS

  Returns the group IDs of all groups in the system in a list.

• The prepare refresh procedures (PREPARE_REFRESH, EXECUTE_REFRESH, and ABORT_REFRESH) can work multiple groups. Their overloaded versions accept lists of group IDs at a time.

Example 8-8 illustrates how to prepare the sync refresh groups of MV1, MV2, and MV3.

EXECUTE DBMS_SYNC_REFRESH.PREPARE_REFRESH(
        DBMS_SYNC_REFRESH.GET_GROUP_ID_LIST('MV1, MV2, MV3'));

Example 8-9 illustrates how to prepare and execute the refresh of all sync refresh groups in the system.

EXECUTE DBMS_SYNC_REFRESH.PREPARE_REFRESH(
        DBMS_SYNC_REFRESH.GET_ALL_GROUP_IDS);

EXECUTE DBMS_SYNC_REFRESH.EXECUTE_REFRESH(
        DBMS_SYNC_REFRESH.GET_ALL_GROUP_IDS);

Working with Partition Operations

Using the REGISTER_PARTITION_OPERATION procedure, you can provide the change data directly. This method is applicable only to fact tables. For each fact table partition that is changed, you must provide an outside table containing the data for that partition. The synchronous refresh demo (syncref_run.sql and syncref_run.log) contains an example. The steps are:

1. Create an outside table for the partition that it is intended to replace. It must have the same constraints as the fact table, and can be created in any desired tablespace.

   CREATE TABLE      fact_ot_fp3(
      time_key       DATE NOT NULL REFERENCES time(time_key),
      store_key      INTEGER NOT NULL REFERENCES store(store_key),
      dollar_sales   NUMBER (6,2),
      unit_sales     INTEGER)
      tablespace     syncref_fp3_tbs;
   

2. Insert the data for this partition into the outside table.

3. Register this table for partition exchange.

   begin
      DBMS_SYNC_REFRESH.REGISTER_PARTITION_OPERATION(
          partition_op                 => 'EXCHANGE',
          schema_name                  => 'SYNCREF_USER',
          base_table_name              => 'FACT',
          partition_name               => 'FP3',
          outside_partn_table_schema   => 'SYNCREF_USER',
          outside_partn_table_name     => 'FACT_OT_FP3');
      end;
     /
   \
   

When you register the outside table and execute the refresh, Oracle Database performs the following operation at EXECUTE_REFRESH time:

ALTER TABLE FACT EXCHANGE PARTITION fp3 WITH TABLE fact_ot_fp3
INCLUDING INDEXES WITHOUT VALIDATION;

However, you are not allowed to issue the above statement directly on your own. If you do, Oracle Database will give this error:

ORA-31908: Cannot modify the contents of a table with a staging log.

Besides the EXCHANGE operation, the two other partition operations that can be registered with the REGISTER_PARTITION_OPERATION procedure are DROP and TRUNCATE.

Example 8-10 illustrates how to specify the drop of the first partition (FP1), by using the following statement.

begin
 DBMS_SYNC_REFRESH.REGISTER_PARTITION_OPERATION(
              partition_op                 => 'DROP',
              schema_name                  => 'SYNCREF_USER',
              base_table_name              => 'FACT',
              partition_name               => 'FP1');
end;
/

SELECT   TABLE_NAME, PARTITION_OP, PARTITION_NAME,
         OUTSIDE_TABLE_SCHEMA ot_schema, OUTSIDE_TABLE_NAME ot_name
FROM     USER_SR_PARTN_OPS
ORDER BY TABLE_NAME;
 
TABLE_NAME PARTITION_ PARTITION_NAME  OT_SCHEMA       OT_NAME
---------- ---------- --------------- --------------- --------------------
FACT       EXCHANGE   FP3             SYNCREF_USER    FACT_OT_FP3
 
1 row selected.

begin
 DBMS_SYNC_REFRESH.UNREGISTER_PARTITION_OPERATION(
              partition_op                  => 'EXCHANGE',
              schema_name                   => 'SYNCREF_USER',
              base_table_name               => 'FACT',
              partition_name                => 'FP3');
end;
/

Working with Staging Logs

In synchronous refresh, staging logs play a role similar to materialized view logs in incremental refresh. They are created with a DDL statement and can be altered to a materialized view log. Unlike materialized view logs, however, you are responsible for loading changes into the staging logs in a specified format. Each row in the staging log must have a key to identify it uniquely; this key is called the staging log key, and is defined in "Staging Log Key".

You are responsible for populating the staging log, which will consist of all the columns in the base table and an additional control column DMLTYPE$$ of type CHAR(2). This must have the value 'I' to denote the row is being inserted, 'D' for delete, and 'UN' and 'UO' for the new and old values of the row being updated, respectively. The last two must occur in pairs.

The staging log is validated by the PREPARE_STAGING_LOG procedure and consumed by the synchronous refresh operations (PREPARE_REFRESH and EXECUTE_REFRESH). During validation by PREPARE_STAGING_LOG, if errors are detected, they will be captured in an exceptions table. You can query the view USER_SR_STLOG_EXCEPTIONS to get details on the exceptions.

Synchronous refresh requires that, before calling PREPARE_REFRESH for sync refresh groups, the staging logs of all tables in the group must be processed with PREPARE_STAGING_LOG. This is necessary even if a table has no change data and its staging log is empty.

This section contains the following topics:

• Staging Log Key

• Staging Log Rules

• Columns Being Updated to NULL

• Examples of Working with Staging Logs

• Error Handling in Preparing Staging Logs

EXECUTE DBMS_SYNC_REFRESH.PREPARE_STAGING_LOG('syncref_user', 'store',
                                 DBMS_SYNC_REFRESH.INSERT_TRUSTED +
                                 DBMS_SYNC_REFRESH.DELETE_TRUSTED);

INSERT INTO st_store (dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE)
VALUES ('I',   5, 5, 'Store 5', '03060');

INSERT INTO st_store (dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE)
VALUES ('I',   6, 6, 'Store 6', '03062');
 
INSERT INTO st_store (dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE)
VALUES ('UO',  4, 4, 'Store 4', '03062');
 
INSERT INTO st_store (dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE)
VALUES ('UN',  4, 4, 'Stor4NewNam', '03062');
 
INSERT INTO st_store (dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE)
VALUES ('D',   3, 3, 'Store 3', '03060');

EXECUTE DBMS_SYNC_REFRESH.PREPARE_STAGING_LOG('syncref_user', 'store');

-- display initial contents of st_store

SELECT dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE
FROM st_store
ORDER BY STORE_KEY ASC, dmltype$$ DESC;

DM  STORE_KEY  STORE_NUMBER  STORE_NAME    ZIPCODE
--  ---------  ------------  ----------    -------
D            3            3  Store 3         03060
UO           4            4  Store 4         03062
UN           4            4  Stor4NewNam     03062
I            5            5  Store 5         03060
I            5            5  Store 6         03062

5 rows selected.

INSERT INTO st_store (dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE)
VALUES ('D',  3, NULL, NULL, NULL);

INSERT INTO st_store (dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE)
VALUES ('UO',  4, NULL, NULL, NULL);
 
INSERT INTO st_store (dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE)
VALUES ('UN',  4, NULL, NULL, '03063');
 
EXECUTE DBMS_SYNC_REFRESH.PREPARE_STAGING_LOG('syncref_user', 'store');
 
SELECT dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE
FROM ST_STORE ORDER BY STORE_KEY ASC, dmltype$$ DESC;
 
DM STORE_KEY STORE_NUMBER STORE_NAME  ZIPCODE
-- --------- ------------ ----------- ---------
D          3            3 Store 3     03060
UO         4            4 Store 4     03062
UN         4            4 Store 4     03063

INSERT INTO st_store (dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE)
VALUES ('UO',  4, NULL, 'Store 4', NULL);
 
INSERT INTO st_store (dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE)
VALUES ('UN',  4, NULL, NULL, '03063');
 
EXECUTE DBMS_SYNC_REFRESH.PREPARE_STAGING_LOG('syncref_user', 'store');
 
SELECT dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE
FROM st_store ORDER BY STORE_KEY ASC, dmltype$$ DESC;
 
DM STORE_KEY STORE_NUMBER STORE_NAME  ZIPCODE
-- --------- ------------ ----------- --------
UO         4            4 Store 4     03062
UN         4            4             03063

SELECT   TABLE_NAME, STAGING_LOG_NAME, NUM_INSERTS, NUM_DELETE, NUM_UPDATES
FROM     USER_SR_STLOG_STATS
ORDER BY TABLE_NAME;
 
TABLE_NAME STAGING_LOG_NAME NUM_INSERTS NUM_DELETES NUM_UPDATES
---------- ---------------- ----------- ----------- -----------
FACT       ST_FACT                    4           1           1
STORE      ST_STORE                   2           1           1
TIME       ST_TIME                    1           0           0
 
3 rows selected.

Overview of the Status of Refresh Operations

The DBMS_SYNC_REFRESH package provides three procedures to control the refresh execution process. You initiate synchronous refresh with the PREPARE_REFRESH procedure, which plans the entire refresh operation and does the bulk of the computational work for refresh, followed by the EXECUTE_REFRESH procedure, which carries out the refresh. The third procedure provided is ABORT_REFRESH, which is used to recover from errors if either of these procedures fails.

The USER_SR_GRP_STATUS and USER_SR_OBJ_STATUS catalog views contain all the information on the status of these refresh operations for current groups:

• The USER_SR_GRP_STATUS view shows the status of the group as a whole.

  • The OPERATION field indicates the current refresh procedure run on the group: PREPARE or EXECUTE.

  • The STATUS field indicates the status of the operation - RUNNING, COMPLETE, ERROR-SOFT, ERROR-HARD, ABORT, PARTIAL. These are explained in detail later.

  • The group is identified by its group ID.

• The USER_SR_OBJ_STATUS view shows the status of each individual object.

  • The object is identified by its owner, name, and type (TABLE or MVIEW) and group ID.

  • The STATUS field, which may be NOT PROCESSED, ABORT, or COMPLETE. These are explained in detail later.

How PREPARE_REFRESH Sets the STATUS Fields

When you launch a new PREPARE_REFRESH job, the group's STATUS is set to RUNNING and the STATUS of the objects in the group is set to NOT PROCESSED. When the PREPARE_REFRESH job finishes, the status of the objects remains unchanged, but the group's status is changed to one of following three values:

• COMPLETE if the job completed successfully.

• ERROR_SOFT if the job encountered the ORA-01536: space quota exceeded for tablespace '%s' error.

• ERROR_HARD otherwise (that is, if the job encountered any error other than ORA-01536).

Some points to keep in mind when using the PREPARE_REFRESH procedure:

• The NOT PROCESSED status of the objects in the group signifies that the data of the objects has not been modified by the PREPARE_REFRESH job. The data modification will occur only in the EXECUTE_REFRESH step, at which time the status will be changed as appropriate. This is described later.

• If the STATUS is ERROR_SOFT, you can fix the ORA-01536 error by increasing the space quota for the specified tablespace, and resume PREPARE_REFRESH. Alternatively, you can choose to abort the refresh with ABORT_REFRESH.

• If the STATUS value is ERROR_HARD, then your only option is to abort the refresh with ABORT_REFRESH.

• If the STATUS value after the PREPARE_REFRESH procedure finishes is RUNNING, then an error has occurred. Contact Oracle Support Services for assistance.

• A STATUS value of ERROR_HARD might be related to running out of resources because the PREPARE_REFRESH procedure can be resource-intensive. If you are not able to identify the problem, then contact Oracle Support Services for assistance. But if you can identify the problem and fix it, then you might be able to continue using synchronous refresh, by first running ABORT_REFRESH and then the PREPARE_REFRESH procedure.

• Remember that you can launch a new PREPARE_REFRESH job only when the previous refresh operation on the group (if any) has either completed execution successfully or has aborted.

• If the STATUS value of the PREPARE_REFRESH procedure at the end is not COMPLETE, you cannot proceed to the EXECUTE_REFRESH step. If you are unable to get PREPARE_REFRESH to work correctly, then you can proceed to the unregistration phase, and maintain the objects in the groups with other refresh methods.

Examples of PREPARE_REFRESH

This section offers examples of common cases when preparing a refresh.

Example 8-16 shows a PREPARE_REFRESH procedure completing successfully.

EXECUTE DBMS_SYNC_REFRESH.PREPARE_REFRESH( DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1'));
 
PL/SQL procedure successfully completed.
 
SELECT OPERATION, STATUS
FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');

OPERATION  STATUS
---------  ------
PREPARE    COMPLETE

Example 8-17 shows a PREPARE_REFRESH procedure encountering ORA-01536.

EXECUTE DBMS_SYNC_REFRESH.PREPARE_REFRESH( DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1'));
BEGIN DBMS_SYNC_REFRESH.PREPARE_REFRESH(DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1')); END;
 
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'DUMMY_TS'
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 63
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 411
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 429
ORA-06512: at line 1PL/SQL procedure successfully completed.
 
SELECT OPERATION, STATUS
FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');
 
OPERATION  STATUS
---------  ------
PREPARE    ERROR_SOFT

Example 8-18 is a continuation of Example 8-2. After the ORA-01536 error is raised, increase the tablespace for DUMMY_TS and rerun the PREPARE_REFRESH procedure, which now completes successfully. Note that the PREPARE_REFRESH procedure will resume processing from the place where it stopped. Also note the usage of the PREPARE_REFRESH procedure is no different from normal, and does not require any parameters or settings to indicate the procedure is being resumed.

EXECUTE DBMS_SYNC_REFRESH.PREPARE_REFRESH(DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1'));
 
PL/SQL procedure successfully completed.
 
SELECT OPERATION, STATUS
FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');
 
OPERATION  STATUS
---------  ------
PREPARE    COMPLETE

Example 8-19 assumes the PREPARE_REFRESH procedure has failed and the STATUS value is ERROR_HARD. You then run the ABORT_REFRESH procedure to abort the prepare job. Note that the STATUS value has changed from ERROR_HARD to ABORT at the end.

SELECT OPERATION, STATUS
FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');
 
OPERATION  STATUS
---------  ------
PREPARE    ERROR_HARD
 
EXECUTE DBMS_SYNC_REFRESH.ABORT_REFRESH( DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1'));
 
PL/SQL procedure successfully completed.
 
SELECT OPERATION, STATUS
FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');
 
OPERATION  STATUS
---------  ------
PREPARE    ABORT

How EXECUTE_REFRESH Sets the Status Fields

The EXECUTE_REFRESH procedure divides the group of objects in the sync refresh group into subgroups, each of which is refreshed atomically. The first subgroup consists of the base tables. Each materialized view in the sync refresh group is placed in a separate subgroup and refreshed atomically.

In the case of the EXECUTE_REFRESH procedure, the possible end states of the STATUS field are: COMPLETE, PARTIAL, and ABORT:

• STATUS = COMPLETE

  This state is reached if the base tables and all the materialized views refresh successfully.

• STATUS = ABORT

  This state indicates the refresh of the base tables subgroup has failed; the data in the tables and materialized views is consistent but unchanged. If this happens, then there should be an error associated with the failure. If it is a user error, such as a constraint violation, then you can fix the problem and retry the synchronous refresh operation from the beginning (that is, PREPARE_STAGING_LOG for each table in the group PREPARE_REFRESH and EXECUTE_REFRESH.). If it is not a user error, then you should contact Oracle Support Services.

• STATUS = PARTIAL

  If all the base tables refresh successfully and some, but not all, materialized views refresh successfully, then this state is reached. The data in the tables and materialized views that have refreshed successfully are consistent with one another; the other materialized views are stale and need complete refresh. If this happens, there should be an error associated with the failure. Most likely this is not a user error, but an Oracle error that you should report to Oracle Support Services. You have two choices in this state:

  • Retry execution of the EXECUTE_REFRESH procedure. In such a case, EXECUTE_REFRESH will retry the refresh of the failed materialized views with another refresh method like PCT-refresh or COMPLETE refresh. If all materialized views succeed, then the status will be set to COMPLETE. Otherwise, the status will remain at PARTIAL.

  • Invoke the ABORT_REFRESH procedure to abort the materialized views. This will roll back changes to all materialized views and base tables. They will all have the same data as in the original state before any of the changes in the staging logs or registered partition operations has been applied to them.

In the case of errors in the EXECUTE_REFRESH procedure, the following fields in the USER_SR_GRP_STATUS view are also useful:

• NUM_MVS_COMPLETED, which contains the number of materialized views that completed the refresh operation successfully.

• NUM_MVS_ABORTED, which contains the number of materialized views that aborted.

• ERROR and ERROR_MESSAGE, which records the error encountered in the operation.

At the end of the EXECUTE_REFRESH, procedure, the statuses of the objects in the group are marked as follows in the USER_SR_OBJ_STATUS view:

• The status of an object is set to COMPLETE if the changes were applied to it successfully.

• The status of an object is set to ABORT if the changes were not applied successfully. In this case, the object will be in the same state as it was before the refresh operation. The ERROR and ERROR_MESSAGE fields record the error encountered in the operation.

• The status of an object remains NOT PROCESSED if no changes were applied to it.

Examples of EXECUTE_REFRESH

This section provides examples of common cases when executing a refresh.

Example 8-20 shows an EXECUTE_REFRESH procedure completing successfully.

EXECUTE DBMS_SYNC_REFRESH.EXECUTE_REFRESH( DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1'));

PL/SQL procedure successfully completed.
 
SELECT OPERATION, STATUS
FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');
 
OPERATION  STATUS
---------  ------
EXECUTE    COMPLETE

Example 8-21 shows an EXECUTE_REFRESH procedure succeeding partially. In this example, the EXECUTE_REFRESH procedure fails after refreshing the base tables but before completing the refresh of all the materialized views. The resulting status of the group is PARTIAL and the QSM-03280 error message is thrown.

EXECUTE DBMS_SYNC_REFRESH.EXECUTE_REFRESH(DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1'));
BEGIN DBMS_SYNC_REFRESH.EXECUTE_REFRESH(DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1')); END;
 
*
ERROR at line 1:
ORA-31928: Synchronous refresh error
QSM-03280: One or more materialized views failed to refresh successfully.
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 63
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 411
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 446
ORA-06512: at line 1

SELECT OPERATION, STATUS FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');
 
OPERATION  STATUS
---------  -------------
EXECUTE    PARTIAL

SELECT NAME, TYPE, STATUS FROM USER_SR_OBJ_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1')
ORDER BY TYPE, NAME;
 
NAME             TYPE       STATUS
---------------- ---------- ----------------
MV1              MVIEW      ABORT
MV1_HALFMONTH    MVIEW      COMPLETE
MV2              MVIEW      COMPLETE
MV2_YEAR         MVIEW      COMPLETE
FACT             TABLE      COMPLETE
STORE            TABLE      NOT PROCESSED
TIME             TABLE      NOT PROCESSED
 
7 rows selected.
 
SELECT NUM_TBLS, NUM_MVS, NUM_MVS_COMPLETED, NUM_MVS_ABORTED
FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');
 
NUM_TBLS NUM_MVS NUM_MVS_COMPLETED NUM_MVS_ABORTED
-------- ------- ----------------- ---------------
       3       4                 3               1

Example 8-22 illustrates a continuation of Example 8-21. You retry the EXECUTE_REFRESH procedure and it succeeds:

EXECUTE DBMS_SYNC_REFRESH.EXECUTE_REFRESH(DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1'));
 
PL/SQL procedure successfully completed.
 
--Check the status of the group itself after the EXECUTE_REFRESH operation;
--note that the operation field is set to EXECUTE and status is COMPLETE.

SELECT OPERATION, STATUS
FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');
 
OPERATION  STATUS
---------  ---------
EXECUTE    COMPLETE

SELECT NAME, TYPE, STATUS FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1')
ORDER BY TYPE, NAME;
 
NAME             TYPE       STATUS
---------------- ---------- ----------------
MV1              MVIEW      COMPLETE
MV1_HALFMONTH    MVIEW      COMPLETE
MV2              MVIEW      COMPLETE
MV2_YEAR         MVIEW      COMPLETE
FACT             TABLE      COMPLETE
STORE            TABLE      NOT PROCESSED
TIME             TABLE      NOT PROCESSED
 
7 rows selected.
 
SELECT NUM_TBLS, NUM_MVS, NUM_MVS_COMPLETED, NUM_MVS_ABORTED
FROM  USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');
 
NUM_TBLS NUM_MVS NUM_MVS_COMPLETED NUM_MVS_ABORTED
-------- ------- ----------------- ---------------
       3       4                 4               0

Example 8-23 illustrates aborting a refresh procedure that is in a PARTIAL state.

EXECUTE DBMS_SYNC_REFRESH.ABORT_REFRESH(DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1'));
 
PL/SQL procedure successfully completed.

SELECT OPERATION, STATUS FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');
 
OPERATION  STATUS
---------- -------
EXECUTE    ABORT

SELECT NAME, TYPE, STATUS FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1')
ORDER BY TYPE, NAME;
 
NAME             TYPE       STATUS
---------------- ---------- ----------------
MV1              MVIEW      ABORT
MV1_HALFMONTH    MVIEW      ABORT
MV2              MVIEW      ABORT
MV2_YEAR         MVIEW      ABORT
FACT             TABLE      ABORT
STORE            TABLE      NOT PROCESSED
TIME             TABLE      NOT PROCESSED
 
7 rows selected.
 
SELECT NUM_TBLS, NUM_MVS, NUM_MVS_COMPLETED, NUM_MVS_ABORTED
FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');
 
NUM_TBLS NUM_MVS NUM_MVS_COMPLETED NUM_MVS_ABORTED
-------- ------- ----------------- ---------------
       3       4                 0               4

Example of EXECUTE_REFRESH with Constraint Violations

In the synchronous refresh method, change data is loaded into the tables and materialized views at the same time to keep them synchronized. In the other refresh methods, change data is loaded into tables first, and any constraints that are enabled are checked at that time. In the synchronous refresh method, the outside table is prepared using trusted data from the user, and constraint validation is turned off to save execution time. The following example shows a constraint violation that is caught by the EXECUTE_REFRESH procedure. In such cases, the final status of the EXECUTE_REFRESH procedure will be ABORT. You will have to identify and fix the problem in the change data and begin the synchronous refresh phase all over.

INSERT INTO st_store (dmltype$$, STORE_KEY, STORE_NUMBER, STORE_NAME, ZIPCODE)
VALUES ('D',   3, 3, 'Store 3', '03060');
 
-- Prepare the staging logs
EXECUTE DBMS_SYNC_REFRESH.PREPARE_STAGING_LOG('syncref_user', 'fact');
EXECUTE DBMS_SYNC_REFRESH.PREPARE_STAGING_LOG('syncref_user', 'time');
EXECUTE DBMS_SYNC_REFRESH.PREPARE_STAGING_LOG('syncref_user', 'store');
 
-- Prepare the refresh
EXECUTE DBMS_SYNC_REFRESH.PREPARE_REFRESH(DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1'));
 
-- Execute the refresh
EXECUTE DBMS_SYNC_REFRESH.EXECUTE_REFRESH( -
           DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1'));
BEGIN DBMS_SYNC_REFRESH.EXECUTE_REFRESH(DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1')); END;
 
*
ERROR at line 1:
ORA-02292: integrity constraint (SYNCREF_USER.SYS_C0031765) violated - child record found
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 63
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 411
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 446
ORA-06512: at line 1

SELECT OPERATION, STATUS
FROM USER_SR_GRP_STATUS
WHERE GROUP_ID = DBMS_SYNC_REFRESH.GET_GROUP_ID('MV1');
 
OPERATION  STATUS
---------- --------------
EXECUTE    ABORT

Using SYNCREF_TABLE

The output of the CAN_SYNCREF_TABLE function can be directed to a table named SYNCREF_TABLE. You are responsible for creating SYNCREF_TABLE; it can be dropped when it is no longer needed. The format of SYNCREF_TABLE is as follows:

CREATE TABLE SYNCREF_TABLE(
          statement_id             VARCHAR2(30),
          schema_name              VARCHAR2(30),
          table_name               VARCHAR2(30),
          mv_schema_name           VARCHAR2(30),
          mv_name                  VARCHAR2(30),
          eligible                 VARCHAR2(1),   -- 'Y' , 'N'
          seq_num                  NUMBER,
          msg_number               NUMBER,
          message                  VARCHAR2(4000)
);

You must provide a different statement_id parameter for each invocation of this procedure on the same table. If not, an error will be thrown. The statement_id, schema_name, and table_name fields identify the results for a given table and statement_id.

Each row contains information on the eligibility of either the table or its dependent materialized view. The CAN_SYNCREF_TABLE function guarantees that each row has values for both mv_schema_name and mv_name that are either NULL or non-NULL. These rows have the following semantics:

• If the mv_schema_name value is NULL and mv_name is NULL, then the ELIGIBLE field describes whether the table is eligible for synchronous refresh; if the table is not eligible, the MSG_NUMBER and MESSAGE fields provide the reason for this.

• If the mv_schema_name value is NOT NULL and mv_name is NOT NULL, then the ELIGIBLE field describes whether the materialized view is eligible for synchronous refresh; if the materialized view is not eligible, the MSG_NUMBER and MESSAGE fields provide the reason for this.

You must provide a different statement_id parameter for each invocation of this procedure on the same table, or else an error will be thrown. The statement_id, schema_name, and table_name fields identify the results for a given table and statement_id.

Using a VARRAY

You can save the output of the CAN_SYNCREF_TABLE function in a PL/SQL VARRAY. The elements of this array are of type CanSyncRefMessage, which is predefined in the SYS schema as shown in the following example:

TYPE CanSyncRefMessage IS OBJECT (
          schema_name         VARCHAR2(30),
          table_name          VARCHAR2(30),
          mv_schema_name      VARCHAR2(30),
          mv_name             VARCHAR2(30),
          eligible            VARCHAR2(1),    -- 'Y' , 'N'
          seq_num             NUMBER,
          msg_number          NUMBER,
          message             VARCHAR2(4000)
);

The array type, CanSyncRefArrayType, which is a VARRAY of CanSyncRefMessage objects, is predefined in the SYS schema as follows:

TYPE  CanSyncRefArrayType AS VARRAY(256) OF CanSyncRefMessage;

Each CanSyncRefMessage record provides a message concerning the eligibility of the base table or a dependent materialized view for synchronous refresh. The semantics of the fields is the same as that of the corresponding fields in SYNCREF_TABLE. However, SYNCREF_TABLE has a statement_id field that is absent in CanSyncRefMessage because no statement_id is supplied (because it is not required) when the CAN_SYNCREF_TABLE procedure is called with a VARRAY parameter.

The default size limit for CanSyncRefArrayType is 256 elements. If you need more than 256 elements, then connect as SYS and redefine CanSyncRefArray. The following commands, when connected as the SYS user, redefine CanSyncRefArray and set the limit to 2048 elements:

CREATE OR REPLACE TYPE CanSyncRefArrayType AS VARRAY(2048) OF SYS.CanSyncRefMessage;
/
GRANT EXECUTE ON SYS.CanSyncRefMessage TO PUBLIC;

CREATE OR REPLACE PUBLIC SYNONYM CanSyncRefMessage FOR SYS.CanSyncRefMessage;
/
GRANT EXECUTE ON SYS.CanSyncRefArrayType TO PUBLIC;

CREATE OR REPLACE PUBLIC SYNONYM CanSyncRefArrayType FOR SYS.CanSyncRefArrayType;
/

Demo Scripts

The synchronous refresh demo scripts in the rdbms/demo directory contain examples of the most common scenarios of the various synchronous refresh operations, including CAN_SYNCREF_API. The main script is syncref_run.sql and its log is syncref_run.log. The file syncref_cst.sql defines two procedures DO_CST and DO_CST_ARR, which simplify the usage of the CAN_SYNCREF_TABLE function and display the information on the screen in a convenient format. This format is documented in the syncref_cst.sql file.