Archives of the TeradataForum
Message Posted: Thu, 03 Jun 2004 @ 13:38:20 GMT
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
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|