Product join

Database
Enthusiast

Product join

Explain SELECT MAX(TABLE1.FD_DT) 
                     FROM TABLE1 AS TABLE1
WHERE TABLE1.ACC_NO_MH       = TABLE1.ACC_NO_MH
  AND TABLE1.FYRMD  < TABLE2.NXT_PD_DT

  1) First, we lock a distinct "pseudo table" for read
     on a RowHash to prevent global deadlock for
     TABLE1.
  2) Next, we lock a distinct "pseudo table" for read
     on a RowHash to prevent global deadlock for
     TABLE2.
  3) We lock TABLE1 for read, and we lock
     TABLE2 for read.
  4) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from TABLE1
          by way of an all-rows scan with no residual conditions into
          Spool 4 (all_amps), which is redistributed by the hash code
          of (TABLE1.ACC_NO_MH) to all AMPs.
          Then we do a SORT to order Spool 4 by row hash.  The size of
          Spool 4 is estimated with high confidence to be 357,253 rows
          (16,790,891 bytes).  The estimated time for this step is 0.11
          seconds.
       2) We do an all-AMPs RETRIEVE step from
          TABLE2 by way of an all-rows scan
          with no residual conditions into Spool 6 (all_amps), which is
          built locally on the AMPs.  The size of Spool 6 is estimated
          with high confidence to be 27,655,935 rows (470,150,895
          bytes).  The estimated time for this step is 0.64 seconds.
  5) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a
     RowHash match scan, which is joined to Spool 4 by way of a RowHash
     match scan.  Spool 4 and Spool 4 are joined using a merge join,
     with a join condition of ("ACC_NO_MH =
     ACC_NO_MH").  The result goes into Spool 7 (all_amps),
     which is built locally on the AMPs.  The size of Spool 7 is
     estimated with low confidence to be 8,722,901 rows (235,518,327
     bytes).  The estimated time for this step is 1 minute and 7
     seconds.
  6) We do an all-AMPs partial SUM step to aggregate from Spool 7 (Last
     Use) by way of an all-rows scan , grouping by field1 (
     TABLE1.FYRMD).  Aggregate
     Intermediate Results are computed globally, then placed in Spool 9.
     The size of Spool 9 is estimated with low confidence to be
     8,722,901 rows (305,301,535 bytes).  The estimated time for this
     step is 0.07 seconds.
  7) We do an all-AMPs RETRIEVE step from Spool 9 (Last Use) by way of
     an all-rows scan into Spool 8 (all_amps), which is duplicated on
     all AMPs.  The size of Spool 8 is estimated with low confidence to
     be 8,373,984,960 rows (259,593,533,760 bytes).
  8) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an
     all-rows scan, which is joined to Spool 8 (Last Use) by way of an
     all-rows scan.  Spool 6 and Spool 8 are joined using a product
     join, with a join condition of ("FYRMD <
     NXT_PD_DT").  The result goes into Spool 3 (all_amps), which
     is built locally on the AMPs.  The result spool file will not be
     cached in memory.  The size of Spool 3 is estimated with low
     confidence to be 111,803,510,694,200 rows (*** bytes).  The
     estimated time for this step is 170 hours and 21 minutes.
  9) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
     way of an all-rows scan.  Aggregate Intermediate Results are
     computed globally, then placed in Spool 11.  The size of Spool 11
     is estimated with high confidence to be 1 row (25 bytes).  The
     estimated time for this step is 102 hours and 15 minutes.
 10) We do an all-AMPs RETRIEVE step from Spool 11 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs.  The size of Spool 1 is estimated with high
     confidence to be 1 row (47 bytes).  The estimated time for this
     step is 0.01 seconds.
 11) 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.
     BEGIN RECOMMENDED STATS ->
 12) "COLLECT STATISTICS COLUMN (FYRMD
     ,ACC_NO_MH) ON TABLE1".
     (LowConf)
     <- END RECOMMENDED STATS

Hi,

We are getting a product join due to the following code. IS there a better way to write this query ?

Both tables have an UPI but not used in the query.

3 REPLIES
Senior Apprentice

Re: Product join

If this is the actual SQL it's simply plain stupid :-(

TABLE2 is not listed in FROM and the join is using the same table twice:

WHERE TABLE1.ACC_NO_MH       = TABLE1.ACC_NO_MH

Should be

SELECT MAX(TABLE1.FD_DT) 
FROM TABLE1 AS TABLE1
JOIN TABLE2
ON TABLE1.ACC_NO_MH = TABLE2.ACC_NO_MH
AND TABLE1.FYRMD < TABLE2.NXT_PD_DT
Enthusiast

Re: Product join

