reset a sequence in a trigger

#11

Werner Hofmann wrote:

Quote:

Stupid question maybe but why reset it? A sequence is a large number.
Are you try to save numbers? You can set a max value and then cycle
it. Resetting a sequence just seems like you're going about solving
the wrong problem.

Thanks,

Lewis

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.

Werner

Then why do't you include a updated_when column on the table? Then sort
according to the time it was updated rather than tricking around with pseudo-keys.

--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

#12

Werner Hofmann wrote:

Quote:

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!

--
Regards,
Frank van Bortel

#13

Frank van Bortel <frank.van.bortel (AT) gmail (DOT) com> wrote:

Quote:

Unless I'm mistaken (and my math isn't what it used to be): bullocks!

That's bollocks (or the Dublin variation bollix).

Paul...

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analyzing and attempting to help, please
do not top post.

#14

Paul wrote:

Quote:

Frank van Bortel <frank.van.bortel (AT) gmail (DOT) com> wrote:

Unless I'm mistaken (and my math isn't what it used to be): bullocks!

That's bollocks (or the Dublin variation bollix).

<g> - thankx. Would Someone rather have maths instead of math?!?
--
Regards,
Frank van Bortel

#15

Frank van Bortel wrote:

Quote:

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

#16

"Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> wrote

Quote:

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

#17

Mark C. Stock wrote:

Quote:

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

++ mcs

But only *after* you ran into it, eh?
<g>
--
Regards,
Frank van Bortel

#18

Frank van Bortel <frank.van.bortel (AT) gmail (DOT) com> wrote:

Quote:

Unless I'm mistaken (and my math isn't what it used to be): bullocks!

That's bollocks (or the Dublin variation bollix).

g> - thankx. Would Someone rather have maths instead of math?!?

To the best of my knowledge, bollocks is a term unknown to Merkans, so
variant spelling vis-à-vis western-Atlantikers does not apply.

Paul...

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analyzing and attempting to help, please
do not top post.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

reset a sequence in a trigger

comp.databases.oracle.misc comp.databases.oracle.misc

Re: reset a sequence in a trigger - 05-20-2005 , 12:47 AM

Re: reset a sequence in a trigger - 05-20-2005 , 10:18 AM

Re: reset a sequence in a trigger - 05-20-2005 , 11:32 AM

Re: reset a sequence in a trigger - 05-20-2005 , 02:36 PM

Re: reset a sequence in a trigger - 05-21-2005 , 03:07 AM

Re: reset a sequence in a trigger - 05-21-2005 , 05:23 AM

Re: reset a sequence in a trigger - 05-21-2005 , 05:56 AM

Re: reset a sequence in a trigger - 05-21-2005 , 06:00 AM