How to define an index on a source or target table in ODI

Oracle Data Integrator (ODI) Logo

 

 

 

 

 

 

 

 

 

 

The topic of indexes in Oracle Data Integrator (ODI) is an easy one once you learn your way around in ODI studio. Indexes are defined under Design > Models > [your model] > [Table] > [Constraints]. They are independent of the column definitions for the most part although they do reference valid column definitions.

 

  • The first step to create metadata for an index is to add a new constraint and name it:image

  • On the Columns tab define the list of columns to be indexedimage

  • Ensure that the index is active and marked for creation on the database on the Control tabimage

  • Review all additional options on the Flexfields tab, for some of the options such as Index Type you do need to manually type your selection.
    image

 

 

After you have defined an index on metadata you need to run a new load for the index to be created on the database.

 

My Tools:

Oracle Business Intelligence Applications (OBIA) 11.1.1.7.0

Oracle Data Integrator 11g (ODI Studio, ODI Server, no clustering)

 

How to Make sure an index is defined as unique in odi

Oracle Data Integrator

Indexes are defined as constraints on the Model view in ODI Studio. To make a field unique follow one of the two alternatives listed below.

 

PRIMARY KEY

 

Define the Index as a Primary Key constraint object on the Models area within the design view

Description view on primary key index definition in ODI

 

Add the unique columns on the Columns tab

image

 

 

In the Control tab make sure the constraint is active and marked to be defined in the database for both flow and static control checkboxes

 

 

 

image

 

Review that the correct settings are configured in the Flexfields tab

Flexfields view on Primary Key Index in ODI

 

ALTERNATE KEY

 

Define the constraint object for your table as an alternate key in the ODI models area

Defining a unique index in Oracle ODI

 

Add the unique columns on the Columns tab

image

 

In the Control tab make sure the constraint is active and marked to be defined in the database for both flow and static control checkboxes

 

image

Review that the correct settings are configured in the Flexfields tab

Flexfields view on unique index in ODI