Select columns ending in product join

Analytics
Teradata Employee

Re: Select columns ending in product join

I tried it and you were right in your previous answer when you thought it won't change the behaviour of the optimizer.

 

I understand now why it is acting like this so I'm going to try to find a workaround.

 

If you got an idea, feel free to share it here !

 

Thanks 

Ambassador

Re: Select columns ending in product join

Hi,

Can you share the explain plan after having collected stats?

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Select columns ending in product join

Sure, here it is :

Explain SELECT 
P.*
    FROM SOC.SCHD P
        INNER JOIN SOC.EXPSR_AGMT EXPO
            ON EXPO.AGMT_ID = P.AGMT_ID
            AND expo.STTM_DT = P.STTM_DT 
            AND expo.STTM_TYPE_CD = P.STTM_TYPE_CD 
            AND expo.ENT_ID = P.ENT_ID 
    WHERE  expo.EXPSR_NATR_CD = 'P'
        AND P.STTM_DT = DATE'2017-08-31'
        AND P.STTM_TYPE_CD = 'M'
        AND P.ENT_ID IN (3285,3281,3282);

  1) First, we lock SOC.EXPO for read on a reserved
     RowHash in 5 partitions to prevent global deadlock.
  2) Next, we lock SOC.P for read on a reserved RowHash
     in 5 partitions to prevent global deadlock.
  3) We lock SOC.EXPO for read on 5 partitions, and we
     lock SOC.P for read on 5 partitions.
  4) We do an all-AMPs RETRIEVE step from 3 partitions of
     SOC.EXPO with a condition of (
     "(SOC.EXPO.END_TT = TIMESTAMP '9999-12-31
     23:59:59.999999+00:00') AND
     ((SOC.EXPSR_AGMT.BEGIN_TT < TIMESTAMP '9999-12-31
     23:59:59.999999+00:00') AND ((SOC.EXPO.ENT_ID IN
     (3281 TO 3282 ,
3285 )) AND ((SOC.EXPO.STTM_DT =
     DATE '2017-08-31') AND ((SOC.EXPO.EXPSR_NATR_CD =
     'P') AND (SOC.EXPO.STTM_TYPE_CD = 'M')))))") into
     Spool 2 (all_amps), which is duplicated on all AMPs.  Then we do a
     SORT to partition by rowkey.  The input table will not be cached
     in memory, but it is eligible for synchronized scanning.  The size
     of Spool 2 is estimated with no confidence to be 40,345,840 rows (
     3,953,892,320 bytes).  The estimated time for this step is 3.30
     seconds.
  5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
     all-rows scan, which is joined to 3 partitions of
     SOC.P with a condition of (
     "(SOC.P.END_TT = TIMESTAMP '9999-12-31
     23:59:59.999999+00:00') AND ((SOC.SCHD.BEGIN_TT <
     TIMESTAMP '9999-12-31 23:59:59.999999+00:00') AND
     ((SOC.P.STTM_DT = DATE '2017-08-31') AND
     ((SOC.P.ENT_ID IN (3281 TO 3282 ,
3285 )) AND
     (SOC.P.STTM_TYPE_CD = 'M'))))").  Spool 2 and
     SOC.P are joined using a product join, with a join
     condition of ("(AGMT_ID = SOC.P.AGMT_ID) AND
     ((STTM_DT = SOC.P.STTM_DT) AND ((STTM_TYPE_CD =
     SOC.P.STTM_TYPE_CD) AND (ENT_ID =
     SOC.P.ENT_ID )))") enhanced by dynamic partition
     elimination.  The input table SOC.P will not be
     cached in memory, but it is eligible for synchronized scanning.
     The result goes into Spool 1 (group_amps), which is built locally
     on the AMPs.  The size of Spool 1 is estimated with no confidence
     to be 19,813,202 rows (12,343,624,846 bytes).  The estimated time
     for this step is 53.95 seconds.
  6) 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 total estimated time is 57.25 seconds.

Tags (1)