best way to delete large number of rows
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: best way to delete large number of rows

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Posts
    257

    best way to delete large number of rows

    Hi,

    I have a table containing about 2 million rows. I need to delete about a million of them based on simple criteria, say column x = 1. But I am worry about Oracle's RBS may grow so big that it can't handle it. What is the best way to delete rows in this case? If I have to allocate a big RBS for this purpose, how do I tell it to keep before-image of the deletion in this particular RBS? Or there is a better way to achieve this?

    Thanks,
    Last edited by a128; 01-19-2003 at 08:44 PM.

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    create a new table,
    insert as select into the new table from the old,
    backup the original table,
    drop the original table
    rename the new table to the old table,
    rebuild indexes grants etc on the new table

    rbs is the least of your concerns , mass deletes
    can be a serious performance hit as such an action
    does no reset the highwater mater and subsequent
    scans will scnn up the high water mark even though
    those blocks may be empty.

    steve
    I'm stmontgo and I approve of this message

  3. #3
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    you can delete from the table with logging off.
    here you do not need to worry about rollback segments.
    careful. you won't be able to go back in case any problem.

    eg.

    delete from table1
    where x=y nologging;

    - raja balachandran

  4. #4
    Join Date
    Jan 2002
    Location
    India
    Posts
    105
    delete from table1 where x=y nologging;
    I dont think this will work.
    Good idea is already suggested by stmontgo

    I use PL/SQL loop commiting after every 5000 rows

    Regards

    ---------------------------
    9i OCP
    First post After Becoming Oracle Certified
    A Wise Man Knows How much he doesn't know !!!

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by rajabalachandra
    you can delete from the table with logging off.
    here you do not need to worry about rollback segments.
    careful. you won't be able to go back in case any problem.

    eg.

    delete from table1
    where x=y nologging;

    - raja balachandran
    Wrong! Nologging has no effect on rollback generation. Better delete in small bathces if you do not have sufficient rollback space.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  6. #6
    Join Date
    Jan 2002
    Location
    India
    Posts
    105
    Wrong! Nologging has no effect on rollback generation. Better delete in small bathces if you do not have sufficient rollback space



    Yes 100% True.

    Now Why it will use Rollback Segments is becaz
    READ CONSISTANCY FUNDAMENTALS

    Regards

    Viraj
    ------------
    9i OCP

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Select count(*) into variable from table where ur condition

    variable:=Round((variable)/(max records u think u can delete))+1

    For cnt 1 to variable loop

    delete with ur condition and with rownum < (max records u think u can delete)+1.

    Loop.

    Regards
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    think you want a COMMIT in that loop
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    Select count(*) into variable from table where ur condition

    variable:=Round((variable)/(max records u think u can delete))+1

    For cnt 1 to variable loop

    delete with ur condition and with rownum < (max records u think u can delete)+1.

    Loop.
    You've forgotten to stick a COMMIT inside your loop aftere each batch delete.

    Besides, you can code this much more elegantly without the need for the initial SELECT if instead of FOR LOOP you simply use LOOP with EXIT:
    Code:
    LOOP
      DELETE with ur condition and
             with rownum < (max records u think u can delete);
      EXIT WHEN sql%ROWCOUNT = 0;
      COMMIT;
    END LOOP;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by DaPi
    think you want a COMMIT in that loop
    Thas correct....

    forgot to type...

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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