Oracle sequence 4

[newbieDev]

Hi Everyone,

I am trying to generate id numbers using sequence for an existing database.

The problem is that the data currently in the database does not follow any order and the numbers are pretty random. The highest ID in the table is 2345 - this does not mean that every number from 1 - 2345 has been used. If I use a sequence (with a trigger), will the use of all the unused numbers be lost? Could it create possible problems in the future? If I delete an ID, it wouldn't affect the next generated number? If I turn off the trigger and then turn it on later, will the next generated number always be the next number of the last number generated?


Please let me know if my question isn't clear enough and I will try to explain better.

Thank you!

 

[jimbopalmer]

A sequence always gives out ascending numbers, but they are not always consecutive. (caching speeds up the process, at the risk of losing the numbers in the cache if the instance crashes) It is very rare to run out.

I tried to remain child-like, all I acheived was childish.

 

[jimirvine]

A sequence always gives out ascending numbers

Unless you set the increment by parameter to a negative number

 

[Thargy]

newbie,

sequences do what jimbopalmer said, they typically generate monotonically increasing integers from their initial value (usually 1) in steps of their "increment by" value, also usually 1. These are the defaults and suit most folks fine.

If you have a table which is already populated with data, and whose primary key is driven by a trigger and a sequence, then the missing numbers that you mention don't really matter. Since oracle can cope with 38 digits, it'll be a few million years before you run out of numbers.

To solve the problem of the sequence value being below the maximum value of the pk in the table, just select nextval from the sequence until it's past your existing maximum.

Would you like me to post some samples of what I mean, or do you want a chance to teach yourself first?

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

...just select nextval from the sequence until it's past your existing maximum.

...or, more simply...

SQL> create sequence newbie_seq start with 2346;

Sequence created.

SQL> select newbie_seq.nextval from dual;

   NEXTVAL
----------
      2346

When you create a sequence, you can specify any starting (positive, zero, or negative) value, any positive or negative increment value, any positive or negative maximum or minimum value, how many values to cache, and whether or not to restart (cycle) at the minimum value when the sequence reaches its maximum value.

Let us know if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Dave,

as usual, one example is worth a thousand words.
Much clearer than my text explanation.

T

Grinding away at things Oracular

 

[newbieDev]

Thank you all for your help and explanations! I love this forum!