テーブルの数値列が連続して空いている箇所を探すSQL

ユーザテーブルから使われていないユーザIDの範囲を調べたいようなときに使えるかも。
テーブルを作って、データを入れる。

SQL> create table test(id number(4));
SQL> begin
for i in 1..500 loop
insert into test (id) values(i);
end loop;
commit;
end;
/
SQL> begin
for i in 900..1000 loop
insert into test (id) values(i);
end loop;
commit;
end;
/
SQL> select min(id), max(id), count(id) from test;

   MIN(ID)    MAX(ID)  COUNT(ID)
---------- ---------- ----------
         0       1000        602

test表のid列が連続して空いている箇所を探す。

SQL> select min(c.num),max(c.num),count(c.id) from 
    (select a.num num, b.id id from (select level num from dual connect by level <= 1000) a
    	left outer join test b 
    		on a.num=b.id order by a.num) c
    group by trunc(c.num/100);

MIN(C.NUM) MAX(C.NUM) COUNT(C.ID)
---------- ---------- -----------
         1         99          99
       100        199         100
       200        299         100
       300        399         100
       400        499         100
       500        599           1
       600        699           0
       700        799           0
       800        899           0
       900        999         100
      1000       1000           1

11 rows selected.

600番台〜800番台は使われていないことがわかる。

環境は Oracle9iR2