The @RANGE function, used within the FILTER option, helps divide workload into multiple, randomly distributed groups of data, while guaranteeing that the same row will always be processed by the same program. For example, @RANGE can be used to split the workload by different key ranges for a heavily accessed table into different Replicat processes.
The user specifies both a range that applies to the current process, and the total number of ranges (generally the number of processes), and optionally a list of column names to use to calculate the range against.
@RANGE computes a hash value of all the columns specified, or if no columns are specified, the primary key columns of the source table. A remainder of the hash and the total number of ranges is compared with the ownership range to determine whether or not @RANGE produces true or false results. Oracle GoldenGate adjusts the total number of ranges so that they are evenly distributed.
Note:
Calculating ranges in an Extract parameter file is more efficient that doing so in a Replicat parameter file. Calculating ranges on the target requires Replicat to read all of the Oracle GoldenGate trail data to find the data meeting each range specification.
Note:
Using the @RANGE function within a FILTER provides different capabilities, such as specifying columns, than using the RANGE option of FILE or MAP. And both of these are different than the RANGE option of ALTINPUT.
Syntax
@RANGE (range, total_ranges [, column] [, column] [, ...]))
rangeThe 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_rangesThe total number of ranges allocated. For example, to divide data into three groups, use the value 3.
columnThe name of a column, or columns, on which to base the range allocation. This argument is optional. If not used, Oracle GoldenGate calculates ranges based on the table's primary key.
Examples
In the following example, the workload is split into three ranges, between three Replicat processes, based on the ID column of the SRCTAB table.
Replicat parameter file #1 contains:
MAP $PRODSRC.PRODMSTR.SRCTAB, TARGET $PROD.MASTER.TARGTAB, FILTER (@RANGE(1,3, ID));
Replicat parameter file #2 contains:
MAP $PRODSRC.PRODMSTR.SRCTAB, TARGET $PROD.MASTER.TARGTAB, FILTER (@RANGE(2,3, ID);
Replicat parameter file #3 contains:
MAP $PRODSRC.PRODMSTR.SRCTAB, TARGET $PROD.MASTER.TARGTAB, FILTER (@RANGE(3,3, ID));
In the following example, the TABLE parameter in the Extract parameter file 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 $DATA.GGSDAT.AA TABLE ACCOUNT, FILTER (@RANGE (1, 2)); RMTTRAIL $DATA.GGSDAT.BB TABLE ACCOUNT, FILTER (@RANGE (2, 2));
In the following example, the ORDMASTR table has a key of ORDERID and the ORDDETL table has a key of ITEMNUM. Because the key ORDERID 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 $PRODSRC.PRODMSTR.ORDMASTR, TARGET $PROD.MASTER.ORDMASTR, FILTER (@RANGE (1, 2, ORDERID)); MAP $PRODSRC.PRODMSTR.ORDDETL, TARGET $PROD.MASTER.ORDDETL, FILTER (@RANGE (1, 2, ORDERID));
(Parameter file #2)
MAP $PRODSRC.PRODMSTR.ORDMASTR, TARGET $PROD.MASTER.ORDMASTR, FILTER (@RANGE (2, 2, ORDERID)); MAP $PRODSRC.PRODMSTR.ORDDETL, TARGET $PROD.MASTER.ORDDETL, FILTER (@RANGE (2, 2, ORDERID));