cache - valeurs de séquence de cache pilote peut Oracle JDBC - Stack Overflow

Oracle sequence values can be cached on the database side through use of the 'Cache' option when creating sequences. For example

CREATE SEQUENCE sequence_name
  CACHE 1000;

will cache up to 1000 values for performance.

My question is whether these values can be cached in the the oracle drivers.

In my application I want to pull back a range of sequence values but don't want to have to go back to the database for each new value. I know Hibernate has similar functionality but I've been unable to find out exactly how it's accomplished.

Any advice would be greatly appreciated.

Thanks,

Matt

No, you can not reserve one batch of numbers in one session (if I understood correctly). Setting correct cache value would very likely make this acceptable from performance perspective.
If you still insist you can can create similar functionality yourself - to be able to reserve at once one range of numbers

As mentioned by igr it seems that the oracle drivers cannot cache sequence values in the java layer.

However, I have got round this by setting a large increment on the sequence and generating key values myself. The increment for a sequence can be set as follows:

CREATE SEQUENCE sequence_name 
INCREMENT BY $increment;

In my application, every time sequence.nextval is executed I assume that the previous $increment values are reserved, and can be used as unique key values. This means that the database is hit once for every $increment key values that are generated.

So lets say for example that $increment=5000, and we have a starting sequence value of 1. When sequence.nextval is run for the first time the sequence value is incremented to 5001. I then assume that the values 2..5001 are reserved. Then the 5000 values are used in the application (In my use case, they are used for table primary keys), as soon as they are all used up sequence.nextval is run again to reserve another 5000 values, and the process repeated.

The only real downside I can see to this approach is that there is a tiny risk from someone running ddl to modify the $increment between the time the nextval is run and the $increment is used to generate values. Given this is very unlikely, and in my case the ddl will not be updated at runtime, the solution was acceptable.

I realise this doesn't directly answer the question I posed but hopefully it'll be useful to someone else.

Thanks,

Matt.

Oracle sequence values can be cached on the database side through use of the 'Cache' option when creating sequences. For example

CREATE SEQUENCE sequence_name
  CACHE 1000;

will cache up to 1000 values for performance.

My question is whether these values can be cached in the the oracle drivers.

In my application I want to pull back a range of sequence values but don't want to have to go back to the database for each new value. I know Hibernate has similar functionality but I've been unable to find out exactly how it's accomplished.

Any advice would be greatly appreciated.

Thanks,

Matt

No, you can not reserve one batch of numbers in one session (if I understood correctly). Setting correct cache value would very likely make this acceptable from performance perspective.
If you still insist you can can create similar functionality yourself - to be able to reserve at once one range of numbers

As mentioned by igr it seems that the oracle drivers cannot cache sequence values in the java layer.

However, I have got round this by setting a large increment on the sequence and generating key values myself. The increment for a sequence can be set as follows:

CREATE SEQUENCE sequence_name 
INCREMENT BY $increment;

In my application, every time sequence.nextval is executed I assume that the previous $increment values are reserved, and can be used as unique key values. This means that the database is hit once for every $increment key values that are generated.

So lets say for example that $increment=5000, and we have a starting sequence value of 1. When sequence.nextval is run for the first time the sequence value is incremented to 5001. I then assume that the values 2..5001 are reserved. Then the 5000 values are used in the application (In my use case, they are used for table primary keys), as soon as they are all used up sequence.nextval is run again to reserve another 5000 values, and the process repeated.

The only real downside I can see to this approach is that there is a tiny risk from someone running ddl to modify the $increment between the time the nextval is run and the $increment is used to generate values. Given this is very unlikely, and in my case the ddl will not be updated at runtime, the solution was acceptable.

I realise this doesn't directly answer the question I posed but hopefully it'll be useful to someone else.

Thanks,

Matt.

Source