Missing Numbers (Between two numbers)

Is there any option to get Missing numbers between
two numbers with out using loop statement????


---Sathy

SELECT :v_lower + ROWNUM AS missing_number
FROM ALL_OBJECTS
WHERE ROWNUM < :v_upper - :v_lower;

If I understood that, Jurij, I might be impressed ;)

It depends on what you mean by a "missing number". Now, if the number " " went missing . . . . . . hey! where's " "? Has any one seen " "?

Actually Iam having table coloumn having values from 400000 to 500000.
And some numbers are missing between these number..

Is there any inbuild function is there to find out
the missed numbers?.

--Sathy

PHP Code:

---

SQL> create table test as select rownum rno from user_objects where rownum<10;

Table created.

Elapsed: 00:00:00.47
SQL> select * From test;

       RNO                                                                                                                    
==========                                                                                                                    
         1                                                                                                                    
         2                                                                                                                    
         3                                                                                                                    
         4                                                                                                                    
         5                                                                                                                    
         6                                                                                                                    
         7                                                                                                                    
         8                                                                                                                    
         9                                                                                                                    

9 rows selected.

Elapsed: 00:00:00.78
SQL> delete from test where rno=6;

1 row deleted.

Elapsed: 00:00:00.40
SQL> commit;

Commit complete.Elapsed: 00:00:00.25
SQL>  select rno+1 from test a
  2   where not exists (select null from test b
  3   where b.rno=a.rno+1)
  4  /

     RNO+1                                                                                                                    
==========                                                                                                                    
         6                                                                                                                    
        10                                                                                                                    

Elapsed: 00:00:00.19 


---

http://www.experts-exchange.com/Data..._20815063.html

Quote:

---

Originally posted by jmodic
SELECT :v_lower + ROWNUM AS missing_number
FROM ALL_OBJECTS
WHERE ROWNUM < :v_upper - :v_lower;

---

Jurij :

I dont understand if this will fit what poster has asked for :rolleyes: ..

How about

Code:

---


Select Column_Name + 1 From My_Table Where Column_Name + 1 <= My_Max_Value And Column_Name + 1 >= My_Min_Value
Minus
Select Column_Name From My_Table Where Column_Name <= My_Max_Value And Column_Name >= My_Min_Value

---

Do either of the above allow for a gap of two or more?

How about:

Code:

---

Select * From
(SELECT :v_lower + ROWNUM AS missing_number
 FROM ALL_OBJECTS, ALL_OBJECTS
 WHERE ROWNUM < :v_upper - :v_lower)
Where missing_number not in
(select my_col from my_table
 where  my_col is not NULL);

---

With aknowledgements to Jurij.

P.S: just noticed we need 100'000 numbers! Hope this won't blow the temp area!

Quote:

---

Originally posted by abhaysk
Jurij :

I dont understand if this will fit what poster has asked for :rolleyes: ..

---

Of course it will not. That's because at the time when I wrote my ansewer there were far less information available about what Sathy realy wants than it was when you replied. I have missinterpreted the original question as "given the minimum and maximum nuber, can I get all the numbers between them without using loop statement". Now after Sathy's further explanation it is obvious that I have totaly misunderstood the question.

However it is also obvious that your suggesten was equaly useless and wrong.

Suppose we have three values in the table: 10, 13 and 17. Sowe want to get all the missing numbers, that is 11, 12, 14, 15 and 16. BTW, your proposed sollution returns only values 11 and 14.

Here is my next porposal (with the table and column names you have used in your suggestion):

Code:

---

SELECT :my_min_value+rownum FROM all_objects
 WHERE rownum < :my_max_value - :my_min_value
MINUS
SELECT column_name FROM my_table;

---

For any given two integers (my_max_value and my_min_value) it will list all the integers inside this range that are not allready in my table. The assumption is of course that the given range is not larger than number of rows returned by ALL_OBJECTS (typicaly more than 20,000 if you have java loaded into your database).

Hm, just realised that DaPi allready posted the sollution that goes almost the same as mine. Plus I didn't noticed that in the example given by Sathy the requested range is 100,000! Gosh! Although performing a cartesian product between two resultsets from all_rows - hm, not every server will be able to cope with this! ;)

