Commit after every 1000 row delete from table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Commit after every 1000 row delete from table

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    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

  3. #3
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    A few thousand rows is nothing -- just delete them.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ============
    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

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    TRUNCATE maybe?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Nov 2001
    Posts
    335
    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!

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Are you trying to delete all rows from the table?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    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
  •  



Click Here to Expand Forum to Full Width