@RANGE

Use the @RANGE function to divide the rows of any table across two or more Oracle GoldenGate processes. It can be used to increase the throughput of large and heavily accessed tables and also can be used to divide data into sets for distribution to different destinations. Specify each range in a FILTER clause in a TABLE or MAP statement.

@RANGE is safe and scalable. It preserves data integrity by guaranteeing that the same row will always be processed by the same process group. To ensure that rows do not shift partitions to another process group and that the DML is performed in the correct order, the column on which you base the @RANGE partitioning must not ever change during a process run. Updates to the partition column may result in "row not found" errors or unique-constraint errors.

@RANGE computes a hash value of the columns specified in the input. If no columns are specified, the KEYCOLS clause of the TABLE or MAP statement is used to determine the columns to hash, if a KEYCOLS clause exists. Otherwise, the primary key columns are used.

Oracle GoldenGate adjusts the total number of ranges to optimize the even distribution across the number of ranges specified.

Because any columns can be specified for this function, rows in tables with relational constraints to one another must be grouped together into the same process or trail to preserve referential integrity.

Note:

Using Extract to calculate the ranges is more efficient than using Replicat. Calculating ranges on the target side requires Replicat to read through the entire trail to find the data that meets each range specification.

Syntax

@RANGE (range, total_ranges [, column] [, column] [, ...])

range

The range assigned to the specified process or trail. Valid values are 1, 2, 3, and so forth, with the maximum value being the value defined by total_ranges.

total_ranges

The total number of ranges allocated. For example, to divide data into three groups, use the value 3.

column

The name of a column on which to base the range allocation. This argument is optional. If not used, Oracle GoldenGate allocates ranges based on the table's primary key.

Your data cannot contain missing or NULL columns; this will cause the @RANGE function to abend.

The columns specified in a list of columns must exist in the trail file. You can control this using KEYCOLS in the Extract to include this column, or by using FETCHCOLS in the Extract for the column, or by ensuring that the column is part of the supplemental log group and then using LOGALLSUPCOLS.

Examples

Example 1

In the following example, the replication workload is split into three ranges (between three Replicat processes) based on the ID column of the source acct table.

(Replicat group 1 parameter file)

MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (1, 3, ID));

(Replicat group 2 parameter file)

MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (2, 3, ID));

(Replicat group 3 parameter file)

MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (3, 3, ID));

Example 2

In the following example, one Extract process splits the processing load into two trails. Since no columns were defined on which to base the range calculation, Oracle GoldenGate will use the primary key columns.

RMTTRAIL /ggs/dirdat/aa
TABLE fin.account, FILTER (@RANGE (1, 2));
RMTTRAIL /ggs/dirdat/bb
TABLE fin.account, FILTER (@RANGE (2, 2));

Example 3

In the following example, two tables have relative operations based on an order_ID column. The order_master table has a key of order_ID, and the order_detail table has a key of order_ID and item_number. Because the key order_ID establishes relativity, it is used in @RANGE filters for both tables to preserve referential integrity. The load is split into two ranges.

(Parameter file #1)

MAP sales.order_master, TARGET sales.order_master,
FILTER (@RANGE (1, 2, order_ID));
MAP sales.order_detail, TARGET sales.order_detail,
FILTER (@RANGE (1, 2, order_ID));

(Parameter file #2)

MAP sales.order_master, TARGET sales.order_master,
FILTER (@RANGE (2, 2, order_ID));
MAP sales.order_detail, TARGET sales.order_detail,
FILTER (@RANGE (2, 2, order_ID));