Commit after every 1000 row delete from table

[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

[stmontgo]

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

[akwete]

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.

[slimdave]

A few thousand rows is nothing -- just delete them.

[gopi]

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

[tamilselvan]

============
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

[jmodic]

TRUNCATE maybe?

[BV1963]

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.

[julian]

Are you trying to delete all rows from the table?

[akwete]

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.