Re: Checking for Sequential Numbers

Try

select --+ all_rows

        a.id
from

        id_tbl a, id_tbl b
where a.id >= 2
and a.id + 1 = b.id (+)
and b.id is null
/

Martin

Buck Turgidson wrote:
>
> I have a table that should have sequential numbers, and I want to spot any
> gaps.
>
> I have written the following query which works using a small test (see
> below), but is unacceptably slow in checking the 28,000 rows in the actual
> table. Can anyone suggest a better way?
>
> select
> a.id
> from id_tbl a
> where a.id > 1
> and not exists (select b.id
> from id_tbl b
> where b.id - a.id = -1
> and b.id = (select max(c.id)
> from id_tbl c
> where c.id < a.id))
>
> create table id_tbl (id number (6) not null);
>
> insert into id_tbl values (1);
> insert into id_tbl values (2);
> insert into id_tbl values (3);
> insert into id_tbl values (4);
> insert into id_tbl values (5);
> insert into id_tbl values (6);
> insert into id_tbl values (7);
> -- << missing
> sequence
> insert into id_tbl values (9);
> insert into id_tbl values (10);
>
> create index id_tbl_idx on id_tbl(id);
Received on Wed Jan 05 2000 - 18:43:08 CST