Rollover in Identity/Serial data types?
Ian Michael Gumby
end, you have an issue of collisions and errors in the backing index.
The current solution is to just increase the size and hope that its
large enough that you won't run out of numbers.
This is true of any and all of the databases.
But in theory, when a roll over occurs, you should be able to do some
predictive analysis and determine the next open slot within the B-Tree
index.
While this is a very esoteric issue, as disk becomes cheap and more
data is captured, this may become an issue faster than people think.
Clive Eisen
For most people there is an implied time ordering with serials which
your suggestion would break
That's what serial8 is for
It ranges to 9,223,372,036,854,775,807 which is probably enough for most
applications
Ian Michael Gumby
> Ian Michael Gumby wrote:
> > One of the issues with a serial data type is that if you roll past the
> > end, you have an issue of collisions and errors in the backing index.
>
> > The current solution is to just increase the size and hope that its
> > large enough that you won't run out of numbers.
> > This is true of any and all of the databases.
>
> > But in theory, when a roll over occurs, you should be able to do some
> > predictive analysis and determine the next open slot within the B-Tree
> > index.
>
> > While this is a very esoteric issue, as disk becomes cheap and more
> > data is captured, this may become an issue faster than people think.
>
> For most people there is an implied time ordering with serials which
> your suggestion would break
>
With multiple OLTP transactions occurring simultaneously, with a
serial you only guarantee uniqueness. You don't guarantee order.
And with the current implementation, when you go past the end of the
largest number of a serial, you start over. So today, you still wrap
around.
> That's what serial8 is for
>
> It ranges to 9,223,372,036,854,775,807 which is probably enough for most
> applications
I know. Its a large number. But I'm starting to see some applications
where its possible to surpass that number.
Remember this doesn't mean that the table has to hold that many rows
since they can be purged, but the serial counter will continue to
grow.
This is more of a theoretical issue. The idea is if you can look at a
b-tree index and find the next open node.
Clive Eisen
> On Feb 6, 10:07 am, Clive Eisen <cl...@serendipita.com> wrote:
>
>> Ian Michael Gumby wrote:
>>
>>> One of the issues with a serial data type is that if you roll past the
>>> end, you have an issue of collisions and errors in the backing index.
>>>
>>> The current solution is to just increase the size and hope that its
>>> large enough that you won't run out of numbers.
>>> This is true of any and all of the databases.
>>>
>>> But in theory, when a roll over occurs, you should be able to do some
>>> predictive analysis and determine the next open slot within the B-Tree
>>> index.
>>>
>>> While this is a very esoteric issue, as disk becomes cheap and more
>>> data is captured, this may become an issue faster than people think.
>>>
>> For most people there is an implied time ordering with serials which
>> your suggestion would break
>>
>>
> No, it doesn't.
>
three about this stuff.
> With multiple OLTP transactions occurring simultaneously, with a
> serial you only guarantee uniqueness. You don't guarantee order.
> And with the current implementation, when you go past the end of the
> largest number of a serial, you start over. So today, you still wrap
> around.
>
>
>> That's what serial8 is for
>>
>> It ranges to 9,223,372,036,854,775,807 which is probably enough for most
>> applications
>>
>
> I know. Its a large number. But I'm starting to see some applications
> where its possible to surpass that number.
> Remember this doesn't mean that the table has to hold that many rows
> since they can be purged, but the serial counter will continue to
> grow.
>
> This is more of a theoretical issue. The idea is if you can look at a
> b-tree index and find the next open node.
>
>
ricew
I am curious what you define as where it is possible to surpass this
number.
By my math even at a 10 trillion transactions a day
(10,000,000,000,000). it still takes 92,230 days(over 250 years) to
get past this number.
As a note, my math says 10 trillion a day comes out to 115 million a
second.
Am I missing something?
Will
Wikking
Yes, Gumby is a no-brainer.
--
}(:
ricew
As a note, please change transactions to records, as I am sure there
will be some pendantic poster out there (though I know there are none
on this board) who is going to bring up how it is quite possible to
insert multiple records in one transaction.
Will
Mark Townsend
>>
>> Am I missing something?
>
> Yes, Gumby is a no-brainer.
>
With a small set of bells
Ian Michael Gumby
I just walk silently and carry a small knotted rope. ;-)
> Informix-list mailing list
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
Windows Live™: E-mail. Chat. Share. Get more ways to connect. Check it out.
Obnoxio The Clown
>
> silently
Silently? I wish.
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
RedGrittyBrick
Obnoxio The Clown wrote:
> Ian Michael Gumby wrote:
>>
>> silently
>
> Silently? I wish.
>
Alt-M, a
--
RGB