Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    2

    Question Unanswered: Delete on 1 table takes 5 hours???

    Oracle 9i 9.2.0.1.0 running on win2K server.

    One table with 50000+ records takes 5 hours to do a simple delete
    DELETE Table
    This table is the foreign key for about 40 other tables. These tables are empty. There is no other activity but this delete going on.

    WHere do I start to look for problems and how can I start to tune it? I'm just looking for someplace to begin.

  2. #2
    Join Date
    May 2003
    Posts
    87

    Re: Delete on 1 table takes 5 hours???

    Did you try truncate command instead of delete ?

    And if you don't have indexes on the foreign keys, it will take definitely long.

    Originally posted by fly42
    Oracle 9i 9.2.0.1.0 running on win2K server.

    One table with 50000+ records takes 5 hours to do a simple delete
    DELETE Table
    This table is the foreign key for about 40 other tables. These tables are empty. There is no other activity but this delete going on.

    WHere do I start to look for problems and how can I start to tune it? I'm just looking for someplace to begin.

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Delete on 1 table takes 5 hours???

    Try

    SQL> Truncate Table tablename;

    A truncate is much faster as it does not write to the rollback segment.

    If this table is populated during a single session and then the records deleted I would suggest using a temp table. This would cause the data to be deleted at the end of the session.

    Originally posted by fly42
    Oracle 9i 9.2.0.1.0 running on win2K server.

    One table with 50000+ records takes 5 hours to do a simple delete
    DELETE Table
    This table is the foreign key for about 40 other tables. These tables are empty. There is no other activity but this delete going on.

    WHere do I start to look for problems and how can I start to tune it? I'm just looking for someplace to begin.

  4. #4
    Join Date
    Jun 2003
    Posts
    2
    Thanks for the tips. I can use Truncate but I was just wondering why it took so long to do this simple delete. I'll check into the indexes also.

    Thanks again.

  5. #5
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    The indexes make a big difference.

    One of the biggest performance hits is probably the rollback segment. Oracle has to populate a rollback segment with every record you are deleting so you can "undo" rollback your changes.

    This is why a truncate is so fast. No rollback.

    Hope that helps.

    Originally posted by fly42
    Thanks for the tips. I can use Truncate but I was just wondering why it took so long to do this simple delete. I'll check into the indexes also.

    Thanks again.

  6. #6
    Join Date
    Jun 2003
    Posts
    2
    Did you have ON DELETE CASCADE option on the parent table. If so oracle will check each of the 40 tables for a corrsponding dependant row to delete. Would be good to index the foreign key as well as the columns used as predictates in the query.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by fly42
    Thanks for the tips. I can use Truncate but I was just wondering why it took so long to do this simple delete. I'll check into the indexes also.

    Thanks again.
    Even though the 40 child tables are empty, Oracle has to check them to see if there are children. If the FK columns in these tables are not indexed, this will require a full table scan. If a child table has previously contained a lot of data that has been deleted (not truncated), then a full table scan will still take some time, because it reads every block that ever has contained data (i.e. up to the "high water mark"). Your timing of 5 hours = 18,000 seconds to delete ~50,000 rows says each delete is taking 18,000/50,000 = 0.36 seconds. That could be caused by a full table scan on even just ONE of the child tables, if it consists of many blocks.

Posting Permissions

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