@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 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 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 usingFETCHCOLS
in the Extract for the column, or by ensuring that the column is part of the supplemental log group and then usingLOGALLSUPCOLS
.
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 sourceacct
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. Theorder_master
table has a key oforder_ID
, and theorder_detail
table has a key oforder_ID
anditem_number
. Because the keyorder_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));
Parent topic: Column Conversion Functions