What's New in ODI 11g? - Part 4: Core Features

Oracle Data Integrator 11gR1 includes a large number of features and enhancements to the 10gR3 release. In this blog series I will try to explain the major directions taken by the product and give a quick overview of these features and enhancements.

For a detailed list of features, the following documentation link can be used as a reference.

There are major areas of changes in this release:

  • New Architecture for Enterprise-Scale Deployment
  • New Design-Time Experience
  • New Run-Time Experience
  • Core Enhancements to E-LT and Declarative Design

Features that enhance developer's productivity, including the new ODI Studio are already detailed in Part 2. In this fourth and last part, I will focus on an overview of the changes related to the core product engine. These changes are enhancements to both the E-LT architecture and Declarative Design approach.

Datasets and Set-Based Operators

Datasets are a big leap in interface design in ODI 11g: Imagine that you want to union in a target table information coming from a set flat files with information from a set of tables (with different transformations, of course). With ODI 10g, you start thinking about multiple interfaces, procedures and packages. Well, ODI 11g allows you to do this in a single interface.

In an ODI 11g interface, Datasets represent flows of data that are merged into a single target. Each flow corresponds to a set of sources and has its own set of transformations (mappings, joins, filters, and so forth). These different datasets are merged using set-based operators (UNION, INTERSECT and so forth).

ODI is able to generate the code corresponding to all the datasets and merge all these data flows into a single flow that can be checked and then integrated using any of the existing integration strategies.

ODI 11g Dataset and Set-Based Operators.
Figure 1: Datasets can be added and managed from the mapping tab. Each dataset appears as a sub-tab of the mapping and will contain different sources, joins, filters, mappings and so forth).

Derived Select for Temporary Interfaces

When using a temporary interface as a source in another interface, it is possible not to persist the temporary datastore and generate instead a Derived Select (sub-select) statement. The temporary interface no longer needs to be executed to load the temporary datastore, and developments using temporary tables are greatly simplified.


Figure 2: If the Use Temporary Interface as Derived Table option is selected, the temporary datastore SRC_CUSTOMER will not be persisted and turned into a sub-select instead.


Oracle Data Integrator 11g introduces the concept of Lookup in the interfaces. Lookups are created using a wizard, have a specific graphical artifact and a dedicated property inspector in the interface window. Lookups are generated by ODI for processing in the database engine in the form of a Left Outer Join in the FROM clause or as an expression in the SELECT clause (in-memory lookup with nested loop).

Figure 3: The Lookup Wizard simplifies lookup creation.

Other Changes

In addition to these three major changes, other improvements have been made to the core product to support more databases capabilities.


Partitioning information can be reverse-engineered in datastores, and specific partitions can be used when such datastore is used as a source or a target.

Figure 4: The partitions reverse-engineered with the TRG_CUSTOMER datastore can be selected when this datastore is used as a target (or a source) in an interface.

Temporary Indexing

Joins and filters in interfaces can be automatically indexed for better performances. By selecting index types on a join or a filter, the user can request that ODI creates temporary indexes on the columns participating to the join or index when this interface runs.

Figure 5: Temporary indexing for a join. An index type has to be specified for both sides of the join.

Native Sequences

ODI Sequences can now directly map to native sequences defined in a database. These sequence can be reverse-engineered. Such a sequence is used with an ODI sequence syntax (for example #PROJECT001.MYSEQUENCE) and is automatically converted to the database's syntax in the generated code.

Figure 6: A Native sequence is declared and selected from the list of sequences present in an Oracle schema.

Natural Joins

Joins in interfaces now support the Natural Join type. This join does not require any join expression, and is handled by the execution engine, which matches automatically columns with the same name.

odi_11g_core_natural join.gif
Figure 7: A natural join does not require any join expression.


The various features included in Oracle Data Integrator 11g Release 1 bring the state of the art in data integration to a new level:

Learn More

To learn more about the new ODI design-time features, please review the following documents:

Post a Comment:
  • HTML Syntax: NOT allowed

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality


« April 2014