can we alter the sequence of columns in a table?

[yenni143]

Hi all,
can we alter the sequence of columns already defined in a table ?
table emp has empno,ename,loc

like empno,loc,ename ?


if i have to drop a column, does it have to be empty ?

thanks

[Mr.Hanky]

I am no expert but i think you have to re-create the table and then insert the data. At least this is one way of doing it.
MH

[yenni143]

I think this should be good instead of dropping,recreating etc.
for changing the column sequences.


table A (1,2,3)

create table b (3,2,1) as select (3,2,1) from A

[amar]

How about creating a view to achieve the same results?

-amar

[pv_sriram]

Instead of just creating a view we can go for materialised view...

--Sriram.

[jmodic]

Originally posted by pv_sriram
Instead of just creating a view we can go for materialised view...

--Sriram.

And for what purpose, may I ask?

[puneet]

Not in my knowledge in the same table


regards

[chrisrlong]

Originally posted by jmodic

Originally posted by pv_sriram
Instead of just creating a view we can go for materialised view...

--Sriram.

And for what purpose, may I ask?

I'd take it another step further... why worry about the physical order of the columns at all? That is almost as bad as worrying about the physical order of the rows. It is irrelevant. Now, don't get me wrong - If you are interested simply because you're anal retentive (as am I) and like your db to match your model, then that's fine, as long as you understand that the order is *functionally* irrelevant. If the order of the columns *does* functionally affect you, then that means the you probably have INSERT statements without column lists and therefore the physical order is messing up your SQL. In that case - *fix the SQL*, if at all possible.


- Chris