Tricks For Generating n Rows

Posted 7 April, 2006 - 13:41

Every so often it becomes necessary to use a row source that guarantees to return an arbitrary number of rows. For example, one may wish to use a row source which generates a row for each day of the calendar year, or a row for every time a team has won the Champions League trophy or whatever other reason happens to come knocking at your door. Enter the concept of Row Generators

Use A Predefined Table

One simple solution is to create a table containing M rows and restrict the results to the number that satisfies your needs. This is sometimes referred to as a pivot table. The slight problem with this approach is determining the value of M, since this value must be finite but it may not necessarily be known in advance.

Use A Pipelined Function

A variation on the pivot table approach is to use a feature in ORACLE 9i called pipelined functions. Rather than being forced into creating a table with the requisite number of rows in advance, ORACLE can use some magic to make a function call look like and behave as though it were a table.

Here is an example. First, we'll set up the data types and define the function (figure 1.0).

Figure 1.0

CREATE TYPE pivot_row AS OBJECT(x NUMBER);

CREATE TYPE pivot_table AS TABLE OF pivot_row;

CREATE FUNCTION pivot(num_rows IN NUMBER)
RETURN pivot_table
PARALLEL_ENABLE PIPELINED
AS
    outrow pivot_row := pivot_row(0);
BEGIN
    FOR x IN 0..num_rows
    LOOP
        outrow.x := x;
        PIPE ROW(outrow);
    END LOOP;
    RETURN;
END pivot;

To access the pivot function, one uses the ORACLE TABLE operator as shown below (figure 1.1):

Figure 1.1

sql> SELECT * FROM TABLE(pivot(1000));

Use ORACLE's Group By Cube Operator

This is yet another variation on the pivot table approach, this time using some aggregation trickery with ORACLE's dual table (figure 1.2).

Figure 1.2

SELECT ROWNUM n
FROM   (SELECT 1
        FROM dual
        GROUP BY CUBE(1, 2, 3, 4, 5, 6, 7, 8, 9)
       )
WHERE ROWNUM <= 365

The inner query will generate a result set comprising 2⁹ rows, which are then restricted to the first 365 in this particular example. Simple but may be a performance drain if the number of arguments to the GROUP BY CUBE operator is large.

Using ORACLE Tree Walking SQL

This one is really a trick using ORACLE's hierarchical relationship querying extension: CONNECT BY (figure 1.3).

Figure 1.3

SELECT ROWNUM n
FROM   (SELECT 1
        FROM dual
        CONNECT BY LEVEL <= 365
       )

Simple and effective!

So there you have it. Whichever approach you decide to take, bear in mind the impact on performance and scalability as well as flexibility.

Site Menu