0

We have a "audit" table that we create lots of rows in. Our persistence layer queries the audit table sequence to create a new row in the audit table. With millions of rows being created daily the select statement to get the next value from the sequence is one of our top ten most executed queries. We would like to reduce the number of database roundtrips just to get the sequence next value (primary key) before inserting a new row in the audit table. We know you can't batch select statements from JDBC. Are there any common techniques for reducing database roundtrips to get a sequence next value?

user1452701
  • 144
  • 1
  • 10
  • 2
    Do you have to run a separate query for the sequence rather than putting the `nextval` call in the `insert` and returning the value with the `returning into` clause? What is the `cache` set to on the sequence? If you've left it at the default of 20, bumping that up a couple orders of magnitude will make running the queries much more efficient. – Justin Cave Jul 27 '15 at 04:57
  • possible duplicate of [Query several NEXTVAL from sequence in one satement](http://stackoverflow.com/questions/8292199/query-several-nextval-from-sequence-in-one-satement) – wero Jul 27 '15 at 05:39

3 Answers3

4

Get a couple (e.g. 1000) of sequence values in advance by a single select:

select your_sequence.nextval
from dual 
connect by level < 1000

cache the obtained sequences and use it for the next 1000 audit inserts. Repeat this when you have run out of cached sequence values.

wero
  • 32,544
  • 3
  • 59
  • 84
1

Skip the select statement for the sequence and generate the sequence value in the insert statement itself.

insert (ID,..) values (my_sequence.nextval,..)

No need for an extra select. If you need the sequence value get it by adding a returning clause.

insert (ID,..) values (my_sequence.nextval,..) returning ID into ..

Save some extra time by specifying a cache value for the sequence.

Rene
  • 10,391
  • 5
  • 33
  • 46
0

I suggest you change the "INCREMENT BY" option of the sequence and set it to a number like 100 (you have to decide what step size must be taken by your sequence, 100 is an example.) then implement a class called SequenceGenerator, in this class you have a property that contains the nextValue, and every 100 times, calls the sequence.nextVal in order to keep the db sequence up to date. in this way you will go to db every 100 inserts for the sequence nextVal

every time the application starts, you have to initialize the SequenceGenerator class with the sequence.nextVal.

the only downside of this approach is that if your application stops for any reason, you will loose some of the sequences values and there will be gaps in your ids. but it should not be a logical problem if you don't have anu business logic on the id values.

Hossein
  • 1,151
  • 9
  • 19