Thanks to all who send answers and ideas - Larry Ekins, Connor McDonnald,
Jeffery Stevenson.
JP
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego,
select MIN(a.mt1)+1 INTO XY FROM
(select mt1 FROM MT MINUS SELECT mt1-1 FROM MT) a;
is better, but still full scan ...
JP
On Mon 19. November 2001 12:40, you wrote:
Hello,
I've a table mt ( mt1 NUMBER(10) NOT NULL ) with unique index on the mt1
column.
There's a sequence of numbers
Maybe something like:
select /*+ INDEX(a mt_ix) */ *
from mt a
where mt1 0
and not exists
( select null
from mt
where mt1 = a.mt1+1)
and rownum = 1
where 'mt_ix' is the index on MT1. This should work
ok as long as the number is anticipated to be toward
the lower end of the range.
Are the numbers actually in sequential order in the mt1 column? If so, you
could probably do the following:
select m.row_num
from (select mt1, rownum row_num from mt) m
where m.mt1 m.row_num
and rownum = 1;
...but it will only work if the values are actually in order for mt1.
Jeffery
