Page 1 of 2
oracle sequence resued for processing.
Posted: Mon Mar 02, 2009 3:19 pm
by efxuser
I searched the forum for oracle sequences for surrogate key generator.
My requirement is that I use oracle sequence .nextval to insert into oracle table.Also use these sequence numbers for later use.
I came across so many posts about sparse lookup and getting max sequence number from oracle ,keymanagement functions that now i am totally lost.
can some body kindly detail me steps i should follow for these? I am trying to take a gues shere...
1. Try to find maximum sequence number.
2. use .nextval to insert into oracle
3.Lost here...how do I get these rows along with sequence numbers to be used for further rpocessing?
Even you point me to other posts which i should foillow will also be more helpful.
Also, my admin seems convinced that surrogate key generator stage in ds is not efficient .ANy truth to his claim?
Thanks
[/img]
Re: oracle sequence resued for processing.
Posted: Mon Mar 02, 2009 3:35 pm
by betterthanever
you want to use that DB sequence to create the unique number every time you run one particular job or you want to use that DB sequence in other jobs as well???
Re: oracle sequence resued for processing.
Posted: Mon Mar 02, 2009 3:52 pm
by betterthanever
Code: Select all
source
|
|
|
|
Ora(Reference)-----Lkp-------Target
( Lkp type: sparse)
(Read Method : User-Defined sql)
(SQL Query : select sample_seq.nextval as seq_num from dual)
on the Lkp stage map the field seq_num to the ouput field which is unique seq number on the target side
Posted: Mon Mar 02, 2009 4:23 pm
by Mike
If given a choice, I never use a database sequence (or identity column). They are too slow and and promote inefficient flow design.
Database sequences are fine for OLTP processing, but add far too much overhead for bulk data processing.
The surrogate key generator stage with a state file is the way to go. The NextSurrogetKey() function is available for use in a transformer stage or SCD stage when and where it is needed for efficient flow design.
The more parallel processing you have, the worse the database sequence bottleneck will become.
Mike
Posted: Mon Mar 02, 2009 4:30 pm
by betterthanever
i agree....
Posted: Mon Mar 02, 2009 4:31 pm
by betterthanever
i agree....
Posted: Tue Mar 03, 2009 8:06 am
by efxuser
thanks for your quick response.can you please elaborate about the first stage you have (oracle reference) what is that to do? also in the lookup stage I dont find any option for sparse lookup?
Posted: Tue Mar 03, 2009 8:08 am
by efxuser
thanks for your quick response.can you please elaborate about the first stage you have (oracle reference) what is that to do? also in the lookup stage I dont find any option for sparse lookup?
Posted: Tue Mar 03, 2009 8:10 am
by efxuser
thanks for your quick response.can you please elaborate about the first stage you have (oracle reference) what is that to do? also in the lookup stage I dont find any option for sparse lookup?
Posted: Tue Mar 03, 2009 10:10 am
by betterthanever
efxuser wrote:thanks for your quick response.can you please elaborate about the first stage you have (oracle reference) what is that to do? also in the lookup stage I dont find any option for sparse lookup?
can you please elaborate about the first stage you have (oracle reference) what is that to do?----just to fetch the number from DB sequence.
also in the lookup stage I dont find any option for sparse lookup?---you would set this in oracle stage not in lookup stage
Posted: Tue Mar 03, 2009 10:11 am
by betterthanever
duplicate post
Posted: Tue Mar 03, 2009 10:12 am
by betterthanever
duplicate post
Posted: Tue Mar 03, 2009 10:21 am
by chulett
Sheesh... posts so good they need to be repeated - three times. Seriously, please start checking after you post and delete any duplicates, especially if it takes 'some time' to complete - that's pretty much a sure sign you'll have duplicate posts.
Posted: Tue Mar 03, 2009 10:27 am
by betterthanever
[quote="chulett"]Sheesh... posts so good they need to be repeated - [i]three times.[/i] Seriously, please start checking after you post and delete any duplicates, especially if it takes 'some time' to complete - that's pretty much a sure sign you'll have duplicate posts.[/quote]
can you help me delete the duplicate ones???
Posted: Tue Mar 03, 2009 10:38 am
by chulett
Once they've been replied to, they cannot be deleted, which is why it's important to check right away when you suspect there might have been a problem. Best you could do now would be to edit two of them and just say 'duplicate post' or some such in the body.