Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 05 Nov 2009 @ 12:33:47 GMT

  <Prev Next>  
Next> Last>>  

Subj:   UPI not behaving as expected.
From:   Anomy Anom

<-- Anonymously Posted: Thursday, November 05, 2009 07:16 -->

I have a query which have inner joins on many tables, one of the inner join is quite a mystry to me so I just thought to share with you and know if we can resolve this mystry.

A table is have a single-column UPI and this table is being joined with other table on UPI when I see the explain plan, it tells that this particular join be being performed using merge join. Even though there is UPI on this table still this table is being duplicated on all the 400 AMPs and then merge join is performed..due to this duplication this step is taking over 19 min. Here I can not see the advantage of UPI with uptodate stats. Can some one please explain as to why this is happening and can I avoid it? Below is exact copy of explain plan step....

     1) We do an all-AMPs RETRIEVE step from
         in view
        by way of an all-rows
        scan with no residual conditions into Spool 15 (all_amps)
        (compressed columns allowed), which is duplicated on all AMPs.
        Then we do a SORT to order Spool 15 by the hash code of (
        ).  The
        result spool file will not be cached in memory.  The size of
        Spool 15 is estimated with high confidence to be
        3,928,867,200 rows (184,656,758,400 bytes).  The estimated
        time for this step is 19 minutes and 24 seconds.



  <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