Home » SQL & PL/SQL » SQL & PL/SQL » commit interval after delete
commit interval after delete [message #331441] Thu, 03 July 2008 05:53 Go to next message
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 Go to previous messageGo to next message
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 #331449 is a reply to message #331444] Thu, 03 July 2008 06:09 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
so there is no solution for this.
Re: commit interval after delete [message #331451 is a reply to message #331449] Thu, 03 July 2008 06:15 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #331479 is a reply to message #331477] Thu, 03 July 2008 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
freakabhi wrote on Thu, 03 July 2008 07:33
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


No, I won't give a loaded pistol to a child.
Eliminate the rows with a single DELETE & then COMMIT!
Re: commit interval after delete [message #331483 is a reply to message #331441] Thu, 03 July 2008 10:10 Go to previous messageGo to next message
flyboy
Messages: 1832
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;
But, single DELETE statement is better.
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 Go to previous messageGo to next message
Frank
Messages: 7880
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?
Re: commit interval after delete [message #331485 is a reply to message #331441] Thu, 03 July 2008 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>so that my rollback segment does not overflow..
Frequent intervening COMMIT will greatly increase the probability for ORA-01555, snapshot too old, error!
Re: commit interval after delete [message #331501 is a reply to message #331485] Thu, 03 July 2008 12:10 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2345591157689


Breaking inserts/updates/deletes into manageable chunks to avoid resource limit issues is a common practice. Intermediate commits in ETL is the rule rather than the exception. I avoid intermidiate commits when I have sufficient undo.
Previous Topic: Comparing cursor with null
Next Topic: select statement help?
Goto Forum:
  


Current Time: Sun Dec 04 04:22:14 CST 2016

Total time taken to generate the page: 0.09585 seconds