set feedback on
set pagesize 1000

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

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

Table created.


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

PL/SQL procedure successfully completed.


select count(*) from integers ;

</pre>
<pre class="samp">
  COUNT(*)
----------
      2001
</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 100 ;

Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
       1683  bytes sent via SQL*Net to client
        450  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  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 100 increment 1 ] = cv(key) )
;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1683  bytes sent via SQL*Net to client
        450  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  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 <= 100 ;

Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
       1676  bytes sent via SQL*Net to client
        450  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  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 <= 100 ;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1675  bytes sent via SQL*Net to client
        450  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        100  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 5' );

select rownum
from
(
  select 1
  from   dual
  group by cube( 1,2,3,4,5,6,7 ) -- 2^7 = 128
)
where rownum <= 100 ;

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


execute ssth_ctrl.stop_test( 'Method 5' );

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

execute ssth_ctrl.start_test( 'Method 6' );

select column_value
from
  table
  ( integer_table_type
    (
      1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,
      21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,
      41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,
      61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,
      81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100
    )
  ) ;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         33  consistent gets
          0  physical reads
          0  redo size
       1682  bytes sent via SQL*Net to client
        450  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed


execute ssth_ctrl.stop_test( 'Method 6' );

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
;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
       1676  bytes sent via SQL*Net to client
        450  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        100  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,100) ) ;

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


execute ssth_ctrl.stop_test( 'Method 8' );

spool off