Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    New Delhi
    Posts
    7

    Exclamation Unanswered: multiple deletes

    I am having a problem.
    I have delete statements for multiple tables carrying huge amount of data each.This is all written in a procedure.Another procedure calls this procedure and if no exception occurs in deleting from all these tables, it finally does the commit, otherwise just rolls it back.This is taking good amount of time.Can u suggest me some ways so that i can make it the fastest.
    I am trying to use delete /*parallel(table_name,4) from table_name;, but it is not giving me any benefits.

    Kindly answer it , as its important.

    regards,
    Sharad

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Since you are deleting all rows, can you not use TRUNCATE TABLE instead?

  3. #3
    Join Date
    Jan 2004
    Location
    New Delhi
    Posts
    7

    Arrow multiple delete(reply to Tony Andrews

    Hi Tony,
    I obviously can't use truncate as i have made it quite clear in my question itself that there might be condition when i have to rollback,if exception occurs.Truncate will autocommit it.
    regards
    Sharad

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    TRUNCATE is much faster than DELETE, but it doesn't allow you to rollback. You either act cautiously & slowly with DELETE or sacrifice caution for the speed of TRUNCATE.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by sharad.nisheeth
    Hi Tony,
    I obviously can't use truncate as i have made it quite clear in my question itself that there might be condition when i have to rollback,if exception occurs.Truncate will autocommit it.
    regards
    Sharad
    Well, pardon me for being so stupid!

    I find it hard to envisage a situation where I wanted to delete all rows from a table, but might want to roll back. All rows sounds like a pretty major purge. I therefore asked if you had considered TRUNCATE TABLE. Apparently you have, and have rejected it. No need to fly off the handle!

    What is the situation you are in that requires this total yet transactional purge of the data?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •