How To Load Data Directly From The Source To The Target Without Creating Any Temporary Table in an ODI Integration Interface (Doc ID 762723.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Data Integrator - Version 4.1.01.01 to 11.1.1.9.99 [Release 4.1.01 to 11gR1]
Information in this document applies to any platform.

Goal

It is useful to understand why and when ODI 10g and ODI 11g are creating temporary tables. The descriptions below apply to standard cases.

C$ Collect tables

A C$_ table is created by an LKM in order to transfer data from a source server to the staging area - which, by default, is on the target server.

This will allow ODI to join data sets coming from heterogeneous source data servers. For example, if as sources you are using an Excel spreadsheet and an Oracle table to load a Teradata database, ODI will retrieve the content of the Oracle table and the Excel file into 2 collect tables, both hosted on the staging area. This will allow ODI to join the data coming from Excel and Oracle.

If the Integration Interface contains several source Datastores which are joined on the Source, the C$_ table will contain the result of the join operation. If the join is set to be executed on the staging area, ODI will create 2 distinct C$_ tables.

Notice that when the source Datastores are on the same server as the target Datastore or same server as the staging area, there is no LKM to set at the Integration Interface level.

I$ Integration tables

Most of the time, the I$_ tables are created by the IKMs whenever there is a need for updating a target table or running a flow control.

If this is not required, the creation of the I$_ table is of no use since ODI can retrieve the data directly from the collect tables. The structure of the I$_ table is to have an almost identical structure as the target table in order to contain the data that ODI is about to insert or to use to update the target table.

E$ Error tables

From an Integration Interface, E$_ tables are created and populated by the CKM if the IKM option called Flow Control is set to yes.

So, only under the following conditions:

    1. Only one source Datastore, not journalized
    2. Loading strategy = Truncate/Insert or basic Insert (no updates or deletes)
    3. No flow nor static control is required

... are the C$, I$, E$ tables of no use, and in such a case,  it would be more efficient to directly load data into the target table.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms