ODI 11g – Oracle Multi Table Insert

转载 2012年03月29日 20:36:58

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.

ODI11G 在Linux上的安装配置

OS环境:Red hat Linux x86_64   一、JDK安装         1. 去oracle官网上下载 http://www.oracle.com/technetwork/java/j...
  • shulkial
  • shulkial
  • 2013年07月04日 16:37
  • 1862

Oracle 数据库误truncate table恢复过程

北京某国企客户 Oracle 11g R2 数据库误truncate table CM_CHECK_ITEM_HIS,表数据丢失,业务查询到该表时报错,此时发现数据库的备份不能用,表数据无法查询。 ...
  • beiya123
  • beiya123
  • 2017年01月06日 14:42
  • 2283

ODI数据抽取教程_01设置数据源

根据前面的环境搭建步骤,ODI的环境已经搭建完成,代理也配置成功。 现在开始学习数据抽取的步骤。第一步:设置抽取的数据源和目标数据源1、 首先要有两个数据库,一个是提供源数据的数据库,可以是ODI的...
  • LuckyDouer
  • LuckyDouer
  • 2016年09月23日 17:14
  • 2683

ODI 11g和Oracle BAM的集成

近日终于完全测试通过了ODI11g和Oracle BAM的集成,颇费了不少周折。 回顾过程,不外乎有以下几点比较重要: 1)配置ODI和Oracle BAM的环境,官网有详细介绍,此处不多说; ...
  • qingqingxuelang
  • qingqingxuelang
  • 2012年04月28日 14:19
  • 1556

Oracle ODI 11g官方文档

  • 2011年11月10日 20:32
  • 8.75MB
  • 下载

Oracle ODI 11g 官方教程

  • 2013年04月05日 23:27
  • 7.51MB
  • 下载

[ODI] Oracle Data Integrator 11g 经典实例 (英文版)

  • 2013年09月27日 11:04
  • 6.38MB
  • 下载

Oracle ODI 11g 官方教程

  • 2013年07月27日 17:03
  • 8.79MB
  • 下载

Oracle ODI 11G安装与技术文档

  • 2015年12月21日 16:12
  • 4.32MB
  • 下载

【学习笔记】oracle11g multi column statistics深入研究笔记

oracle研究中心:目前,CBO(Cost-Based Optimizer)是Oracle默认使用的查询优化器Query Optimizer模式。在CBO中,SQL执行计划的生成,是以一种寻找成本(...
  • techsupporter
  • techsupporter
  • 2017年11月18日 22:37
  • 55
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ODI 11g – Oracle Multi Table Insert
举报原因:
原因补充:

(最多只允许输入30个字)