-
I am testing to do a update on emp table .
I added dname column to emp table.
Made emp table to about 500000 rows and trying to do an update so that there is commit for 100 rows ...
Update is happening ...I can select dname .But it is going in a loop .
I have set serveroutput on
but then also 'Commit in progress' is not getting displayed at all
why ???
Declare
cnt number := 0 ;
Cursor loc is
select b.dname dname ,B.deptno deptno
from emp a, dept b
where a.deptno=b.deptno
for update of a.dname ;
Begin
For c1 in loc
loop
update emp set dname = C1.dname
where deptno = C1.deptno ;
cnt := cnt + 1 ;
If cnt = 100 then
cnt := 0 ;
commit ;
dbms_output.put_line ('Commit in progress') ;
end if ;
end loop ;
end ;
/
[Edited by Subha on 02-26-2002 at 02:31 PM]
-
You need to use the WHERE CURRENT OF or ROWID to make sure that you are updating the current cursor record with the dbname you are getting from the cursor.
[Edited by kris109 on 02-26-2002 at 04:51 PM]
-
All outputs made by dbms_output are stored on server side and sent to client just after finishing the job. Thus you cannot see progress of that script, just final report.
For watching progress you could use a log table and insert a row in it instead of putting a line by dbms_output. Then you can watch this log table from another session.
BTW, updating big table in this fashion is very slow. The task you are doing is better and faster done with a single or several UPDATE statements.
I also suppose that iside the loop you are updating all emp rows with that deptno, not just current row. Then you don't do 500000 updates but 500000*rows_of_deptno.
Use WHERE CURRENT OF or ROWID as kris109 proposed.
I'm not sure, but with this approach you can also get a "snapshot too old error".
HTH,
Ales
-
For the dname pulled by the cursor, the rows in the emp table are being updated atleast half a million times. I am sure this is not what the poster wants.
[Edited by kris109 on 02-27-2002 at 04:12 PM]
-
first the problem is that rollback segment is not sufficient.
first u assign the temporary rollback segment to the current transaction as
SET TRANSACTION USE ROLLBACK SEGMENT
then run that procedure.
it will work according to ur requirement.
the problem is that cursor select statement returns 500000*
means if dept table contains 5 rows then that stament returns 2500000 rows.
inside the loop for each row, it searches 500000 rows in emp table whether deptno is matching or not.
so for 100 rows it searches 500000 * 100 rows which is a very very huge transaction.
so the problem is with rollback segments.
that rollback segment should be very large in size
Vth Regards,
Bhaskar akkala....
