Archives of the TeradataForum

Message Posted: Thu, 04 Oct 2001 @ 16:03:51 GMT

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

Ruth,

I have read several of the responses and agree with the consensus that you are sane. An insert\select sounds like the best bet.

I think it was Mark who pointed out that this implies you need free space equal to your largest table. I would think you would need this anyway. For example, you need the free space to support redistribution or spooling. If you chose the alternative method of alter table, I would think you would use a similar amount of your available space during journaling.

If you didn't have the space, you could start writing NULL to those columns you intend to drop when you updated the table. You could then hide the columns in the base view of the table. If you are aging data off the table, the populated columns would eventually drop off leaving only NULL filled columns. Of course if your goal is to reclaim the space these columns take on this big table, this doesn't work, but if your goal is to retire the column then this would allow you to do so over time.

Just to share an experience, we 'accidentally' did an update table on our largest table a few years ago only to run out of space on the box. This caused a lot of problems as it would lock up, restart and try to rollback. It could not roll back because it didn't have the space and things just kept getting worse. Running out of space while Inserting into an empty table would just fail. It would not journal and would not try to rollback.

I wonder if there is any internal gains by doing the insert select. For example, does the data get blocked or hashed more efficiently as it is rebuilt during the insert select. comments?

Good luck

Jim

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