set feedback on
set pagesize 1000

----------------------------------------------------------------------
-- Setup prerequisite objects for all methods
----------------------------------------------------------------------
create table tt as
select key, c
from   dual
where  1=2
model
  dimension by ( 0 as key )
  measures     ( '1,2,3,4,5,6,7,8,9,10' as c  )
  rules upsert
  (
    c[ for key from 0 to 9990  increment 10 ] = null               ,
    c[ for key from 1 to 9991  increment 10 ] = '1'                ,
    c[ for key from 2 to 9992  increment 10 ] = '1,2'              ,
    c[ for key from 3 to 9993  increment 10 ] = '1,2,3'            ,
    c[ for key from 4 to 9994  increment 10 ] = '1,2,3,4'          ,
    c[ for key from 5 to 9995  increment 10 ] = '1,2,3,4,5'        ,
    c[ for key from 6 to 9996  increment 10 ] = '1,2,3,4,5,6'      ,
    c[ for key from 7 to 9997  increment 10 ] = '1,2,3,4,5,6,7'    ,
    c[ for key from 8 to 9998  increment 10 ] = '1,2,3,4,5,6,7,8'  ,
    c[ for key from 9 to 9999  increment 10 ] = '1,2,3,4,5,6,7,8,9'
  )
;

Table created.


select
  count(*),
  avg( length( regexp_replace( c, ',', null ) ) ) avg_value_count
from tt ;

  COUNT(*) AVG_VALUE_COUNT
---------- ---------------
     10000               5

1 row selected.



create table tt2 as
select key, c
from   dual
where  1=2
model
  dimension by ( 0 as key )
  measures     ( '<V1>1</V1>,<V2>2</V2>,<V3>3</V3>,<V4>4</V4>,<V5>5</V5>,<V6>6</V6>,<V7>7</V7>,<V8>8</V8>,<V9>9</V9>' as c  )
  rules upsert
  (
    c[ for key from 0 to 9990  increment 10 ] = '<V></V>' ,
    c[ for key from 1 to 9991  increment 10 ] = '<V1>1</V1>' ,
    c[ for key from 2 to 9992  increment 10 ] = '<V1>1</V1><V2>2</V2>' ,
    c[ for key from 3 to 9993  increment 10 ] = '<V1>1</V1><V2>2</V2><V3>3</V3>' ,
    c[ for key from 4 to 9994  increment 10 ] = '<V1>1</V1><V2>2</V2><V3>3</V3><V4>4</V4>' ,
    c[ for key from 5 to 9995  increment 10 ] = '<V1>1</V1><V2>2</V2><V3>3</V3><V4>4</V4><V5>5</V5>' ,
    c[ for key from 6 to 9996  increment 10 ] = '<V1>1</V1><V2>2</V2><V3>3</V3><V4>4</V4><V5>5</V5><V6>6</V6>' ,
    c[ for key from 7 to 9997  increment 10 ] = '<V1>1</V1><V2>2</V2><V3>3</V3><V4>4</V4><V5>5</V5><V6>6</V6><V7>7</V7>' ,
    c[ for key from 8 to 9998  increment 10 ] = '<V1>1</V1><V2>2</V2><V3>3</V3><V4>4</V4><V5>5</V5><V6>6</V6><V7>7</V7><V8>8</V8>' ,
    c[ for key from 9 to 9999  increment 10 ] = '<V1>1</V1><V2>2</V2><V3>3</V3><V4>4</V4><V5>5</V5><V6>6</V6><V7>7</V7><V8>8</V8><V9>9</V9>'
  )
;

Table created.


select count(*)
from   tt2 ;

  COUNT(*)
----------
     10000

1 row selected.



create function csv_num_to_varray( p_string in varchar2 )
return
  integer_varray_type -- this type defined in Setup topic for this section
is
  v_table integer_varray_type ;
begin

  execute immediate
    'begin :v_table := integer_varray_type( ' || p_string || ' ); end;'
    using in out v_table
  ;

  return( v_table );

end;
/

Function created.


-- drop old metrics from prior runs and analyze schema

execute ssth_ctrl.drop_all_metrics ;

PL/SQL procedure successfully completed.


execute dbms_stats.gather_schema_stats( ownname => null )

PL/SQL procedure successfully completed.



spool off
----------------------------------------------------------------------
-- new SQL*Plus session starts here
----------------------------------------------------------------------
variable d varchar2(1)

execute :d := ','

set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Test 1' );

select key, 1 as position, rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 1 ), :d ) as val
from   tt
union all
select key, 2 as position, rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 2 ), :d ) as val
from   tt
where  regexp_substr( c || :d, '.*?' || :d, 1, 2 ) is not null
union all
select key, 3 as position, rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 3 ), :d ) as val
from   tt
where  regexp_substr( c || :d, '.*?' || :d, 1, 3 ) is not null
union all
select key, 4 as position, rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 4 ), :d ) as val
from   tt
where  regexp_substr( c || :d, '.*?' || :d, 1, 4 ) is not null
union all
select key, 5 as position, rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 5 ), :d ) as val
from   tt
where  regexp_substr( c || :d, '.*?' || :d, 1, 5 ) is not null
union all
select key, 6 as position, rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 6 ), :d ) as val
from   tt
where  regexp_substr( c || :d, '.*?' || :d, 1, 6 ) is not null
union all
select key, 7 as position, rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 7 ), :d ) as val
from   tt
where  regexp_substr( c || :d, '.*?' || :d, 1, 7 ) is not null
union all
select key, 8 as position, rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 8 ), :d ) as val
from   tt
where  regexp_substr( c || :d, '.*?' || :d, 1, 8 ) is not null
union all
select key, 9 as position, rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 9 ), :d ) as val
from   tt
where  regexp_substr( c || :d, '.*?' || :d, 1, 9 ) is not null
union all
select key, 10 as position, rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 10 ), :d ) as val
from   tt
where  regexp_substr( c || :d, '.*?' || :d, 1, 10 ) is not null
;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3352  consistent gets
          0  physical reads
          0  redo size
     803408  bytes sent via SQL*Net to client
      35557  bytes received via SQL*Net from client
       3068  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      46000  rows processed


