Select columns ending in product join

Analytics
Teradata Employee

Select columns ending in product join

Hi,

 In a query, when I'm doing a SELECT *, the optimizer takes a plan without product join but when I specifically SELECT some fields then I get a product join.

Every fields used in the join have statistics collected on it.

With DIAGNOSTIC NOPRODJOIN ON FOR SESSION, the query is working perfectly but it's, of course, not a solution for a production environment.

 

If someone knows a work around to select some specific fields without falling for this product join, I'll be happy to hear it ?

 

Thanks


Accepted Solutions
Senior Supporter

Re: Select columns ending in product join

I guess @DaveWellman is on the right track.

 

The different plans are related to the size of the answer set and spool files.

 

1. The product join SQL is estimating less time and therefore is considered by the optimizer as the better plan.

2. The main reason for the different plan is that p.* doesn't fetch any data from the other table -> the other table spool file will only hold the join columns -> significant less IO.

(3,960,877,760 bytes) + (9,453,881,087 bytes)
vs.
(484,500,226 bytes) + (27,667,799,975 bytes) + (24,416,203,321 bytes)

 

It is worth to consider stats on the relevant columns but I don't think this might not change the behaviour.

Product joins are not always bad...
 

1 ACCEPTED SOLUTION
12 REPLIES
Senior Apprentice

Re: Select columns ending in product join

Hi,

Can you share an example of what you're seeing?

The sql, ddl and explain plans for both.

Cheers,

Dave

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

Re: Select columns ending in product join

 This query is the one giving the following plan with a product join

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)

Explain plan with the product join 

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,417,120 rows (
     3,960,877,760 bytes).  The estimated time for this step is 3.31
     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 15,174,769 rows (9,453,881,087 bytes).  The estimated time
     for this step is 51.32 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 54.63 seconds.

 

Second query with the SELECT * not resulting in a product join

 

SELECT 
*
    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)

 

 

