| commit interval after delete [message #331441] |
Thu, 03 July 2008 05:53  |
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
|
Member |
|
|
HI All,
i have one query regarding delete statement.
suppose i have 10000 records, i want commit interval for the deletion of every 100 records. can anyone help on this. Thanks in Advance.
delete stmt. -- (100 records)
commit; then again delete 100 records then again commit like this.
|
|
|
|
| Re: commit interval after delete [message #331444 is a reply to message #331441] |
Thu, 03 July 2008 05:54  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
| Quote: | i want commit interval for the deletion of every 100 records.
|
No, you don't.
you may think you want it, but you are mistaken.
The only thing it will do is slow down your code, and lead to an increased incidence of ORA-1555 errors in your code and other peoples code that is running at the same time.
|
|
|
|
|
|
| Re: commit interval after delete [message #331451 is a reply to message #331449] |
Thu, 03 July 2008 06:15 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Lets look at this from the other end:
Why do you feel you need to commit every 100 rows?
There are procedural ways of doing it, but they are all slower and more error prone than simply doing a single DELETE.
You can use a FORALL statement.
You can delete sets of rows matching a subquery that returns the 1st 100 rows from the table that match the deletion criteria.
You can step through a cursor and count the number of individual delete statements.
Except in exceptional cases, you shouldn't do any of them.
|
|
|
|
| Re: commit interval after delete [message #331477 is a reply to message #331451] |
Thu, 03 July 2008 09:33 |
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
|
Member |
|
|
hi...
reason I want to have this is, code will delete 1 million records everyday, my procedural code should commit after every 100 records, so that my rollback segment does not overflow..
Please suggest me how can I write procedure code for this...
from
FreakAbhi
|
|
|
|
|
|
| Re: commit interval after delete [message #331483 is a reply to message #331441] |
Thu, 03 July 2008 10:10 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Alternatively, you may loop DELETE statement for given number of rows. Something like loop
delete <table>
where <conditions>
and rownum <= <given number of rows>;
exit when sql%rowcount = 0;
commit;
end loop;
commit;
Also, if you are deleting that many rows, you shall think about faster and more efficient ways to get rid of them, e.g. partition truncate or drop.
Also, commiting after 100 rows is very frequent; I would opt for far greater number, 100000 at least.
|
|
|
|
| Re: commit interval after delete [message #331484 is a reply to message #331477] |
Thu, 03 July 2008 10:10 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| freakabhi wrote on Thu, 03 July 2008 16:33 | reason I want to have this is, code will delete 1 million records everyday
|
| Quote: | suppose i have 10000 records
|
What is it? 10,000 or 1 million?
If it is 1 million, how many rows are there in total in the table?
How do you handle 1 million inserts a day? Also through intermittent committing?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
