Query runs for 5 hours

Database
Enthusiast

Query runs for 5 hours

explain 
UPDATE E_TBL
      FROM DB1.WHSL_E_TBL         AS E_TBL,
        DB1.WHSL_A_TBL          AS A_TBL
   SET CURR_A_GEN_ID      = A_TBL.A_GEN_ID
   , CURR_A_MTCH_FLG  = 'OBGR'
    WHERE E_TBL.OBGR_ID_MTCH  = A_TBL.ACCT_NBR_MTCH
        AND  E_TBL.CURR_A_MTCH_FLG IS NULL
        AND  (   (E_TBL.SYS_ID = 'AFS3' AND A_TBL.ACCT_TYPE_CD = 1 AND E_TBL.REPORTING_LOB NOT IN ('FOO','BCR'))
        OR (E_TBL.SYS_ID = 'IBIS' AND A_TBL.ACCT_TYPE_CD = 1)
        OR (E_TBL.SYS_ID = 'AFI'  AND A_TBL.ACCT_TYPE_CD = 1)
        OR (E_TBL.SYS_ID = 'STRT' AND A_TBL.ACCT_TYPE_CD = 3)
        OR (E_TBL.SYS_ID = 'STRE' AND A_TBL.ACCT_TYPE_CD = 3)
        OR (E_TBL.SYS_ID = 'CIS'  AND A_TBL.ACCT_TYPE_CD = 4)
        OR (E_TBL.SYS_ID = 'LUCA' AND A_TBL.ACCT_TYPE_CD = 4)
        OR (E_TBL.SYS_ID = 'ABLE' AND A_TBL.ACCT_TYPE_CD = 5)
        OR (E_TBL.SYS_ID = 'EQF'  AND A_TBL.ACCT_TYPE_CD = 6) 
        OR (E_TBL.SYS_ID = 'EQF2' AND A_TBL.ACCT_TYPE_CD = 6)
        OR (E_TBL.SYS_ID = 'MIDA' AND A_TBL.ACCT_TYPE_CD = 7)
        OR (E_TBL.SYS_ID = 'AFG'  AND A_TBL.ACCT_TYPE_CD = 9)
        OR (E_TBL.SYS_ID = 'NTAB' AND A_TBL.ACCT_TYPE_CD = 11)
        OR (E_TBL.SYS_ID = 'IWK'  AND A_TBL.ACCT_TYPE_CD = 12)
        OR (E_TBL.SYS_ID = 'LEAS' AND A_TBL.ACCT_TYPE_CD = 13)
        OR (E_TBL.SYS_ID = 'WFFC' AND A_TBL.ACCT_TYPE_CD = 14)
        OR (E_TBL.SYS_ID = 'STMF' AND A_TBL.ACCT_TYPE_CD = 15)
        OR (E_TBL.SYS_ID = 'TRGS' AND A_TBL.ACCT_TYPE_CD = 16)
        OR (E_TBL.SYS_ID = 'LIQ'  AND A_TBL.ACCT_TYPE_CD = 18)
        OR (E_TBL.SYS_ID = 'CHCE' AND A_TBL.ACCT_TYPE_CD = 19)
        OR (E_TBL.SYS_ID = 'RLM'  AND A_TBL.ACCT_TYPE_CD = 21)
        OR (E_TBL.SYS_ID = 'TRSL' AND A_TBL.ACCT_TYPE_CD = 24)
        OR (E_TBL.SYS_ID = 'TRLL' AND A_TBL.ACCT_TYPE_CD = 24)
        OR (E_TBL.SYS_ID = 'TANA' AND A_TBL.ACCT_TYPE_CD = 25)
       )
      
        AND  A_TBL.FNLIZED_DT = (
    SELECT MAX(FNLIZED_DT)
        FROM DB1.WHSL_A_TBL AS ART_MAX
        WHERE ART_MAX.ACCT_NBR_MTCH       = A_TBL.ACCT_NBR_MTCH
            AND  ART_MAX.ACCT_TYPE_CD          = A_TBL. ACCT_TYPE_CD
            AND  ART_MAX.FINALYEARMONTHDAY          < E_TBL.NEXT_PD_DT
                                 );

  1) First, we lock a distinct DB1."pseudo table" for read
     on a RowHash to prevent global deadlock for
     DB1.A_TBL.
  2) Next, we lock a distinct DB1."pseudo table" for write
     on a RowHash to prevent global deadlock for
     DB1.WHSL_E_TBL.
  3) We lock DB1.A_TBL for read, and we lock
     DB1.WHSL_E_TBL for write.
  4) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from
          DB1.WHSL_E_TBL by way of an all-rows scan
          with a condition of (
          "DB1.WHSL_E_TBL.CURR_A_MTCH_FLG IS NULL")
          into Spool 2 (all_amps) fanned out into 2 hash join
          partitions, which is built locally on the AMPs.  The size of
          Spool 2 is estimated with low confidence to be 30,841,066
          rows (2,035,510,356 bytes).  The estimated time for this step
          is 0.56 seconds.
       2) We do an all-AMPs RETRIEVE step from DB1.A_TBL
          by way of an all-rows scan with no residual conditions into
          Spool 3 (all_amps) fanned out into 2 hash join partitions,
          which is duplicated on all AMPs.  The size of Spool 3 is
          estimated with high confidence to be 362,392,320 rows (
          22,468,323,840 bytes).  The estimated time for this step is
          4.78 seconds.
       3) We do an all-AMPs RETRIEVE step from DB1.ART_MAX
          by way of an all-rows scan with a condition of ("NOT
          (DB1.ART_MAX.ACCT_TYPE_CD IS NULL)") into
          Spool 4 (all_amps), which is redistributed by the hash CD
          of (DB1.ART_MAX.ACCT_TYPE_CD,
          DB1.ART_MAX.ACCT_NBR_MTCH) to all AMPs.
          The size of Spool 4 is estimated with high confidence to be
          377,492 rows (18,497,108 bytes).  The estimated time for this
          step is 0.11 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 2 partitions, with a join condition of ("(((ACCT_TYPE_CD =
     25) AND (SYS_ID = 'TANA')) OR (((ACCT_TYPE_CD = 24) AND
     (SYS_ID = 'TRLL')) OR (((ACCT_TYPE_CD = 24) AND (SYS_ID =
     'TRSL')) OR (((ACCT_TYPE_CD = 21) AND (SYS_ID = 'RLM ')) OR
     (((ACCT_TYPE_CD= 19) AND (SYS_ID = 'CHCE')) OR
     (((ACCT_TYPE_CD = 18) AND (SYS_ID = 'LIQ ')) OR
     (((ACCT_TYPE_CD = 16) AND (SYS_ID = 'TRGS')) OR
     (((ACCT_TYPE_CD = 15) AND (SYS_ID = 'STMF')) OR
     (((ACCT_TYPE_CD = 14) AND (SYS_ID = 'WFFC')) OR
     (((ACCT_TYPE_CD = 13) AND (SYS_ID = 'LEAS')) OR
     (((ACCT_TYPE_CD = 12) AND (SYS_ID = 'IWK ')) OR
     (((ACCT_TYPE_CD = 11) AND (SYS_ID = 'NTAB')) OR
     (((ACCT_TYPE_CD = 9) AND (SYS_ID ='AFG ')) OR
     (((ACCT_TYPE_CD = 7) AND (SYS_ID = 'MIDA')) OR ((((SYS_ID =
     'AFS3') AND (((REPORTING_LOB > 'BCR ') AND (REPORTING_LOB < 'FOO
     ')) OR ((REPORTING_LOB < 'BCR ') OR (REPORTING_LOB > 'FOO ')))) OR
     ((SYS_ID = 'IBIS') OR ((SYS_ID = 'AFI ') OR ((SYS_ID = 'STRT') OR
     ((SYS_ID = 'STRE') OR ((SYS_ID = 'CIS ') OR ((SYS_ID = 'LUCA') OR
     ((SYS_ID = 'ABLE') OR ((SYS_ID = 'EQF ') OR (SYS_ID =
     'EQF2')))))))))) AND (((ACCT_TYPE_CD = 3) OR
     ((ACCT_TYPE_CD = 1) OR ((ACCT_TYPE_CD = 3) OR
     ((ACCT_TYPE_CD = 4) OR ((ACCT_TYPE_CD = 5) OR
     (ACCT_TYPE_CD = 6 )))))) AND (((((((((((((((ACCT_TYPE_CD
     = 1 )OR (SYS_ID = 'STRT')) AND ((ACCT_TYPE_CD = 3) OR
     (((SYS_ID = 'AFS3') AND (((REPORTING_LOB > 'BCR ') AND
     (REPORTING_LOB < 'FOO ')) OR ((REPORTING_LOB < 'BCR ') OR
     (REPORTING_LOB > 'FOO ')))) OR ((SYS_ID = 'IBIS') OR (SYS_ID =
     'AFI '))))) OR (SYS_ID = 'STRE')) AND (((((ACCT_TYPE_CD = 1)
     OR (SYS_ID = 'STRT')) AND ((ACCT_TYPE_CD = 3) OR (((SYS_ID =
     'AFS3') AND (((REPORTING_LOB > 'BCR ') AND (REPORTING_LOB < 'FOO
     ')) OR ((REPORTING_LOB < 'BCR ') OR (REPORTING_LOB > 'FOO ')))) OR
     ((SYS_ID = 'IBIS') OR (SYS_ID = 'AFI '))))) OR (ACCT_TYPE_CD
     = 3 )) AND (((SYS_ID = 'STRE') OR ((ACCT_TYPE_CD = 3) OR
     (ACCT_TYPE_CD = 1 ))) AND ((ACCT_TYPE_CD = 3) OR
     (((SYS_ID = 'AFS3') AND (((REPORTING_LOB > 'BCR ') AND
     (REPORTING_LOB < 'FOO ')) OR ((REPORTING_LOB < 'BCR ') OR
     (REPORTING_LOB > 'FOO ')))) OR ((SYS_ID = 'IBIS') OR ((SYS_ID =
     'AFI ') OR (SYS_ID = 'STRT')))))))) OR (SYS_ID = 'CIS ')) AND
     (((((((ACCT_TYPE_CD = 1) OR (SYS_ID = 'STRT')) AND
     ((ACCT_TYPE_CD = 3) OR (((** additional conditions not listed
     **) AND (** additional conditions not listed **)) OR (**
     additional conditions not listed **)))) OR (** additional
     conditions not listed **)) AND (** additional conditions not
     listed **)) OR (** additional conditions not listed **)) AND (**
     additional conditions not listed **))) OR (** additional
     conditions not listed **)) AND (** additional conditions not
     listed **)) OR (** additional conditions not listed **)) AND (**
     additional conditions not listed **)) OR (** additional conditions
     not listed **)) AND (** additional conditions not listed **)) OR
     (** additional conditions not listed **)) AND (** additional
     conditions not listed **)))))))))))))))))) AND (** additional
     conditions not listed **)").  The result goes into Spool 5
     (all_amps), which is built locally on the AMPs.  The size of Spool
     5 is estimated with low confidence to be 36,301,167 rows (
     2,686,286,358 bytes).  The estimated time for this step is 2.16
     seconds.
  6) We do an all-AMPs RETRIEVE step from Spool 5 by way of an all-rows
     scan into Spool 6 (all_amps), which is redistributed by the hash
     CD of (DB1.A_TBL.ACCT_TYPE_CD,
     DB1.A_TBL.ACCT_NBR_MTCH) to all AMPs.  The
     size of Spool 6 is estimated with low confidence to be 36,301,167
     rows (2,686,286,358 bytes).  The estimated time for this step is
     2.16 seconds.
  7) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of
     an all-rows scan into Spool 7 (all_amps), which is redistributed
     by the hash CD of (DB1.A_TBL.ACCT_TYPE_CD,
     DB1.A_TBL.ACCT_NBR_MTCH) to all AMPs.  Then
     we do a SORT to order Spool 7 by row hash.  The size of Spool 7 is
     estimated with low confidence to be 36,301,167 rows (
     2,686,286,358 bytes).  The estimated time for this step is 2.44
     seconds.
  8) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
     all-rows scan, which is joined to Spool 6 (Last Use) by way of an
     all-rows scan.  Spool 4 and Spool 6 are joined using a single
     partition hash join, with a join condition of ("((NOT
     (ACCT_NBR_MTCH IS NULL )) AND (NOT (ACCT_TYPE_CD IS
     NULL ))) AND ((ACCT_NBR_MTCH = ACCT_NBR_MTCH) AND
     ((ACCT_TYPE_CD = ACCT_TYPE_CD) AND (FINALYEARMONTHDAY <
     NEXT_PD_DT )))").  The result goes into Spool 8 (all_amps),
     which is built locally on the AMPs.  The size of Spool 8 is
     estimated with low confidence to be 150,716,001 rows (
     16,729,476,111 bytes).  The estimated time for this step is 5.23
     seconds.
  9) We do an all-AMPs SUM step to aggregate from Spool 8 (Last Use) by
     way of an all-rows scan.  Aggregate Intermediate Results are
     computed locally, then placed in Spool 10.  The size of Spool 10
     is estimated with no confidence to be 150,716,000 rows (
     13,715,156,000 bytes).  The estimated time for this step is 17.00
     seconds.
 10) We do an all-AMPs RETRIEVE step from Spool 10 (Last Use) by way of
     an all-rows scan into Spool 9 (all_amps), which is built locally
     on the AMPs.  Then we do a SORT to order Spool 9 by the hash CD
     of (DB1.A_TBL.ACCT_NBR_MTCH,
     DB1.A_TBL.ACCT_TYPE_CD).  The size of Spool 9
     is estimated with no confidence to be 150,716,001 rows (
     7,987,948,053 bytes).
 11) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of an
     all-rows scan, which is joined to Spool 9 (Last Use) by way of an
     all-rows scan.  Spool 7 and Spool 9 are joined using an inclusion
     merge join, with a join condition of ("((ACCT_TYPE_CD =
     ACCT_TYPE_CD) AND ((ACCT_NBR_MTCH =
     ACCT_NBR_MTCH) AND (UniqueId = UniqueId ))) AND
     (FNLIZED_DT = Field_5)").  The result goes into Spool 1
     (all_amps), which is redistributed by the hash CD of (
     DB1.WHSL_E_TBL.ROWID) to all AMPs.  Then we do a
     SORT to order Spool 1 by the sort key in spool field1.  The size
     of Spool 1 is estimated with low confidence to be 36,301,167 rows
     (1,089,035,010 bytes).  The estimated time for this step is 1.02
     seconds.
 12) We do a MERGE Update to DB1.WHSL_E_TBL from Spool
     1 (Last Use) via ROWID.  The size is estimated with low confidence
     to be 36,301,167 rows (6,679,414,728 bytes).  The estimated time
     for this step is 7 minutes and 2 seconds.
 13) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

Hi all,

I need help in tuning this query , This query refers to an outer table and takes 5 hours and gets stuck at step 11.

8 REPLIES
Enthusiast

Re: Query runs for 5 hours

Dieter, need your expert opinion!

Teradata Employee

Re: Query runs for 5 hours

It is not clear exactly what the question is for the subquery but I note that it is a correlated subquery which does a self join between two instances of DB1.WHSL_A_TBL by specifying a join on ACCT_NBR_MTCH and then in that same subquery joins to DB1.WHSL_E_TBL without a condition to join to OBGR_ID_MTCH. This means it will have to join all rows of E_TBL to the result of the self join to match the date qualification.

I also note that it appears that you do not have stats on E_TBL.CURR_A_MTCH_FLG so the optimizer is guessing how many rows contain NULL.

It is generally helpful to know how many AMPs in your system and what Teradata software release you are running.

Enthusiast

Re: Query runs for 5 hours

Thanks for responding! The  CURR_A_MTCH_FLG has stats. We are currently on TD 15 and its a 960 amp system.

Enthusiast

Re: Query runs for 5 hours

Dieter need your expert opinion!

Senior Apprentice

Re: Query runs for 5 hours

Without DBQL Stepinfo it's hard to tell.

As Todd said the MAX-subquery looks suspicious, a rewrite might be possible, but depends on the actual data... 

