Skip to Main Content
  • Questions

  • Trying to Split Number ranges (having real tough time !)

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: February 15, 2003 - 12:51 pm UTC

Last updated: February 25, 2003 - 9:52 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Tom,

Sorry. Yes, you are correct I can execute the SQL that you sent and this is the output.

SQL> select row_number() over (order by dummy) from dual;

ROW_NUMBER()OVER(ORDERBYDUMMY)
------------------------------
1


My aplologies for troubling you further, but how can I use this in my case? I guess I sound dumb, but I have never used much of Analytic functions. I have only seen you do it and admired its power. Can you guide me?

Thanks



Hi Tom,

Thanks ! No the gaps are not typo. It is to be expected. The serial numbers can be asked in any order. The check that I have to perform is, has it ever been used before. I can give out only those serial numbers that have never been used. In the first example, the user was checking if he can use serial numbers 30-40, and based on the data in the table, the only serials that I could assign was 30 - 39 (40 being already used.)

Please let me know if I have provided enough information.

THANK YOU :-)

Hi Tom,
I am having hard time trying to split number ranges. Following 3 examples would help explain better.
If we can do this in SQL it would be great, if not a procedure or function would also be fine. I have tried both,
but cannot seem to handle all the cases as described below. Could you please help me. I cannot use Analytic functions
as I have Oracle 9.2 Standard Edition.


Table with 2 columns serial_start and serial_end has the following data:

SERIAL_START SERIAL_END
------------- ----------
40 140
60 145
100 110
150 250

User Input: Serial_Start = 30, Serial_End = 40

Expected Output: 30-39

When a user inputs a serial_begin=30 and serial_end=40, I have to look into this table and say, OK,
the only serials that can be assigned are 30 - 39. This is because, 40 is already used in the table.



Another example: Same table has the following data

SERIAL_START SERIAL_END
------------- ----------
200 230


User Input: Serial_Start = 150, Serial_End = 250

Expected Output: 150 - 199
231 - 250

When a user inputs a serial_begin=150 and serial_end=250, I have to look into this table and say, OK,
the only serials that can be assigned are 150 - 199 and 231 - 250. This is because, 200 - 230 is already used in the table.


Another example: Same table has the following data

SERIAL_START SERIAL_END
------------- ----------
50 98
100 150
160 190
200 230


User Input: Serial_Start = 90, Serial_End = 120

Expected Output: 99 - 99


Thanks

and Tom said...

See the "reviews" below -- we worked through it there.

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent

A reader, February 15, 2003 - 3:37 pm UTC

Tom,

It makes me feel better to know that there is no easy way for this. Since yesterday, I had serious doubts on whether I was really missing something very obvious.

On the other hand, I was excited to be able to ask you this question and was expecting a magical solution (as you always do for others). I have tried procedural approach, but don't seem to succeed even with these 3 cases. I am sure there are other combinations and permutations out there that I am not even aware of.

I have copied my procedure to show what I have been up to.
(This is just a temporary version)
You don't have to write code for me, but if there is some algorithm that I could always use or if you have any advice on how to approach this procedurally, it would help me a lot.

-- Drop serial tables
DROP TABLE serial_ranges;
DROP TABLE temp_serial_ranges;

-- Store existing Serial Ranges
CREATE TABLE serial_ranges(
serial_begin NUMBER,
serial_end NUMBER);

-- Insert new serial ranges based on user input. This would be truly a global temp table.
CREATE TABLE temp_serial_ranges(
serial_begin NUMBER,
serial_end NUMBER);


CREATE OR REPLACE PROCEDURE Insert_Exclusions(
serial_begin_exclude_ NUMBER,
serial_end_exclude_ NUMBER)

IS

CURSOR GetAll IS
SELECT *
FROM serial_ranges;
BEGIN

FOR rec_ IN GetAll LOOP
IF serial_begin_exclude_ NOT BETWEEN rec_.serial_begin AND rec_.serial_end AND
serial_end_exclude_ BETWEEN rec_.serial_begin AND rec_.serial_end THEN

INSERT INTO temp_serial_ranges VALUES (serial_begin_exclude_, rec_.serial_begin - 1);

ELSIF serial_end_exclude_ NOT BETWEEN rec_.serial_begin AND rec_.serial_end AND
serial_begin_exclude_ BETWEEN rec_.serial_begin AND rec_.serial_end THEN

INSERT INTO temp_serial_ranges VALUES (rec_.serial_end + 1, serial_end_exclude_);

ELSIF serial_begin_exclude_ NOT BETWEEN rec_.serial_begin AND rec_.serial_end AND
serial_end_exclude_ NOT BETWEEN rec_.serial_begin AND rec_.serial_end AND
rec_.serial_begin BETWEEN serial_begin_exclude_ AND serial_end_exclude_ AND
rec_.serial_end BETWEEN serial_begin_exclude_ AND serial_end_exclude_ THEN

INSERT INTO temp_serial_ranges VALUES (serial_begin_exclude_, rec_.serial_begin - 1);
INSERT INTO temp_serial_ranges VALUES (rec_.serial_end + 1, serial_end_exclude_);

END IF;
END LOOP;
END Insert_Exclusions;
/





Tom Kyte
February 15, 2003 - 4:16 pm UTC

tell me -- is there a reasonable upper bound on serial start and serial end -- eg: 1-10000 or something?

Or is the range infinite -- I mean the values supplied AND the values in the table?

Thanks for your patience

A reader, February 15, 2003 - 4:49 pm UTC

Tom,

We control the UPPER BOUND of the serial range(i.e. SERIAL END).

Assumption here is,

SERIAL START will always be > 0 (no negatives allowed)
SERIAL END can have a max value of say 999999. (This is setup by the user before they use the application)

So, in essence, my MAX SERIAL START can be 999999 and
my MAX SERIAL END can be 999999. We cannot exceed 999999, for either SERIAL START or SERIAL END.

Thanks

Tom Kyte
February 15, 2003 - 5:43 pm UTC 

Ok, so you make me think about it and -- well, there is a SQL solution after all (isn't there always....)

ops$tkyte@ORA920> create table t ( ss int, se int );
Table created.

ops$tkyte@ORA920> variable serial_start number;
ops$tkyte@ORA920> variable serial_end   number;
ops$tkyte@ORA920> exec :serial_start := 30; :serial_end := 40;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select * from t;

        SS         SE
---------- ----------
        40        140
        60        145
       100        110
       150        250

ops$tkyte@ORA920> select max( decode(mod(rn,2), 1, r, null ) ),
  2         max( decode(mod(rn,2), 0, r, null ) )
  3    from (
  4  select r, last_r, next_r, row_number() over ( order by r ) rn
  5    from (
  6  select r,
  7         nvl(lag(r) over ( order by r )+1, -1) last_r,
  8         nvl(lead(r) over ( order by r )-1, -1 ) next_r
  9    from ( select rownum + :serial_start-1 r
 10             from all_objects
 11                    where rownum <= :serial_end-:serial_start+1 )
 12   where 0 = (select count(*)
 13                from t where ss <= r
 14                             and se >= r
 15                             AND rownum = 1)
 16         )
 17   where r <> last_r or r <> next_r
 18         )
 19   group by ceil(rn/2)
 20  /

MAX(DECODE(MOD(RN,2),1,R,NULL)) MAX(DECODE(MOD(RN,2),0,R,NULL))
------------------------------- -------------------------------
                             30                              39




ops$tkyte@ORA920> exec :serial_start := 150; :serial_end := 250;
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select * from t;

        SS         SE
---------- ----------
       200        230

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select max( decode(mod(rn,2), 1, r, null ) ),
  2         max( decode(mod(rn,2), 0, r, null ) )
  3    from (
  4  select r, last_r, next_r, row_number() over ( order by r ) rn
  5    from (
  6  select r,
  7         nvl(lag(r) over ( order by r )+1, -1) last_r,
  8         nvl(lead(r) over ( order by r )-1, -1 ) next_r
  9    from ( select rownum + :serial_start-1 r
 10             from all_objects
 11                    where rownum <= :serial_end-:serial_start+1 )
 12   where 0 = (select count(*)
 13                from t where ss <= r
 14                             and se >= r
 15                             AND rownum = 1)
 16         )
 17   where r <> last_r or r <> next_r
 18         )
 19   group by ceil(rn/2)
 20  /

MAX(DECODE(MOD(RN,2),1,R,NULL)) MAX(DECODE(MOD(RN,2),0,R,NULL))
------------------------------- -------------------------------
                            150                             199
                            231                             250



ops$tkyte@ORA920> exec :serial_start := 90; :serial_end := 120;
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select * from t;

        SS         SE
---------- ----------
        50         98
       100        150
       160        190
       200        230

ops$tkyte@ORA920> select max( decode(mod(rn,2), 1, r, null ) ),
  2         max( decode(mod(rn,2), 0, r, null ) )
  3    from (
  4  select r, last_r, next_r, row_number() over ( order by r ) rn
  5    from (
  6  select r,
  7         nvl(lag(r) over ( order by r )+1, -1) last_r,
  8         nvl(lead(r) over ( order by r )-1, -1 ) next_r
  9    from ( select rownum + :serial_start-1 r
 10             from all_objects
 11                    where rownum <= :serial_end-:serial_start+1 )
 12   where 0 = (select count(*)
 13                from t where ss <= r
 14                             and se >= r
 15                             AND rownum = 1)
 16         )
 17   where r <> last_r or r <> next_r
 18         )
 19   group by ceil(rn/2)
 20  /

MAX(DECODE(MOD(RN,2),1,R,NULL)) MAX(DECODE(MOD(RN,2),0,R,NULL))
------------------------------- -------------------------------
                             99



Now that works as long as the number of rows in all objects exceeds the difference between start and stop.  If not, you can always go one more step:

ops$tkyte@ORA920> create or replace type numArray as table of number
  2  /
Type created.

ops$tkyte@ORA920> create or replace function virtual_table( p_numrows in number ) return numArray
  2  pipelined
  3  as
  4  begin
  5          for i in 1 .. p_numrows loop
  6                  pipe row(i);
  7          end loop;
  8          return;
  9  end;
 10  /

Function created.

ops$tkyte@ORA920> variable serial_start number;
ops$tkyte@ORA920> variable serial_end   number;
ops$tkyte@ORA920> exec :serial_start := 30; :serial_end := 40;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select * from t;

        SS         SE
---------- ----------
        40        140
        60        145
       100        110
       150        250

ops$tkyte@ORA920> select max( decode(mod(rn,2), 1, r, null ) ),
  2         max( decode(mod(rn,2), 0, r, null ) )
  3    from (
  4  select r, last_r, next_r, row_number() over ( order by r ) rn
  5    from (
  6  select r,
  7         nvl(lag(r) over ( order by r )+1, -1) last_r,
  8         nvl(lead(r) over ( order by r )-1, -1 ) next_r
  9    from ( select rownum + :serial_start-1 r
 10             from TABLE(virtual_table(:serial_end-:serial_start+1))
 11             )
 12   where 0 = (select count(*)
 13                from t where ss <= r
 14                             and se >= r
 15                             AND rownum = 1)
 16         )
 17   where r <> last_r or r <> next_r
 18         )
 19   group by ceil(rn/2)
 20  /

MAX(DECODE(MOD(RN,2),1,R,NULL)) MAX(DECODE(MOD(RN,2),0,R,NULL))
------------------------------- -------------------------------
                             30                              39

Now for the "how and why" it works.


Read the query from the inside out.  Take each query and run it in turn (removing aggregates).  Like this:


ops$tkyte@ORA920> select rownum + :serial_start-1 r
  2    from TABLE(virtual_table(:serial_end-:serial_start+1))
  3  /

         R
----------
        30
        31
        32
        33
        34
        35
        36
        37
        38
        39
        40

11 rows selected.

<b>there is every number in our "series"...</b>

ops$tkyte@ORA920> select r,
  2         nvl(lag(r) over ( order by r )+1, -1) last_r,
  3         nvl(lead(r) over ( order by r )-1, -1 ) next_r
  4    from ( select rownum + :serial_start-1 r
  5             from TABLE(virtual_table(:serial_end-:serial_start+1))
  6             )
  7   where 0 = (select count(*)
  8                from t where ss <= r
  9                             and se >= r
 10                             AND rownum = 1)
 11  /

         R     LAST_R     NEXT_R
---------- ---------- ----------
        30         -1         30
        31         31         31
        32         32         32
        33         33         33
        34         34         34
        35         35         35
        36         36         36
        37         37         37
        38         38         38
        39         39         -1

10 rows selected.

<b>now for each number in our series -- only keep the ones that are NOT already in the table (thats what 0 = (select count(*) ...) does.

For each one not in the table already -- look backwards and forwards in the set and grab the prior/next R.  If null (end points in the set), use -1 (you said they must be > 0 so that is "safe")
</b>

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select r, last_r, next_r, row_number() over ( order by r ) rn
  2    from (
  3  select r,
  4         nvl(lag(r) over ( order by r )+1, -1) last_r,
  5         nvl(lead(r) over ( order by r )-1, -1 ) next_r
  6    from ( select rownum + :serial_start-1 r
  7             from TABLE(virtual_table(:serial_end-:serial_start+1))
  8             )
  9   where 0 = (select count(*)
 10                from t where ss <= r
 11                             and se >= r
 12                             AND rownum = 1)
 13         )
 14   where r <> last_r or r <> next_r
 15  /

         R     LAST_R     NEXT_R         RN
---------- ---------- ---------- ----------
        30         -1         30          1
        39         39         -1          2

<b>Next, we only keep the ones where the prior or next R is NOT minus or plus 1...  Also, we assign row_number() to each one in turn -- 1,2,3,4,5,6.... we'll use this to pivot below.  Each ODD row will be a start point, each EVEN row an end point:</b>


ops$tkyte@ORA920> select ( decode(mod(rn,2), 1, r, null ) ),
  2         ( decode(mod(rn,2), 0, r, null ) ),
  3             ceil(rn/2)
  4    from (
  5  select r, last_r, next_r, row_number() over ( order by r ) rn
  6    from (
  7  select r,
  8         nvl(lag(r) over ( order by r )+1, -1) last_r,
  9         nvl(lead(r) over ( order by r )-1, -1 ) next_r
 10    from ( select rownum + :serial_start-1 r
 11             from TABLE(virtual_table(:serial_end-:serial_start+1))
 12             )
 13   where 0 = (select count(*)
 14                from t where ss <= r
 15                             and se >= r
 16                             AND rownum = 1)
 17         )
 18   where r <> last_r or r <> next_r
 19         )
 20  /

(DECODE(MOD(RN,2),1,R,NULL)) (DECODE(MOD(RN,2),0,R,NULL)) CEIL(RN/2)
---------------------------- ---------------------------- ----------
                          30                                       1
                                                       39          1

<b>see how the decode makes a "sparse matrix", if we had more in the series -- like 



MAX(DECODE(MOD(RN,2),1,R,NULL)) MAX(DECODE(MOD(RN,2),0,R,NULL))
------------------------------- -------------------------------
                            150                             199
                            231                             250

did, this would look like:


150                       1
            199           1
231                       2
            250           2

and the max "squishes out" the nulls for us nicely</b>


QED ;)

You are GOD :-)

A reader, February 15, 2003 - 6:09 pm UTC

Tom,

Words are not sufficient to describe what you have done and I don't want to embarrass you by saying anything else. I tested the 3 cases (you had that too !) and it works like a charm. Now, the next difficult part is to take this master query of yours and understand. I went through your explanation (thank you for that, it was a nice bonus) and to be honest don't seem to understand much. I will keep going through it and hopefully it will make sense.

What else can I say Tom, except a sincere THANK YOU. By the way you mentioned about QED. What does it stand for?

Thanks Tom. You are THE MAN !

Great Query But...........

Rasin, February 16, 2003 - 6:10 am UTC

dread@ORA92> exec :serial_start := 99; :serial_end := 200

PL/SQL procedure successfully completed.

dread@ORA92> select * from t;

SS SE
---------- ----------
50 98
100 150
160 190
200 230

dread@ORA92> select max( decode(mod(rn,2), 1, r, null ) ),
2 max( decode(mod(rn,2), 0, r, null ) )
3 from (
4 select r, last_r, next_r, row_number() over ( order by r ) rn
5 from (
6 select r,
7 nvl(lag(r) over ( order by r )+1, -1) last_r,
8 nvl(lead(r) over ( order by r )-1, -1 ) next_r
9 from ( select rownum + :serial_start-1 r
10 from TABLE(virtual_table(:serial_end-:serial_start+1))
11 )
12 where 0 = (select count(*)
13 from t where ss <= r
14 and se >= r
15 AND rownum = 1)
16 )
17 where r <> last_r or r <> next_r
18 )
19 group by ceil(rn/2)
20 /

MAX(DECODE(MOD(RN,2),1,R,NULL)) MAX(DECODE(MOD(RN,2),0,R,NULL))
------------------------------- -------------------------------
99 151
159 191
199


The expected output from above should be
start end
99
151 159
191 199

The numbers are correct but the order is changed.


Tom Kyte
February 16, 2003 - 11:20 am UTC 

Ok, so i'll set up an infinite loop here.... related question:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:7867939004010 

had a similar problem and I mentioned there we could use the same cartesian product on this question to fix the 99 NULL problem above.  Looks like I need it for the more general case, here is the fix:


ops$tkyte@ORA920>    select max( decode(mod(rn,2), 1, r, null ) ),
  2            max( decode(mod(rn,2), 0, r, null ) )
  3       from (
  4     select r, last_r, next_r, row_number() over ( order by r ) rn
  5       from (
  6     select r,
  7            nvl(lag(r) over ( order by r )+1, -1) last_r,
  8            nvl(lead(r) over ( order by r )-1, -1 ) next_r
  9       from ( select rownum + :serial_start-1 r
 10                from TABLE(virtual_table(:serial_end-:serial_start+1))
 11                )
 12      where 0 = (select count(*)
 13                   from t where ss <= r
 14                                and se >= r
 15                                AND rownum = 1)
 16            )<b>, (select 1 xx from dual union all select 2 xx from dual )
 17      where ( r <> last_r and xx = 1 ) or ( r <> next_r and xx = 2 )</b>
 18            )
 19      group by ceil(rn/2)
 20  /

