Re: add a new column into the middle of an existing table

You probably should consider what will actually break if you change the table :-

    storage... if you will then populate the new columns (via an update) then you end up with row migration (i.e. is grown to big to fit back in where it lived in the block and will get moved (leaving behind a forwarding address as rowids dont change). If the table is wide you may then also get row chaining. This will impact performance.

    queries... if you have any hardcoded select * from xxx then you may get more than you wanted.

    existing inserts should be okay as long as the columns are nullable.

Recommend :-
Just add the columns at the end and test everything works afterwards OR
Recreate the table (investigate what it has hanging off it (triggers/fks/indexes/grants etc), export it first.... and then test.

Alun

"stinky" <stankonia_at_stunkitystunk.org> wrote in message news:3CCF3CA7.4070301_at_stunkitystunk.org...
> Daniel Morgan wrote:
>
> >lei wang wrote:
> >
> >>Is there an easy way to add a new column into the middle of an
> >>existing table? Seems to me that you have to create a new table and
> >>copies data from the old table. <Alter table add column> statement
> >>will only adds columns at the end of an existing table.
> >>
> >>I am using Oracle 8.1.7 EE on Solaris 2.7.
> >>
> >>Thanks.
> >>
> >
> >You can not ... but it is equally true that the location of a column in
> >a table is absolutely irrelevant. Stick it at the end and adjust your
> >thinking.
> >
>
> I thought it was recommended to place nullable columns at the end of a
> table. I thought I read that somewhere. Something about saving space
> in the data block.
>
> >
> >
> >Daniel Morgan
> >
>
>
Received on Sat May 18 2002 - 12:04:17 CDT