I have a table with 5 mill. of rows. It has constraints, indexes, triggers... you name it - it has it all.
Of those 5 mill. rows only 1 mill is active and 4 mill. rows are inactive. I need to move those 4 mill. to a history table then remove them from main table leaving only 1 mill of active rows. What is the best and most efficient way to do this please?
I thought about CTAS - create as select, then drop the orig table, then rename the new table into the name of original name table... But this involves recreating constraints, triggers etc... Is there other way to delete a few mill. of rows efficiently? Please advise.
Thank you very much.