Explain UPDATE ALIAS2
     FROM TABLE2         AS ALIAS2
       , TABLE1          AS ALIAS1
   SET CURR_A_G_ID      = ALIAS1.A_G_ID
     , CURR_ART_MATCH_FLAG  = 'RE LOAN'
 WHERE ALIAS2.R_L_N_MH  = ALIAS1.ACC_NO_MH
   AND ALIAS2.R_L_N_MH <> 'UNKNOWN'
   AND ALIAS2.S_ID IN ('AAAA','BBB')
   AND ALIAS1.FD_DT = (SELECT MAX(FD_DT)
                                    FROM TABLE1 AS TABLE1
  TABLE1.ACC_NO_MH       = ALIAS1.ACC_NO_MH
  AND TABLE1.FYRMD  < ALIAS2.NXT_PD_DT
                  );

  1) First, we lock a distinct "pseudo table" for read
     on a RowHash to prevent global deadlock for
     ALIAS1.
  2) Next, we lock a distinct "pseudo table" for write
     on a RowHash to prevent global deadlock for
     TABLE2.
  3) We lock ALIAS1 for read, and we lock
     TABLE2 for write.
  4) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from ALIAS1
          by way of an all-rows scan with a condition of (
          "(ALIAS1.ACC_NO_MH > 'UNKNOWN ')
          OR (ALIAS1.ACC_NO_MH < 'UNKNOWN
          ')") into Spool 2 (all_amps), which is redistributed by the
          hash code of (ALIAS1.ACC_NO_MH)
          to all AMPs.  The size of Spool 2 is estimated with low
          confidence to be 357,253 rows (21,435,180 bytes).  The
          estimated time for this step is 0.12 seconds.
       2) We do an all-AMPs RETRIEVE step from
          TABLE2 by way of an all-rows scan
          with a condition of (
          "((TABLE2.S_ID = 'BBB ') OR
          (TABLE2.S_ID = 'AAAA')) AND
          ((TABLE2.R_L_N_MH > 'UNKNOWN
          ') OR (TABLE2.R_L_N_MH <
          'UNKNOWN '))") into Spool 3 (all_amps), which is
          redistributed by the hash code of (
          TABLE2.R_L_N_MH) to all AMPs.
          The size of Spool 3 is estimated with low confidence to be
          592,880 rows (24,900,960 bytes).  The estimated time for this
          step is 1.22 seconds.
       3) We do an all-AMPs RETRIEVE step from TABLE1
          by way of an all-rows scan with no residual conditions into
          Spool 4 (all_amps), which is redistributed by the hash code
          of (TABLE1.ACC_NO_MH) to all AMPs.
          The size of Spool 4 is estimated with high confidence to be
          357,253 rows (16,790,891 bytes).  The estimated time for this
          step is 0.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 single
     partition hash join, with a join condition of ("R_L_N_MH =
     ACC_NO_MH").  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 11,266,392 rows (811,180,224
     bytes).  The estimated time for this step is 0.44 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 built locally on the AMPs.
     The size of Spool 6 is estimated with low confidence to be
     11,266,392 rows (811,180,224 bytes).  The estimated time for this
     step is 0.44 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 code of (UniqueId,
     ALIAS1.ACC_NO_MH,
     CAST((ALIAS1.FD_DT) AS TIMESTAMP(6)))
     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 11,266,392
     rows (923,844,144 bytes).  The estimated time for this step is
     0.87 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
     (ACC_NO_MH IS NULL )) AND ((ACC_NO_MH =
     ACC_NO_MH) AND (FYRMD < NXT_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 274,864,371 rows (27,761,301,471 bytes).  The estimated time
     for this step is 27.15 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 275,086,922 rows (
     22,282,040,682 bytes).  The estimated time for this step is 42.95
     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 redistributed
     by the hash code of (UniqueId,
     ALIAS1.ACC_NO_MH,
     CAST((TABLE1.FD_DT) AS TIMESTAMP(6))) to
     all AMPs.  Then we do a SORT to order Spool 9 by row hash.  The
     size of Spool 9 is estimated with no confidence to be 274,864,371
     rows (14,292,947,292 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 ("((ACC_NO_MH =
     ACC_NO_MH) AND (UniqueId = UniqueId )) AND
     (FD_DT = Field_4)").  The result goes into Spool 1
     (all_amps), which is redistributed by the hash code of (
     TABLE2.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 11,266,392 rows
     (337,991,760 bytes).  The estimated time for this step is 1.58
     seconds.
 12) We do a MERGE Update to TABLE2 from Spool
     1 (Last Use) via ROWID.  The size is estimated with low confidence
     to be 11,266,392 rows (2,073,016,128 bytes).  The estimated time
     for this step is 12 minutes and 1 second.
 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.

This is part of the query whih has a bad explain. Actual query is below which had poor performance...but I had a feeling that this Sel is the cuplrit.

Any way to tune this?

 

Enthusiast

Re: Product join

Dieter,

Appreciate any suggestions.