This isn't a question. I just wanted to share a technique I stumbled upon while

jamming with Vadim Tropashko on his blog recently.



Occasionally the ability to create n copies of a row is required.

For example, given a table with this data


create table t ( key integer, string varchar2(10), qty integer );



insert into t values ( 1, 'a', 2 );

insert into t values ( 2, 'b', 3 );

commit;



       KEY STRING            QTY


----------

         1 a                   2

         2 b                   3


sometimes we need queries that produce 2 copies of row 1 and 3 copies of row 2,

like this.


       KEY STRING            QTY


----------

         1 a                   2

         1 a                   2

         2 b                   3

         2 b                   3

         2 b                   3


A query like the following would be ideal, but unfortunately Oracle doesn't

like the existence of a CONNECT BY loop.


select key, string, qty, level from t

CONNECT BY STRING = PRIOR STRING AND LEVEL <= QTY

order by 1, 4 ;

select key, string, qty, level from t

                                    *

ERROR at line 1:

ORA-01436: CONNECT BY loop in user data


Without the CONNECT BY loop we won't get the right number of rows however.


select key, string, qty, level, sys_connect_by_path( string, '/' ) path

from t

CONNECT BY LEVEL <= QTY

order by 1, 4 ;



       KEY STRING            QTY      LEVEL PATH


----------

----------

         1 a                   2          1 /a

         1 a                   2          2 /b/a

         1 a                   2          2 /a/a

         2 b                   3          1 /b

         2 b                   3          2 /b/b

         2 b                   3          2 /a/b

         2 b                   3          3 /b/a/b

         2 b                   3          3 /b/b/b

         2 b                   3          3 /a/a/b

         2 b                   3          3 /a/b/b


The final solution employs CONNECT_BY_ROOT to yield the desired result

without triggering an error.


select key, string, qty, level, sys_connect_by_path( string, '/' ) path

from t

CONNECT BY CONNECT_BY_ROOT KEY = KEY AND LEVEL <= QTY

order by 1, 4 ;



       KEY STRING            QTY      LEVEL PATH


----------

----------

         1 a                   2          1 /a

         1 a                   2          2 /a/a

         2 b                   3          1 /b

         2 b                   3          2 /b/b

         2 b                   3          3 /b/b/b


Here's an example of this "CONNECT BY CONNECT_BY_ROOT" technique in action.

It splits a comma separated value string into one row per value.


delete from t;

insert into t values( 1, 'a,bb,ccc'  , null );

insert into t values( 2, 'dddd,eeeee', null );

commit;



select

  string ,

  level as position ,

  regexp_substr( string, '^,+', 1, level ) as val

from

  t

connect by

  connect_by_root key = key and

  regexp_substr( string, '^,+', 1, level ) is not null

order by

  1, 2

;



STRING       POSITION VAL


----------

a,bb,ccc            1 a

a,bb,ccc            2 bb

a,bb,ccc            3 ccc

dddd,eeeee          1 dddd

dddd,eeeee          2 eeeee

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 14 14:45:18 2007



set timing on



select count(*) from t connect by

  key = prior key and

  prior dbms_random.value is not null and

  level <=1000000

;



  COUNT(*)


   2000000



Elapsed: 00:00:18.32



select count(*) from t connect by

  connect_by_root key = key and

  level <=1000000

;



  COUNT(*)


   2000000



Elapsed: 00:00:02.48

michaels>  set timing on
 
michaels>  select count(*) from t connect by  key = prior key and  prior sys_guid() is not null and  level <=1000000
 
  COUNT(*)
----------
   2000000
 
Elapsed: 00:00:09.91
 
michaels>  select count(*) from t connect by  key = prior key and  prior dbms_random.value  is not null and  level <=1000000
 
  COUNT(*)
----------
   2000000
 
Elapsed: 00:00:24.25

WITH t AS (SELECT 1 key, 'a' string, 2 qty
             FROM dual
            UNION
           SELECT 2, 'b', 3
             FROM dual
          )
SELECT t.*
  FROM t
     , (SELECT ROWNUM rn
          FROM all_objects
         WHERE ROWNUM <= (SELECT MAX(qty)
                            FROM t
                         )
       ) t2
 WHERE t2.rn <= t.qty
;