Running Oracle For loop in parallel

Question

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

In theory, you could use DBMS_JOB to create a lot of jobs, but I'm not exactly sure if this is a good idea in your case. — Erich Kitzmueller
– Erich Kitzmueller, Commented Jun 26, 2014 at 13:43

Patrick Hofman · Accepted Answer · 2014-06-26 13:45:05Z

1

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.

answered Jun 26, 2014 at 13:45

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Patrick Hofman Over a year ago

@Amer: It is very unlikely you will gain anything by running in parallel. Try to optimize your sql and indexes. That might help more.

Amer Over a year ago

My sql is very simple... do you think there is anything I can do to it? any changes that I can do on the source or target tables that could help?

Patrick Hofman Over a year ago

You tagged it Oracle. Are you targeting MySQL or Oracle? Both have indexes by the way.

Patrick Hofman Over a year ago

Okay, then I misunderstood. Still, indexes can help a lot in certain circumstances. An index on b.userid, b.XYZ would help a lot already.

Amer Over a year ago

Thanks Patrick... my oracle knowledge is very limited... any specific type of index would be useful?

Collectives™ on Stack Overflow

Running Oracle For loop in parallel

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related