Re: How to Query Min PK Value NOT in Table

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Wed, 22 Jun 2005 23:09:59 +0000 (UTC)
Message-ID: <d9cr47$m6t$1@klatschtante.init7.net>

On 2005-06-22, simon <simon_at_simon.com> wrote:
> I have a table with primary key values:
>
> 1, 2, 6, 8, 10, 13, 15,...
>
> Suppose I need to insert a new row with the minimum PK not used (here it is
> 3).
>
> Qn 1) What query should I use to get this min value? Assuming a single user
> environment.

Something like this

create table first_missing_test (
  a number
);

insert into first_missing_test values ( 8);
insert into first_missing_test values ( 1);
insert into first_missing_test values (13);
insert into first_missing_test values (10);
insert into first_missing_test values ( 7);
insert into first_missing_test values ( 2);
insert into first_missing_test values (15);
insert into first_missing_test values ( 6);

select next_value from (
  select
    row_number() over (order by a) r, a + 1 next_value   from (
    select

      case when lead(a) over (order by a)  = a+1 then 0 else 1 end l,
      a
    from 
      first_missing_test

  )
  where l = 1
)
where r = 1;

> Qn 2) How could I handle the same query in multi-user environment where >1
> users may simultaneously get the minimum PK available is 3 and all try to
> insert 3? (recall that in Oracle reader never get blocked by writer, so
> simple locking mechanism doesnt prevent >1 users reading the value 3)

Imho, not possible with such an approach. Use a table with all possible values 1..n and a table that sits between those tables.

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Wed Jun 22 2005 - 18:09:59 CDT