-
Commit after every 1000 row delete from table
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
-
Re: Commit after every 1000 row delete from table
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
with a cursor
I'm stmontgo and I approve of this message
-
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
-
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
One, who thinks that the other one who thinks that know and does not know, does not know either!
-
Are you trying to delete all rows from the table?
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|