| how often is recomended to use commit in a loop [message #20195] |
Mon, 06 May 2002 08:20  |
Juan Delfin
Messages: 2
Registered: May 2002
|
Junior Member |
|
|
I need to know how often is recommended to make a commit when you are in
a loop
In this simple example, i need to find the best x for become the best and
quickly load of one table, and for optimize the rollback segments space
i:=0;
while i<100000 loop
insert into emp (i,to_char(i));
if i =x then
commit;
end if
i:=i+1;
end loop;
|
|
|
|
| Re: how often is recomended to use commit in a loop [message #20218 is a reply to message #20195] |
Tue, 07 May 2002 06:58  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
If you are trying to load data into a table in bulk, you should consider using SQL*Loader.
Otherwise, I wouldn't say it makes a massive amount of difference. Commit's take pretty much the same amount of time regardless of how much data there is to commit.
|
|
|
|
| Re: how often is recomended to use commit in a loop [message #20236 is a reply to message #20195] |
Wed, 08 May 2002 08:11 |
sridhar
Messages: 119
Registered: December 2001
|
Senior Member |
|
|
Two things,
1. First of all in your code you are commiting only once irrespective of what 'x' value is,
i:=0;
while i<100000 loop
insert into emp (i,to_char(i));
if i =x then
commit;
end if
i:=i+1;
end loop;
You should add i := 0; line after commit;
2. How often to commit? It really depends on the size of RBS(rollback segment), number of processes running at that time in db AND the size of your table. I would commit as per mod(number_of_rows_in_table, 1000).
Thx,
SriDHAR
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
