-
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.
-
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.
-
unfortunately we don't have partitioned tables in our database.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|