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

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.

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 Tue Apr 30 2002 - 23:11:00 CDT