Archives of the TeradataForum

Message Posted: Wed, 26 May 2004 @ 14:59:57 GMT

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

Hi Prasanna:

Without knowing anything about your query, there are two things you need to investigate:

1. Redistribution of rows to build the table. Since you did not specify a PRIMARY INDEX clause in your CREATE TABLE the first column becomes a NUPI and data rows are distributed to the AMPs accordingly.

2. SET table is probably the default for your new table (Teradata mode vs ANSI mode). The SET definition does not allow duplicate rows and Teradata must enforce this at the time you are populating your table. Since #1 tells us that the first column is a NUPI and there cannot be any duplicate rows, then everytime you insert a row with the same value in the NUPI column, Teradata must check all the other columns of these NUPI dups to make sure that at least one is different. By the time you have inserted 100 NUPI dups, you have done: 99+98+97+.....+5+4+2+1 (thousands of) duplicate row comparisons. It will eat your lunch and not even say thank you afterwards.

The most common cure for this is to declare at least one other column as unique (an SI). However, since this is a Volatile table in SPOOL, there can be no SI definition.

If you need the first column to be be a NUPI, then the next best solution is to declare a UNIQUE PRIMARY INDEX. If this is not possible then you can make your volatile table a MULTISET table. This works if you know there are no duplicates and therefore you cannot have duplicates. Does your SELECT produce dups? If so, your select can contain a DISTINCT or GROUP BY to eliminate them. This of course will add a little processing time at CREATE TABLE time (but much less than you seem to be experiencing now - if this is indeed caused by dup row checks). If you need to delay the elimination of dups, I guess you could do the DISTINCT or GROUP BY on the resultant table before using it. This works on the concept that you can pay now or you pay later.

I hope this helps you find a solution.

Regards,

Michael Larkins
Certified Teradata Master

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