Oracle bulk insert new records n times depending on source table

Question

I have a request to insert n lines into a second table based on a number of points existing on a source table.

Ex: Source Table

UserId   Points
  1       2
  2       1
  3       4

Expected Output Final Table: Based on points from source table

UserId   
  1       
  1            
  2       
  3      
  3      
  3       
  3       

In terms of volume, the source table has over 1 million unique records; and taking in consideration the average number of points its estimated that the process would generate over 42 million records on the final table.

I have considered doing in memory but i doubt that it would be more efficient than doing on at the DB level.

I also considered using 2 cursors but i do not know if its the best option because i basically would be doing in line processing.

I would like to know if there is any a better away to approach this chalalnge and if there is any bulk processing function that can be used to accomplish such task.

My DBMS is Oracle 11g

Answer 1

Here's one option; test CTE represents your source data. Query begins at line #6.

SQL> with test (userid, points) as
  2    (select 1, 2 from dual union all
  3     select 2, 1 from dual union all
  4     select 3, 4 from dual
  5    )
  6  select userid
  7  from test cross join
  8    table(cast(multiset(select level from dual
  9                        connect by level <= points
 10                       ) as sys.odcinumberlist));

    USERID
----------
         1
         1
         2
         3
         3
         3
         3

7 rows selected.

SQL>