Hi Tom

Excellent answer as usual - clear and precise.
Could You perhaps explain the sql shortly?

Thank you !
Would you also know of a way to do this without analytic functions?

PS: I've just registred for you session in Vienna and am happy seeing you in Person in September.

self joins
correlated subqueries (see below)
scalar subqueries

ops$tkyte%ORA9IR2> select *
  2    from (
  3  select x, (select min(x) from t t2 where t2.x > t.x) next_x
  4    from t
  5         )
  6   where x <> next_x-1
  7  /

         X     NEXT_X
---------- ----------
         2          5
         7         10


many ways, none as good as analytics though! 

Hey Tom,

I guess you have already answered that long back. I just remember the topic ..

Hi Tom,

is this question similar to this thread?

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3225867788098

i tried to use the same logic from there, but it is not giving the correct missing values: 3,4,8,9,11

Example:

SQL> select * from t order by x;

         X
----------
         1
         2
         5
         6
         7
        10
        12
        13

SQL> select x+1
  2         from t
  3         where NOT EXISTS ( select null
  4                            from t t2
  5                            where t2.x = t.x+1 )
  6  order by x
  7  /

       X+1
----------
         3
         8
        11
        14
Using the Analytic approach correctly gives the expected missing Gap-Ranges:
SQL> select x, next_x
  2        from (
  3             select  x, lead(x) over (order by x) next_x
  4        from t
  5             )
  6  where x <> next_x-1
  7  /

         X     NEXT_X
---------- ----------
         2          5
         7         10
        10         12

However, how can i expand the missing gap ranges from the above Analytic approach into a List of Numbers:
3,4,8,9,11
 

Following could be another approach (although it will not give you the same results as that of Tom's query..it will show the next missing no. only).


SQL> select a+1 from t t1
  2  where not exists(Select null from t t2 where t1.a+1 = t2.a);

       A+1
----------
         3
         8
        11 

How about:

SQL> SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 10 MINUS SELECT * FROM t;

    ROWNUM
----------
         3
         4
         8
         9

SQL> 

Several years back (1997) I solved a similar problem for a customer that needed a fast list (view) showing available phone numbers from a list of numbers where the used ones were removed. The list should present available numbers in ranges.
It was solved by the use of the pseudocolumn ROWNUM:

SELECT MIN(x) START_NO
,MAX(x) END_NO
FROM (SELECT X
,X+ROWNUM*(-1) RANGE_ID
FROM t)
GROUP BY RANGE_ID

Regards
G.Edland

I realise Analytics are much more efficient ( in cases where they make sense - much better than correlated sub-query here for example), but I have a hard time visualising how these work..

Do these functions actually read 2 rows for every row outputed? Do they store 2 lines in buffer so that when we need to output the next row, we dont have to do a read again?

Are there any docs which talk about how these analytics function work?

Have your Effective Oracle by Design and totally forgot about the other book that you wrote.. ( held up on buying it till your new version released and then just forgot.. (: )
Time to pony up and buy that and Cost-Based Oracle Fundamentals by Jonathan Lewis and get some reading done on the long flights. Now if only these were small paperback books that one can carry easily....

I just had to use this to analyze gaps in sequences in our test environment to avoid key clashes.