Sequence [message #290459] |
Sat, 29 December 2007 00:58 |
vanathi
Messages: 74 Registered: December 2007 Location: India
|
Member |
|
|
Hi,
CREATE SEQUENCE db_seq_PART
START WITH 1
INCREMENT BY 1;
I am using this sequence.
This is incrementing everytime.
Can I initialize this at any point?
|
|
|
|
Re: Sequence [message #290461 is a reply to message #290459] |
Sat, 29 December 2007 01:07 |
vanathi
Messages: 74 Registered: December 2007 Location: India
|
Member |
|
|
It is keep on increasing while adding the data.
Later on, This may give problem.
Please ignore the initialization in my previous post.
Please advice.
|
|
|
Re: Sequence [message #290462 is a reply to message #290461] |
Sat, 29 December 2007 01:13 |
dhananjay
Messages: 635 Registered: March 2002 Location: Mumbai
|
Senior Member |
|
|
this is what sequences are meant for.they generate unique numbers.still not clear of your requirment.what exactly are you trying to achive ?
regards,
|
|
|
Re: Sequence [message #290463 is a reply to message #290462] |
Sat, 29 December 2007 01:18 |
vanathi
Messages: 74 Registered: December 2007 Location: India
|
Member |
|
|
Yes...
But It will keep on increasing...
It may give probelm. Because I am using this to insert data into a table. The output of this sequence will go to a column in that table. It may exceed the size of that column in future.
Is there any solution for this problem?
|
|
|
|
Re: Sequence [message #290469 is a reply to message #290467] |
Sat, 29 December 2007 01:29 |
vanathi
Messages: 74 Registered: December 2007 Location: India
|
Member |
|
|
But, It is going to be used for years.
Thats why I am worrying about it.
Ok. Fine.
Thanks dhananjay.
|
|
|
|
Re: Sequence [message #290479 is a reply to message #290459] |
Sat, 29 December 2007 01:57 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Just puzzled you do not worry about the number of rows stored in that table.
I see much bigger problem (performance, data storage) with billions rows table than the larger value of one column.
Of course it depends on the significant operations taken on that table.
|
|
|
Re: Sequence [message #290520 is a reply to message #290469] |
Sat, 29 December 2007 06:36 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
vanathi wrote on Sat, 29 December 2007 08:29 | But, It is going to be used for years.
Thats why I am worrying about it.
Ok. Fine.
Thanks dhananjay.
|
You are aware of the fact that a number(38) aka 10E38 is an ENORMOUSLY high number? I want to bet you that your application will NEVER reach it. Ever.
|
|
|
|
|
Re: Sequence [message #290749 is a reply to message #290748] |
Mon, 31 December 2007 07:14 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Quote: | But that column in my table is primary key.
|
Quote: | I am using this sequence. This is incrementing everytime. Can I initialize this at any point?
| Don't you think these two statements are contradicting ?
Regards
Raj
P.S : Removed extra blank lines
[Updated on: Mon, 31 December 2007 07:15] Report message to a moderator
|
|
|
Re: Sequence [message #290750 is a reply to message #290461] |
Mon, 31 December 2007 07:15 |
vanathi
Messages: 74 Registered: December 2007 Location: India
|
Member |
|
|
vanathi wrote on Sat, 29 December 2007 12:37 |
Please ignore the initialization in my previous post.
|
|
|
|
Re: Sequence [message #290751 is a reply to message #290750] |
Mon, 31 December 2007 07:20 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Then believe me, as Frank already said It is almost impossible to raech that number.
|
|
|
|
|
Re: Sequence [message #291050 is a reply to message #290459] |
Wed, 02 January 2008 18:40 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Yes, but Michel, I thought 38 for numbers was the number of bytes, not bits, thus answer is more like 3 quadrillion trillion years, not 200 years. My math works like this:
1000 sequences per seconds = 32 billion sequence numbers / year
set numwidth 50
select 1000*60*60*24*365 lots_of_sequences_per_year from dual;
I can get Oracle to create this really big integer.
select to_number(lpad(9,38,9)) a_really_big_integer from dual;
so I figure divide this big integer by the number of sequences per year and get number of years.
select trunc(to_number(lpad(9,38,9))/31536000000) how_long_is_forever from dual;
thus if generating 1000 sequence numbers per second continuously, it would take 3 quadrillion trillion years to create a number too big for oracle to store on my:
SQL> select * from v$version;
BANNER
---------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
SQL> set numwidth 50
SQL> select 1000*60*60*24*365 lots_of_sequences_per_year from dual;
LOTS_OF_SEQUENCES_PER_YEAR
--------------------------------------------------
31536000000
1 row selected.
SQL>
SQL> select to_number(lpad(9,38,9)) a_really_big_integer from dual;
A_REALLY_BIG_INTEGER
--------------------------------------------------
99999999999999999999999999999999999999
1 row selected.
SQL>
SQL> select trunc(to_number(lpad(9,38,9))/31536000000) how_long_is_forever from dual;
HOW_LONG_IS_FOREVER
--------------------------------------------------
3170979198376458650431253170
1 row selected.
SQL>
Is my math correct? Kevin
|
|
|
Re: Sequence [message #291109 is a reply to message #291050] |
Thu, 03 January 2008 00:57 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | Yes, but Michel, I thought 38 for numbers was the number of bytes, not bits,
|
It is the number of digits, I'm really silly to think it as bits.
So my formula is correct if you replace 2 by 10 and 38 by 39:
SQL> select power(10,39)/(365.2425*24*60*60*1000) from dual;
POWER(10,39)/(365.2425*24*60*60*1000)
-------------------------------------
31688738506811430964562103463
1 row selected.
Regards
Michel
|
|
|
Re: Sequence [message #291236 is a reply to message #290459] |
Thu, 03 January 2008 08:48 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Yes, sorry, the number of digits. Cool!, I always love telling people this, they gasp.
Thanks, Kevin
|
|
|