Home » SQL & PL/SQL » SQL & PL/SQL » Change of column positions..
Change of column positions.. [message #185097] Mon, 31 July 2006 00:36 Go to next message
deepayan
Messages: 51
Registered: December 2005
Member
Dear All,

I have table with certain columns. Now I have added some columns .So they are coming in last position.But I want to bring them in first position.How can I change these column positions..??

--Deepayan
Re: Change of column positions.. [message #185100 is a reply to message #185097] Mon, 31 July 2006 00:40 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Create table2 from table1 selecting columns in order of ur requirment...
Then delete table1...
rename table2 to table1....


Naveen
Re: Change of column positions.. [message #185127 is a reply to message #185100] Mon, 31 July 2006 02:00 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why is it so important to rearrange columns order in a table? The only place which could matter is SQL*Plus or similar tool, so that

SELECT * FROM this_table;

looks fine. But that is true only for limited number of columns, as the output goes to another line and everything gets messed up anyway.

Use of any other tool (Report Builder, Forms Builder, ...) will require you to manually set fields on the screen, so default column order doesn't mean much. At least, from my point of view.
Re: Change of column positions.. [message #185164 is a reply to message #185127] Mon, 31 July 2006 05:35 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

Why is it so important to rearrange columns order in a table? The only place which could matter is SQL*Plus


I would normally have thought that, however, my colleague recently came across a situation where this can be 'important' to a certain extent. The situation was, that a large table (150 columns, many millions of rows) was organised in such a way, that many of the columns held, for the most part, null values. In table a, these columns were at the end of the column list. During etl, the columns were re-arranged into table b, placing a column very near the end, which , for the most part, had values.
Table b grew to nearly 3 times the size of table a due to the fact that, with trailing nulls, no storage space is 'reserved' for the column, however, when a column with values was placed near the end, each of the null columns now stored 1 byte for each row (we are talking 10 million rows, nearly half the columns)
The table b's column order had to be re-arranged to reduce the size. Actually, they changed the etl so that the columns were in the appropriate order again, but the point stands, column order can affect space usage.


Jim
Re: Change of column positions.. [message #185175 is a reply to message #185164] Mon, 31 July 2006 06:53 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I see ... storage benefits never came across my mind. Thank you, Jim!
Re: Change of column positions.. [message #185190 is a reply to message #185097] Mon, 31 July 2006 07:58 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
Thanks experts..... a wholesome of resourceful ideas..
thanks a lot..
Re: Change of column positions.. [message #185192 is a reply to message #185175] Mon, 31 July 2006 08:01 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
I wouldn't have thought of it either but for a serrendipitous error Smile
Previous Topic: date problem
Next Topic: EXCEL UPLOAD
Goto Forum:
  


Current Time: Fri Dec 09 09:37:54 CST 2016

Total time taken to generate the page: 0.11378 seconds