I know, I know column reorder in Oracle doesn't exist and the only ways to do it aren't really column re-order, its just creating a new table which resets the explain plans. But unfortunately, i have no choice but to do this, my arguments over the past couple of months against this to our business partners have fallen on deaf ears.
The table that needs to have its columns reordered has 124 columns with 30GBs of data. This table is used by about 100 or so downstream views, functions, packages, etc.
I'm half inclined to just create a new table with the new order, drop the old table and rename the new table to the old tables name, and then watch the world burn as the optimizer starts with explain plans from scratch so i can have my "I told you so" moment while none of our applications work tomorrow.
But I don't think that's really an option unfortunately. So i'm planning to create a new table with the new column order and slowly move the 100 or so dependencies a handful at a time from the old table onto the new table, checking their performance and asking a DBA to force the old explain plans onto the dependent queries. I don't know if that will work perfectly but i"m hoping it would help minimize the risk.
If you were forced to reorder columns of a large, highly used and important table, how would you tackle the problem which minimizes the risk of query performance blowing up? Any ideas would be helpful? Would DBMS_REDEFINITION help at all?
Oracle Version - Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Thanks!