GoldenGate中filter参数限制行

GoldenGate中filter的过滤功能很强大，这里说一下行过滤(Selecting rows with a FILTER clause)
官方文档有如下描述：
Use a FILTER clause to select rows based on a numeric value by using basic operators or one or more Oracle GoldenGate column-conversion functions.
NOTE To filter a column based on a string, use one of the Oracle GoldenGate string
functions or use a WHERE clause.
Syntax TABLE <table spec> ,

, FILTER (
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
, <filter clause>);

Or...
Syntax MAP <table spec>, TARGET <table spec>,

, FILTER (
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
[, RAISEERROR ]
, <filter clause>);

Valid FILTER clause elements are the following:

● An Oracle GoldenGate column-conversion function. These functions are built into
  Oracle GoldenGate so that you can perform tests, manipulate data, retrieve values,
  and so forth. For more information about Oracle GoldenGate conversion functions, see
  “Testing and transforming data” on page 158.
● Numbers
● Columns that contain numbers
● Functions that return numbers
● Arithmetic operators:
  + (plus)
  - (minus)
  * (multiply)
  / (divide)
  \ (remainder)
● Comparison operators:
  > (greater than)
  >= (greater than or equal)
  < (less than)
  <= (less than or equal)
  = (equal)
  <> (not equal)
  Results derived from comparisons can be zero (indicating FALSE) or non-zero (indicating TRUE).
● Parentheses (for grouping results in the expression)
● Conjunction operators: AND, OR

下面是官方给出的几个例子：
Example 1 The following calls the @COMPUTE function to extract records in which the price multiplied  by the amount exceeds 10,000.
             以下使用@COMPUTE函数提取的记录，其中的价格乘以金额超过10000

MAP SALES.TCUSTORD, TARGET SALES.TORD,
FILTER (@COMPUTE (PRODUCT_PRICE*PRODUCT_AMOUNT) > 10000);

Example 2 The following uses the @STREQ function to extract records where a string is equal to ’JOE’. This example assumes that the USEANSISQLQUOTES parameter is  used in the GLOBALS  parameter file to apply SQL-92 rules for single and double quote marks.

TABLE ACCT.TCUSTORD, FILTER (@STREQ ("Name", ’joe’) > 0);

Example 3 The following selects records in which the amount column is greater than 50 and executes the filter on updates and deletes.

TABLE ACT.TCUSTORD, FILTER (ON UPDATE, ON DELETE, AMOUNT > 50);

Example 4 You can use the @RANGE function to divide the processing workload among multiple FILTER clauses, using separate TABLE or MAP statements. For example,    the following splits the replication workload into two ranges (between two Replicat processes) based on the ID column of the source acct table.
                Note that object names are case-sensitive in this case. (Replicat group 1 parameter file)

MAP "sales"."acct", TARGET "sales"."acct", FILTER (@RANGE (1, 2, ID));
(Replicat group 2 parameter file)
MAP "sales"."acct", TARGET "sales"."acct", FILTER (@RANGE (2, 2, ID));

下面是一个现实中客户的一个需求：
比如我表中有如下的记录：
ID          COL2        COL3       COL1
---------- ------------ ---------- ----
16 08-APR-16 sdfg 0
21 08-APR-16 xxxx 5
22 08-APR-16 zzzz 1
23 08-APR-16 xxxx 1
我在源端做delete from tablename where col1=1的时候，目标端不做同步（即不删除），但是做col1<>1的时候正常同步（即正常删除）。 其他的insert，update等DML语句照常同步。 
可以使用下面方法实现：
MAP source.test1, TARGET target.test1, FILTER (ON DELETE, flag <> 1);

以上官方内容来自：
Oracle? GoldenGate Windows and UNIX Administrator’s Guide 11g Release 2 Patch Set 1 (11.2.1.0.1) E29397-01 这个文档第11章。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/205377/viewspace-2081561/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/205377/viewspace-2081561/