Possibly "my_table" can be used to generate the ROWNUM set? Only Sathy will know that.

Jurij, MINUS does look neater than my NOT IN.

Quote:

---

Originally posted by jmodic
However it is also obvious that your suggesten was equaly useless and wrong.

---

Well, yes its useless.. was in haste.. & never thought of a case with gaps more than 1.

Interesting thread. There are a few recent features which can help out here too, for example using analytic functions to find the gaps.

Code:

---

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> CREATE OR REPLACE TYPE number_table AS TABLE OF NUMBER (10);
  2  /

Type created.

SQL> CREATE TABLE table_name (
  2    column_name NUMBER (10));

Table created.

SQL> INSERT INTO table_name
  2  SELECT column_value
  3  FROM  TABLE (number_table (2, 4, 5, 8, 10));

5 rows created.

SQL> COMMIT;

Commit complete.

SQL> VARIABLE min NUMBER;
SQL> VARIABLE max NUMBER;
SQL> EXEC :min := 1; :max := 10;

PL/SQL procedure successfully completed.

SQL> SELECT now + 1 || DECODE (now + 2, nxt, NULL, ' - ' || (nxt - 1)) gap
  2  FROM  (SELECT now, LEAD (now) OVER (ORDER BY now) nxt
  3        FROM  (SELECT column_name now
  4                FROM  table_name
  5                UNION ALL
  6                SELECT column_value
  7                FROM  TABLE (number_table (:min - 1, :max + 1))))
  8  WHERE  now + 1 != nxt;

GAP
--------------------------------------------------------------------------
1
3
6 - 7
9

4 rows selected.

SQL>

---

Of course to get all the values of the gaps themselves we are (as we have seen) going to have to synthesize some rows. An alternative to ALL_OBJECTS here is to use a table function. This gives us an interesting choice - we can either synthesize all the rows up front or we can synthesize rows on a row-by-row basis via the TABLE () syntax as required. First up is the row-by-row approach - perhaps if there are only a few gaps this could be a more efficient approach.

Code:

---

SQL> CREATE OR REPLACE FUNCTION many (
  2    p_rows IN NUMBER)
  3    RETURN number_table PIPELINED
  4  IS
  5  BEGIN
  6    FOR l_row IN 1..p_rows LOOP
  7      PIPE ROW (l_row);
  8    END LOOP;
  9    RETURN;
 10  END;
 11  /

Function created.

SQL> SELECT now + column_value now
  2  FROM  (SELECT now, LEAD (now) OVER (ORDER BY now) nxt
  3        FROM  (SELECT column_name now
  4                FROM  table_name
  5                UNION ALL
  6                SELECT column_value
  7                FROM  TABLE (number_table (:min - 1, :max + 1)))),
  8        TABLE (many (nxt - now - 1))
  9  WHERE  now + 1 != nxt;

      NOW
----------
        1
        3
        6
        7
        9

5 rows selected.

SQL>

---

Alternatively we can (as mentioned) synthesize all the rows up-front using the table function and anti-join, outer-join or MINUS as suggested by Dapi and Jurij.

Code:

---

SQL> SELECT now
  2  FROM  (SELECT :MIN + column_value - 1 now
  3        FROM  TABLE (many (:max - :min + 1))) a,
  4        table_name b
  5  WHERE  column_name IS NULL
  6  AND    column_name(+) = now;

      NOW
----------
        1
        3
        6
        7
        9

5 rows selected.

SQL>

---

If follows that we can extend these two examples to give us all the values (and also know which is fake or real).

Code:

---

SQL> SELECT DECODE (now, now - 1 + column_value, 'REAL', 'FAKE') src,
  2        now - 1 + column_value now
  3  FROM  (SELECT now, LEAD (now) OVER (ORDER BY now) nxt
  4        FROM  (SELECT column_name now
  5                FROM  table_name
  6                UNION ALL
  7                SELECT column_value
  8                FROM  TABLE (number_table (:min - 1, :max + 1)))),
  9        TABLE (many (NVL (nxt - now, 1)))
 10  WHERE now - 1 + column_value NOT IN (:min - 1, :max + 1);

