Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Robert.

Asked: July 10, 2008 - 3:41 pm UTC

Last updated: July 11, 2008 - 8:16 am UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

I'm using an "ALTER ADD TABLE ADD COLUMN..." command to add multiple columns to a table. Is the order of new columns in the altered table determined by the order in the statement, or is does the database do some optimization, potentially leading to different orders on different machines?

I know that in principle the order shouldn't matter, but I have a situation where it does and I expect the tables to always have the same structure across multiple machines. On most machines where the alteration has been done, the order is as I expect. On one development machine, the order is different. This *might* be because someone manually changed the structure after the alter script ran but I can't determine that.

I think it's an interesting question nonethless.

and Tom said...

the alter table command should be deterministic that way, although it is not documented to be so, so - it could be (but I've never seen it be) different.

I would lean heavily towards "that would be someone messing with the table"

if the order of columns in the describe of a table is relevant to you, I would strongly encourage you to use VIEWS, ignore the tables, just use VIEWS.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ravi, July 11, 2008 - 2:11 am UTC

We are facing the same issue while moving the table structure from UAT to prod it change the order of column in the database and i have written a pl/sql code which will do SCD(slowly changing dimension ) based on the order of columns in the table.So we have again changing the order by deleting the table and creating new one its very difficult to do it always.

Tom do we have any option to change the order without deleting the columns or without deleting the relationship

thanks

Tom Kyte
July 11, 2008 - 8:16 am UTC

rename table to table_tab

create view table as select columns,in,any,order,that,makes,you,happy from table_tab;

use view.


or do the right thing which would be to maintain your OWN METADATA in a format you own, control, can fix, can change, maintain and so on. And have your process be driven off of YOUR metadata.

Internal column order

Wolfgang, July 11, 2008 - 10:32 am UTC

The internal order of the column is also not garanteed when you have a LONG Column in your CREATE TABLE Script.
Oracle will put a LONG column to the end of the table, but will still keep the original triage when you execute a select *
Example:
create table t1 (col1 long, col2 number,col3 varchar2(10));
create table t2 (col1 number, col2 number,col3 varchar2(10));

SQL> select table_name,column_name,column_id, segment_column_id 
 from user_tab_cols 
where table_name IN('T1','T2') order by 1,2;

TABLE_NAME   COLUMN_NAME   COLUMN_ID   SEGMENT_COLUMN_ID
------------ ------------- ----------- -----------------
T1           COL1          1           3
T1           COL2          2           2
T1           COL3          3           1
T2           COL1          1           1
T2           COL2          2           2
T2           COL3          3           3


So a LONG column will change the order of how the row is stored on the block.

Wolfgang
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.