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…

Question: why can’t the optimizer do better with these?
Amazon EC2: how I recovered

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

Your email address will not be published / Required fields are marked *