DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Delete Problem in a big table

  1. #1
    Join Date
    Nov 1999
    Posts
    226
    Hi

    I have a table called SHOPPER which is accessed by my first page of the web site. It actually checks whether the user has been to the iste before or not. When a user comes he may or may not register the status of which is shown in one column as Y or N .

    This table is 2.9 million rows and I wish to delete appx 1.1 million rows. The problem i sthis is my PROD database and when i did a delete yesterday for about 20,000 rows the webiste cannot be accessed during that delete .

    is there a way I can delete without locking the table or any other way in which I can delete these rows without affecting the website.

    Thanks

  2. #2
    Join Date
    Jan 2001
    Posts
    36
    It sounds like you need to pick an off-peak time to run
    your deletes. (Do you have off-peak ?)

    Because you are not deleting the entire
    table, you CANNOT USE TRUNCATE, which deletes all rows in
    the table very quickly.

    It sounds like you need to write a PLSQL procedure/package
    with a cursor loop, to delete the rows in small quantities at a
    time, and then commits.
    Try and tune the where clause of the delete statement to access the rows by rowid.

  3. #3
    Join Date
    Jun 2000
    Location
    Toronto, ON, Canada
    Posts
    50
    If you delete based on a criteria (like date, for example) why don't you partition the table as to truncate a specific partition instead of deleting. This will take a lot less time than deletion because rollback segment is not used an it doesn't lock the entire table.

  4. #4
    Join Date
    Aug 2000
    Posts
    163

    Cool

    Mark_Woz method should work.
    As alternative, if you have time and resources you can
    1) export the table into a temp table,
    2)delete records from the temp table,
    3)rename your production table to something else,
    4) rename temp table to a production table name.
    5)Drop a production table.
    However, I wouldn't drop it right away untill I was absolutely sure I've 100% correct results during my delete. I think it would be a good idea to keep your production table for a while either as a db object or a dump file.
    If you use scenario described above there will be absolute minimum down time for your users.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    1. Create a temporary table with the key values from SHOPPER that you want to delete. (Assuming key_value is your PK)

    create table delkeys as select key_value from shopper
    where (the criteria you want to delete)
    /

    2. Write a PL/SQL Loop that deletes the keys one by one, commits every 1000 records, and then sleeps for 100 seconds. (To allow other updates to happen. Using _ as a space place holder).
    declare
    ___i integer;
    begin
    ___i:=0;
    ___for x in (select key_value from delkeys) loop
    ______i:=i+1;
    ______delete from shopper where key_value = x.key_value;
    ______if mod(i,1000) = 0 then
    _________commit work;
    _________dbms_lock.sleep(100);
    ______end if;
    ___end loop;
    ___commit work; -- commit the last partial batch
    end;
    Jeff Hunter

  6. #6
    Join Date
    Jun 2000
    Location
    Conway,AR,USA
    Posts
    29
    Hi,
    you can try this thing.
    i. Create an index on the column you are using as a criteria to delete records in the table.
    ii. Delete records from the table for every (1000-5000) rows.
    Soumya
    still learning

  7. #7
    Join Date
    Nov 1999
    Posts
    226
    Hi marist

    I wrote a PL/SQL block and it deletes 100 rows at a time . Just one problem not getting back the control back of screen . May be you guys can help

    declare
    id number := 0;
    begin
    select count(*) into id from puneet;
    loop
    delete from shopper where shfrnbr in (select shfrnbr from puneet where rownum < 100);
    delete from puneet where rownum < 100;
    commit;
    id := id - 100;
    EXIT when id =0;
    end loop ;
    end;

  8. #8
    Join Date
    Aug 2000
    Posts
    194
    change "EXIT when id =0;" to "EXIT when id <= 0;" and try.


    "EXIT WHEN ID Less than or equal to 0 "
    (in case the less than symbol is not displaying.

  9. #9
    Join Date
    Jun 2000
    Posts
    417
    I would go with Jeff's solution. If you need to delete 1.1million rows in a specific amount of time you can adjust how long it sleeps for if 100 secs is too long, or increase the batch size to slightly larger.

    Then you can just truncate the delkeys table for the next time you need to insert your keys into it.

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