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.

Synchronous refresh offers the following advantages over traditional types of methods used to refresh materialized views in a data warehouse:

• 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.

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

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"

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;
     /
   /
   

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

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

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;
/

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 "About the 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:

• About the Staging Log Key

• About Staging Log Rules

• About Columns Being Updated to NULL

• Examples of Working with Staging Logs

• Error Handling in Preparing Staging Logs

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.

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.

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.

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.