How to change column order when using SELECT *

articles: 

Not infrequently, I see questions in forums such as "how can I add a column to a table, in between two existing columns?" The answers given are always (a) you can't, and (b) why do you want to?
I do not intend to address (b) in any detail. The reason is usually that the developer is using * in the column projection list of his SELECTs and/or not specifying a column list in his INSERTs. There are many reasons why these are poor programming practice.
But let us assume that for some legitimate reason, it is necessary to insert a new column between existing ones. Here is a 12c technique for doing it. Consider the table SCOTT.DEPT:

orclz> desc dept
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- ----------------------------------------
 DEPTNO                                                      NOT NULL NUMBER(2)
 DNAME                                                                VARCHAR2(14)
 LOC                                                                  VARCHAR2(13)

orclz>
and a requirement to add a new column TOTSAL in between the columns DNAME and LOC. Add the column, and see where it is:
orclz>
orclz> alter table dept add (totsal number);

Table altered.

orclz> desc dept;
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- ----------------------------------------
 DEPTNO                                                      NOT NULL NUMBER(2)
 DNAME                                                                VARCHAR2(14)
 LOC                                                                  VARCHAR2(13)
 TOTSAL                                                               NUMBER

orclz> select * from dept;

    DEPTNO DNAME          LOC               TOTSAL
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

orclz>
and the new column is at the end. Now a little 12c hack: make LOC invisible, then visible again:
orclz>
orclz> alter table dept modify (loc invisible);

Table altered.

orclz> select * from dept;

    DEPTNO DNAME              TOTSAL
---------- -------------- ----------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

orclz> alter table dept modify (loc visible);

Table altered.

orclz> select * from dept;

    DEPTNO DNAME              TOTSAL LOC
---------- -------------- ---------- -------------
        10 ACCOUNTING                NEW YORK
        20 RESEARCH                  DALLAS
        30 SALES                     CHICAGO
        40 OPERATIONS                BOSTON

orclz>
How about that? I've adusted the column order!
Let's try to work out what may be happening:
orclz>
orclz> select column_name,hidden_column,column_id from user_tab_cols where table_name='DEPT';

COLUMN_NAME                    HID  COLUMN_ID
------------------------------ --- ----------
DEPTNO                         NO           1
DNAME                          NO           2
LOC                            NO           4
TOTSAL                         NO           3

orclz> desc dept
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- ----------------------------------------
 DEPTNO                                                      NOT NULL NUMBER(2)
 DNAME                                                                VARCHAR2(14)
 TOTSAL                                                               NUMBER
 LOC                                                                  VARCHAR2(13)

orclz> alter table dept modify (dname invisible);

Table altered.

orclz> desc dept
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- ----------------------------------------
 DEPTNO                                                      NOT NULL NUMBER(2)
 TOTSAL                                                               NUMBER
 LOC                                                                  VARCHAR2(13)

orclz> select column_name,hidden_column,column_id from user_tab_cols where table_name='DEPT';

COLUMN_NAME                    HID  COLUMN_ID
------------------------------ --- ----------
TOTSAL                         NO           2
LOC                            NO           3
DNAME                          YES
DEPTNO                         NO           1

orclz> alter table dept modify (dname visible);

Table altered.

orclz> desc dept
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- ----------------------------------------
 DEPTNO                                                      NOT NULL NUMBER(2)
 TOTSAL                                                               NUMBER
 LOC                                                                  VARCHAR2(13)
 DNAME                                                                VARCHAR2(14)

orclz> select column_name,hidden_column,column_id from user_tab_cols where table_name='DEPT';

COLUMN_NAME                    HID  COLUMN_ID
------------------------------ --- ----------
TOTSAL                         NO           2
LOC                            NO           3
DNAME                          NO           4
DEPTNO                         NO           1

orclz> select * from dept;

    DEPTNO     TOTSAL LOC           DNAME
---------- ---------- ------------- --------------
        10            NEW YORK      ACCOUNTING
        20            DALLAS        RESEARCH
        30            CHICAGO       SALES
        40            BOSTON        OPERATIONS

orclz>
It would seem that marking a column invisible sets its COLUMN_ID to NULL, and adjusts the COLUMN_ID of all other columns acordingly. Then when making it visible it is assigned the next available number, and the column sequence is determined accordingly.
How much use is this trick? Well, it could be a quick get-you-out-of-trouble if you have to change column ordering. A better (and supported) solution would be to cover the table with a view. And the real solution is not to use SELECT * but rather to specify a column projection list.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

Comments

Thank you for this nice tip.
Let me add, that this could be useful with very 'wide' tables, when the number of columns is already bigger than 255 and you need to add a column that will be used often in WHERE clauses.
Moving it to top IDs can increase dramatically the performance and eliminate multiple table scans.