Archives of the TeradataForum

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

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

<-- 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.



Regards

Anom

 
 
 
 


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