Archives of the TeradataForum

Message Posted: Mon, 25 Jul 2005 @ 17:23:38 GMT

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

Bill,

This is hard to explain.

Alter tables can be very fast even on large tables if the right conditions are present. Basically they will be almost instantaneous if the data blocks don't have to be changed.

Here are the conditions that must be met to not alter the blocks.

1) add a varchar column withour a default value

or

add a fixed length column with compression

2) must have extra indicator bits for what is needed. 1 for not null and one or more for compression depending on values compressed.

If column is not null must add default phrase.

The hard part here is knowing if indicator bits are available, you will have to add them up and see if another byte is needed as the bits come 8 at a time. I don't know if EXPLAIN plans change based on indicator bits needed.

An alter table that does not need to alter the data blocks should run in under a minute depending on DBC table activity at the same time.

Some examples:

     Alter table dbase.tablenm
     Add Col1 varchar(30) ;


     Alter table dbaase.tablenam
     Add col1 char(2) compress;

     Alter table dbase.tblanme
     Add col1 char(2) not null default 'TM' compress 'TM';

If your alter table cannot meet these conditions, I would create a new table and load from the old one.

Thanks,

Bob Diehl
Travelcoity.com

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