Enthusiast

Re: Query runs for 5 hours

How can it be rewritten ?

Enthusiast

Re: Query runs for 5 hours

Try creating a temporary table and do the join so multiple OR condition is eliminated.

CREATE MULTISET VOLATILE TABLE E_TBL_LIST
(
SYS_ID VARCHAR(10),
ACCT_TYPE_CD INTEGER
) PRIMARY INDEX (SYS_ID,ACCT_TYPE_CD)
ON COMMIT PRESERVE ROWS
;

INSERT INTO E_TBL_LIST VALUES ('AFS3',1);
INSERT INTO E_TBL_LIST VALUES ('IBIS',1);
INSERT INTO E_TBL_LIST VALUES ('AFI',1);
...

UPDATE E_TBL
FROM DB1.WHSL_E_TBL AS E_TBL,
DB1.WHSL_A_TBL AS A_TBL
SET CURR_A_GEN_ID = A_TBL.A_GEN_ID
, CURR_A_MTCH_FLG = 'OBGR'
WHERE E_TBL.OBGR_ID_MTCH = A_TBL.ACCT_NBR_MTCH
AND E_TBL.CURR_A_MTCH_FLG IS NULL
AND ( (E_TBL.SYS_ID = 'AFS3' AND A_TBL.ACCT_TYPE_CD = 1 AND E_TBL.REPORTING_LOB NOT IN ('FOO','BCR'))
OR (E_TBL.SYS_ID,A_TBL.ACCT_TYPE_CD) IN (SELECT SYS_ID,ACCT_TYPE_CD FROM E_TBL_LIST)
)
AND A_TBL.FNLIZED_DT = (
SELECT MAX(FNLIZED_DT)
FROM DB1.WHSL_A_TBL AS ART_MAX
WHERE ART_MAX.ACCT_NBR_MTCH = A_TBL.ACCT_NBR_MTCH
AND ART_MAX.ACCT_TYPE_CD = A_TBL. ACCT_TYPE_CD
AND ART_MAX.FINALYEARMONTHDAY < E_TBL.NEXT_PD_DT
);
Enthusiast

Re: Query runs for 5 hours

The values are not known beforehand nore are fixed so the voaltile table cannot be populated.

Dieter, 

Can you help rewriting this query?