Team,
For our discussion/requirement , let us consider the scott.emp table .
This table has now 40 lakh records with different empno values(Primary key applies here).
Now we need to update the empno from a sequence . The sequence code goes below:
CREATE SEQUENCE seq_empno MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 START WITH 1001
NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
We are using the below code snippet
declare
v_empno number;
begin
for rec in (select deptno,empno,hiredate from emp order by deptno,empno,hiredate )
loop
select seq_empno.nextval into v_empno from dual;
update emp set empno= v_empno
where empno= rec.empno and deptno = rec.deptno
and trunc(hiredate) = trunc(rec.hiredate);
end loop;
commit;
end;
The update has to be happen base on the order of deptno, empno and hiredate.
But our code is taking a lot of time for the update to be executed . Can someone help me with implementation of bulk update in chunks of data .
please let us know which would be the best option for commit statment …to be in the loop or after the loop
Any code suggestion mostly welcome.
Regards,
Satyam