Hello Team,
First of all, thanks for all the good work you are doing.
Request your help with a query related to re-sequencing of table columns using invisible columns. Is it safe to change order of columns in a production environment, following the method described in the following link ?
https://connor-mcdonald.com/2013/07/22/12c-invisible-columns/ We tested it and are not able to find anything unusual. However, any particular "gotchas" we should lookout for?
I know that ideally order of table columns should not matter. However, in our situation, codebase can have legacy code that don't use column names in insert statements.
Pasted below is the detailed scenario on how/why we are planning to use this.
Thanks,
A
----------------------------------------------
Our requirement is to encrypt a column in existing tables in PROD ENVT. These tables can have hundreds of millions of rows. This task has be done during a down time window that is not large enough.
In order to achieve this, we are trying to do as much work as possible out side the downtime window.
Our plan is to add an invisible column to the tables. Data from the original column will be encrypted and stored into these invisible columns. This can be done outside the downtime window and will not affect the day to day operations. We also have a mechanism to identify and handle delta in the original column.
The only task pending for the downtime will be to move values from the invisible column to the original column. In order to complete it in the short downtime window, We will make the invisible column visible and will swap it's name with the original column. The redundant original column can then be dropped.
This approach works fine except that the order of the columns change. The encrypted column now appears as the last column in the table. Ideally, the order should not matter. However, these tables are used by some applications that have legacy code that inserts without specifying the column name.
We are exploring if we can add the new column at the position of the original column.
The biggest gotcha by far is breaking select *s and inserts with unspecified column names. But as you intend to use this to re-instate the current order hopefully that's a non-issue.
As the physical order of columns no longer match the logic order, you could end up with different space usage. For example, if all the columns at the end of a row are null, they consume no space. If you put a non-null value at the end, they will use a byte to store the null. If the current blocks are mostly full, you might also end up with lots of row chaining. Whether either of these have a noticeable effect depends on your current design.
That said, in this situation, I'd consider either of two different approaches: Edition-based Redefinition (EBR) or DBMS_redefinition.
These both allow you to change the column with little/no downtime.
If you use EBR you'll be able to do all future schema changes with zero downtime. But it requires some setup work - you need to enable the schema and create editioning views. So if you need a tactical solution, DBMS_redefinition will be easier in the short term.
EBR
https://www.oracle.com/technetwork/database/features/availability/edition-based-redefinition-1-133045.pdf DBMS_redefinition
https://oracle-base.com/articles/9i/high-availability-enhancements-9i https://oracle-base.com/articles/10g/online-table-redefinition-enhancements-10gr1