Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: add a new column into the middle of an existing table

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

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Wed, 1 May 2002 12:33:11 +1000
Message-ID: <fwIz8.38$su6.212@news.oracle.com>

"stinky" <stankonia_at_stunkitystunk.org> wrote in message news:3CCF3F98.6050902_at_stunkitystunk.org...
> Howard J. Rogers wrote:
>
> >Not in 8i.
> >
> >However:
> >
> >1. why is column order particularly important? For LONGs and LONG RAWs,
it
> >could be, but for pretty much everything else, it's irrelevant.
> >
>
> Why is it important for LONG and LONG RAW ?
>

Because those old (and deprecated) data types are always stored in-line, as part of the row. Since they can be up to 2Gb in length, if you were after a small column's data where that column appeared *after* the LONG or LONG RAW, you would have to wade through the (potentially) 2Gb of data to get to it.

By sticking such columns at the end of the table, access to the earlier small columns can be achieved without that excessive effort.

Since you should really be using LOB data types, it's no longer an issue: above 4K, those data are stored out-of-line, meaning that all that is left within the table row itself is a small pointer to the location of the large LOB. Therefore, getting to the last column(s) of the table when stored after the LOB column is no longer a drama.

Incidentally, I saw a post you made about whether nullable columns should be at the end of the table definition. The answer is: it makes no difference. When a previously null column is updated to receive new data, the existing row is effectively moved in its entirety to the top of a block. That movement (and, potentially, row migration) will occur whether the nullable columns are in the middle of the row or at the end.

Regards
HJR
> >
> >
> >2. You can always add your column onto the end of the table as you've
> >described, and then create a view on that table. When you create the
view,
> >you get to select the columns, and the order in which they are displayed,
> >regardless of what is actually stored in the underlying table.
> >
> >3. In 9i, there is a dbms_redefinition package which allows you to do
> >exactly what you are (plus more, of course!). So if it is absolutely
> >essential that you stick this new column in the middle of the original
> >table, perhaps an upgrade is in order?
> >
> >Regards
> >HJR
> >
> >PS. Don't crosspost.
> >
> >"lei wang" <leiwang_q_at_hotmail.com> wrote in message
> >news:af074b9c.0204301247.65da02f9_at_posting.google.com...
> >
> >>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.
> >>
> >
> >
>
>
Received on Tue Apr 30 2002 - 21:33:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US