如何在主键列中重用 Oracle 序列间隙? [英] How do I reuse Oracle sequence gaps in primary key column?

问题描述

我使用Oracle序列作为表的主键，并在Java应用程序中使用int映射这个主键，现在我发现我的客户已经达到了表中的最大int，甚至序列可以连续增加.但是Java int 不再能够存储它，我不想因为非常大的成本而将Java 代码从int 更改为long.然后我发现客户数据库在 ID 列中有很多很大的差距.有什么办法可以重用这些丢失的 ID 号吗?

I used Oracle sequence as primary key of a table, and used int in Java application mapping this primary key, now I found my customer has reached to the maximum int in table, even the sequence can be continuous increase. but Java int has no longer able to store it, I don't want change Java code from int to long because of very big cost. then I found customer DB there have many big gaps in ID column. can any way I can reuse these missing Id number?

如果可以在 DB 级别执行此操作，例如我可以重新组织此序列以将这些缺失的数字添加到其中，因此无需更改 Java 代码，然后我就可以使用这些间隙.应该很棒.

If can do this in DB level, something like I can re-org this sequence to add these missing number to it, so no Java code change then I can use these gaps. it should be great.

我将编写一个函数来查找间隙范围，在获得这些数字后，如果可以，我想将它们分配给序列值的池，所以从现在开始，它不会使用自动递增，只需使用我分配的号码.在 Java 代码中，我可以继续使用 findNextNumber 来调用序列.但序列将能够返回我分配给它的值.好像不可能吧?有什么替代方法吗?

I will write a function to find the gap ranges, after having these numbers, If I can, I want assign them to pool of the sequence value, so maybe from now on, it will not use auto-incrementing, just use the number I assigned. in Java code, I can continue use findNextNumber to call the sequence. but sequence will be able to return the value I assigned to it. it seems impossible, right? any alternative?

推荐答案

你的意思是，序列会返回一个间隙"范围内的值吗?我不这么认为，除非您出于某种原因删除/重新创建它.我想您可以编写某种函数来查找表中的 PK 间隙，然后将这些间隙范围保存到另一个表中，并使用间隙表滚动"您自己的序列函数.十分难看.试图恢复"这些差距听起来像是避免不可避免的绝望尝试 - 您的 java PK 数据类型应该与 DB 数据类型保持一致.很久以前，我在一个 VB 应用程序中遇到了同样的问题，该应用程序的类键定义为 16 位整数，并且序列超过 32K，不得不将变量更改为 Long.我说，咬紧牙关，进行转换.现在有点痛苦，以后会为您节省很多持续的痛苦.只是我的意见.

Do you mean, will the sequence ever return a value that is in a "gap" range? I don't think so, unless you drop/re-create it for some reason. I guess you could write a function of some sorts to find the PK gaps in your table, then save those gap ranges to another table, and "roll" your own sequence function using the gap table. Very ugly. Trying to "recover" these gaps just sounds like a desperate attempt to avoid the unavoidable - your java PK data type should have aligned with the DB data type. I had the same problem a long time ago with a VB app that had a class key defined as 16-bit integer, and the sequence exceeded 32K, had to change the variables to a Long. I say, bite the bullet, and make the conversion. A little pain now, will save you a lot of ongoing pain later. Just my opinion.

这篇关于如何在主键列中重用 Oracle 序列间隙?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！