Explain plan without product join : 

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 execute the following steps in parallel.
       1) 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) fanned out into 12 hash join partitions, which
          is built locally on the AMPs.  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 505,214 rows (484,500,226 bytes).  The
          estimated time for this step is 0.77 seconds.
       2) We do an all-AMPs RETRIEVE step from 3 partitions of
          SOC.P with a condition of ("(NOT
          (SOC.P.AGMT_ID IS NULL )) AND
          ((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')))))") into Spool 3
          (all_amps) fanned out into 12 hash join partitions, which is
          redistributed by the hash code of (
          SOC.P.AGMT_ID) to all AMPs.  The input table
          will not be cached in memory, but it is eligible for
          synchronized scanning.  The size of Spool 3 is estimated with
          no confidence to be 48,117,913 rows (27,667,799,975 bytes).
          The estimated time for this step is 1 minute and 12 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 Spool 3 (Last Use) by way of an
     all-rows scan.  Spool 2 and Spool 3 are joined using a hash join
     of 12 partitions, with a join condition of ("(AGMT_ID = AGMT_ID)
     AND ((STTM_DT = STTM_DT) AND ((STTM_TYPE_CD = STTM_TYPE_CD) AND
     (ENT_ID = ENT_ID )))").  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 15,174,769 rows (
     24,416,203,321 bytes).  The estimated time for this step is 42.98
     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 1 minute and 55 seconds.

 

We are using temporal table (only transaction time), that's why we got END_TT and BEGIN_TT in the where condition.

Here is the two DDL used in this query : 

CREATE MULTISET TABLE SOC.SCHD ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      SCHD_ID VARCHAR(150) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      HOST_SCHD_ID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      SCHD_STRT_DT DATE FORMAT 'YY/MM/DD',
      SCHD_END_DT DATE FORMAT 'YY/MM/DD',
      SCHD_NUM INTEGER,
      SCHD_NB INTEGER,
      AGMT_ID VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
      AGMT_SRC_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      HOST_AGMT_ID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      HOST_AGMT_NUM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      HOST_SUB_AGMT_NUM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      NON_POSTNG_MGR_ENT_ID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      EXTR_AGMT_ID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      SCHD_MATURTY_DT DATE FORMAT 'YY/MM/DD',
      OPRTN_LEG_TYPE_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      AMRTZN_TYPE_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      RATE_TYPE_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      FLOATG_RATE_BCHMK_INDX_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      CUST_RATE DECIMAL(18,12),
      BEF_SCHD_OUTST_CPTL_AMT DECIMAL(22,4),
      SCHD_AMRTZD_CPTL_AMT DECIMAL(22,4),
      CPTL_CURY_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      SCHD_INT_AMT DECIMAL(22,4),
      INT_CURY_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      FINC_APLCTN_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      HOST_PRTY_NUM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      STTM_DT DATE FORMAT 'YY/MM/DD' NOT NULL,
      STTM_TYPE_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      POSTNG_ENT_ID INTEGER NOT NULL,
      ENT_ID INTEGER NOT NULL,
      FLOW_TYPE_CD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      FLOW_SCOPE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      SOURCE_SYSTEM VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
      STATUS VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      LINE_NUMBER INTEGER,
      FILE_ID INTEGER,
      JOB_ID INTEGER,
      BEGIN_TT TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      END_TT TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      PERIOD FOR PERIOD_TT  (BEGIN_TT, END_TT) AS TRANSACTIONTIME)
PRIMARY INDEX ( SCHD_ID )
PARTITION BY ( CASE_N(
END_TT =  TIMESTAMP '9999-12-31 23:59:59.999999+00:00',
NO CASE),RANGE_N(STTM_DT  BETWEEN DATE '2016-01-01' AND DATE '2999-01-01' EACH INTERVAL '1' MONTH ),
RANGE_N(ENT_ID  BETWEEN 0  AND 99999  EACH 1 ) );

CREATE MULTISET TABLE SOC.EXPSR_AGMT ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      AGMT_ID VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      EXPSR_SBTYPE_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      EXPSR_NATR_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      RISK_TYPE_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      FAC_AMT DECIMAL(22,4),
      SRY_LVL_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      SYNDN_IND CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      SCURTZN_IND CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      BSL_II_APRCH_TYPE_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      RWA_ENGN_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      GRR_ENGN_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      BSL_PTFOL_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      POSBL_NUMTR_DEDCTN_IND CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      UNDRLY_INVSTMT_PROD_IND CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      GRNLT_IND CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      LAST_PRT_ACQ_DT DATE FORMAT 'YY/MM/DD',
      ATACHMT_PNT_VAL DECIMAL(16,12),
      DTACHMENT_PNT_VAL DECIMAL(16,12),
      RSDUL_MATURTY_VAL DECIMAL(11,4),
      AGMT_RISK_OFF_BAL_SHET_IND CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      CENTRLZD_CMPSTN_IND CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      IMPARD_ASSET_IND CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      ORIGNL_PRBTY_DFLT_RATE DECIMAL(18,12),
      ORIGNL_RTAIL_RISK_GRADE_VAL VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
      ORIGNL_RTG_AGNCY_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      ORIGNL_EXTR_CORP_RISK_GRADE_VAL VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
      ORIGNL_INTRNL_CORP_RISK_GRADE_VAL VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
      ORIGNL_RISK_RTG_METH_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      ORIGNL_RTG_MODL_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      ORIGNL_GRADE_SEGMT_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      ORIGNL_RISK_GRADE_VAL VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
      STRC_TRANSPARNCY_IND CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      GRPG_ID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      OPRTN_TYPE_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      CR_OBJT_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      AGMT_HIERY_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      CNFRMTN_IND CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      SPCFC_FAC_IND CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      DRWG_DEADLN_DT DATE FORMAT 'YY/MM/DD',
      AGMT_NXT_RVSN_DT DATE FORMAT 'YY/MM/DD',
      INITATR_ENT_ID INTEGER,
      FAC_AUTHZD_CURY_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      EXPSR_MAXIMAL_DURTN_NUM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      DFLT_LIQUITY_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      AUTHZD_TSACTN_RTG_VAL VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
      EXPSR_CALCN_METH_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      CVA_CALCN_METH_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      RISK_LVL_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      GRADE_CTGY_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      PRBTY_DFLT_RATE DECIMAL(18,12),
      CVRD_OPRTN_RISK_CTGY_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      OMP_LOCL_CRITRN_1_VAL VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
      OMP_LOCL_CRITRN_2_VAL VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
      STTM_DT DATE FORMAT 'YY/MM/DD' NOT NULL,
      STTM_TYPE_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      POSTNG_ENT_ID INTEGER NOT NULL,
      ENT_ID INTEGER NOT NULL,
      FLOW_TYPE_CD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      FLOW_SCOPE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      SOURCE_SYSTEM VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
      STATUS VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      LINE_NUMBER INTEGER,
      FILE_ID INTEGER,
      JOB_ID INTEGER,
      BEGIN_TT TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      END_TT TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      PERIOD FOR PERIOD_TT  (BEGIN_TT, END_TT) AS TRANSACTIONTIME)