SRC        NOW
---- ----------
FAKE          1
REAL          2
FAKE          3
REAL          4
REAL          5
FAKE          6
FAKE          7
REAL          8
FAKE          9
REAL        10

10 rows selected.

SQL> SELECT DECODE (column_name, NULL, 'FAKE', 'REAL') src, now
  2  FROM  (SELECT :MIN + column_value - 1 now
  3        FROM  TABLE (many (:max - :min + 1))) a,
  4        table_name b
  5  WHERE  column_name(+) = now;

SRC        NOW
---- ----------
FAKE          1
REAL          2
FAKE          3
REAL          4
REAL          5
FAKE          6
FAKE          7
REAL          8
FAKE          9
REAL        10

10 rows selected.

SQL>

---

"Any sufficiently advanced technology is indistinguishable from magic!"

Quote:

---

Originally posted by padders
Interesting thread. There are a few recent features which can help out here too, for example using analytic functions to find the gaps.

---

Yesss! That's the right answer.

Quote:

---

Originally posted by jmodic
Yesss! That's the right answer.

---

Analytic functions are the right answer to any question.

The simple solution to find missing numbers is using cube:

SQL> select * from t1;

ID
----------
10
13
14
17
19
20

6 rows selected.

SQL>
1 select rn
2 from (select /*+ no_merge */ rownum as rn
3 from ( select 1 from dual group by cube (1,1,1,1,1)) a,
4 ( select 1 from dual group by cube (1,1,1,1,1,1)) b
5 )
6 where rn >= (select min(id) from t1) and
7 rn <= (select max(id) from t1)
8 minus
9* select id from t1
SQL> /

RN
----------
11
12
15
16
18

Technically you can generate millions of millions of rows using cube.
On my IBM P690 unix AIX machine (8 cpus with 1500 MH spped, 16 GB RAM), I generated 4 million rows table in 110 seconds.
If you want to generate more number of rows, just add 1s in the cube function.

Tamil

Yes TamilSelvan..It works.. But it take lengthy time...

