Missing Numbers (Between two numbers)

[tamilselvan]

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

[padders]

> 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.

[tamilselvan]

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

[padders]

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

[Sathy]

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

I got the solution through your discusstions..

Thanks a lot for all.

-Sathy

[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.

[slimdave]

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