Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Are records being updated at expected pace?

vpolasaApr 26 2025 — edited Apr 26 2025

I have a table of 500 million records with 5 partitions (with row movement enabled) on column: STATUS, with each partition holding 100 million records.
I'm running an update using a FORALL loop with limit of 10,000 to update STATUS (to move records to another partition), with commit in the loop.
Expected rows to be updated are 140 million.
The table has 12 columns (either number or varchar datatype) and 4 indexes.

It's currently updating at a rate of 40,000 records/minute. By this estimation, it would take 46 hours.

Is this expected behavior? I think the performance can be improved further, but I'm unable to find another approach.

Are there any DB parameters I can check with our DBAs to make this more efficient?

Working on Oracle 19c Production. Window 10

DECLARE
   	TYPE emp_array_type IS TABLE OF emp.emp_num%type INDEX BY BINARY_INTEGER;

   	emp_array_object emp_array_type;
   	fetch_size NUMBER := 10000;

   	CURSOR emp_cursor IS
   	SELECT emp_num from emp_num_temp;
BEGIN
	OPEN emp_cursor;
	LOOP
		FETCH emp_cursor BULK COLLECT
		INTO emp_array_object LIMIT fetch_size;

		FORALL I IN 1 .. emp_array_object.count SAVE EXCEPTIONS
		UPDATE emp
		SET status_num = 46,
		userid = 1,
		Lastmod = sysdate WHERE emp_num = emp_array_object (I) ;

		COMMIT;
		EXIT WHEN emp_cursor%notfound;
		END LOOP;
	CLOSE emp cursor;
END;
This post has been answered by Paulzip on Apr 27 2025
Jump to Answer
Comments
Post Details
Added on Apr 26 2025
5 comments
190 views