Archives of the TeradataForum

Message Posted: Thu, 09 Aug 2007 @ 09:22:07 GMT


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

Subj:		High skew while doing rank

From:		Anomy Anom

<-- Anonymously Posted: Wednesday, August 08, 2007 20:34 -->

Hi,

The following SQL has poor performance due to the high skew.

     Select activity_prd_id ,
        site_id,
        user_id,
        activity_count ,
        rank() over (partition by  activity_prd_id , site_id
                     order by activity_count desc ) activity_count_rank
        from tb_test

The PI of table tb_test is (USER_ID, SITE_ID, ACTIVITY_PRD_ID)

Explain:

`Explanation --------------------------------------------------`
	`1)`	`First, we lock a distinct support_scratch."pseudo table" for read on a RowHash to prevent global deadlock for support_scratch.tb_test.`
	`2)`	`Next, we lock support_scratch.tb_test for read.`
	`3)`	`We do an all-AMPs STAT FUNCTION step from support_scratch.tb_test by way of an all-rows scan with no residual conditions into Spool 5 (Last Use), which is assumed to be redistributed by value to all AMPs. The result rows are put into Spool 1 (group_amps), which is built locally on the AMPs.`
	`4)`	`Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.`
	`->`	`The contents of Spool 1 are sent back to the user as the result of statement 1.`

The stats info is like below:

     Date          Time        Unique Values    Column Names
     07/08/02     00:29:54       8,966,211      USER_ID
     07/08/02     00:29:52              15      SITE_ID
     07/08/02     00:29:51               1      ACTIVITY_PRD_ID
     07/08/02     02:05:12           1,421      activity_count
     07/08/06     18:47:13       9,719,467      USER_ID, SITE_ID, ACTIVITY_PRD_ID
     07/08/02     00:36:10              15      SITE_ID, ACTIVITY_PRD_ID

We have more than 1000 AMPs, so you can see that the table has very few values of activity_prd_id , site_id and activity_count as well.

Is there anybody who can give us some suggestions on this?

Thanks


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

Attachments

Library

Quick Reference