Archives of the TeradataForum

Message Posted: Thu, 04 Oct 2001 @ 14:31:17 GMT

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

I have enjoyed hearing the merits of insert/select into an empty table. This is clearly the way to go. I have do this with a 1.5 billion row table. However at the time I did not have enough space to do the operation. Specifically I did not have enough space as described by Mark Landry.

By way of background, the machine had enough space to do an insert/select of maybe 20% of the table into an empty table. Part of the prime index was telephone number. With analysis, two digits of the phone number represent 1% of the table. Selecting 00 thru 19 is then 20% of the rows. Here was the moment of greatest concern, the original 20% had to be deleted to free up database space and the system was too large to archive for a restore.

The original 20% insert/select, commit, delete, and commit operations were followed with 5% of the same. This picked up the remaining 80% of the table. So with numerous queries, the table was copied and done so by reusing database space as it progressed. This took the whole weekend. We shut down user access and ETL. Be real cautious, clean work habits and serious planning is required.

Clearly this is the hard way to go, but with Teradata this can be done. Buy stock in EMC? maybe

Further gossip.

Last Friday, a major client used alter table to add 4 columns to a 400 million row table on a 24 node 5150. Good grief, each alter table took 3 hours and a few locks were applied to DBC causing the entire system to lock out users. After 6 hours they then did the insert/select (they had the space). During the final 6 hours, the system was fully functional. INSERT/SELECT is the way to go.

Regards to all

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