set feedback on
set pagesize 1000

----------------------------------------------------------------------
-- Setup prerequisite objects for all methods
----------------------------------------------------------------------

create table t1 as
select
  rownum as key, c1, c2, c3, c1 as c4, c2 as c5, c3 as c6, c1 as c7, c2 as c8, c3 as c9, c1 as c0
from
  t,
  table( varray_type( '1','2','3','4','5','6','7','8','9','10') ) vt1 ,
  table( varray_type( '1','2','3','4','5','6','7','8','9','10') ) vt2
;

Table created.


select count(*) from t1 ;

  COUNT(*)
----------
       800

1 row selected.


commit;

Commit complete.


-- drop old metrics from prior runs

execute ssth_ctrl.drop_all_metrics ;

PL/SQL procedure successfully completed.


spool off
----------------------------------------------------------------------
-- new SQL*Plus session starts here
----------------------------------------------------------------------

set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Test 1' );

select key, 'C1'  as source, c1 as val from t1 union all
select key, 'C2'  as source, c2 as val from t1 union all
select key, 'C2'  as source, c3 as val from t1 union all
select key, 'C2'  as source, c4 as val from t1 union all
select key, 'C2'  as source, c5 as val from t1 union all
select key, 'C2'  as source, c6 as val from t1 union all
select key, 'C2'  as source, c7 as val from t1 union all
select key, 'C2'  as source, c8 as val from t1 union all
select key, 'C2'  as source, c9 as val from t1 union all
select key, 'C10' as source, c0 as val from t1
;

Statistics
----------------------------------------------------------
        254  recursive calls
          0  db block gets
        686  consistent gets
          0  physical reads
          0  redo size
     117309  bytes sent via SQL*Net to client
       6236  bytes received via SQL*Net from client
        535  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       8000  rows processed


execute ssth_ctrl.stop_test( 'Test 1' );

set autotrace off

spool off
----------------------------------------------------------------------
-- new SQL*Plus session starts here
----------------------------------------------------------------------

set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Test 2' );

select
  key ,
  vals.name  as source ,
  vals.value as val
from
  t1 ,
  table
  ( name_value_varray
    ( name_value_pair( 'C1', t1.c1 ),
      name_value_pair( 'C2', t1.c2 ),
      name_value_pair( 'C3', t1.c2 ),
      name_value_pair( 'C4', t1.c2 ),
      name_value_pair( 'C5', t1.c2 ),
      name_value_pair( 'C6', t1.c2 ),
      name_value_pair( 'C7', t1.c2 ),
      name_value_pair( 'C8', t1.c2 ),
      name_value_pair( 'C9', t1.c2 ),
      name_value_pair( 'C0', t1.c3 )
    )
  ) vals
;

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        581  consistent gets
          0  physical reads
          0  redo size
     117574  bytes sent via SQL*Net to client
       6236  bytes received via SQL*Net from client
        535  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       8000  rows processed


execute ssth_ctrl.stop_test( 'Test 2' );

set autotrace off

spool off
----------------------------------------------------------------------
-- new SQL*Plus session starts here
----------------------------------------------------------------------

set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Test 3' );

select key, source, val
from   t1
model
  return updated rows
  partition by ( key )
  dimension by ( 0 as i )
  measures
  (
    'xx' as source,
    'xxxxx' as val,
    c1, c2, c3, c4, c5, c6, c7, c8, c9, c0
  )
  rules upsert all
  (
    source[ 1 ] = 'C1' ,
    source[ 2 ] = 'C2' ,
    source[ 3 ] = 'C3' ,
    source[ 4 ] = 'C4' ,
    source[ 5 ] = 'C5' ,
    source[ 6 ] = 'C6' ,
    source[ 7 ] = 'C7' ,
    source[ 8 ] = 'C8' ,
    source[ 9 ] = 'C9' ,
    source[ 10] = 'C0' ,
    val[ 1 ] = c1[ 0 ] ,
    val[ 2 ] = c2[ 0 ] ,
    val[ 3 ] = c3[ 0 ] ,
    val[ 4 ] = c4[ 0 ] ,
    val[ 5 ] = c5[ 0 ] ,
    val[ 6 ] = c6[ 0 ] ,
    val[ 7 ] = c7[ 0 ] ,
    val[ 8 ] = c8[ 0 ] ,
    val[ 9 ] = c9[ 0 ] ,
    val[ 10] = c0[ 0 ]
  )
;

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
     126790  bytes sent via SQL*Net to client
       6236  bytes received via SQL*Net from client
        535  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       8000  rows processed


execute ssth_ctrl.stop_test( 'Test 3' );

set autotrace off

spool off
----------------------------------------------------------------------
-- new SQL*Plus session starts here
----------------------------------------------------------------------

set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Test 4' );

select key, source, val
from
  t1 unpivot include nulls
    ( val for( source ) in
        ( c1 as 'C1',
          c2 as 'C2',
          c3 as 'C3',
          c4 as 'C4',
          c5 as 'C5',
          c6 as 'C6',
          c7 as 'C7',
          c8 as 'C8',
          c9 as 'C9',
          c0 as 'C0'
        )
    )
order by key ;

Statistics
----------------------------------------------------------
         31  recursive calls
          0  db block gets
         76  consistent gets
          0  physical reads
          0  redo size
     125978  bytes sent via SQL*Net to client
       6236  bytes received via SQL*Net from client
        535  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       8000  rows processed


execute ssth_ctrl.stop_test( 'Test 4' );

set autotrace off

spool off