Deletions take a very long time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Deletions take a very long time

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    I have created partitions, re created the indexes, am using a large rollback segment, have set the table for nologging
    and also commiting every thousand records.....

    All these I am doing to delete around 2lakh records from a table.

    What other parameters should I check to make the deletions faster, Now the deletions are taking a very long time and testing our patience.

    Badrianth

  2. #2
    Join Date
    Jan 2001
    Posts
    642
    Forgot to tell you, Deleting even thousand records take as long as 10-14 minutes.

    Here is the procedure ,I use to delete records;



    create or replace procedure del_rec
    as
    county number := 0;
    COUNTY1 NUMBER :=0;
    c_one account_delete_hlp.account_number%type;
    cursor c1 is select account_number from account_delete_hlp;
    begin
    open c1;
    loop
    fetch c1 into c_one;
    exit when c1%notfound;
    delete from acc_partition partition (part02)
    where account_number = c_one;
    county := county + 1;
    if mod(county,1000) = 0 then
    dbms_output.put_line(to_char(county));
    end if;
    if county = 1000 then
    BEGIN
    COUNTY1 := COUNTY1 + COUNTY;
    commit;
    county := 0;
    END;
    end if;
    end loop;
    close c1;
    end;

    Please suggest me a solution;
    Badrinath

  3. #3
    Join Date
    Jan 2001
    Posts
    71
    use truncate instead of delete, much fast

  4. #4
    Join Date
    Jul 2000
    Posts
    243
    first, how many lines do you update?

    second, haveing any index that supports "where account_number = c_one; "? if no, create it, if yes, try to rebuild it.

    therd, check function "mod(county,1000) " (to me it looks like a function) for preformance problems. see if your probrm is there.

    and last, check you cursor, you may find the select statment there to be preforming very bad. check index in your where clouse, rebuld theme if you need.



  5. #5
    Join Date
    Feb 2001
    Posts
    125
    I am not getting

    1. why mod() function has been used,
    while you can use 'if country = 1000'
    It will never cross the 1000 becuase
    u r reset country = 0 when country = 1000;

    2. why are you using country1 ?

    If i am unable to understand please explain.

    P. Soni

  6. #6
    Join Date
    Jan 2001
    Posts
    642
    COUNTY1 JUST TELLME HOW MANY RECORDS HAVE BEEN DELETED SO FAR !!!JUST ANOTHER COUNTER THAT'S IT!

    NBNath

  7. #7
    Join Date
    Jan 2001
    Posts
    642
    I have removed the mod() function and 'am using 'IF' condition.

    Deleting 4000 records seems to take over 2 hours. The deletion happens on a partition of a table.

    In the v$session, I see one session as inactive. Is this causing any problem
    Badrinath

  8. #8
    Join Date
    Oct 2000
    Posts
    123
    I've facing this problem before, mod() really affected not so much based on this condotion, just use counter to record it if you want to replace it. Should you create index on the column in where clause will boost you operation time, of course this experience i got is not for partion table, but at least you can try that. If this not help, probably there is some other things involved.

    Take care

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