select rn from
(select rownum as rn from (
select 1 from dual group by cube (1,1,1,1,1,1,1,1,1,1,1)) a,
(select 1 from dual group by cube (1,1,1,1,1,1,1,1,1,1,1,1)) b)
where rn >=(select min(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate-2)
and rn <=(select max(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate-2)
minus
select to_number(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate-2

Elapsed Time: 00:00:40.38

Is any opttion to reduce...???
Any Index Problem...???

==========
select min(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate-2
==========
You need to to tune the subquery.

Do you have a composite index on daddt,datype, and dadno columns?

Tamil

Quote:

---

Originally posted by tamilselvan
The simple solution to find missing numbers is using cube:

---

I think that i would prefer to use either a list of numbers in a table or a pipelined function to generate the required values ...

Code:

---

create or replace type TypTabNum as table of number
/
create or replace package utilities
as
  function tab_of_numbers (p_start number, p_finish number) return TypTabNum pipelined;
end;
/

create or replace package body utilities
as
  function tab_of_numbers (p_start number, p_finish number)
      return TypTabNum
      pipelined
  is
  begin
      if p_finish > p_start
      then
        for i in p_start .. p_finish
        loop
            pipe row (i);
        end loop;
      else
        for i in reverse p_finish .. p_start
        loop
            pipe row (i);
        end loop;
      end if;
  return;
  end;
end;
/

select * from table (utilities.tab_of_numbers(1,10))
/

select * from table (utilities.tab_of_numbers(10,1))
/

---

So anyway, you could ...

Code:

---

create table my_table
  (my_number number);
 
insert into my_table values (3);
insert into my_table values (4);
insert into my_table values (8);
insert into my_table values (10);
insert into my_table values (12);

commit;

select
  column_value as missing
from
  table (utilities.tab_of_numbers(
      (select min(my_number) from my_table),
      (select max(my_number) from my_table)))
minus
select
  my_number
from
  my_table
/

  MISSING
----------
        5
        6
        7
        9
        11

---

Just depends what kind of result set you want ... analytical functions are good for identifying ranges "5-7", this is good for listing individual values.

Using pipe is a good example.

Here is another method w/o pipe:

drop type myTableType
/

drop type myRecordtype
/

drop function my_function
/

create or replace type myRecordType
as object ( x int)
/

create or replace type myTableType
as table of myRecordType;
/
create or replace function my_function
(p_st in number, p_end in number )
return myTableType
as
my_data myTableType := myTabletype();
J number := 0 ;

begin
for I in p_st .. p_end loop
J := I - p_st + 1;
my_data.extend;
my_data(J) := myRecordType(I);
end loop;

return my_data;
end;
/

select * from TABLE ( cast( my_function(10, 20) as mytableType ) )
/

select * from TABLE
( cast ( my_function((select min(id) from t1), (select max(id) from t1)) as mytabletype))
minus
select * from t1
/

The cube solution is simple that works w/o a procedure/functions.

Tamil

> works w/o a procedure/functions

And what exactly is the problem with creating procedures or functions? I often see this wheeled out as an excuse for bizarre or arcane SQL. In any case we are not talking about creating 'functions' we are proposing creating *one* PL/SQL function to support this functionality everywhere.

I don't know why you think CUBE is simple. A pipelined function returning n rows is fast, results in rather less code and can be used flexibly and generically in SQL and PL/SQL passing only a parameter (i.e. bind variable) of the number of rows required. To generate different numbers of rows with CUBE requires that you physically change the query which would typically require that you re-code it every time and use dynamic SQL in PL/SQL and all that that entails. On top of that the pipelined function returns exactly the right amount of rows whereas your solution with CUBE would return significantly more rows (the next power of two greater?) than required and then filter the result set.

Even if it was simple, CUBE *sucks* at synthesizing rows. It might make for an interesting little 'I read AskTom' interview trick for SELECTing lots of rows from dual but that does not translate into it being efficient at synthesizing lots of rows. Surprisingly the cost appears to be mainly in the parsing overhead - to parse a query with a twelve dimensional CUBE on dual took me a staggering 37 seconds (this cube would return only 4096 rows). Your example is not fast because it uses CUBE, rather it is fast because it generates moderately small result sets with CUBE and then generates a cartesian product (the fast bit) of the two. Hence if you use CUBE you need to keep the number of dimensions low and create a product from multiple CUBEs for efficiency (i.e. join CUBE, CUBE, CUBE in a cartesian product). In which case CUBE is of little or no value since it is just as easy to use a small integer table, UNION ALL of dual or TABLE function and join to it repeatedly to create a product.

Since pipelined table function returns 4 million rows in approx. 3 seconds quite frankly I see little reason to get more complicated.

Padders, you did not read the post correctly or you might have missed some thing.

================================================
Is there any option to get Missing numbers between
two numbers with out using loop statement????
================================================

Sathy wanted a solution without a loop statement.

One can solve a problem in many ways.

I gave 2 solutions and Slimdave gave one solution.

What you are saying is post-mortem report.
First of all, we don't know why Sathy wants missing numbers. If it is for inserting rows in a table as using these missing numbers for PK, then it is a design fault. He has to go his drawing board, and redesign the appln. Or he just wants to know how to find out missing numbers for his academic knowledge, it is fine to use CUBE or PIPE or PL/SQL Table.

Tamil

Hey no offence Tamil. Yes all the solutions work. I was just interested in which one was most efficient, that was all.

ya Thanks Mr. Tamil & Mr. Padders etc...

I got the solution through your discusstions..

Thanks a lot for all.

-Sathy

Hi , you can use Lag function to get all the rows with the gap between them is greater the one (assuming we have integers ) . and you can compute the different btween this pair of records do get the missing numbers).

This will won't do a cartizen join , and only one pass on the table.

2 points to remeber :

1. 9i and above only
2. Oracle will have to sort the data.

I will send a sample later.

Quote:

---

Originally posted by agonen
Hi , you can use Lag function to get all the rows with the gap between them is greater the one (assuming we have integers ) . and you can compute the different btween this pair of records do get the missing numbers).

This will won't do a cartizen join , and only one pass on the table.

2 points to remeber :

1. 9i and above only
2. Oracle will have to sort the data.

I will send a sample later.

---

I think that was addressed earlier in the thread