Checking for Sequential Numbers

From: Buck Turgidson <jcman*NOSPAM*_at_worldnet.att.net>
Date: Wed, 5 Jan 2000 19:01:20 -0800
Message-ID: <850m1s$gis$1@bgtnsc03.worldnet.att.net>

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 - 21:01:20 CST