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

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 207 other followers

%d bloggers like this: