Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>  
Next> Last>>  

Subj:   High skew while doing rank
From:   Anomy Anom

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


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

     Select activity_prd_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)


  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?


  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023