Re: Adding a new column

Bruce Bristol wrote:
>
> Hello,
>
> We have 8 Oracle tables which contain anywhere from 14 to 50 million
> rows (most are around 25M).
>
> We need to add a new column, and I know I can do this.
>
> I'm going to have each table unloaded and truncated for a purge process
> we do quarterly.
>
> What will be better for performance? Adding the new column and then
> reloading the data (which doesn't have the new column at the end), or
> reload the data and then add the column?
>

Will you be populating this new column? If most rows will have a null value for the column, then it doesn't matter whether you add it before or after your purge. Adding a new column to a table does not require that the entire table be rewritten, because Oracle does not store trailing null valued columns.

If you will be populating the new column (with an update statement), be sure that the data blocks have enough free space for the update. If there is not enough space the rows will chain to new blocks, and performance will be hurt. In this case populate the new column first, then perform your unload/reload to get rid of the chained rows.

Roger Harris
Oracle DBA
GE Capital
Danbury, CT Received on Thu Feb 06 1997 - 00:00:00 CST