To Specify the location for New Column in Oracle
15 pts.
0
Q:
To Specify the location for New Column in Oracle
Why it is not possible in Oracle to specify the location for any newly added column. why it always goes to the last. 



I know we can do the same using MySQL. 



Is there any option/chances in the up coming versions of Oracle



Software/Hardware used:
oracle 10g
ASKED: Sep 23 2009  8:50 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
0
105 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Here is a short version of the answer (missing some of the details)

Adding a column to a table in Orcle (or even Db2) will place the column physically at the end of the row. WHY?
Because in order to stick a new column into the middle of an existing row would require every row to be expanded. When a row is expanded, it will not be written back to the same location. So every row in the table would be moved. So now you may be wondering about adding a column to the end of the row, it cannot be rewritten to the same physical location either. But, the newly added column doesn't physically exist until someone puts some data other than the default value into this new column. So only rows that have data other than the default value physically have the new column. An export/import (in Oracle) or a REORG (in DB2) makes the column physically exist in all rows.


----------------- kccrosser

A bigger question is why do you care where the column is placed?

The only time the column order is important is if you are doing a "select *" from the table, which is generally a very poor practice.

If you always enumerate the column names, then the "physical" order of the columns is unimportant.

Consider adding a new column to an existing table. If all the previous queries against that table enumerate the column names in the query, they will continue to work after the column is added. Howevver, any "select *" statement that selects into a defined set of variables will now fail, because the number of columns will not match the number of variables.

We highly discourage the use of "Select *". In addition to creating problems if a table is later modified, often the "select *" query returns extraneous data, which is a waste of resources (CPU, memory, and network).


---------------- zagrim

And if the application just has to use " select * " style, there is of course the possibility to put a view between the actual table and the application. Just order your columns the way you want in the view and redirect your application to use the view instead of the table as the data source. Performance may degrade a bit but there has to be always a downside...
Last Answered: Sep 24 2009  11:13 AM GMT by Zagrim   105 pts.
Latest Contributors: Mrdenny   46735 pts., Kccrosser   1835 pts., Carlosdl   29750 pts., Meandyou   1840 pts.
0
0
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

mrdenny   46735 pts.  |   Sep 24 2009  12:43AM GMT

MySQL may appear to let you do this, but what it is probably doing in the background is creating a new table with the column where you place it, moving all the data into the new table, dropping the old table, then renaming the new name.

Microsoft SQL allows you do to this, by using that exact process. It even shows you that its doing this.

 

Carlosdl   29750 pts.  |   Sep 24 2009  1:08AM GMT

Valuable points, and explanations.

However, Why Oracle doesn’t let you do the same, and “Is there any option/chances in the up coming versions of Oracle” are unanswered questions so far, and that’s why I said “I’m afraid only Oracle could answer these questions”.

 

Meandyou   1840 pts.  |   Sep 24 2009  8:19PM GMT

My favorite answer to questions such as “Why doesn’t company/product XYZ allow …”
is to say that during development this very topic was discussed, they flipped a coin, and it came up heads, and they decided not to …

 
0