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.
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.