oracle - 在 Oracle 中从 varchar 查找下一个 id

Question

我有一行是具有唯一约束的 varchar(50)，我想为新插入获取下一个具有给定前缀的唯一编号。

我的行可能如下所示:

ID (varchar)
00010001
00010002
00010003
00080001

因此，如果我想从前缀“0001”中获取下一个唯一号码，它将是“00010004”，但如果我想从前缀“0008”中获取它，它将是“00080002”。

此表中将有超过 1 百万个条目。 Oracle 11 有没有一种方法可以相当快地执行这种操作？

我知道这个设置非常疯狂，但这是我必须处理的。我无法创建任何新表等。

Answer 1

您可以搜索指定前缀的最大值并递增:

SQL> WITH DATA AS (
  2     SELECT '00010001' id FROM DUAL UNION ALL
  3     SELECT '00010002' id FROM DUAL UNION ALL
  4     SELECT '00010003' id FROM DUAL UNION ALL
  5     SELECT '00080001' id FROM DUAL
  6  )
  7  SELECT :prefix || to_char(MAX(to_number(substr(id, 5)))+1, 'fm0000') nextval
  8    FROM DATA
  9   WHERE ID LIKE :prefix || '%';

NEXTVAL
---------
00010004

我相信您知道这是一种低效的生成主键的方法。此外，它不会在多用户环境中很好地发挥作用，因此无法扩展。由于列上有 UNIQUE 约束，并发插入将等待然后失败。

如果前缀总是相同的长度，您可以稍微减少工作量:您可以创建一个专门的索引，以最少的步骤找到最大值:

CREATE INDEX ix_fetch_max ON your_table (substr(id, 1, 4), 
                                         substr(id, 5) DESC);

然后下面的查询可以使用索引并将在检索到的第一行停止:

SELECT id 
  FROM (SELECT substr(id, 1, 4) || substr(id, 5) id
          FROM your_table
         WHERE substr(id, 1, 4) = :prefix
         ORDER BY substr(id, 5) DESC)
 WHERE rownum = 1

如果你需要同时插入相同的前缀，我建议你使用DBMS_LOCK请求锁定指定的 newID。如果调用失败是因为有人已经插入了这个值，请尝试使用 newID+1。尽管这涉及比传统序列更多的工作，但至少您的插入不会相互等待(可能导致死锁)。