Re: Sequence numbers

Steve Phelan (stevep_at_no-spam.pmcgettigan.demon.co.uk) wrote:
: 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.

Main reason is that it is indeed slow. When I have to make 10000 calls to Oracle, it is much slower then making 100 calls and doing the increment oif the numbers in my own program.

Regards,

Marcel. Received on Thu May 15 1997 - 00:00:00 CDT