Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Tuesday, September 12, 2006

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.12.2We 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ł

No comments:

 

Copyright © Paweł Barut
Printing from DOS to USB Printer