To Specify the location for New Column in Oracle

20 pts.
Oracle 10g
Oracle Table
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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…

Discuss This Question: 6  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Denny Cherry
    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.
    69,055 pointsBadges:
  • carlosdl
    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".
    85,250 pointsBadges:
  • Meandyou
    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 ...
    5,220 pointsBadges:
  • DagnerMouse
    That's just not true. If you're writing COBOL code that gets run through the Pro*COBOL precompiler, and you're using VARCHAR2 or LOB data types, and you do an INSERT, and your column order as spec'd in the COBOL does not match EXACTLY the column ids of your Oracle table definition, the INSERT will flat out fail. So how in the hell do we get around this, eh? Nobody seems to know, but the problem clearly exists.
    10 pointsBadges:
  • carlosdl
    I don't see how the last comments relate to the original question, which was about adding new columns to a table (not about inserting new rows).
    85,250 pointsBadges:
  • Kccrosser
    DangerMouse - the same comment about "select *" applies in reverse for Insert statements. Instead of using
    insert into tablename values (...valuelist...)
    you should always enumerate the inserted column names, like
    insert into tablename (...columnnamelist...) values (...valuelist...)
    Then, as long as any new columns are not specified as Non-Null, your old Insert statements will still work. Using positional assumptions about database table columns is not a recommended practice with any database.
    3,830 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: