Oracle Golden Gate provides a variety of levels and conditions for selection and filtering data.
Let’s start with the selection of table :
We can map table1 with table2 so operation is done on table1 will be replicated on table2.
map hr.table1 , target hr.table2;
Data Selection with WHERE clause :
Where () clause is the easiest and simplest form of data selection.
The Where() clause appears on either the Map or the Table parameter and must enclose in parentheses.
Let’s take an example :
Map hr.emp1 , Target hr.emp2, where (emp_name='JAMES');
According to the above example, all the operations done on the emp1 table of source table will be replicated to the emp2 table of target database where emp_name will be “JAMES”.
Data Selection with Where clause :
- We can perform column selection with where clause.
- We can use Comparision operators like = , <> , > , < etc.
- We can compare rows to numeric values like 67,-98 etc
- We can compare rows to a literal string like “JAMES”, “AHMEDABAD” etc.
Note: Arithmetic operators are not supported by the golden gate.
Data Selection with FILTER :
Filter clause gives a complex evaluation to include data selection or to exclude data selection. Must be enclosed in parentheses and appears on either the Map or the Table parameter.
With FILTER clause we can use Oracle Golden Gate built-in functions.
We can even raise a user-defined error for exception processing with filter clause. The Filter RAISEERROR option creates a user-defined error number if the Filter clause is true.
Filter ( ON UPDATE , BEFORE.TIMESTAMP < CHECK.TIMESTAMP , RAISEERROR 1111);
We can use multiple filters on one statement:- Failure of any filter results in failure for all filters.
Data Selection with RANGE [@] :
@Range helps divide the rows of any table across two or more Oracle GoldenGate processes.
As an example, the @Range function can be used to split workload based on different key ranges. We can take benefit of this for a table accessed in heavy manner and multiple replicat processes.
The user can specify both a range that applies to the current process and the total number of ranges. @Range calculates a hash value of all the column specified, and if no specific column specified, it takes primary key columns of the source table.
A reminder of the hash and a total number of ranges is compared with the ownership range to identify where @Range returns true or false.
Note: If primary key updates are performed on database @Range cannot be used.
Table hr.emp1 , filter (@Range (1,2,emp_id));
Table hr.emp1 , filter (@Range (2,2,emp_id));
emp1 table of hr schema requires 2 replicats to handle the transaction volumes.
By using the hashing mechanism of the emp_id column, related rows are always processed to the same replicat.
If you want to be updated with all our articles send us an Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp