Archives of the TeradataForum
Message Posted: Fri, 15 Mar 2013 @ 19:39:51 GMT
<-- Anonymously Posted: Friday, March 15, 2013 09:02 -->
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|