How to generate a dynamic sequence in Oracle

Question

I have a table A which represents a valid sequence of numbers, which looks something like this:

| id | start |   end | step |
|----|-------|-------|------|
|  1 |  4000 |  4999 |    4 |
|  2 |     3 | 20000 |    1 | 

A[1] thus represents the sequence [4000, 4004, 4008, ...4996]

and another B of "occupied" numbers that looks like this:

|  id | number | ... |
|-----|--------|-----|
|   1 |   4000 | ... |
|   2 |   4003 | ... |
| ... |    ... | ... |

I want to construct a query which using A and B, finds the first unoccupied number for a particular sequence.

I have been trying – and failing – to do, is to generate a list of valid numbers from a row in A and then left outer join table B on B.number = valid_number where B.id is null from which result I could then select min(...).

Answer 1

I'll ask the obvious and suggest why not use an actual sequence?

SQL> set timing on
SQL> CREATE SEQUENCE SEQ_TEST_A
START WITH 4000
INCREMENT BY 4
MINVALUE 4000
MAXVALUE 4999
NOCACHE 
NOCYCLE 
ORDER
Sequence created.
Elapsed: 00:00:01.09
SQL> CREATE SEQUENCE SEQ_TEST_B
START WITH 3
INCREMENT BY 1
MINVALUE 3
MAXVALUE 20000
NOCACHE 
NOCYCLE 
ORDER
Sequence created.
Elapsed: 00:00:00.07

SQL> -- get nexvals from A
SQL> select seq_test_a.nextval from dual

   NEXTVAL
----------
      4000
1 row selected.
Elapsed: 00:00:00.09
SQL> select seq_test_a.nextval from dual

   NEXTVAL
----------
      4004
1 row selected.
Elapsed: 00:00:00.08
SQL> select seq_test_a.nextval from dual

   NEXTVAL
----------
      4008
1 row selected.
Elapsed: 00:00:00.08
SQL> -- get nextvals from B
SQL> select seq_test_b.nextval from dual

   NEXTVAL
----------
         3
1 row selected.
Elapsed: 00:00:00.08
SQL> select seq_test_b.nextval from dual

   NEXTVAL
----------
         4
1 row selected.
Elapsed: 00:00:00.08
SQL> select seq_test_b.nextval from dual

   NEXTVAL
----------
         5
1 row selected.
Elapsed: 00:00:00.08

Answer 2

You can use LEAD to compute the difference between ordered rows in table B. Any row having a difference (to the next row) that exceeds the step value for that sequence is a gap.

Here's that concept, implemented (below). I threw in a sequence ID "3" that has no values in table B, to illustrate that it generates the proper first value.

with
   a (id, cstart, cend, step) as
     (select 1, 4000,  4032, 4 from dual union all
      select 2,    3, 20000, 1 from dual union all
      select 3,  100,   200, 3 from dual
     ),
   b (id, cnumber) as
     (select 1, 4000 from dual union all
      select 1, 4004 from dual union all
      select 1, 4012 from dual union all
      select 2, 4003 from dual
     ),
work1 as (
select a.id, 
       b.cnumber cnumber, 
       lead(b.cnumber,1) over ( partition by b.id order by b.cnumber ) - b.cnumber diff,
       a.step,
       a.cstart,
       a.cend
from   a left join b on b.id = a.id )
select w1.id, 
       CASE WHEN min(w1.cnumber) is null THEN w1.cstart 
            WHEN min(w1.cnumber)+w1.step < w1.cend THEN min(w1.cnumber)+w1.step 
            ELSE null END next_cnumber
from work1 w1
where ( diff is null or diff > w1.step )
group by w1.id, w1.step, w1.cstart, w1.cend
order by w1.id

+----+--------------+
| ID | NEXT_CNUMBER |
+----+--------------+
|  1 |         4008 |
|  2 |         4004 |
|  3 |          100 |
+----+--------------+

You can further improve the results by excluding rows in table B that are impossible for the sequence. E.g., exclude a row for ID #1 having a value of, say, 4007.

Answer 3

How about this?

I simplified your test case (END value isn't that high) in order to save space (otherwise, I'd have to use smaller font :)).

What does it do?

• CTEs A and B are your sample data
• FULL_ASEQ creates a sequence of numbers from table A
  • if you want what it returns, remove everything from line #17 and - instead of it - run select * from full_aseq
• the final query returns the first available sequence number, i.e. the one that hasn't been used yet (lines #19 - 23).

Here you go:

SQL> with
  2  a (id, cstart, cend, step) as
  3    (select 1, 4000,  4032, 4 from dual union all
  4     select 2,    3,    20, 1 from dual
  5    ),
  6  b (id, cnumber) as
  7    (select 1, 4000 from dual union all
  8     select 1, 4004 from dual union all
  9     select 2, 4003 from dual
 10    ),
 11  full_aseq as
 12    (select a.id, a.cstart + column_value * a.step seq_val
 13     from a cross join table(cast(multiset(select level from dual
 14                                           connect by level <= (a.cend - a.cstart) / a.step
 15                                          ) as sys.odcinumberlist))
 16    )
 17  select f.id, min(f.seq_val) min_seq_val
 18  from full_aseq f
 19  where not exists (select null
 20                    from b
 21                    where b.id = f.id
 22                      and b.cnumber = f.seq_val
 23                   )
 24  group by f.id;

        ID MIN_SEQ_VAL
---------- -----------
         1        4008
         2           4

SQL>