Infinite Query

This is the query that never ends,
It just goes on and on, my friends.
Some people started fetching not knowing what it was,
And now they can’t stop fetching forever just because…

This is the query that never ends,

CREATE TYPE number_table_type IS TABLE OF NUMBER;

CREATE FUNCTION row_generator
RETURN number_table_type
PIPELINED IS
BEGIN
  LOOP
    FOR i IN 1..100 LOOP
      PIPE ROW (i);
    END LOOP;
  END LOOP;
  RETURN;
END;

SELECT * FROM TABLE(row_generator);

…inspired by…

5 thoughts on “Infinite Query

  1. Certainly not the fastest – any pipeline function is guaranteed to be slower than a pure SQL solution due to the context switching overhead.

    select rownum from dual
    connect by rownum > 1

    will do the job nicely.

    Like

  2. … and by changing the comparison you can have as many or as few rows as you like

    select rownum from dual
    connect by rownum < 11 — or 101, 100000001, 100000000000000000001, etc

    Like

  3. This topic has already been done to death – and my post wasn’t really about “how to generate rows” – so I won’t comment any further on this except to link to the following articles comparing different row generators and their performance:

    http://jonathanlewis.wordpress.com/2011/12/08/test-data/

    http://blog.tanelpoder.com/2008/06/08/generating-lots-of-rows-using-connect-by-safely/

    http://www.oracle-developer.net/display.php?id=408

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s