Re: pl/sql : gaps in ordinal number

In article <7mcafp$qp$1_at_news3.Belgium.EU.net>, Heidi Luyten <hlu_at_technum.be> writes
>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 ?
>
>

How about something like:

select min(nr_id) + 1
from my_table mt1
where not exists (

        select null
        from my_table mt2
        where mt2.nr_id = mt1.nr_id + 1
        )

Should be quicker, using a couple of index scans?

Andy
--
Andy Hardy. PGP key available on request