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

Thread: How to keep 20% of the table and delete the rest from a 35 million row table

  1. #1
    Join Date
    Oct 2007
    Posts
    17

    How to keep 20% of the table and delete the rest from a 35 million row table

    Oracle info: 9.2.0.7
    Hi all,

    I'm planning to archive our production table which contain 35 million rows. I want to keep only 20% of the current table and move the rest to another archive table. I'm planning to do this by renaming the original table and creating a new table(original table_name) with 20% of the data using (create table as select *).

    Are there any other efficient way to do this?

    Thanks in advance.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Your process will do it just fine.

    Question is... is your affected table partitioned in a way which helps your archiving process?
    If the answer is Yes... partition swithching would do the trick very nicely.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Oct 2007
    Posts
    17
    unfortunately we don't have partitioned tables in our database.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    But you can partition the table while you get rid of 80% of the data. Otherwise, as PAVB said, your process will be fine.

    You could try an append hint with the nologging clause to reduce redo. But that shouldn't matter that much.

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