Commit after every 1000 row delete from table.
I am trying to delete from a table that has few thousands of rows. I will like to commit the delete after each 1000. How would I specify that in my delete statement?
I appreciate your help.
KC
Printable View
Commit after every 1000 row delete from table.
I am trying to delete from a table that has few thousands of rows. I will like to commit the delete after each 1000. How would I specify that in my delete statement?
I appreciate your help.
KC
with a cursorQuote:
Originally posted by akwete
Commit after every 1000 row delete from table.
I am trying to delete from a table that has few thousands of rows. I will like to commit the delete after each 1000. How would I specify that in my delete statement?
I appreciate your help.
KC
Hi Steven,
I am using delete * from table. I am thinking that there is a way to specify how many to commit.
Can you give me an example using CURSOR?
Thank you.
A few thousand rows is nothing -- just delete them.
I think just doing the straight delete and using cursor are right answers. But here's another one. Find a numerical column that is well distributed with data.
example:
delete from tab1 where MOD(numeric_col_with_diverse_values,3) = 0;
3333 rows deleted
commit;
delete from tab1 where MOD(numeric_col_with_diverse_values,3) = 1;
3333 rows deleted
commit;
delete from tab1 where MOD(numeric_col_with_diverse_values,3) = 2;
3334 rows deleted
commit;
Ya' see what I mean about the numeric column being distributed evenly? You would not want:
9998 rows deleted
1 rows deleted
1 rows deleted
============
I am using delete * from table. I am thinking that there is a way to specify how many to commit.
Can you give me an example using CURSOR?
====================
Do you use delete * ?
Are you using Oracle ?
Tamil
TRUNCATE maybe?
Search for delete_commit procedure in Metalink. It does exactly what you are looking for . It is also very helpfull to have and index on the fields which are used in delete statement because based on frequency of commits and number of rows it can take a long time to do full scans.
Are you trying to delete all rows from the table?
BV1963,
We successfully deleted the rows using the delete_commit procedure found on Metalink (Note:37777.1: delete_commit procedure and
Note:1073480.6: How to Delete Blocks of records Using COMMIT).
Thank you very much.
KC.