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

Quote:

---

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

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.

hi

what is ur criteria to delete
u want to delete some records or all records
if u r deleting by
delete from where
then what is the problem
u can write simple script file
write delete command and then commit;

or deleteing by any procedure/forms

bhatia_dba

All times are GMT -4. The time now is 10:03 AM.