Commit outside the cursor

[amoldiego]

Suppose I create one for loop for update statement . That updates arround 1000 rows .
So please tell me where i should place commit .
Inside the loop or outside the loop .
and how that improve the performance.

Please reply

[debasisdas]

place the commit inside the loop.

[gintsp]

Suppose I create one for loop for update statement . That updates arround 1000 rows .
So please tell me where i should place commit .
Inside the loop or outside the loop .
and how that improve the performance.

Please reply

The question however remains - cannot you just issue a simple update statement? It would be the fastest. If you can do something in pure SQL do it - it would be faster, easier to understand and maintain.

Gints Plivna
[Edit: Sorry Gints, links not allowed in technical questions]

[amoldiego]

Thanks for Ur reply ...
Could u plz tell me how it will improve the performance if i palce commit inside the loop ..

Thanks

[amoldiego]

I have tried both posibilities ie commit inside the loop and outside the loop ..

I have one table emptest with 8214 rows...
i ve created one cursor for update sal of emptest and place commit inside the loop ..

declare
cursor c1 is select empno from emptest ;
begin
for r1 in c1
loop
update emptest set sal =5000 where empno=r1.empno and rownum<=200;
commit; ----commit inside the loop
end loop;
end;

That code took arround Elapsed: 00:02:57.03 to execute ..
Then i placed commit outside the loop and fired the code

The total time for execution is Elapsed: 00:03:17.01

But how it will improve the performance by placing commit inside the loop ...
what happens internally in oracle when u place commit inside the loop ..

Please reply

[gintsp]

I have tried both posibilities ie commit inside the loop and outside the loop ..

I have one table emptest with 8214 rows...
i ve created one cursor for update sal of emptest and place commit inside the loop ..

declare
cursor c1 is select empno from emptest ;
begin
for r1 in c1
loop
update emptest set sal =5000 where empno=r1.empno and rownum<=200;
commit; ----commit inside the loop
end loop;
end;

That code took arround Elapsed: 00:02:57.03 to execute ..
Then i placed commit outside the loop and fired the code

The total time for execution is Elapsed: 00:03:17.01

But how it will improve the performance by placing commit inside the loop ...
what happens internally in oracle when u place commit inside the loop ..

Please reply

WHY you cannot just
update emptest set sal =5000?????????? ???????

Have you measured the time for this statement?

Gints Plivna
[Edit: Sorry Gints, links not allowed in technical questions]

[amol10]

I dont want to use simple update statement my requirement is to write a PL/sql code for that ...so please tell me which will be more effective inside the loop or outside the loop.


thanks

[debasisdas]

I dont want to use simple update statement my requirement is to write a PL/sql code for that ...so please tell me which will be more effective inside the loop or outside the loop.

thanks

Commit must be inside the loop.

[ellenraju]

Suppose I create one for loop for update statement . That updates arround 1000 rows .
So please tell me where i should place commit .
Inside the loop or outside the loop .
and how that improve the performance.

Please reply

If you simply asks where to put the commit, it's better put out side the cursor, becasue frequent commit gives performace problem and lot of I/O will happen.
You mensioned number of updates but you did not mension on what type of objects whether updation on smaller table or very large.......,
1. Iif updates are very small data or normal table use above or
2. You can use inside the cursor to commit for every specific nuber of loops...for example handle parameter that every 10 loops one commit.

[amitpatel66]

If you simply asks where to put the commit, it's better put out side the cursor, becasue frequent commit gives performace problem and lot of I/O will happen.
You mensioned number of updates but you did not mension on what type of objects whether updation on smaller table or very large.......,
1. Iif updates are very small data or normal table use above or
2. You can use inside the cursor to commit for every specific nuber of loops...for example handle parameter that every 10 loops one commit.

Make use of FORALL to improve performance as shown below

[CODE=oracle]
forall r1 in c1
update emptest set sal =5000 where empno=r1.empno and rownum<=200;
commit;
[/CODE]