RE: sequence of numbers

JP,

Depending upon the data, are the gaps "early" in the sequence of numbers, "late" in the sequence of numbers, etc there could be different approaches. With that said, you can try the following:

Select y.rx, y.mt1
from (Select rownum rx, x.mt1
from (Select /*+ index (mt mt_pk) */ mt1 from mt ) x ) y where y.rx <> y.mt1
  and rownum = 1

I went with the index hint instead of an order by in the in-line view. It is going to process the MT1 values sequentially ascending. It will stop on the first occurrence. We are avoiding a SORT (the index access handles this) and a GROUP BY function for MIN (rownum = 1). This also assumes your "first" value is a 1. Change the name of the index in the hint to whatever you index is named.

And if this isn't good for you, there are other approaches as well. Some with sub-queries, I could have used the windowing analytical functions to compare current and prior, all kinds of ways to skin this cat. Anyway, try the above and see what happens.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jan Pruner
> Sent: Monday, November 19, 2001 6:25 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: sequence of numbers
>
>
> 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 1,2,3,4,6,7,8,10,11 ... in the mt1
> >
> > Now, I want to get the smallest number which is not in mt1
> > (excluding min(mt1)-x of course) into XY (in my example is it n. 5).
> >
> > When I use SELECT MIN(mt1)-1 INTO XY FROM MT WHERE MT1 NOT IN (select
> > mt1+1 from mt)
> > my perfromance goes pretty down :-))) (there's about 5 mil. of
> numbers).
> >
> > Some idea how to get it without cursor?
> >
> > Thanks (and sorry it's monday)
> > JP

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).