DB: Logical Reordering of Columns

Applicable to 12c

Following post will help you to logically(not physical) reorder the columns in a table with the help of invisible columns feature in 12c.

Let's say, we have a table TAB1 having 2 columns COL1, COL2.

CREATE TABLE TAB1

  ( COL1 NUMBER,

    COL2 NUMBER);

Now suppose that, we need to add new column COL3 and it should appear in middle of the table.. We could achieve this column reordering without using invisible columns as follows.

• Rename the table TAB1 to something, For Ex: TAB2
• Add new column COL3 to table TAB2, which would appear at the end of the column list.
• Create a view or an editioning view named TAB1 that explicitly selects the columns in the right order
• Revoke the grants on TAB2 and issue those grants on the view TAB1
• Update the scripts(indexes, triggers, constraints) to refer renamed table TAB2

This may help us to make the column seem to appear in the middle of the table, but require lot of efforts. But we can achieve the the logical reordering with minimal efforts using invisible columns feature in 12c as follows.

1. First hide the column COL2 and Add column COL3. At this point, column COL3 will appear to be right after column COL1.

ALTER TABLE TAB1 MODIFY COL2 INVISIBLE;
ALTER TABLE TAB1 ADD COL3 NUMBER;


2. Next unhide column COL2 and it will appear after column COL3 logically.

ALTER TABLE TAB1 MODIFY COL2 VISIBLE;


3. Describe the table TAB1

DESC TAB1

 Name       Type
 —————   ——————
 COL1        NUMBER
 COL3        NUMBER
 COL2        NUMBER

But physically these columns will be stored as COL1, COL2, COL3 on disk.