Change Capture Stage vs Upsert
Posted: Fri May 15, 2009 6:58 am
Current water cooler debate that maybe someone outside can help with this discussion.
For daily loads to Oracle Fact tables, that can have millions of rows. Would an Upsert work faster than the Change Capture Stage that will compare each input record from the daily transaction against each record in the Fact table to identify the deltas and then do the Upsert.
The number of daily transactions can be from 1k to 20k records.
There are up to 8 Fact tables that are part of the daily load process.
Take into account that the production window is not very large for Total daily night time process, maybe 3-4 hrs.
I have my vote already just looking for additional input from the DsX community.
Thanks.
Charlie
PLATFORM AIX, ORACLE 10G, DS VERS 7.5.2
For daily loads to Oracle Fact tables, that can have millions of rows. Would an Upsert work faster than the Change Capture Stage that will compare each input record from the daily transaction against each record in the Fact table to identify the deltas and then do the Upsert.
The number of daily transactions can be from 1k to 20k records.
There are up to 8 Fact tables that are part of the daily load process.
Take into account that the production window is not very large for Total daily night time process, maybe 3-4 hrs.
I have my vote already just looking for additional input from the DsX community.
Thanks.
Charlie
PLATFORM AIX, ORACLE 10G, DS VERS 7.5.2