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

From: Jim Kennedy <kennedy-family_at_attbi.com>
Date: Thu, 02 May 2002 03:01:50 GMT
Message-ID: <y_1A8.90845$CH.12724_at_rwcrnsc52.ops.asp.att.net>


You aren't going to save IO. It has to read the whole block anyway, Oracle does not read a part of a block.
Jim
"D.Y." <dyou98_at_aol.com> wrote in message news:f369a0eb.0205011057.74c79356_at_posting.google.com...
> Pete Sharman <peter.sharman_at_oracle.com> wrote in message
news:<aanpsk01hv1_at_drn.newsguy.com>...
> > In article <3CCF3CA7.4070301_at_stunkitystunk.org>, stinky says...
> > >
> > >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.
> >
> > Years and years ago (I think it was prior to 7.3) we did some testing of
column
> > placement because of the very issue you raised initially - new columns
are added
> > to the end of the row. What we found was placement of a new column
could be
> > important for two reasons:
> >
> > 1. Storage - if you have a null column in the middle of a row, there is
a
> > length indicator for the null column. If the null column is at the end
of the
> > row there is no length indicator. Not a major concern, but there is
some wasted
> > space having null columns in the middle of a row.
> >
> > 2. Performance - when Oracle reads a row for a query it only reads as
far along
> > the row as it needs to satisfy the query. Let's say you had a query
that needed
> > only the first two columns in the row. The rest of the row is not read.
Since
> > mandatory columns can also be queried frequently, this may mean that
when you
> > add a mandatory column that it's useful to add it at the front of the
row.
> >
>
> This makes sense. I would guess queries are still done this way. How do
you
> get to the second column without at least reading the length indicator of
the
> first column if it's a variable length field. People are mostly concerned
with
> I/O and you certainly will save little I/O, if not nothing, whatever the
column
> order is. But if you can save a few CPU cycles without hurting anything
else
> then why not?
>
> > Now I haven't tested this in a long time so the behaviour may be
different now.
> > If it's not, then the way to reorg the table is dependent on the
version. In
> > versions before Oracle9i, you need to recreate the table with the
columns in the
> > right order. In 9i, you can use the DBMS_REDEFINITION package to
reorder the
> > columns while the table remains online.
> >
> > >
> > >>
> > >>
> > >>Daniel Morgan
> > >>
> > >
> > >
> >
> > HTH. Additions and corrections welcome.
> >
> > Pete
> >
> > SELECT standard_disclaimer, witty_remark FROM company_requirements;
Received on Thu May 02 2002 - 05:01:50 CEST

Original text of this message