set feedback on
set pagesize 1000

------------------------------------------------------------
-- setup
------------------------------------------------------------

create table integers
  ( integer_value integer primary key )
  organization index
;

Table created.


begin
  for i in -100000 .. 100000 loop
    insert into integers values ( i );
  end loop;
  commit;
end;
/

PL/SQL procedure successfully completed.


select count(*) from integers ;

</pre>
<pre class="samp">
  COUNT(*)
----------
    200001
</pre>

1 row selected.


create function integer_series
( p_lower_bound in number,
  p_upper_bound in number )
  return integer_table_type pipelined
as
begin
  for i in p_lower_bound .. p_upper_bound
  loop pipe row(i); end loop;
  return;
end;
/

Function created.


----------------------------------------------------------------------
-- drop old metrics from prior runs
----------------------------------------------------------------------

execute ssth_ctrl.drop_all_metrics ;

PL/SQL procedure successfully completed.


----------------------------------------------------------------------
-- run each technique in a separate session
----------------------------------------------------------------------
spool off
----------------------------------------------------------------------
-- new SQL*Plus session starts here
----------------------------------------------------------------------
set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Method 1' );

select integer_value
from   integers
where  integer_value between 1 and 100000 ;

Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
       6834  consistent gets
          0  physical reads
          0  redo size
    1455975  bytes sent via SQL*Net to client
      73710  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed


execute ssth_ctrl.stop_test( 'Method 1' );

spool off
----------------------------------------------------------------------
-- new SQL*Plus session starts here
----------------------------------------------------------------------
set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Method 2' );

select integer_value
from   dual
where  1=2
model
  dimension by ( 0 as key )
  measures     ( 0 as integer_value )
  rules upsert
  ( integer_value[ for key from 1 to 100000 increment 1 ] = cv(key) )
;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
    1455975  bytes sent via SQL*Net to client
      73710  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed


execute ssth_ctrl.stop_test( 'Method 2' );

spool off
----------------------------------------------------------------------
-- new SQL*Plus session starts here
----------------------------------------------------------------------
set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Method 3' );

select rownum
from   integers
where rownum <= 100000 ;

Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
       6882  consistent gets
          0  physical reads
          0  redo size
    1455968  bytes sent via SQL*Net to client
      73710  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed


execute ssth_ctrl.stop_test( 'Method 3' );

spool off
----------------------------------------------------------------------
-- new SQL*Plus session starts here
----------------------------------------------------------------------
set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Method 4' );

select  level
from    dual
connect by level <= 100000 ;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
    1455967  bytes sent via SQL*Net to client
      73710  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     100000  rows processed


execute ssth_ctrl.stop_test( 'Method 4' );

spool off
----------------------------------------------------------------------
-- new SQL*Plus session starts here
----------------------------------------------------------------------
set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Method 7' );

select rownum
from
  table( integer_table_type( 1,2,3,4,5,6,7,8,9,10 ) ) i1,
  table( integer_table_type( 1,2,3,4,5,6,7,8,9,10 ) ) i2,
  table( integer_table_type( 1,2,3,4,5,6,7,8,9,10 ) ) i3,
  table( integer_table_type( 1,2,3,4,5,6,7,8,9,10 ) ) i4,
  table( integer_table_type( 1,2,3,4,5,6,7,8,9,10 ) ) i5
;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         33  consistent gets
          0  physical reads
          0  redo size
    1455968  bytes sent via SQL*Net to client
      73710  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     100000  rows processed


execute ssth_ctrl.stop_test( 'Method 7' );

spool off
----------------------------------------------------------------------
-- new SQL*Plus session starts here
----------------------------------------------------------------------
set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Method 8' );

select *
from   table( integer_series(1,100000) ) ;

Statistics
----------------------------------------------------------
         30  recursive calls
          0  db block gets
         66  consistent gets
          0  physical reads
          0  redo size
    1455974  bytes sent via SQL*Net to client
      73710  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     100000  rows processed


execute ssth_ctrl.stop_test( 'Method 8' );

spool off