MAX(DECODE(MOD(RN,2),1,R,NULL)) MAX(DECODE(MOD(RN,2),0,R,NULL))
------------------------------- -------------------------------
                             99                              99
                            151                             159
                            191                             199

The code in bold is the "fix"

Thank You :-)

A reader, February 16, 2003 - 3:40 pm UTC


Great query after fix is now Greatest

Rasin, February 17, 2003 - 2:00 am UTC


Follow up and additional questions

A reader, February 20, 2003 - 2:45 am UTC

Hi Tom,

I am running into 2 issues for this query. Maybe you can help me again.

1) When my table T has 1 record
SERIAL_START SERIAL_END
------------- ----------
1 200

User Input: Serial_Start = 1, Serial_End = 999999999

It takes a longgggg time to execute this query (more than 5 minutes). I understand why it does this. Can this be in anyway improved. I am really happy with the query but I will use this a few times in my application and the response time is not very good.

2)I want to use this Query in a Package, but get compilation errors when I use the VIRTUAL_TABLE. Is there any way I can get around this? Any pointers to existing example would be really helpful.

Thanks for all your help



Tom Kyte
February 20, 2003 - 7:34 am UTC 

1) well, you just changed the design goals, remember when you said:

....
SERIAL START will always be > 0 (no negatives allowed)
SERIAL END can have a max value of say 999999. (This is setup by the user before 
they use the application)
.......

You'll have to start investigating procedural implementations -- whereby you can optimize the processing by using some nice algorithms (that you come up with ;)

2) what errors specifically?  If you do it the way I showed -- create the object type, create the function, code the sql, it should work?


ops$tkyte@ORA920.US.ORACLE.COM> begin
  2      for x in ( select * from TABLE( virtual_table(10) ) )
  3      loop
  4          dbms_output.put_line( 'fetching.... ' || x.column_value );
  5      end loop;
  6  end;
  7  /

works dandy.  You are not trying to create the object type IN the plqsl package are you?  It MUST be done in SQL.

Thanks

A reader, February 23, 2003 - 7:04 pm UTC

Hi Tom,
I got your response but could not reply back soon. Thanks for your time. With regards to your last reply:

1) Yes, I did say that the upper bound would be 9999. My fault. I should have thought more about it before replying to that one. Next time, you ask a question, I would probably think twice as this might make a real difference to the solution you would be proposing.. But, procedural option is the way to go and it is working OK. No problems with performance. The good thing about your master query is it has made me realize how powerful Analytic functions can be. So, THANK YOU.

2) I should have been more specific. I was able to create the object type, create the function, code the sql and was able to run in SQL-Plus. I was trying to use the SQL Code in PL/SQL package. I had already created the Object Type in SQL-Plus. I guess, I cannot call use it in PL/SQL packages?

Tom, once again, thanks for all your time. It was a pleasure.

Tom Kyte
February 23, 2003 - 7:13 pm UTC

2) yes you can -- you need to give us an example. I use them in plsql all of the time (packages or not).




A reader, February 25, 2003 - 9:52 pm UTC

Tom,

I have not tried this again. I did not try hard enough to see what the problem was. Since you say you have used it, I am sure the problem is on my side. I will re-test it carefully again.

Thanks

simplified query

RVH, January 18, 2007 - 1:29 am UTC

The following query simplifies the solution:

SQL> select * from t;

SS SE
---------- ----------
50 98
100 150
160 190
200 230

SQL> var v_from number
SQL> var v_to number
SQL>
SQL> exec :v_from := 10;

PL/SQL procedure successfully completed.

SQL> exec :v_to := 1000;

PL/SQL procedure successfully completed.

SQL> select greatest(:v_from,ss_gap) as "from", least(:v_to,se_gap) as "to"
2 from (select (lag(se, 1, :v_from-1) over (order by se)) + 1 ss_gap, ss - 1 se_gap from t
3 union
4 select max(se) + 1 ss_gap, :v_to se_gap from t)
5 where greatest(:v_from,ss_gap) <= least(:v_to,se_gap)
6 order by 1
7 /

from to
---------- ----------
10 49
99 99
151 159
191 199
231 1000

SQL>
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.