PRIMARY INDEX ( AGMT_ID )
PARTITION BY ( CASE_N(
END_TT =  TIMESTAMP '9999-12-31 23:59:59.999999+00:00',
NO CASE),RANGE_N(STTM_DT  BETWEEN DATE '2016-01-01' AND DATE '2999-01-01' EACH INTERVAL '1' MONTH ),
RANGE_N(ENT_ID  BETWEEN 0  AND 99999  EACH 1 ) );
Senior Supporter

Re: Select columns ending in product join

(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')

is indicating that you are accessing at least one view. 

So please share the view definitions as well.

 

you might run

show
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)

to get all DDLs involved in this SQL...

 

Senior Supporter

Re: Select columns ending in product join

and can you share the exact two sqls which resulted in the different plans please

Senior Apprentice

Re: Select columns ending in product join

Hi,

Thanks for that.

Based on what you've said so far the only difference between the two queries is "SELECT P.*..." vs. "SELECT *...".

 

Two such queries will produce different answer sets; the number of rows will be the same, the difference will be the columns in each row (the "SELECT *..." will retrieve all columns from all tables in the query).

 

Having said that I'm a bit surprised that the plans are this different. This may be due to data volume. Is the "SOC.EXPSR_AGMT EXPO" table very wide? (lots of columns, or maybe very wide columns?)

 

It also looks like you've got few stats on the relevant columns - even the selection out of the base tables shows 'no confidence'. Of course that isn't so important if the row count estimates are about right.

 

You might want to try collecting stats on:

- PARTITION of both tables

Selection columns from each table

- expo.EXPSR_NATR_CD

- P.STTM_DT , P.STTM_TYPE_CD ,P.ENT_ID

Join columns from each table

- AGMT_ID , STTM_DT ,STTM_TYPE_CD , ENT_ID

 

The other thing to consider is: Is the product join hurting performance?

Product Joins in Teradata are quite common, it is simply one join strategy that the optimiser has available to it.

 

Cheers,

Dave

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

Re: Select columns ending in product join

I edited my previous answer to provide all of the differents informations (SQL and DDL)

Senior Supporter

Re: Select columns ending in product join

I guess @DaveWellman is on the right track.

 

The different plans are related to the size of the answer set and spool files.

 

1. The product join SQL is estimating less time and therefore is considered by the optimizer as the better plan.

2. The main reason for the different plan is that p.* doesn't fetch any data from the other table -> the other table spool file will only hold the join columns -> significant less IO.

(3,960,877,760 bytes) + (9,453,881,087 bytes)
vs.
(484,500,226 bytes) + (27,667,799,975 bytes) + (24,416,203,321 bytes)

 

It is worth to consider stats on the relevant columns but I don't think this might not change the behaviour.

Product joins are not always bad...
 

Teradata Employee

Re: Select columns ending in product join

There is not that much data on the EXPSR_AGMT table actually, only 600k in the EXPSR_AGMT table but 58M in the SCHD table.

 

I'm not really familiar with how statistics work so when you say 

Join columns from each table

- AGMT_ID , STTM_DT ,STTM_TYPE_CD , ENT_ID

Should I calculate the stats this way COLUMN (AGMT_ID , STTM_DT ,STTM_TYPE_CD , ENT_ID) or each individually

 

The query with the product join was running for 15 hours before we killed it, so it's a bit of a problem to have a product join this time.

 

Erwan

Senior Supporter

Re: Select columns ending in product join

start with COLUMN (AGMT_ID , STTM_DT ,STTM_TYPE_CD , ENT_ID)