5 How Rules Are Used in Oracle Streams

Apply All Row LCRs Related to the hr.locations Table

The changes in these row LCRs originated at the dbs1.example.com source database.

Run the ADD_TABLE_RULES procedure to create this rule:

BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name          =>  'hr.locations',
    streams_type        =>  'apply',
    streams_name        =>  'apply',
    queue_name          =>  'streams_queue',
    include_dml         =>  TRUE,
    include_ddl         =>  FALSE,
    include_tagged_lcr  =>  FALSE,
    source_database     =>  'dbs1.example.com',
    inclusion_rule      =>  TRUE);
END;
/

Notice that the inclusion_rule parameter is set to TRUE. This setting means that the system-created rule is added to the positive rule set for the apply process.

The ADD_TABLE_RULES procedure creates a rule with a rule condition similar to the following:

(((:dml.get_object_owner() = 'HR' and :dml.get_object_name() = 'LOCATIONS')) 
and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 
'DBS1.EXAMPLE.COM' )

Apply All DDL LCRs Related to the hr.countries Table

The changes in these DDL LCRs originated at the dbs1.example.com source database.

Run the ADD_TABLE_RULES procedure to create this rule:

BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name          =>  'hr.countries',
    streams_type        =>  'apply',
    streams_name        =>  'apply',
    queue_name          =>  'streams_queue',
    include_dml         =>  FALSE,
    include_ddl         =>  TRUE,
    include_tagged_lcr  =>  FALSE,
    source_database     =>  'dbs1.example.com',
    inclusion_rule      =>  TRUE);
END;
/

Notice that the inclusion_rule parameter is set to TRUE. This setting means that the system-created rule is added to the positive rule set for the apply process.

The ADD_TABLE_RULES procedure creates a rule with a rule condition similar to the following:

(((:ddl.get_object_owner() = 'HR' and :ddl.get_object_name() = 'COUNTRIES')
or (:ddl.get_base_table_owner() = 'HR' 
and :ddl.get_base_table_name() = 'COUNTRIES')) and :ddl.is_null_tag() = 'Y' 
and :ddl.get_source_database_name() = 'DBS1.EXAMPLE.COM' )

The GET_BASE_TABLE_OWNER and GET_BASE_TABLE_NAME member functions are used in the DDL LCR rule because the GET_OBJECT_OWNER and GET_OBJECT_NAME functions can return NULL if a user who does not own an object performs a DDL change on the object.

The generated DDL table rule evaluates to TRUE for any DDL change that operates on the table or on an object that is part of the table, such as an index or trigger on the table. The rule evaluates to FALSE for any DDL change that either does not refer to the table or refers to the table in a subordinate way. For example, the rule evaluates to FALSE for changes that create synonyms or views based on the table. The rule also evaluates to FALSE for a change to a PL/SQL subprogram that refers to the table.

Summary of Rules

In this example, the following table rules were defined:

• A table rule that evaluates to TRUE if a row LCR contains a row change that results from a DML operation on the hr.locations table.

• A table rule that evaluates to TRUE if a DDL LCR contains a DDL change performed on the hr.countries table.

Given these rules, the following list provides examples of changes applied by an apply process:

• A row is inserted into the hr.locations table.

• Five rows are deleted from the hr.locations table.

• A column is added to the hr.countries table.

The apply process dequeues the LCRs containing these changes from its associated queue and applies them to the database objects at the destination database.

Given these rules, the following list provides examples of changes that are ignored by the apply process:

• A row is inserted into the hr.employees table. This change is not applied because a change to the hr.employees table does not satisfy any of the rules.

• A row is updated in the hr.countries table. This change is a DML change, not a DDL change. This change is not applied because the rule on the hr.countries table is for DDL changes only.

• A column is added to the hr.locations table. This change is a DDL change, not a DML change. This change is not applied because the rule on the hr.locations table is for DML changes only.

Row Migration During Capture

When a subset rule is in the rule set for a capture process or synchronous capture, an update that satisfies the subset rule can be converted into an insert or delete when it is captured.

For example, suppose you use a subset rule to specify that a capture process or a synchronous capture captures changes to the hr.employees table where the employee's department_id is 50 using the following subset condition: department_id = 50. Assume that the table at the source database contains records for employees from all departments. If a DML operation changes an employee's department_id from 80 to 50, then the subset rule converts the update operation into an insert operation and captures the change. Therefore, a row LCR that contains an INSERT is enqueued into the queue. Figure 5-2 illustrates this example with a subset rule for a capture process.

Figure 5-2 Row Migration During Capture by a Capture Process

Description of "Figure 5-2 Row Migration During Capture by a Capture Process"

Similarly, if a captured update changes an employee's department_id from 50 to 20, then a capture process or synchronous capture with this subset rule converts the update operation into a DELETE operation.

Row Migration During Propagation

When a subset rule is in the rule set for a propagation, an update operation can be converted into an insert or delete operation when a row LCR is propagated.

For example, suppose you use a subset rule to specify that a propagation propagates changes to the hr.employees table where the employee's department_id is 50 using the following subset condition: department_id = 50. If the source queue for the propagation contains a row LCR with an update operation on the hr.employees table that changes an employee's department_id from 50 to 80, then the propagation with the subset rule converts the update operation into a delete operation and propagates the row LCR to the destination queue. Therefore, a row LCR that contains a DELETE is enqueued into the destination queue. Figure 5-3 illustrates this example.

Figure 5-3 Row Migration During Propagation

Description of "Figure 5-3 Row Migration During Propagation"

Similarly, if a captured update changes an employee's department_id from 80 to 50, then a propagation with this subset rule converts the update operation into an INSERT operation.

Row Migration During Apply

When a subset rule is in the rule set for an apply process, an update operation can be converted into an insert or delete operation when a row LCR is applied.

For example, suppose you use a subset rule to specify that an apply process applies changes to the hr.employees table where the employee's department_id is 50 using the following subset condition: department_id = 50. Assume that the table at the destination database is a subset table that only contains records for employees whose department_id is 50. If a source database captures a change to an employee that changes the employee's department_id from 80 to 50, then the apply process with the subset rule at a destination database applies this change by converting the update operation into an insert operation. This conversion is needed because the employee's row does not exist in the destination table. Figure 5-4 illustrates this example.

Figure 5-4 Row Migration During Apply

Description of "Figure 5-4 Row Migration During Apply"

Similarly, if a captured update changes an employee's department_id from 50 to 20, then an apply process with this subset rule converts the update operation into a DELETE operation.

Row Migration During Dequeue by a Messaging Client

When a subset rule is in the rule set for a messaging client, an update operation can be converted into an insert or delete operation when a row LCR is dequeued.

For example, suppose you use a subset rule to specify that a messaging client dequeues changes to the hr.employees table when the employee's department_id is 50 using the following subset condition: department_id = 50. If the queue for a messaging client contains a persistent row LCR with an update operation on the hr.employees table that changes an employee's department_id from 50 to 90, then when a user or application invokes a messaging client with this subset rule, the messaging client converts the update operation into a delete operation and dequeues the row LCR. Therefore, a row LCR that contains a DELETE is dequeued. The messaging client can process this row LCR in any customized way. For example, it can send the row LCR to a custom application. Figure 5-5 illustrates this example.

Figure 5-5 Row Migration During Dequeue by a Messaging Client

Description of "Figure 5-5 Row Migration During Dequeue by a Messaging Client"

Similarly, if a persistent row LCR contains an update that changes an employee's department_id from 90 to 50, then a messaging client with this subset rule converts the UPDATE operation into an INSERT operation during dequeue.

Use Capture Subset Rules When All Destinations Need Only a Subset of Changes

Use subset rules with a capture process or a synchronous capture when all destination databases of the captured changes need only row changes that satisfy the subset condition for the table. In this case, a capture process or a synchronous capture captures a subset of the DML changes to the table, and one or more propagations propagate these changes in the form of row LCRs to one or more destination databases. At each destination database, an apply process applies these row LCRs to a subset table in which all of the rows satisfy the subset condition in the subset rules for the capture process. None of the destination databases need all of the DML changes made to the table. When you use subset rules for a local capture process or a synchronous capture, some additional overhead is incurred to perform row migrations at the site running the source database.

Use Propagation or Apply Subset Rules When Some Destinations Need Subsets

Use subset rules with a propagation or an apply process when some destinations in an environment need only a subset of captured DML changes. The following are examples of such an environment:

• Most of the destination databases for captured DML changes to a table need a different subset of these changes.

• Most of the destination databases need all of the captured DML changes to a table, but some destination databases need only a subset of these changes.

