Tuning delete
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Tuning delete

  1. #1
    Join Date
    Nov 2000
    Posts
    48
    Hi Everyone.

    I need urgent help from you guyes.

    I am trying to delete from one of the table and it is expected to delete millions of rows everytime.
    The SQL , I am using is as below.

    delete from xyz where time_key
    in (select time_key from dss_dims_time where period_year = 2000) AND rownum < 10001

    I am trying to delete 10000 rows at a time , in order to avoid blowing up the rollback segment.
    I am thinking of writing a PL/SQL procedure in which a cursor having all rowids matching for the condition and deleting one by one in a loop and checking the count , if count is = 10000 then commit it. Pl let me know your ideas and suggestion as soon as possible.

    Thanks in advance.

    Rajesh

  2. #2
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    I think you'll be better off writing a PL/SQL procedure using collections (VARRAY, PL/SQL Tables..etc..) and using "Balk Binding" will speed up thing a lot.

  3. #3
    Join Date
    Nov 2000
    Posts
    48
    Thanks for the prompt reply..

    What is Balk Binding?

    Rajesh

  4. #4
    Join Date
    Oct 2000
    Posts
    21
    How about using the TRUNCATE command. This is fast. The only thing different about truncate is that rollback segments are not used. The data is deleted for good.

  5. #5
    Join Date
    Feb 2001
    Posts
    163

    Have you considered Partitioning

    Have you considered partitioning your table based on a range of the key values. ( may be year in your case)

    that way you can drop the old partition whenever you dont need it.

    Of course there are many gotchas regarding the administration of partitioned tables.

    dba

  6. #6
    Join Date
    Dec 2000
    Posts
    23
    hai,
    here is the small pl/sql script to delte the records with intermediary commits.

    declare
    x number := 0;
    begin
    for i in 1..5
    loop
    x := x+500;
    delete from dept where rownum < x;
    commit;
    end loop;
    end;
    /

    note:
    that proc only 5 times in loop, i.e. if in ur table there are 2500 rows are there, then it will be delete 5 times, at each time it deletes 500 rows. If assume 3000 rows are there then still in ur table 500 records existed,i.e., its deletes only 2500 rows. so according to ur rows u may increase the loop capacity.

    If loop 1..6 then all rows are existed, vice versa..

    good luck
    bye

    sridhar garige

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