OK, so I've got CDC working (synch, i.e. the trigger based one, DB version 10.2.0.4) but what's the best way to process the CDC subscriber view data?
I’m just trying to reproduce copies of transaction tables on my staging area and I'm finding:
• Processing the subscriber views row by row is slow: much time is wasted doing updates that override previous updates, and then if the record is deleted it’s all been a waste of time. Is it better to process the rows smarter and look to see if the last change was a delete etc.?
• To try and fail safe I always delete a row if the OPERATION$ column is D, I or U, and insert the row if OPERATION$ is I or U; is it better to use TARGET_COLMAP$ to construct the statements that were committed on the original database? Doing it my way means I have to temporarily stop constraints being enforced.
• Processing only the changes leaves me uncertain that I have reliable copies of the tables to which I’m subscribing, so I write audit programs to reconcile the two.
• When the audit shows differences, usually due to DDL changes on the source tables, I run routines that make neutral updates to the source tables to trigger re-processing of the rows that are different.
• When there are too many rows to process in a reasonable time I have to run whole-copy routines to reset the tables in my staging area, with resulting timing issues and/or downtime.
So I’m thinking if CDC is so wonderful a way of replicating data, am I doing it right?
Are there any best practice/guidelines/recommendations etc for processing CDC subscriber view data?
All help much appreciated.