Results 1 to 13 of 13

Thread: Delete Hints?

  1. #1
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201

    Unanswered: Delete Hints?

    I have a delete that takes too long.

    I need to use delete I want to know if there is a hint to not write the rollback
    as a rollback would not be feasible in the process that I have.

    will nologging help the speed of my delete?

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    no chance of stopping rollback generation or nologging unless your database is not in archive log mode.

    Could it be its not finding the rows you want to delete very quickly i.e. maybe it needs an index? Or maybe you have too many indexes on the table so its generating lots of extra redo for the indexes.

    Alan

  3. #3
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Darn

    1 index on table on the row i am to select my deletable rows.
    not a FK reference


  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by rbackmann
    I have a delete that takes too long.

    I need to use delete I want to know if there is a hint to not write the rollback
    as a rollback would not be feasible in the process that I have.

    will nologging help the speed of my delete?
    No, and no. Altering the table to NOLOGGING only affects direct path inserts.

    This is the kind of thing partitioning is good for.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    How many records are you deleting? does it use the index for your statement?

    Also are there any triggers on the table?

    Alan

  6. #6
    Join Date
    Jul 2005
    Location
    jakarta
    Posts
    21
    If youre deleting one row at a time perhaps you forgot to put a COMMIT statement after every delete. this is a small error but it usually end up in record lock contention issues.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Duul, I'm afraid that this would be a good way to run into the SNAPSHOT TOO OLD error. Wouldn't it?

    Rbackmann, you said you need to use delete. However, would it be possible to TRUNCATE table instead?

  8. #8
    Join Date
    Jul 2005
    Location
    jakarta
    Posts
    21
    Littlefoot - youre right, that would happen, i hadnt thought of that.

    how large is your table? is it partitioned? if it is, cant you delete by partition?

  9. #9
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    To All who have responded.

    Table size 100+ million
    Delete Size 20+ million

    Table not partioned

    Delete is bases on DATE which has a index on it.

    This is a good canidate to partion however table already created and no time to rewrite porcess. On a daily basis the delete is OKAY about 100K records. The process I am dealing with is a historical rerun.

    This is a problem with the database design and/or the users changing thier minds on what is needed. Just going to have to use a bigger hammer to get this done.

  10. #10
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    How many records per date?

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You can still modify the table to have partations without having to change the code. The table would still be referenced the same way, only it will have multiple partation tables.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Beil
    I assume that you mean drop and recreate the table with partitions.

    I don't think that a table can be modified to add partitions where partitions never existed. If a table can be modified to add partitions could you supply me with a link so that I can leverage this in the future.

    Thanks in advance.

    I will look into partioning however with accessing 20% of the table I don't think that partitioning will help.

    I finally got my records deleted. Used a bigger hammer.

    Thanks to all

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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