In these types of environments, the capture process or synchronous capture must capture all of the changes to the table, but you can use subset rules with propagations and apply processes to ensure that subset tables at destination databases only apply the correct subset of captured DML changes.

Consider these factors when you decide to use subset rules with a propagation in this type of environment:

• You can reduce network traffic because fewer row LCRs are propagated over the network.

• The site that contains the source queue for the propagation incurs some additional overhead to perform row migrations.

Consider these factors when you decide to use subset rules with an apply process in this type of environment:

• The queue used by the apply process can contain all row LCRs for the subset table. In a directed networks environment, propagations can propagate any of the row LCRs for the table to destination queues as appropriate, whether the apply process applies these row LCRs.

• The site that is running the apply process incurs some additional overhead to perform row migrations.

Ensure That the Table Where Subset Row LCRs Are Applied Is a Subset Table

If an apply process might apply row LCRs that have been transformed by a row migration, then Oracle recommends that the table at the destination database be a subset table where each row matches the condition in the subset rule. If the table is not such a subset table, then apply errors might result.

For example, consider a scenario in which a subset rule for a capture process has the condition department_id = 50 for DML changes to the hr.employees table. If the hr.employees table at a destination database of this capture process contains rows for employees in all departments, not just in department 50, then a constraint violation might result during apply:

1. At the source database, a DML change updates the hr.employees table and changes the department_id for the employee with an employee_id of 100 from 90 to 50.

2. A capture process using the subset rule captures the change and converts the update into an insert and enqueues the change into the capture process's queue as a row LCR.

3. A propagation propagates the row LCR to the destination database without modifying it.

4. An apply process attempts to apply the row LCR as an insert at the destination database, but an employee with an employee_id of 100 already exists in the hr.employees table, and an apply error results.

In this case, if the table at the destination database were a subset of the hr.employees table and only contained rows of employees whose department_id was 50, then the insert would have been applied successfully.

Similarly, if an apply process might apply row LCRs that have been transformed by a row migration to a table, and you allow users or applications to perform DML operations on the table, then Oracle recommends that all DML changes satisfy the subset condition. If you allow local changes to the table, then the apply process cannot ensure that all rows in the table meet the subset condition. For example, suppose the condition is department_id = 50 for the hr.employees table. If a user or an application inserts a row for an employee whose department_id is 30, then this row remains in the table and is not removed by the apply process. Similarly, if a user or an application updates a row locally and changes the department_id to 30, then this row also remains in the table.

Dequeue User Messages If region Is EUROPE and priority Is 1

Run the ADD_MESSAGE_RULE procedure to create a rule for messages of region_pri_msg type:

BEGIN
  DBMS_STREAMS_ADM.ADD_MESSAGE_RULE (
    message_type    =>  'strmadmin.region_pri_msg',
    rule_condition  =>  ':msg.region = ''EUROPE'' AND  ' ||
                        ':msg.priority = ''1'' ',
    streams_type    =>  'dequeue',
    streams_name    =>  'msg_client',
    queue_name      =>  'streams_queue',
    inclusion_rule  =>  TRUE);
END;
/

Notice that dequeue is specified for the streams_type parameter. Therefore, this procedure creates a messaging client named msg_client if it does not already exist. If this messaging client already exists, then this procedure adds the message rule to its rule set. Also, notice that the inclusion_rule parameter is set to TRUE. This setting means that the system-created rule is added to the positive rule set for the messaging client. The user who runs this procedure is granted the privileges to dequeue from the queue using the messaging client.

The ADD_MESSAGE_RULE procedure creates a rule with a rule condition similar to the following:

:"VAR$_52".region = 'EUROPE' AND  :"VAR$_52".priority = '1'

The variables in the rule condition that begin with VAR$ are variables that are specified in the system-generated evaluation context for the rule.

Send User Messages to a Message Handler If region Is AMERICAS and priority Is 2

Run the ADD_MESSAGE_RULE procedure to create a rule for messages of region_pri_msg type:

BEGIN
  DBMS_STREAMS_ADM.ADD_MESSAGE_RULE (
    message_type    =>  'strmadmin.region_pri_msg',
    rule_condition  =>  ':msg.region = ''AMERICAS'' AND  ' ||
                        ':msg.priority = ''2'' ',
    streams_type    =>  'apply',
    streams_name    =>  'apply_msg',
    queue_name      =>  'streams_queue',
    inclusion_rule  =>  TRUE);
