Update in parallel mode

Change overview and monitoring

alter table mytable parallel 5;

-- As system:
select pid, status, server_name from v$px_process;


My request for change:

Hi,

Since we are upgrading to v3 for many of our customers, so far Customer_A and Customer_2 and Customer_Big in progress.
One thing clearly stood out was that the script "rel_2_0_4_field_answers_upgrade.sql" took around 1hr for Customer_B, 6hr for Customer_A and a massive 12hr for Customer_Big.

I would like to propose a change to this script (hopefully it will speed up the script without changing any biz-logic), due my lack of knowledge of the application and data model, I am not 100% sure if my change impacts any business logic.

Can someone please take this proposal forward and see if we can go ahead with this, extensive testing required.

Since yesterday, I have done two test runs with Customer_Big (slim db from last Friday, note 7.5 million rows on field_answers), both showed similar results.

Old script took: 4 hour (or 3hr 55min)
New script took: 30 minutes

Please allocate a developer to look into this suggestion, ideally the one who wrote the original script.
For your information this script was our biggest blocker for the Customer_Big migration.

Proposed New Script (using parallel execution):

ALTER TABLE "FIELD_ANSWERS" ADD "ID" VARCHAR2(20);
ALTER TABLE "FIELD_ANSWERS" ADD "TYPE" VARCHAR2(10);

alter table field_answers parallel 5;

UPDATE field_answers set
id='FN' || lpad(keygen('FN'),8,'0'),
type=decode(fk_app_profile,null,decode(fk_hire_profile,null,decode(fk_requestid,null,decode(fk_profile,null,'SAVED_PS','P'),'R'),'PSH'),'PSA')
WHERE id is null;

alter table field_answers parallel 1;


Current Live Script:

ALTER TABLE "FIELD_ANSWERS" ADD "ID" VARCHAR2(20);
ALTER TABLE "FIELD_ANSWERS" ADD "TYPE" VARCHAR2(10);

DECLARE
CURSOR c1 IS SELECT rowid, decode(fk_app_profile,null,decode(fk_hire_profile,null,decode(fk_requestid,null,decode(fk_profile,null,'SAVED_PS','P'),'R'),'PSH'),'PSA') as fatype FROM field_answers where id is null;
faid varchar2(10);

BEGIN
FOR i IN c1 LOOP
BEGIN
faid:='FN' || lpad(keygen('FN'),8,'0');
EXECUTE IMMEDIATE 'UPDATE field_answers set id=''' || faid || ''', type=''' || i.fatype || ''' WHERE rowid = ''' || i.rowid || '''';
END;
END LOOP;

END;
/