RATE THIS ANSWER
0
Click to Vote:
- 0
0
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.
