Data warehousing involves extraction of data from relational databases and loading the data into analytical databases for querying purposes. In data warehousing environment, it is very crucial to identify the changed data for processing since it deals with huge volume of data. Change data capture is a technique to quickly identify the changed data or the delta in data for further processing.
Traditionally, changed or modified data is captured using
Table Differencing:This technique involves transporting the required source tables to a staging layer and a minus operation with older version of the table identifies the modified data. Following are the disadvantages of this technique:
a. Transport costs are very as it involves transporting the entire data set to staging every time
b. “MINUS” is costly operation thus increasing the computational cost
c. This technique cannot identify all the changes that are part of a functional transaction as we identify changes at table level.
Change Value Selection: This technique captures the changed data by filtering on an database column, generally the LAST UPDATE TIME of the record. This was the widely used technique on traditional warehouses, however the following are its disadvantages
a. The overhead of capturing the changed data falls on source database
b. It cannot capture the intermediate changes. For example, if ETL is scheduled to run daily and sales figure on a table changes twice in a day, only the latest value is captured.
c. This technique as well cannot identify all the changes that are part of a Functional Transaction.
Oracle addressed the following drawbacks of traditional change data capture techniques with its Change data Capture Mechanism.
Oracle Change Data Capture (CDC) does not depend on expensive and cumbersome table differencing or change-value selection mechanisms. Instead, it captures the change data resulting from INSERT, UPDATE, and DELETE operations made to user tables. The change data is then stored in a relational table called a change table, and the change data is made available to applications or individuals in a controlled way.
Oracle CDC captures and publishes changed data in one of the following modes:
Synchronous:
Triggers on the source database allow change data to be captured immediately, as each SQL statement that performs a data manipulation language (DML) operation (INSERT, UPDATE, or DELETE) is made. In this mode, change data is captured as part of the transaction modifying the source table. Synchronous Change Data Capture is available with Oracle Standard Edition and Enterprise Edition. This technique is generally not used as it asserts performance overhead on source database. This technique can be used on not so update intensive source databases.
Asynchronous:
This technique captures the changed data from database redo log files. Changed data is not captured as part of transaction that is updating source table and therefore has no effect on that transaction. Asynchronous CDC is available only with Oracle Enterprise Database Edition.
The following lists the advantages of Oracle CDC:
1. Oracle CDC captures all effects of all DML operations including the values before update and after update.
2. Asynchronous CDC does not add any performance overload on source databases
3. Oracle database has out of box DBMS_CDC_PUBLISH and DBMS_CDC_SUBSCRIBE APIs which can be used to capture the changed data.
4. Data warehouse projects need not design an CDC architecture as it is handled by Oracle Database.
Oracle Data Integrator is integrated with Oracle CDC out of box which drastically reduces the development time and lessens maintenance costs to a great extent. Let us discuss about this integration in our next articles.
Apps2Fusion Articles 