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

From: Pablo Sanchez <pablo_at_dev.null>
Date: Wed, 1 May 2002 13:19:53 -0600
Message-ID: <tdXz8.47$VQ6.119884@news.uswest.net>

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3CCF162A.70435A5E_at_exesolutions.com...
> 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.

According to the Ixora News - April 2002, emperical analysis shows that you're incorrect Daniel:

---

A call to order
The order of columns in a table can have an impact on performance. There are two factors to consider.
Firstly, Oracle rows are stored as a row header followed by column data. The row header contains a flag byte, lock byte and column count, then for each column there is a column length followed by the column data. To access the value of any column in a row, Oracle has to first examine the length bytes of all the preceding columns. This is a very quick and efficient operation, but it is done with such frequency that it nevertheless does have an impact on performance.

....

This demonstrates that it took more than 10% longer to access the 10th column in this table than it did to access the 1st column. The principle is simple. Place frequently accessed columns early in the table column order. Remember that column values will normally be accessed more frequently for WHERE clause predicate evaluation, than for select-list expression evaluation. However, column values that appear in important driving predicates may be accessed relatively infrequently if the table is consistently accessed by rowid via an index on that column. In particular, primary key columns are seldom the most intensively accessed table columns, and should not normally be first in the table column order.

--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
Available for short-term and long-term contracts

Received on Wed May 01 2002 - 14:19:53 CDT