Re: reset a sequence in a trigger

"Frank van Bortel" <frank.van.bortel_at_gmail.com> wrote in message news:d6mpv0$r1e$1_at_news2.zwoll1.ov.home.nl...
> Frank van Bortel wrote:
>> Werner Hofmann wrote:
>>
>>>In this table I have a lot of transactions, and it can be, that the ID
>>>which
>>>is the primary key can overrun after some time (some years). I can not
>>>use
>>>another primary key, because this would be with a VARCHAR2 column. And so
>>>I
>>>wantet to be save, that the oldest entry have the smallest ID for
>>>sorting.
>>
>>
>> Let's do the math:
>> Let's take a number (actually, number(38,*)) as ID column.
>>
>> Largest number to be recorded would be 10^39 - 1 (38 nines)
>> Suppose you machine does "a lot of transactions": 1 million per day.
>> 365 days a year, year-in, year-out.
>> That's 365,000,000 transactions a year.
>> that will take you 10^38/3.65*10^8, ehhhmmm about 3*10^30 years
>> to fill up your sequence.
>>
>> By that time, the sun has grown so large, the earth is no more.
>>
>> If you don't like the example, take a gazillion-way cluster, and
>> chew away 1 billion transactions (1000 times as much)
>> per second (86400 times as much). No way you are going
>> to fill up your sequence (well, about 86 million times as fast)
>> in your liftime.
>>
>> Unless I'm mistaken (and my math isn't what it used to be): bullocks!
>>
> Hm - nobody noticed the error in the numbers?
> The Number datatype can store numbers with magnitudes between
> 1*10^-130 and 9.99...9*10^125 (38 nines, followed by 88 zeros),
> with *38 digits of precision*.
>
> So, actually, I was about a factor 10^88 off - minor detail, as
> the earth still ceased to exist :)
>
> --
> Regards,
> Frank van Bortel

we would have caught it in production and issues a help desk ticket on it.

++ mcs Received on Sat May 21 2005 - 05:23:45 CDT