CODEWITHSUNDEEP
sqloracle-databasefor-loopparallel-processingsql-insert
Amer
Amer

Reputation: 335

Running Oracle For loop in parallel

I have to following sql for an oracle database on AWS RDS

BEGIN
 FOR user_id_count IN 1..4000 LOOP
   INSERTUSERINTOFINALTABLE(user_id_count);
 END LOOP;
END;

where the INSERTUSERINTOFINALTABLE PROC is defined as follows

INSERT INTO FinalTable (USERID,  ABC,  DEF, ...,XYZ )
 select
  a.USERID,
  b.ABC,
  b.DEF,
  ....
  b.XYZ
from a
left outer join b on a.USERID = b.userid
where a.userid = USER_ID and b.XYZ not in ( select XYZ from c);

The reason why I am not doing it for all the user as a simple insert int is because my data is really big and wanted to make sure I don't run out of memory.

The question is, is there a way to run this in parallel in oracle?

Thanks

Upvotes: 0

Views: 2172

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 157098

There is no point in inserting many rows in parallel. I will explain why.

If the insert statement is the only statement in your loop, the statements won't really run in parallel. That is because the insert statement is atomic.

You can't have two insert statements in the same session, running at the same time, especially when your insert is based on a select statement from another table, which will most likely lock the table for concurrent read actions.

Upvotes: 1

Related Questions