Archives of the TeradataForum
Message Posted: Mon, 31 Dec 2001 @ 16:42:35 GMT
Charles Perry wrote:
Charles, the responses you have received so far are good, but they haven't quite addressed the main point. In Oracle, a sequence is a database object, just like a table or a view; it automagically returns the next number in a sequence. The starting number and increment can be anything you like; thus, the sequence can be 0,1,2..., or 1,2,3..., or 0,-1,-2, or 200,205,210..., or whatever.
A previous respondent remarked that CSUM would do what you wanted, and this would probably be the best way to go. If you must have an object that mimics the behavior of a sequence, you should probably set up a table with two columns and one row and a macro (or possibly a stored procedure) to go with it. The table would be something like this:
create set table my_sequence
The reason for using the "prim_key" column is to keep the row always on the same AMP. If "next_number" were the index value, the row would have to be moved to a different AMP every time you changed it -- yuk!
The macro must be written to guarantee that, if two transactions are trying to get a new sequence number at the same time, one is forced to wait for the other. The macro would go something like this (UNTESTED):
create macro my_seq_macro as
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|