DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: delete 40million rows

  1. #1
    Join Date
    Mar 2001
    Posts
    287

    delete 40million rows

    I have a table with 80 million rows of data. I need to delete about 40 millions. I can do either

    1. set a huge rollback segment and delete 40millions at once

    or

    2. run a procedure and delete 10000 rows at a time

    Which is faster?

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    Re: delete 40million rows

    Originally posted by dba_admin
    I have a table with 80 million rows of data. I need to delete about 40 millions. I can do either

    1. set a huge rollback segment and delete 40millions at once
    Faster but unreliable.


    2. run a procedure and delete 10000 rows at a time
    Slow but reliable.

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Dec 2000
    Posts
    126
    how about

    1. create new table as select * from exiting table
    where records are the one you wnat to keep.
    2. drop existing table.
    3. rename new table to existing table.

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by culonbu
    how about

    1. create new table as select * from exiting table
    where records are the one you wnat to keep.
    2. drop existing table.
    3. rename new table to existing table.
    Big headache if FK Constraints are there....

    best is 2nd option wat u thought of - DBA_ADMIN..

    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"

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Abhay

    How about disbaling the constraints and then doin somethin like this

    create a table

    insert /*+append*/ into new_table
    select * from old table the rows you wanna kep

    truncate old table


    rename new_table to old table

    enable constraints onec again

    regards
    Hrishy

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Hrishy:

    If it were only 1 FK associated with it then its okie...but in Datawarehousing projects u will have hell lots of tables linked with so many other tables and the table in question in turn referencing many other tables...then it wud not be wise..

    rather delete records and put a exception part...and commit records for every 100000 records..

    but yes if ther are no constraints, then ur method wud be good...

    ahh now there it comes, if the table is Index organised....u then have headache of building indexes again...

    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"

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by hrishy
    Hi Abhay


    truncate old table


    rename new_table to old table

    regards
    Hrishy
    It shud have been Drop old table rather than Truncate...
    Other wise rename wud error.

    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
    Jan 2001
    Posts
    2,828
    Hi Abhay

    tahst very thoughtful ..of you..one practical insight into datawarehousing..:-)

    regards
    Hrishy

  9. #9
    Join Date
    Mar 2001
    Posts
    287
    I actually did:

    1. drop all indexes and constraints.
    2. delete 10 millions rows at a time
    3. rebuild indexes with parallel/nologging option

    It works for me.

  10. #10
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    I take it you made sure you didn't drop any constraints that had a CASCADE ON DELETE setting? Otherwise, you could have some orphans on child tables.

    - Tony.

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