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

In article <f369a0eb.0205011057.74c79356_at_posting.google.com>, dyou98_at_aol.com says...
>
>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?

The one thing I forgot to add on to this last night was that there were lots of things I'd tune with a badly performing database before worrying about reordering tables to match queries (which might be impossible anyway if you have queries with different requirements).

>
>>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;

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Wed May 01 2002 - 14:25:13 CDT