Missing Numbers (Between two numbers) - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 27 of 27

Thread: Missing Numbers (Between two numbers)

  1. #21
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

  2. #22
    Join Date
    Jan 2004
    Posts
    162
    > 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.

  3. #23
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

  4. #24
    Join Date
    Jan 2004
    Posts
    162
    Hey no offence Tamil. Yes all the solutions work. I was just interested in which one was most efficient, that was all.

  5. #25
    Join Date
    Mar 2004
    Location
    India
    Posts
    72
    ya Thanks Mr. Tamil & Mr. Padders etc...

    I got the solution through your discusstions..

    Thanks a lot for all.

    -Sathy

  6. #26
    Join Date
    Feb 2004
    Posts
    2
    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.

  7. #27
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width