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);
Jan Leers
18 April 2011 - 5:12 pm
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!
Mark S
15 December 2011 - 12:57 am
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.
Mark S
15 December 2011 - 1:01 am
… 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
Jan Leers
15 December 2011 - 7:07 am
Certainly not?..,Did you test?
100000001?…. Certainly not ;
Jeffrey Kemp
15 December 2011 - 7:45 am
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