Home » SQL & PL/SQL » SQL & PL/SQL » Sequence
Sequence [message #290459] Sat, 29 December 2007 00:58 Go to next message
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 #290460 is a reply to message #290459] Sat, 29 December 2007 01:04 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

Quote:

Can I initialize this at any point?

with what ? can you explain more.


regards,
Re: Sequence [message #290461 is a reply to message #290459] Sat, 29 December 2007 01:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #290467 is a reply to message #290463] Sat, 29 December 2007 01:27 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

so create that column large enough to accomodate generated number.

regards,
Re: Sequence [message #290469 is a reply to message #290467] Sat, 29 December 2007 01:29 Go to previous messageGo to next message
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 #290471 is a reply to message #290463] Sat, 29 December 2007 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Sequences can have a MAXVALUE that you can specify on ALTER or CREATE SEQUENCE statements.
Set it so it can't exceed your column value range.

Regards
Michel
Re: Sequence [message #290479 is a reply to message #290459] Sat, 29 December 2007 01:57 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #290562 is a reply to message #290459] Sat, 29 December 2007 23:13 Go to previous messageGo to next message
abshrestha
Messages: 5
Registered: April 2005
Location: Kathmandu, Nepal
Junior Member

You may just need to drop and create same sequence dynamically whenever necessary. This is useful if column is not part of Primary Key. Currently I'm using same sequence, drop and created every day, for inserting data into temporary tables, with starting sequence no 1 each day.
You may also include option CYCLE while creating the Sequence.
Hope this will some how solve your problem.
Thanks,
Anup Babu Shrestha
OCA
Kathmandu, Nepal

Re: Sequence [message #290748 is a reply to message #290562] Mon, 31 December 2007 07:12 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Thank you Anup.

But that column in my table is primary key.

Sad
Re: Sequence [message #290749 is a reply to message #290748] Mon, 31 December 2007 07:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #290752 is a reply to message #290751] Mon, 31 December 2007 07:22 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Thank you Raj. Smile
Re: Sequence [message #290758 is a reply to message #290752] Mon, 31 December 2007 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
More precisely if you get 1000 numbers per second each minute of each hour of each day it will take: power(2,38)/(365.2425*60*60*1000)=209 years to exhaust all sequence numbers.
I think Oracle will be out of date before this. Smile

Regards
Michel
Re: Sequence [message #291050 is a reply to message #290459] Wed, 02 January 2008 18:40 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Get the running time
Next Topic: Order by clause doesn't work as wanted
Goto Forum:
  


Current Time: Mon Dec 02 08:03:01 CST 2024