Archives of the TeradataForum

Message Posted: Tue, 19 Mar 2002 @ 18:48:40 GMT

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

ALTER TABLE does not Journal and it does not make a copy of the table. ALTER goes through the table block by block and cylinder by cylinder. It uses a special checkpoint mechanism so that it can continue from the point of failure if a failure occurs during the ALTER, rather than rolling back the changes. ALTER requires one cylinder worth of space per AMP (a couple MB) in addition to the size of the base table to do its work. It is very space efficient.

On a standalone system, using V2R4.1 and a 4850 (700mhz) platform (obviously your mileage will vary on other platforms, no significant SW changes in this area recently):

Add column to a table with 100 byte rows ALTERS approximately 200,000 rows/node/second (20MB/node/sec).

Add column to a table with 500 byte rows ALTERS approximately 50,000 rows/node/second (24MB/node/sec).

This is a fair amount faster than the equivalent Fastload, even with the cost/time for the export. It even beats empty table INSERT SELECT.

The only reason to not use ALTER instead of making a copy or export/load is to avoid having the table locked for the elapsed time of the ALTER. If you can afford to have the table locked for a period, then ALTER is the right tool to use.

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