Archives of the TeradataForum

Message Posted: Fri, 15 Mar 2013 @ 19:39:51 GMT

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

<-- Anonymously Posted: Friday, March 15, 2013 09:02 -->

Hi All,

I'm running TD13.0 and have encountered more than one occasion where the explain plan has shown wrong results. A similar query below- both the join tables have stats up to date. The problem is that when I join, the larger table is getting redistributed. I also checked the tablesizes and the smaller table is around 0.3 gig and the larger is 1.5 gig.

I find this very perplexing. Am I missing something? Please let me know.

     SELECT AC.ACTVY_BK ,
            AC.EQT_BK ,
            AC.EFFECTIVE_START_DTTM  ,
            AC.EFFECTIVE_END_DTTM,
            EQT.EQT_GRP_CD
        FROM  DSV_SEMA_GEM.V_EQUIPMENT AS EQT
        INNER JOIN  DSV_SEMA_GEM.ACTIVITY_CURRENT AS AC
                 ON AC.EQT_BK = EQT.EQT_BK

Result of explain

     2) Next, we execute the following steps in parallel.
            1) We do an all-AMPs RETRIEVE step from DST_SEMA_GEM.EQUIPMENT
               in view DSV_SEMA_GEM.V_EQUIPMENT by way of an all-rows scan
               with no residual conditions into Spool 4 (all_amps), which is
               built locally on the AMPs.  The size of Spool 4 is estimated
               with high confidence to be 1,482,793 rows (91,933,166 bytes).
               The estimated time for this step is 0.95 seconds.
            2) We do an all-AMPs RETRIEVE step from
               DST_SEMA_GEM.ACTIVITY_CURRENT in view
               DSV_SEMA_GEM.ACTIVITY_CURRENT by way of an all-rows scan with
               no residual conditions into Spool 5 (all_amps), which is
               redistributed by the hash code of (
               DST_SEMA_GEM.ACTIVITY_CURRENT.EQT_BK) to all AMPs.  The size
               of Spool 5 is estimated with high confidence to be 10,814,667
               rows (443,401,347 bytes).  The estimated time for this step
               is 4.02 seconds.

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