我如何重复使用主键列甲骨文序列差距？ [英] How do I reuse Oracle sequence gaps in primary key column?

问题描述

我使用了Oracle序列作为表的主键，并在Java应用程序映射中使用INT此主键，现在我发现我的客户已达到表的最大INT，连序列可以持续增长。但Java int的已不再能够存储它们，我不想改变的Java code从int长，因为非常大的成本。后来我发现客户有DB在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 code修改然后我可以使用这些差距。它应该是巨大的。

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中code，我可以继续使用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，只好变量更改为长。我说，忍辱负重，并进行转换。现在有点痛，稍后会​​为您节省大量的持续性疼痛。只是我的意见。

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.

这篇关于我如何重复使用主键列甲骨文序列差距？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！