Super long Delete
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Super long Delete

  1. #1
    Join Date
    Apr 2001
    Posts
    112
    I have a delete that takes xtremely long :

    DELETE FROM temp_det
    WHERE EXISTS (
    SELECT 1
    FROM temp_orders A,
    orig_cap T
    WHERE
    A.DT = temp_det.DT AND
    A.DT = T.DT AND
    A.Prod_ID = temp_det.Prod_ID AND
    T.Prod_ID = temp_det.Prod_ID AND
    T.orig >= temp_det.orig)
    Number of rows

    temp_det 872745 rows
    temp_orders 8444478 rows
    orig_cap 126028 rows

    INDEXES

    temp_orders has 2 indexes
    idxa-- dt
    idxb-- prod_id
    idxc--st,prod_id

    temp_det
    idxd-- dt,prod_id
    idxe--dt

    Is there a way to tune this to reduce the number of
    hours to run this? This process whenever run , runs as the only process allowed in the database. Can We somehow disable the rollback and redo ? Is it a good idea when noone else can access the database to disable redo & rollback?

    Thanks

  2. #2
    Join Date
    Aug 2001
    Posts
    111
    Can you post the number of extents for each table and index
    and also the explain plan for the query.

    Cheers

    Paul
    Performance... Push the envelope!

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    try to create huge rollback segment, let's say INITIAL 20M and NEXT 20M.

    And force that transaction to be able to use that rollback segment by issuing the command below before the delete statement.

    SET TRANSACTION USE ROLLBACK SEGMENT huge_rbs;
    DELETE FROM temp_det......blah....blah. ...blah.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you have to index

    T.orig >= temp_det.orig

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    A composite index on ORIG_CAP(DT, ORIG) will solve your problem. Right now, without any index on ORIG_CAP Oracle must perform full table scan on ORIG_CAP for each row deleted. Also a composite index on TEMP_ORDERS(DT, PROD_ID) would speed things a bit.

    BTW, neither the number of extents nor the large rollback segment assigment are in any way relevant for the performance of this DML.

    [Edited by jmodic on 11-13-2001 at 09:47 AM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857

    Red face

    _________________________________________

    BTW, neither the number of extents nor the large rollback segment assigment are in any way relevant for the performance of this DML.
    _________________________________________
    thanks jurij,
    I didn't pay attention to the sql statement that tansdot posted, there was an indexing problem to begin with.
    For a while I thought it was an INSERT statement.....heheheheh...

    cheers.

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    though delete will still use rollback segments, I believe index should be fix first.

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