需要在Oracle中重置sequence的值 [英] Need to reset the value of sequence in Oracle

问题描述

我正在使用 Spring 和 Hibernate 使用 Java 开发 Web 应用程序.假设我有一张桌子.当我从这个表中删除一些记录时，有时我需要重置主键字段的值.

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.

假设我在一个表中有 10 条记录，我删除了最后 5 条记录.现在，当我插入新记录时，主键字段的值应该从 6 开始，但它会从 11 开始.

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 6 but it would start at 11.

如果我需要在MySql中6(maximum+1)处开始主键值，只需要执行如下SQL语句即可.

If I need to start the primary key value at 6 (maximum +1) in MySql, I just need to execute the following SQL statement.

alter table table_name auto_increment=1;

这将自动将 auto_increment 的值重置为该字段的 maximum + 1 值(概念上可能不正确，但它有效).

This will automatically reset the value of auto_increment to maximum + 1 value of that field (May conceptually be incorrect but it works).

在 Oracle (10g) 中，我将 sequence 与主键一起使用.当某些记录从数据库中删除时，Oracle 有没有办法将 sequence 的值重置为 maximum + 1 值?

In Oracle (10g), I'm using sequence with the primary key. Is there a way in Oracle to reset the value of the sequence to maximum + 1 value when some records are deleted from the database?

推荐答案

如果正在使用该值，则不应重置该值的原因:

如果您有 20 条记录并删除记录 5-10 会怎样?中间有一个间隙，重新设置序列无法解决.序列将 从不生成无间隙的数字序列，完美的1, 2 .. n.

Reasons why you shouldn't reset the value if it's being used:

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.

如果你调用 .nextval 并且不使用它gone的值.你要删除并重新创建序列吗?如果您开始插入并取消它并且 Oracle 回滚 你所做的那些价值观消失了.如果你设置了 nocache，那么你的差距会更小，但会降低性能；这值得么?

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:

1. 首先，找出表中的最大 id(序列)值是多少.
2. 然后删除并重新创建序列.

找到最大值意味着您需要以再次降低性能为代价来动态重新创建序列.

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.

与文档的建议相反，正如 Jeffrey Kemp 在评论中所建议的那样，有一种方法可以在不删除和重新创建序列的情况下做到这一点.

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.

即，通过:

1. 计算表中最大 id 与序列当前值之间的差异.
2. 改变序列以增加这个负数
3. 改变序列以再次增加 1.

1. Working out the difference between the maximum id in your table and the current value of the sequence.
2. Altering the sequence to increment by this negative number
3. Altering the sequence to increment by 1 again.

这样做的好处是对象仍然存在，并且触发器、授权等仍然保留.在我看来，不利的一面是，如果另一个会话与您的会话同时增加此负数，则您可能会回退得太远.

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.

这是一个演示:

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>

这篇关于需要在Oracle中重置sequence的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！