Using Virtual Dependency Definitions
A virtual dependency definition is a description of a dependency that is used by an apply process to detect dependencies between transactions being applied at a destination database. Virtual dependency definitions are useful when apply process parallelism is greater than 1 and dependencies are not described by constraints in the data dictionary at the destination database. There are two types of virtual dependency definitions: value dependencies and object dependencies.
A value dependency defines a table constraint, such as a unique key, or a relationship between the columns of two or more tables. An object dependency defines a
parent-child relationship between two objects at a destination database.
The following sections describe using virtual dependency definitions:
■ Setting and Unsetting Value Dependencies
■ Creating and Dropping Object Dependencies
Setting and Unsetting Value Dependencies
Use the SET_VALUE_DEPENDENCY procedure in the DBMS_APPLY_ADM package to set or unset a value dependency. The following sections describe scenarios for using value dependencies:
■ Schema Differences and Value Dependencies
■ Undefined Constraints at the Destination Database and Value Dependencies
Schema Differences and Value Dependencies This scenario involves an environment that shares many tables between a source database and destination database, but the schema that owns the tables is different at these two databases. Also, in this replication environment, the source database is in the United States and the destination database is in England. A design firm uses dozens of tables to describe product designs, but the tables use United States measurements (inches, feet, and so on) in the source database and metric measurements in the destination database. The name of the schema that owns the database objects at the source database is us_designs, while the name of the schema at the destination database is uk_designs. Therefore, the schema name of the shared database objects must be changed before apply, and all of the
measurements must be converted from United States measurements to metric measurements. Both databases use the same constraints to enforce dependencies between database objects.
See Also: "Apply Processes and Dependencies" on page 1-15 for more information about virtual dependency definitions
Managing Apply for Streams Replication
Rule-based transformations could make the required changes, but the goal is to apply multiple LCRs in parallel. Rule-based transformations must apply LCRs serially. So, a DML handler is configured at the destination database to make the required changes to the LCRs, and apply process parallelism is set to 5. In this environment, the destination database has no information about the schema us_designs in the LCRs being sent from the source database. Because an apply process calculates
dependencies before passing LCRs to apply handlers, the apply process must be informed about the dependencies between LCRs. Value dependencies can be used to describe these dependencies.
In this scenario, suppose a number of tables describe different designs, and each of these tables has a primary key. One of these tables is design_53, and the primary key column is key_53. Also, a table named all_designs_summary includes a summary of all of the individual designs, and this table has a foreign key column for each design table. The all_designs_summary includes a key_53 column, which is a foreign key of the primary key in the design_53 table. To inform an apply process about the relationship between these tables, run the following procedures to create a value dependency at the destination database:
BEGIN
DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
dependency_name => 'key_53_foreign_key', object_name => 'us_designs.design_53', attribute_list => 'key_53');
END;
/ BEGIN
DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
dependency_name => 'key_53_foreign_key',
object_name => 'us_designs.all_designs_summary', attribute_list => 'key_53');
END;
/
Notice that the value dependencies use the schema at the source database (us_
designs) because LCRs contain the source database schema. The schema will be changed to uk_designs by the DML handler after the apply process passes the row LCRs to the handler.
To unset a value dependency, run the SET_VALUE_DEPENDENCY procedure, and specify the name of the value dependency in the dependency_name parameter and NULL in the object_name parameter. For example, to unset the key_53_foreign_
key value dependency that was set previously, run the following procedure:
BEGIN
DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
dependency_name => 'key_53_foreign_key', object_name => NULL,
attribute_list => NULL);
END;
/
Note: "Managing a DML Handler" on page 9-12
Managing Apply for Streams Replication
Undefined Constraints at the Destination Database and Value Dependencies This scenarios involves an environment in which foreign key constraints are used for shared tables at the source database, but no constraints are used for these tables at the destination database. In the replication environment, the destination database is used as a data warehouse where data is written to the database far more often than it is queried. To optimize write operations, no constraints are defined at the destination database.
In such an environment, an apply processes running on the destination database must be informed about the constraints to apply transactions consistently. Value
dependencies can be used to inform the apply process about these constraints.
For example, assume that the orders and order_items tables in the oe schema are shared between the source database and the destination database in this environment.
On the source database, the order_id column is a primary key in the orders table, and the order_id column in the order_items table is a foreign key that matches the primary key column in the orders table. At the destination database, these constraints have been removed. Run the following procedures to create a value dependency at the destination database that informs apply processes about the relationship between the columns in these tables:
BEGIN
DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
dependency_name => 'order_id_foreign_key', object_name => 'oe.orders',
attribute_list => 'order_id');
END;
/ BEGIN
DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
dependency_name => 'order_id_foreign_key', object_name => 'oe.order_items', attribute_list => 'order_id');
END;
/
Also, in this environment, the following actions should be performed so that apply processes can apply transactions consistently:
■ Value dependencies should be set for each column that has a unique key or bitmap index at the source database.
■ The DBMS_APPLY_ADM.SET_KEY_COLUMNS procedure should set substitute key columns for the columns that are primary key columns at the source database.
To unset the value dependency that was set previously, run the following procedure:
BEGIN
DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
dependency_name => 'order_id_foreign_key', object_name => NULL,
attribute_list => NULL);
END;
/
Note: "Managing the Substitute Key Columns for a Table" on page 9-11
Managing Apply for Streams Replication