Jagadeesh

• Apr 3rd, 2006

 

Hi

Good Question... But in any informatica project we will never use oracle seq generator... Due to lot of reasos.

1. Your question itself limited to oracle.

2. It shoud fire this query at datbase level and it should retrive that corresponding data to transformation.

3. It is not easy to handle complex transforamtions

4. U can't run same time same session more than one time..(Very IMP)

like this lot of diff are there...

SO no one will use this seq generator at oracle level

Better to use at informatica level...

If u want further discussion let me know..

I will explain about performance of "informatica and oracle" related to cache's

ok

From

Jagadeesh

Srinu

• Apr 4th, 2006

 

Thanks Jagadeesh,

Can U explain performance of informtica and oracle.

rsannu2k

 Profile Answers by rsannu2k 

• Jun 5th, 2007

 

Good question,

Informatica Sequence Gen, uses its server cache and generates the sequence numbers and uses it along with the load, which happens at the same time, remember it will use the Informatica Cache, which is comparatively not that big with transformations like Lookup and others....? Advantage is very easy to create and use it when we are inserting into "multiple targets"!, Infa seq is part and parcel of mapping(s).

but in case of Oracle Sequence, we need to maintain it separately along with tables and other objects, and the main question is how will you access it everytime in every mapping? solution is there, but do you recommend it?

N SRIRAM K

 Profile Answers by N SRIRAM K Questions by N SRIRAM K

• Jun 25th, 2007

 

No doubt, Sequence generator transformation will be fast in Informatica.

Baloo_nanduri

 Profile Answers by Baloo_nanduri 

• Jul 25th, 2010

 

To answer precisely functionality of both is same and even both are at par w.r.t to performance but there are certain limitations Oracle sequence generator is a stored procedure which can be call from pro*c, PL/SQL or other Oracle compatible programming languages through an SQL statement but Informatica sequence generator can be triggered on any data from different databases or even data from a flat file.

You have to keep one thing in mind it all depends on how you are going to use a sequence generator its always better to use one for each process or application because it just generates unique keys or numbers for example if we are using a sequence generator in one process and loadede 100 records it numbers from 1 to 100 and will waiting at 101 for the next run, in this scenario if you call this from another process which loads altogether a different table your sequence starts from 101 and so on... for better understanding you can look into Informatica PDF

hanimi

 Profile Answers by hanimi 

• Feb 26th, 2011

 

Oracle Sequence Generator is faster.