4.19 RANGE
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
] [, ...]))
-
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 bytotal_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, 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
- Example 1
-
In the following example, the workload is split into three ranges, between three Replicat processes, based on the
ID
column of theSRCTAB
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));
- Example 2
-
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));
- Example 3
-
In the following example, the
ORDMASTR
table has a key ofORDERID
and theORDDETL
table has a key ofITEMNUM
. Because the keyORDERID
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));