Re: Checking for Sequential Numbers

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 05 Jan 2000 17:07:47 -0500
Message-ID: <g3g77sgae9rbcdfl1sc9lh4iiqpa9vib55@4ax.com>

A copy of this was sent to "Buck Turgidson" <jcman*NOSPAM*@worldnet.att.net> (if that email address didn't require changing) On Wed, 5 Jan 2000 19:01:20 -0800, you 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?
>

try

tkyte_at_8i> select 'Gap starting at number ' || id || ' detected'   2 from id_tbl a
  3 where not exists ( select null from id_tbl b where b.id = a.id+1 )   4 /

'GAPSTARTINGATNUMBER'||ID||'DETECTED'

---

Gap starting at number 7 detected
Gap starting at number 10 detected

it'll full scan ID_TBL and then do an indexed read (28,000 of them) to see if the next number exists or not.

>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);
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jan 05 2000 - 16:07:47 CST