Archives of the TeradataForum

Message Posted: Wed, 07 Dec 2005 @ 18:54:36 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

If you have the room for a duplicate table create a new table with the new column sizes as this is the fastest solution as it avoids locking the table (except for rename) also avoids potential rollbacks. Don't forget to take advantage of compression where appropriate.

If you don't have enough space to create a new table, then you can add a column as others suggested. I would also suggest when you add a new column that is fixed width (char, date, time, any numeric datatypes) to use compression. If you do this no data has to be moved to insert the new column. This is much faster and avoids a potential rollback.

Example,

     Alter table databasename.tablename
     Add newcolumnname char(10) compress;

This compresses nulls which all are nulls. If not null then must add a default.

     Alter table databasename.tablename
     Add newcolumnname integer default'0' compress 0;

NOTE: you would also want to add other common values to the COMPRESS as appropriate.

Don't forget to drop secondary indexes that will be changed to avoid long runs. This applies to loading a new table also.

Thanks,

Bob Diehl
Travelocity.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>