| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: pl/sql : gaps in ordinal number
Heidi Luyten wrote:
>
> I have a tabel with a varchar2 , nr_id, which represents an ordinal number
> that should be unique.
>
> At runtime users can insert new rows to this table, and I have to propose
> them a new ordinal number, which they can change. All values must be numeric
> ( the varchar2-type is just one of the mysterious things I came across in
> this application... :-) )
>
> Right now the number I'm proposing is simply a select max +1.
> The problem is that when a user changes the proposed value into something
> very large all following records with continue with these large numbers and
> nr_id will be running out of space...
>
> How to fill the gaps when proposing a new ordinal number ?
>
> I could write some loop like this :
> while dummy > 0 loop
> select count(*) into dummy from table where nr_id = to_char(i)
> i:=i+1
> end loop
> BUT there are already 700000 records in table ...
> Any quicker solution ?
Why do the numbers need to be contiguous ? If you can avoid that condition, then a sequence will give you what you want...
Getting a contigous set of numbers is a lot harder than you think - since if five users all go for a new number at roughly the same time, any kind of sql may lead to overlaps...Then you are into dbms_lock territory to hash values etc etc etc
Ugh !
Cheers
--
"Some days you're the pigeon, and some days you're the statue." Received on Mon Jul 12 1999 - 08:38:34 CDT
 |
 |