Can I Use the @RANGE Function to Split Data Exactly by a Column Value? (Doc ID 1083886.1)

Last updated on JANUARY 12, 2017

Applies to:

Oracle GoldenGate - Version: 4.0.0 and later   [Release: 4.0.0 and later ]
Information in this document applies to any platform.

Goal

If a column is used to calculate the range, why won't the downstream trails be divided exactly by the column value?

Here is a customer case that demonstrates this configuration. A customer wanted to split the load on a heavily used table across more downstream Replicats. The table had 8 partitions which were exactly equal in size and contained the same amount of rows. There was a Column PART_ID in the primary key which identified the partition. The application filled the partition round robin, so the data was evenly distributed across the 8 partitions. The FILE statement was specified as:

Exttrail \SYS.$GGATE.GGSDAT1.ET
file $DATA.APPDATA.TABLE,
filter (@range (1,8 , PART_ID));

Corresponding FILE statements were specified for the remaining 7 target trails.

The customer expected that each downstream trail file would be filled with the data of a single partition, so that he then could have 8 Replicats reading the trail files and processing the same number of records. But the data was distributed as follows:

  • Trail 1 contained the data from partition 1
  • Trail 2 contained no data
  • Trail 3 contained the data from partition 2
  • Trail 4 contained the data from partitions 3, 4 and 5
  • Trail 5 contained no data
  • Trail 6 contained the data from partition 6
  • Trail 7 contained the data from partition 7
  • Trail 8 contained the data from partition 8

Is there a way to direct the @RANGE function to split the data evenly across the target trail files?

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms