1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Row generation Methods in SQL ( Also in PL/SQL )

Discussion in 'SQL PL/SQL' started by rajavu, Oct 15, 2008.

  1. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There are some widely used Row generation techniques using SQL .

    Some of them are :

    1. Using ALL_OBJECTS ( from Old Oracle versions )
    2. Using CONNECT BY ( Oracle 8i and above)
    3. Using CUBE ( Oracle 8i and above)
    4. Using MODEL ( Oracle 10g and above)


    Using ALL_OBJECTS

    It is an old way of searching for a table with enough records in it , like ALL_OBJECTS ( Or any big table ) . Then we could do the following:

    Code (Text):


    SELECT ROWNUM n
    FROM   ALL_OBJECTS
    WHERE  ROWNUM <= 10
     
    Drawback of this method is that the output of this method is purely restricted to the number of records in the table.

    Using CONNECT BY

    This method uses Hierarchical queries and it used recursive hierarchy.

    Code (Text):


    SELECT LEVEL N
    FROM DUAL
    CONNECT BY LEVEL<=10;
     
    It should be noted that in later versions of oracle, at least as far back as 10gR1, operations against dual are optimized such that they require no logical or physical I/O operations. This makes them quite fast.

    Using CUBE


    The CUBE operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the specification, and returns a single row of summary information for each group. You can use the CUBE operation to produce cross-tabulation values.

    For example, given three expressions (n=3) in the CUBE clause of the simple_grouping_clause, the operation results in 2^n = 2^3 = 8 groupings.


    Code (Text):


    SELECT ROWNUM n
    FROM   ( SELECT 1 just_a_column
             FROM   dual
             GROUP BY CUBE(1,2,3,4)
           )
    WHERE  ROWNUM <= 10
     
    Using MODEL

    MODEL clause was introduced in Oracle 10g.


    Code (Text):


    SELECT INTEGER_VALUE
    FROM   DUAL
    WHERE  1=2
    MODEL
      DIMENSION BY ( 0 AS KEY )
      MEASURES     ( 0 AS INTEGER_VALUE )
      RULES UPSERT ( INTEGER_VALUE[ FOR KEY FROM 1 TO 10 INCREMENT 1 ] = CV(KEY) )
     
    PL/SQL Way ..

    Plus there are some PL/Sql way of doing the same . Pipelined function will be one of them

    Pipe line Function ( Table Function )

    This option requires two database objects,

    1. A user-defined table type

    Code (Text):
     
    CREATE OR REPLACE TYPE numtab_type IS TABLE OF NUMBER;
     
    2. The Pipe line function

    Code (Text):


    CREATE FUNCTION rowgen(pin_nor IN NUMBER)
    RETURN numtab_type PIPELINED
    AS
    BEGIN
      FOR i IN 1..pin_nor
      LOOP
        PIPE ROW(i);
      END LOOP;  
      RETURN;
    END rowgen;
     
    Now you have to use SQL with table claus to query the same .

    Code (Text):


    SELECT COLUMN_VALUE n
      FROM TABLE(rowgen(365))
     
    Conclusion

    There are different methods are available for generating rows in SQL and PL/SQL . And they are having potential use in various applications.
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Very comprehensive listing of dummy row generation techniques. It would have been even better if you could have mentioned the statistics of performance with each technique. But very good for reference. :)
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    MODEL Clause will fastest in terms of performance and Slowest technique will be ALL_OBJECTS . By the way my vote goes to CONNECT BY and PIPELINED TABLE function technique :)
     
  4. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Good example. I frequently use connect by method in order to make any quick test on my database in terms of some example creation and testing over it.