Update only 1000 records at a time in oracle

Question

I am pretty new to oracle. My current scenario is I need to add a NOT NULL column and update its value to 'EE'. This is done using below script.

ALTER TABLE CSA.CSA_CALL_AUDIT ADD CSA_CALL_TYPE varchar2(10);
update CSA.CSA_CALL_AUDIT set CSA_CALL_TYPE = 'EE';
ALTER table CSA.CSA_CALL_AUDIT modify CSA_CALL_TYPE not null;

My requirement is to update 1000 records only at a time. In total the table has 39000 records.

39000 records is not a large number of records. Just use a single UPDATE statement. — APC, Mar 14 '17 at 16:44

score 0 · Answer 1 · answered Mar 14 '17 at 17:20

IF EE is a default value this is the most performative way of making this change:

ALTER TABLE CSA.CSA_CALL_AUDIT ADD CSA_CALL_TYPE varchar2(10)
      default 'EE' not null;

Oracle does some magic in the data dictionary without touching the rows at all. (At least in 11g and later, not sure whether they introduced this enhancement earlier.)

But anyway, 39000 records is not a large number of records. If you need to issue an actual UPDATE just use a single statement. It won't take long.

Update only 1000 records at a time in oracle

1 Answers1