Archives of the TeradataForum
Message Posted: Tue, 16 Dec 2008 @ 10:01:08 GMT
| Subj: | | Re: What is the alternative way to sequence |
| |
| From: | | Victor Sokovin |
| | We are migrating our database from oracle to teradata. In procedures we are using the sequences to generate the next values. | |
| | We are invoking this in procedures using statement | |
> > select S4028_GROUPS.NEXTVAL into v_NewGroupId from dual
| | as of I know teradata will not support sequences. So how can we rectify this problem in teradata? | |
If Oracle sequences are used by the application in their full capacity then there is no easy alternative in Teradata. By "full capacity" I mean
that the same sequence is "queried" by many sessions which may request the "next value" at any time and sometimes even in parallel with each
other. Oracle provides for the queuing of requests and pretty fast fetching of the results to the sessions. Another functionality is handling of
exceptions, rollbacks etc, which you need to be aware of.
The alternatives proposed so far in the thread make use of data stored in a table, either autogenerated or maintained by explicit updates after
each new "next value" request. Potentially, sharing the table between multiple sessions can cause all kinds of logistic problems and the table may
become the bottleneck for both the design of the applications and their performance.
So, as always with porting of Oracle code to other RDBMS, it is usually not a one-to-one conversion. You have to understand what exactly the
Oracle application is doing with the sequences, and then build a new solution in the target RDBMS using that RDBMS's strongest features.
Victor
| 
