I'm working with Spring and Hibernate to develop web applications in Java. Let's assume that I have a table. When I delete some records from this table, sometimes I need to reset the value of the primary key field.
Let's say that I have 10 records in a table and I delete the last 5 records. Now, when I insert new records, the value of the primary key field should be started at
alter table table_name auto_increment=1;
maximum + 1
maximum + 1
What happens if you have 20 records and delete records 5-10? You have a gap in the middle that re-setting the sequence will not solve. Sequences will never generate a gap free sequence of numbers, a perfect 1, 2 .. n.
If you call
.nextval and don't use the value it's gone. Are you going to drop and re-create the sequence? If you start an insert and cancel it and Oracle rolls back what you've done those values are gone. If you set
nocache then you will have less gaps but at a cost of a hit to performance; is it worth it?
Your cache should be set to the number of inserts you expect to do at any one time across all sessions to avoid any performance issues. Sequences are designed to provide a very quick, scalable way of creating a surrogate key without any locks etc not to re-generate the set of positive integers.
At the end of the day it shouldn't matter in the slightest. If you're relying on an unbroken sequence as the key of your table then you have a problem with your data rather than sequences.
To actually answer your question you would need to:
Finding the maximum value means you'd need to re-create the sequence dynamically at the cost of another hit to performance.
If you try to insert something into your table whilst this is happening it will fail, and may invalidate any triggers or other objects which use the sequence:
declare l_max_value number; begin select max(id) into l_max_value from my_table; execute immediate 'drop sequence my_sequence_name'; -- nocache is not recommended if you are inserting more than -- one row at a time, or inserting with any speed at all. execute immediate 'create sequence my_sequence_name start with ' || l_max_value || ' increment by 1 nomaxvalue nocycle nocache'; end; /
As I say this is not recommended and you should just ignore any gaps.
Contrary to the documentation's recommendation there is, as Jeffrey Kemp suggested in the comments, a way to do this without dropping and re-creating the sequence.
idin your table and the current value of the sequence.
The benefits of this are that the object still exists so and triggers, grants etc are still maintained. The downside, as I see it, is that if another session increments by this negative number at the same time as yours you can go back too far.
Here's a demonstration:
SQL> create sequence test_seq 2 start with 1 3 increment by 1 4 nomaxvalue 5 nocycle 6 nocache; Sequence created. SQL> SQL> create table tmp_test ( id number(16) ); Table created. SQL> SQL> declare 2 l_nextval number; 3 begin 4 5 for i in 1 .. 20 loop 6 insert into tmp_test values ( test_seq.nextval ); 7 end loop; 8 9 end; 10 / PL/SQL procedure successfully completed. SQL> SQL> select test_seq.currval from dual; CURRVAL ---------- 20 SQL> SQL> delete from tmp_test where id > 15; 5 rows deleted. SQL> commit; Commit complete.
SQL> SQL> declare 2 3 l_max_id number; 4 l_max_seq number; 5 6 begin 7 8 -- Get the maximum ID 9 select max(id) into l_max_id 10 from tmp_test; 11 12 -- Get the current sequence value; 13 select test_seq.currval into l_max_seq 14 from dual; 15 16 -- Alter the sequence to increment by the difference ( -5 in this case ) . 17 execute immediate 'alter sequence test_seq 18 increment by ' || ( l_max_id - l_max_seq ); 19 20 -- 'increment' by -5 21 select test_seq.nextval into l_max_seq 22 from dual; 23 24 -- Change the sequence back to normal 25 execute immediate 'alter sequence test_seq 26 increment by 1'; 27 28 end; 29 / PL/SQL procedure successfully completed. SQL> SQL> select test_seq.currval from dual; CURRVAL ---------- 15 SQL>