Oracle bulk insert new records n times depending on source table-CodePudding

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

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

CodePudding user response：

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>