Archives of the TeradataForum

Message Posted: Thu, 03 Jun 2004 @ 13:38:20 GMT

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

Naveen Ram Prasanna Kambhoji wrote:

     CREATE VOLATILE TABLE TMP_AD_SEGMENT_2 as
          (SELECT row_number() over(order by 1) as Seqno,
......
         )WITH DATA PRIMARY INDEX(Seqno)
         ON COMMIT PRESERVE ROWS;

"order by 1" causes the intermediate spool used to create the SeqNo to be located on a single AMP (probably AMP 0). That AMP has a huge and extremly skewed spool and uses a single CPU for 100% for 15 hours.

Just use any column with lots of distinct values in "order by".

And because row_number is unique modify the PI to "UNIQUE PRIMARY INDEX(Seqno)".

But it looks as SeqNo is created only for distribution, do you really use it in Where or Joins?

If not, remove it and use a different PI, e.g. all

     UNIQUE PRIMARY INDEX
     (MarketID, DateID, DaypartCode, CurrentCompanyID, AdvertiserID,
       DistributorID, ProgramID, CreativeID, ClassificationID, AttributeID)

or any combination providing a good distribution as NUPI

Or just use a RANDOM(-2147483648,2147483647) as SeqNo:

This will be not unique, but almost... And much faster, because there's no Stat Function Step

Dieter

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