How to change column order when using SELECT *
Submitted by John Watson on Sun, 2016-10-02 02:57
Let's try to work out what may be happening:
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
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
»
- John Watson's blog
- Log in to post comments
Comments
Nice tip
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.
Thanks
Thanks for sharing the guide to change column order when using SELECT. I will try to make follow these step to change.
Great Work
Amazing to Thank You