Re: Sequence numbers

Marcel van der Holst wrote:

> Hi all,
>
> I'm using an oracle sequence to generate unique numbers:
>
> sequence.nextval gives me the next unique number.
>
> The sequence caches 250 numbers and the increment is 1.
>
> The question is whether it is possible to ask to oracle for the next
> 100
> unique numbers in a single SQL call.
> At the moment I'm using
> SELECT sequence.nextval from dual;
> to get the next number, however, this query is executed 20000 times.
> Beforehand, I know that I need a lot of numbers anyway.
> One way to do it is to set increment to 100, and than let the
> application handle the numbers in between. However, other applications
> are using the sequence as well, so I have to let the sequence number
> increment to 1.
>
> Note that it is not necessary that all numbers generated by the
> sequence have
> to be consecutive.
>
> Marcel
> holstm_at_logica.com

 AFAIK, there is no way to grab multiple sequence numbers in one call.

It may also be worth asking yourself *why* you think it will be better to grab a batch of numbers and process them yourself? This seems to be defeating the whole point of having the sequence generator in the first place... I suppose you could write a procedure that grabs sequence numbers in blocks of 100 (by running the select statement you gave 100 times) and passes them back as a parameter, but I don't think you'd gain much in terms of performance, which I think is your goal (?), as the sequence numbers and the parsed SQL statement would already be in memory. You'd also have to handle all the concurrency issues yourself which, of course, is what the sequence generator does for you.

Steve Phelan. Received on Thu May 15 1997 - 00:00:00 CDT