Row Generators

You probably now all these methods to generate rows. I've decided to choose the best method. My criterias are:

• Maximum Speed
• Without limitations
• Easy of use

To measure time I use PL/SQL block:

declare
v_start_time  timestamp;
v_end_time    timestamp;
v_max_number  integer;
v_max_gen     integer;
begin
v_max_number := 1000;
v_max_gen := 0;
v_start_time := systimestamp;
for r in (QUERY_HERE) loop
  v_max_gen := r.r;
end loop;
v_end_time := systimestamp;
dbms_output.put_line('Max gen = '||v_max_gen);
dbms_output.put_line('Time = '||(v_end_time-v_start_time));
end;

Where QUERY_HERE was one of:

ALL_OBJECTS	select rownum r from all_objects where rownum <= v_max_number
2*ALL_OBJECTS	select rownum r from all_objects, all_objects where rownum <= v_max_number
group by cube	select rownum r from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13,14)) where rownum <= v_max_number
level	select level r from dual connect by level <= v_max_number
PL/SQL Function	select rownum r from table(rowgen(v_max_number))
	where function was created using: CREATE OR REPLACE TYPE numtab_type IS TABLE OF NUMBER; CREATE or replace FUNCTION rowgen(pin_nor IN NUMBER) RETURN numtab_type DETERMINISTIC PIPELINED AS BEGIN FOR i IN 1..pin_nor LOOP PIPE ROW(i); END LOOP; RETURN; END rowgen; /

My Results, all times in seconds

Method/Rows	1000 rows	10000 rows	1000000 rows	Comment
ALL_OBJECTS	0.1	(*)	(*)	(*) only 5292 rows generated. We cannot generate big number of rows
2*ALL_OBJECTS	0.2	0.7	66.2
group by cube	0	0.1	> 5 min	Number of elements in cube clause vary on how big numbers we want get. For big numbers it is slow and not flexible in setting.
level	0	0.1	2.8
PL/SQL Function	0	0.1	2.2	We have to create Type and Function

Conclusion

If we consider speed, we should use one of two methods: level or PL/SQL Function. When we also consider the Easy of use we have only one solution select level r from dual connect by level <= v_max_number

Cheers, Paweł