Search Oracle Blogs
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:
Cheers, Paweł
- Maximum Speed
- Without limitations
- Easy of use
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:
My Results, all times in seconds
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; /
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_numberCheers, Paweł
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment