Skip to content

Infinite Query

April 16, 2011

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…

About these ads

From → SQL

5 Comments
  1. This must be the cleanest, and probably fastest, row generator I’ve seen. No unnecessary overhead when selecting from all_source or dual with connect by.
    Great!

  2. Mark S permalink

    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.

  3. Mark S permalink

    … 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

  4. Certainly not?..,Did you test?
    100000001?…. Certainly not ;

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

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

Follow

Get every new post delivered to your Inbox.

Join 200 other followers

%d bloggers like this: