Oracle sequence.nextval

[April.Johnson]

Hi,
I need to be able to get 1000 sequence.netval values from an Oracle database. Right now, the Java code loops through 1000 times and executes (by calling statement.executeQuery()) the following PreparedStatement 1000 times.

PreparedStatement statement = connection.prepareStatement( "select SEQ.nextval from dual") 

This takes about 1 minute and 15 seconds. I need to speed this up.
Is there a way I can send one statement to the database and get back 1000 values of nextval?
If I can't do that, any suggestion on how to speed it up any other ways?
This has to be threadsafe.
Thanks for any help.
April

[sandy gupta]

Hi April,
Thats really very easy...
Instead of going to the DB every time, use increment parameter of the sequence to set the increment value to 1000. Then use the SEQ.nextval that u got and in the javacode use ++/+ 1 to increment in the loop a 1000 times.
I hope this will help

Sahil

[April.Johnson]

Thanks, Sahil,
But I don't create the sequence. It's a pre-existing sequence that I just get the next number from. The increment value is set to one.
And actually, for clarification, I need to be able to get any number of nextval values from 2 to 4000. And it will vary from user to user and with each execution. I just used 1000 as an example.
I thought of doing something like the following pseudocode?

int startVal = sequence.nextval;
setSequenceNextValue(startVal + 1000);
// Loop through and assign unique numbers

But (even if I could do this and I don't know I can), the problem with that would be that it wouldn't be thread-safe. If another thread grabbed sequence.nextval before it was set, there would be a conflict.
Thanks for making me think.
April

[Mark Spritzler]

use increment parameter of the sequence to set the increment value to 1000.

April I think what he was saying is that, you get the Nextval, then you use a special function that will automatically add 1000 to the sequence. That way you just told the sequence give me the next val and increment yourself by 1000.
You then loop through 1000 times to add 1 to the original number given to get all those values.
For instance
select mySeq.nextval from dual
then
something like
select mySeq.increment(1000) from dual
(mySeq.increment(1000) This is probably not the exact syntax, but it should be something just like it.)
The first query returns 101.
Then you loop 1000 times adding 1 to 101 till you finish at where the sequence is now at already.
I am not sure about thread safetyness here, However you can expect quick results.
Mark