END;
/

Notice that apply is specified for the streams_type parameter. Therefore, this procedure creates an apply process named apply_msg if it does not already exist. If this apply process already exists, then this procedure adds the message rule to its rule set. Also, notice that the inclusion_rule parameter is set to TRUE. This setting means that the system-created rule is added to the positive rule set for the messaging client.

The ADD_MESSAGE_RULE procedure creates a rule with a rule condition similar to the following:

:"VAR$_56".region = 'AMERICAS' AND  :"VAR$_56".priority = '2'

The variables in the rule condition that begin with VAR$ are variables that are specified in the system-generated evaluation context for the rule.

Summary of Rules

In this example, the following message rules were defined:

• A message rule for a messaging client named msg_client that evaluates to TRUE if a message has EUROPE for its region and 1 for its priority. Given this rule, a user or application can use the messaging client to dequeue messages of region_pri_msg type that satisfy the rule condition.

• A message rule for an apply process named apply_msg that evaluates to TRUE if a message has AMERICAS for its region and 2 for its priority. Given this rule, the apply process dequeues messages of region_pri_msg type that satisfy the rule condition and sends these messages to its message handler or reenqueues the messages into a specified queue.

Apply All DML Changes to the Tables in the hr Schema

These changes originated at the dbs1.example.com source database.

Run the ADD_SCHEMA_RULES procedure to create this rule:

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name         =>  'hr',   
    streams_type        =>  'apply',
    streams_name        =>  'apply',
    queue_name          =>  'streams_queue',
    include_dml         =>  TRUE,
    include_ddl         =>  FALSE,
    include_tagged_lcr  =>  FALSE,
    source_database     =>  'dbs1.example.com',
    inclusion_rule      =>  TRUE);
END;
/

Notice that the inclusion_rule parameter is set to TRUE. This setting means that the system-created rule is added to the positive rule set for the apply process.

The ADD_SCHEMA_RULES procedure creates a rule with a rule condition similar to the following:

((:dml.get_object_owner() = 'HR') and :dml.is_null_tag() = 'Y' 
and :dml.get_source_database_name() = 'DBS1.EXAMPLE.COM' )

Discard Row LCRs Containing DML Changes to the hr.job_history Table

These changes originated at the dbs1.example.com source database.

Run the ADD_TABLE_RULES procedure to create this rule:

BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name          =>  'hr.job_history',
    streams_type        =>  'apply',
    streams_name        =>  'apply',
    queue_name          =>  'streams_queue',
    include_dml         =>  TRUE,
    include_ddl         =>  FALSE,
    include_tagged_lcr  =>  TRUE,
    source_database     =>  'dbs1.example.com',
    inclusion_rule      =>  FALSE);
END;
/

Notice that the inclusion_rule parameter is set to FALSE. This setting means that the system-created rule is added to the negative rule set for the apply process.

Also notice that the include_tagged_lcr parameter is set to TRUE. This setting means that all changes for the table, including tagged LCRs that satisfy all of the other rule conditions, will be discarded. In most cases, specify TRUE for the include_tagged_lcr parameter if the inclusion_rule parameter is set to FALSE.

The ADD_TABLE_RULES procedure creates a rule with a rule condition similar to the following:

(((:dml.get_object_owner() = 'HR' and :dml.get_object_name() = 'JOB_HISTORY')) 
and :dml.get_source_database_name() = 'DBS1.EXAMPLE.COM' )

Summary of Rules

In this example, the following rules were defined:

• A schema rule that evaluates to TRUE if a DML operation is performed on the tables in the hr schema. This rule is in the positive rule set for the apply process.

• A table rule that evaluates to TRUE if a DML operation is performed on the hr.job_history table. This rule is in the negative rule set for the apply process.

Given these rules, the following list provides examples of changes applied by the apply process:

• A row is inserted into the hr.departments table.

• Five rows are updated in the hr.employees table.

• A row is deleted from the hr.countries table.

The apply process dequeues these changes from its associated queue and applies them to the database objects at the destination database.

Given these rules, the following list provides examples of changes that are ignored by the apply process:

• A row is inserted into the hr.job_history table.

• A row is updated in the hr.job_history table.

• A row is deleted from the hr.job_history table.

These changes are not applied because they satisfy a rule in the negative rule set for the apply process.