row generators performance

I wrote a few generators, and listed some existant in
http://laurentschneider.blogspot.com/2005/08/pivot-table.html

I decided to test them

note that this is not a “good” test, it is simply an overview. I am doing a count(*), another operation may be better in one or worst in another one.

all_objects is so slow (6 seconds for 50K rows) that I did not include it in my test

generate 1000 rows : all method are fast, except xquery…

generate 1000000 rows : model and cube never ends, xquery is very slow, connect and union are slow, plsql is ok, simple heap table is very fast (!)

I would like to point out that “generating rows” is rarely a business requirement. If you need to outer join with every day of the year, than create a table and insert every day of the year in it. It will be very fast, it is maintenable, extensible, supported, and self-documented…

Ok, here is my test

create type t_number as TABLE OF number;
/

create function f_number(n number) return t_number pipelined is
begin for i in 1..n loop pipe row(i); end loop; return; end;
/

create table t1000 as select 0 n from xmltable(‘for $i in 1 to 1000 return $i’ );
create table t1000000 as select 0 n from xmltable(‘for $i in 1 to 1000000 return $i’ );

set timi on feedb off echo off head off

prompt model 1000
select count(*)
from (
select null
from dual
model
dimension by (1 h)
measures (1 c)
rules( c[FOR h FROM 1 to 1000 INCREMENT 1] = 1)
)
/

prompt xquery 1000
select count(*)
from
xmltable(‘for $i in 1 to 1000 return $i’ )
/

prompt xquery 1000000
select count(*)
from
xmltable(‘for $i in 1 to 1000000 return $i’ )
/

prompt union 2*2*… 1K
with s as (select null from dual union all select null from dual)
select count(*)
from s,s,s,s,s,s,s,s,s,s
/

prompt union 1M
with s as (select null from dual union all select null from dual)
select count(*)
from
s,s,s,s,s,s,s,s,s,s,
s,s,s,s,s,s,s,s,s,s
/

prompt cube 1K
select count(*)
from (
select 1,2,3,4,5,6,7,8,9,10
from dual
group by cube(1,2,3,4,5,6,7,8,9,10)
)
/

prompt table 1000
select count(*)
from t1000
/

prompt table 1000000
select count(*)
from t1000000
/

set termout off
create type t_number as TABLE OF number;
/

create function f_number(n number) return t_number pipelined is
begin for i in 1..n loop pipe row(i); end loop; return; end;
/
set termout on

prompt plsql 1000
select count(*)
from table(f_number(1000))
/

prompt plsql 1000000
select count(*)
from table(f_number(1000000))
/

prompt all_objects
select count(*)
from all_objects
/

prompt connect 1000
select count(*)
from (
select level
from dual
connect by level