X

Welcome to All Things Warehouse Builder

  • ETL |
    March 27, 2012

ODI 11g – Oracle Multi Table Insert

David Allan
Architect

With the IKM Oracle Multi Table Insert you can generate Oracle specific DML for inserting into multiple target tables from a single query result – without reprocessing the query or staging its result.

When designing this to exploit the IKM you must split the problem into the reusable parts – the select part goes in one interface (I named SELECT_PART), then each target goes in a separate interface (INSERT_SPECIAL and INSERT_REGULAR).

So for my statement below…

/*INSERT_SPECIAL interface */ insert  all
when 1=1 And (INCOME_LEVEL > 250000) then
into SCOTT.CUSTOMERS_NEW
(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)
values
(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)

/* INSERT_REGULAR interface */ when 1=1  then
into SCOTT.CUSTOMERS_SPECIAL
(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)
values
(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)
/*SELECT*PART interface */ select   
    CUSTOMERS.EMAIL EMAIL,
    CUSTOMERS.CREDIT_LIMIT CREDIT_LIMIT,
    UPPER(CUSTOMERS.NAME) NAME,
    CUSTOMERS.USER_MODIFIED USER_MODIFIED,
    CUSTOMERS.DATE_MODIFIED DATE_MODIFIED,
    CUSTOMERS.BIRTH_DATE BIRTH_DATE,
    CUSTOMERS.MARITAL_STATUS MARITAL_STATUS,
    CUSTOMERS.ID ID,
    CUSTOMERS.USER_CREATED USER_CREATED,
    CUSTOMERS.GENDER GENDER,
    CUSTOMERS.DATE_CREATED DATE_CREATED,
    CUSTOMERS.INCOME_LEVEL INCOME_LEVEL
from    SCOTT.CUSTOMERS   CUSTOMERS
where    (1=1)

Firstly I create a SELECT_PART temporary interface for the query to be reused and in the IKM assignment I state that it is defining the query, it is not a target and it should not be executed.

Then in my INSERT_SPECIAL interface loading a target with a filter, I set define query to false, then set true for the target table and execute to false. This interface uses the SELECT_PART query definition interface as a source.

Finally in my final interface loading another target I set define query to false again, set target table to true and execute to true – this is the go run it indicator!

To coordinate the statement construction you will need to create a package with the select and insert statements. With 11g you can now execute the package in simulation mode and preview the generated code including the SQL statements.

Hopefully this helps shed some light on how you can leverage the Oracle MTI statement. A similar IKM exists for Teradata. The ODI IKM Teradata Multi Statement supports this multi statement request in 11g, here is an extract from the paper at www.teradata.com/white-papers/born-to-be-parallel-eb3053/

Teradata Database offers an SQL extension called a Multi-Statement Request that allows several distinct SQL statements to be bundled together and sent to the optimizer as if they were one. Teradata Database will attempt to execute these SQL statements in parallel. When this feature is used, any sub-expressions that the different SQL statements have in common will be executed once, and the results shared among them.

It works in the same way as the ODI MTI IKM, multiple interfaces orchestrated in a package, each interface contributes some SQL, the last interface in the chain executes the multi statement.

Join the discussion

Comments ( 12 )
  • David Tuesday, March 27, 2012
  • Gabriel Wednesday, November 14, 2012

    Hi David,

    I'm a new user of ODI and I implemented something similar to this. I am facing a serious issue when an error occurs during execution (e.g. a primary key violation), then the whole execution fails and the error is not logged in the appropriate $Error table.

    When I change the IKM to "Incremental Update" then the errors get logged correctly.

    Do you have any idea about what could be causing this?


  • David Wednesday, November 14, 2012

    Hi Gabriel

    Its the IKM that does all the error handling work- if you don't incorporate the flow control pattern into the IKM you won't get the error handling. In the shipped IKM Oracle Multi Table Insert there is a restrictions section in the KM's description which states 'Flow/static control is not supported'. If you are using this, then that is why, if you have built your own then you may have not included or thought about the flow control.

    Cheers

    David


  • Gabriel Thursday, November 15, 2012

    Thanks for the response David!

    I am using the IKM Oracle Multi Table Insert, so I guess I will need to build something custom to keep the Multi Table Insert and achieve the logging in the error tables also.


  • saurabh Saturday, February 16, 2013

    Hi David,

    To achieve this result if we select only “IKM SQL control Append” for this 3 interfaces and we don’t do the changes like select “define query” to true & “execute” to true will it cause any problem?If yes then please describe?As I’m fresher I don’t know why you select “IKM Oracle Multitable Insert” instead of “IKM SQL control Append” please clarify?


  • David Sunday, February 17, 2013

    Hi Saurabh

    If you just have 3 interfaces using IKM SQL Control append you will do 3 separate DML statements (3 inserts). So may reprocess the source data 3 times. Multi table insert is an Oracle database optimization.

    Cheers

    David


  • saurabh Sunday, February 17, 2013

    Thanks David :)


  • Anindya gayen Monday, July 22, 2013

    Hi David,

    when I am trying to execute this same thing I am getting an error as follows:

    "ORA-00904: "TEMPORARY_CUSTOMER"."Income_level": invalid identifier"

    there is also a small query regarding the screen shot given above and the documentation:

    In the screen shot the filter is placed in insert regular interface, whereas in the documentation the filter is placed in the insert_special interface, so which one is correct?

    I am not getting why I am getting this error.

    My code is like this:

    insert all

    when 1=1 And (TEMPORARY_CUSTOMER."Income_level">25000) then

    into ABC.CUSTOMER_SPECIAL

    (CUST_ID, CUSTNAME, C_MAIL, DEPT_NO, INCOME_LEVEL)

    values

    (TEMPORARY_CUSTOMER."Cust_id", TEMPORARY_CUSTOMER."Cust_name", TEMPORARY_CUSTOMER."C_Mail", TEMPORARY_CUSTOMER."Dept_no", TEMPORARY_CUSTOMER."Income_level")

    when 1=1 then

    into ABC.CUSTOMER_REGULAR

    (CUST_ID, CUSTNAME, C_MAIL, DEPT_NO, INCOME_LEVEL)

    values

    (TEMPORARY_CUSTOMER."Cust_id", TEMPORARY_CUSTOMER."Cust_name", TEMPORARY_CUSTOMER."C_Mail", TEMPORARY_CUSTOMER."Dept_no", TEMPORARY_CUSTOMER."Income_level")

    select



    CUSTOMER.CUST_ID "Cust_id",

    CUSTOMER.CUSTNAME "Cust_name",

    CUSTOMER.C_MAIL "C_Mail",

    CUSTOMER.DEPT_NO "Dept_no",

    CUSTOMER.INCOME_LEVEL "Income_level"

    from

    ABC.CUSTOMER CUSTOMER

    where

    (1=1)


  • David Monday, July 22, 2013

    Hi Anindya

    The filter for the insert parts of the MTI go in the 'insert' interfaces.

    It looks like there is a bug in the IKM using mixed case names for columns in the temporary interface that defines the select clause. Can you try using upper case names, and ensure the insert interfaces that use it are in sync and the filter expressions that reference those columns are updated? I can get the code to work that way, and I can recreate your problem using mixed case names.

    Cheers

    David


  • Anindya gayen Tuesday, July 23, 2013

    Thank you David.

    Yes after changing the columns in the temporary interface to upper case it is working fine.


  • Anindya gayen Tuesday, July 23, 2013

    Hi David,

    There is a small query.

    Is there any mechanism to use a file and a table in the target side, where there are three tables in the source joined by PK-FK relationship.

    Thanks.


  • David Tuesday, July 23, 2013

    Hi Anindya

    The common source would need to be modeled either as a view or a temporary target and then multiple interfaces consuming it.

    Cheers

    David


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services