execute ssth_ctrl.stop_test( 'Test 1' );

set autotrace off

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

variable d varchar2(1)

execute :d := ','

set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Test 2' );

select
  ta.key ,
  i.column_value as position ,
  substr
  ( ta.c2,
    instr( ta.c2, :d, 1, i.column_value  ) + 1,
    instr( ta.c2, :d, 1, i.column_value + 1 )
      - ( instr( ta.c2, :d, 1, i.column_value ) + 1 )
  ) as val
from
  ( select
      key ,
      :d || c || :d as c2 ,
      length( c || :d ) - nvl( length( replace( c, :d, null ) ), 0 ) as value_count
    from tt
  ) ta
    inner join table( integer_varray_type( 1,2,3,4,5,6,7,8,9,10 ) ) i
      on ( i.column_value <= ta.value_count )
order by
  key, position
;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         62  consistent gets
          0  physical reads
          0  redo size
     880722  bytes sent via SQL*Net to client
      34110  bytes received via SQL*Net from client
       3068  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      46000  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, vt.column_value as numeric_val
from   tt, table( csv_num_to_varray(tt.c) ) (+) vt
;

Statistics
----------------------------------------------------------
      10040  recursive calls
          0  db block gets
       3270  consistent gets
          0  physical reads
          0  redo size
     791673  bytes sent via SQL*Net to client
      34110  bytes received via SQL*Net from client
       3068  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      46000  rows processed


execute ssth_ctrl.stop_test( 'Test 3' );

set autotrace off

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

variable d varchar2(1)

execute :d := ','

set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Test 4' );

select key, position, val
from   tt
model
  return updated rows
  partition by ( key )
  dimension by ( 0 as position )
  measures     ( c || :d as val )
  rules upsert
  ( val
    [ for position
        from 1
        to   length( regexp_replace( val[0], '[^'||:d||']', null ) )
        increment 1
    ] = rtrim( regexp_substr( val[0], '.*?' || :d, 1, cv(position) ), :d )
  )
order by key, position ;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
     880722  bytes sent via SQL*Net to client
      34110  bytes received via SQL*Net from client
       3068  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      46000  rows processed


execute ssth_ctrl.stop_test( 'Test 4' );

set autotrace off

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

set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Test 5' );

select
  tt2.key ,
  tt2a.column_value.getRootElement() as element_name ,
  EXTRACTVALUE( tt2a.column_value , '/*/text()' ) val
from
  tt2 ,
  table
  ( XMLSEQUENCE
    ( XMLPARSE(content tt2.c wellformed ) )
  ) tt2a
;

Statistics
----------------------------------------------------------
         15  recursive calls
          0  db block gets
       3233  consistent gets
          0  physical reads
          0  redo size
     879729  bytes sent via SQL*Net to client
      34110  bytes received via SQL*Net from client
       3068  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      46000  rows processed


execute ssth_ctrl.stop_test( 'Test 5' );

set autotrace off

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

variable d varchar2(1)

execute :d := ','

set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Test 6' );

select
  ta.key ,
  i.integer_value as position ,
  rtrim( regexp_substr( ta.c || :d, '.*?' || :d, 1, i.integer_value ), :d ) as val
from
  ( select
      key ,
      c ,
      length( regexp_replace( c || :d, '[^'||:d||']', null ) ) as value_count
    from tt
  ) ta
    inner join
      ( select integer_value from integers where integer_value <= 10 ) i
      on ( i.integer_value <= ta.value_count )
order by
  key, position
;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
     886722  bytes sent via SQL*Net to client
      34110  bytes received via SQL*Net from client
       3068  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      46000  rows processed



execute ssth_ctrl.stop_test( 'Test 6' );

set autotrace off

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

variable d varchar2(1)

execute :d := ','

set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Test 7' );

select
  key,
  level as position ,
  rtrim( regexp_substr( c || :d, '.*?' || :d, 1, level ), :d ) as val
from
  tt
connect by
  key = prior key and
  prior dbms_random.value is not null and
  level <= length( regexp_replace( c || :d, '[^'||:d||']', null ) )
order by
  key, position
;

Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
     880722  bytes sent via SQL*Net to client
      34110  bytes received via SQL*Net from client
       3068  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      46000  rows processed


execute ssth_ctrl.stop_test( 'Test 7' );

set autotrace off

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

variable d varchar2(1)

execute :d := ','

set autotrace traceonly statistics

execute ssth_ctrl.start_test( 'Test 8' );

select
  key,
  level as position ,
  rtrim( regexp_substr( c || :d, '.*?' || :d, 1, level ), :d ) as val
from
  tt
connect by
  key = connect_by_root key and
  level <= length( regexp_replace( c || :d, '[^'||:d||']', null ) )
order by
  key, position
;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
     880722  bytes sent via SQL*Net to client
      34110  bytes received via SQL*Net from client
       3068  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      46000  rows processed



execute ssth_ctrl.stop_test( 'Test 8' );

set autotrace off

spool off