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…

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your response...

Discuss This Question: 6 &nbspReplies

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

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

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.

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".

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 ...

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.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

Share this item with your network:

To follow this tag...

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy