All Forums Database
31_resu 38 posts Joined 07/13
23 Jun 2016
Error 7547 on Update

Hello,
I'm running an update, and as I develop it, from updating everything incorrectly, to error 7547's, to spool space, and collecting stats on tables invloved, distinct, with and without qualify row_num, I get 7547 or a spool stll. Can someone please help me with this update statement.
There are multirows in the inner, and I only need to know if anything like '%abc%' is present in that condition to them mark a set column what has a 'n' to become a 'y', and I either spool, or keep pulling back more than one row that it wants.
TD Version - 15.10.01.04
Thank you
UPDATE DA
FROM DATABASENAME.DIG_ADOPT AS DA ,
    (SELECT
    DISTINCT(CG.ACCT_NBR)
    FROM DATABASENAME.SRT_CG AS CG
        , DATABASENAME.DIG_ADOPT AS DA
    WHERE CG.ACCT_NBR = DA.ACCT_NBR AND CG.OFFERED LIKE '%ABC%'
    QUALIFY ROW_NUMBER() OVER(PARTITION BY DATABASENAME.SRT_CG.ACCT_NBR ORDER BY CG.OFFERED DESC) = 1
    --GROUP BY 1) AS SRC
    ) AS SRC
SET OFFR_ABCO = 'Y'
WHERE SRC.ACCT_NBR = DATABASENAME.DIG_ADOPT.ACCT_NBR ;

 

dnoeth 4628 posts Joined 11/04
23 Jun 2016

Can you show Explain?
Double check your table aliases, seems like you get a product-join.

Dieter

31_resu 38 posts Joined 07/13
23 Jun 2016

Here it is with datbase/table names changes to be like the example
 
UPDATE DA
FROM DATABASENAME.DIG_ADOPT AS DA ,
    (SELECT
    DISTINCT(CG.ACCT_NBR)
    FROM DATABASENAME.SRT_CG AS CG
        , DATABASENAME.DIG_ADOPT AS DA
    WHERE CG.ACCT_NBR = DA.ACCT_NBR AND CG.OFFERED LIKE '%SSO%'
    QUALIFY ROW_NUMBER() OVER(PARTITION BY DATABASENAME.SRT_CG.ACCT_NBR ORDER BY cg.OFFERED DESC) = 1
    --GROUP BY 1) AS SRC
    ) AS SRC
SET OFFR_SSO = 'Y'
WHERE SRC.ACCT_NBR = DA.ACCT_NBR;
 
  1) First, we lock DATABASENAME.DIG_ADOPT for write on
     a reserved RowHash to prevent global deadlock.
  2) Next, we lock DATABASENAME.CG for read on a reserved RowHash to
     prevent global deadlock.
  3) We lock DATABASENAME.DIG_ADOPT for write, and we
     lock DATABASENAME.CG for read.
  4) We do an all-AMPs RETRIEVE step from DATABASENAME.CG by way of an
     all-rows scan with a condition of ("(NOT (DATABASENAME.CG.ACCT_NBR IS
     NULL )) AND (DATABASENAME.CG.OFFERED LIKE '%SSO%')") into Spool 3
     (all_amps), which is redistributed by the hash code of (
     DATABASENAME.CG.ACCT_NBR) to all AMPs.  Then we do a SORT to order
     Spool 3 by row hash.  The size of Spool 3 is estimated with low
     confidence to be 1,856 rows (647,744 bytes).  The estimated time
     for this step is 0.00 seconds.
  5) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of a
     RowHash match scan, which is joined to DATABASENAME.DA by way of a
     RowHash match scan with no residual conditions.  Spool 3 and
     DATABASENAME.DA are joined using a merge join, with a join condition
     of ("ACCT_NBR = DATABASENAME.DA.Acct_Nbr").  The result goes into
     Spool 4 (all_amps), which is duplicated on all AMPs.  The size of
     Spool 4 is estimated with index join confidence to be 2,886,408
     rows (1,007,356,392 bytes).  The estimated time for this step is
     0.25 seconds.
  6) We do an all-AMPs JOIN step from DATABASENAME.SRT_CG by way
     of an all-rows scan with no residual conditions, which is joined
     to Spool 4 (Last Use) by way of an all-rows scan.
     DATABASENAME.SRT_CG and Spool 4 are joined using a product
     join, with a join condition of ("(1=1)").  The result goes into
     Spool 2 (all_amps), which is built locally on the AMPs.  The size
     of Spool 2 is estimated with index join confidence to be
     18,610,841 rows (9,659,026,479 bytes).  The estimated time for
     this step is 0.24 seconds.
  7) We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by
     way of an all-rows scan into Spool 7 (Last Use), which is assumed
     to be redistributed by value to all AMPs.  The result rows are put
     into Spool 1 (all_amps), which is redistributed by hash code to
     all AMPs.  Then we do a SORT to order Spool 1 by the sort key in
     spool field1 eliminating duplicate rows.  The size is estimated
     with index join confidence to be 9,749 rows (6,658,567 bytes).
  8) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of
     an all-rows scan with a condition of ("NOT (SRC.ACCT_NBR IS NULL)")
     into Spool 11 (all_amps), which is redistributed by the hash code
     of (DATABASENAME.CG.ACCT_NBR) to all AMPs.  Then we do a SORT to order
     Spool 11 by row hash.  The size of Spool 11 is estimated with
     index join confidence to be 9,749 rows (1,764,569 bytes).  The
     estimated time for this step is 0.01 seconds.
  9) We do an all-AMPs JOIN step from
     DATABASENAME.DIG_ADOPT by way of a RowHash match
     scan with no residual conditions, which is joined to Spool 11
     (Last Use) by way of a RowHash match scan.
     DATABASENAME.DIG_ADOPT and Spool 11 are joined
     using a merge join, with a join condition of ("ACCT_NBR =
     DATABASENAME.DIG_ADOPT.Acct_Nbr").  The result goes
     into Spool 10 (all_amps), which is built locally on the AMPs.
     Then we do a SORT to order Spool 10 by the sort key in spool
     field1 (DATABASENAME.DIG_ADOPT.ROWID).  The size of
     Spool 10 is estimated with index join confidence to be 10,025 rows
     (180,450 bytes).  The estimated time for this step is 0.01 seconds.
 10) We do a MERGE Update to DATABASENAME.DIG_ADOPT from
     Spool 10 (Last Use) via ROWID.  The size is estimated with index
     join confidence to be 10,025 rows (1,493,725 bytes).  The
     estimated time for this step is 0.08 seconds.
 11) 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.
 

31_resu 38 posts Joined 07/13
23 Jun 2016

I see that product join,and I have made many joins after the set statement and still cant get past the spool

dnoeth 4628 posts Joined 11/04
23 Jun 2016

You must use a defined alias instead of the original table name:

UPDATE DA
FROM DATABASENAME.DIG_ADOPT AS DA ,
    (SELECT
    DISTINCT(CG.ACCT_NBR)
    FROM DATABASENAME.SRT_CG AS CG
        , DATABASENAME.DIG_ADOPT AS DA
    WHERE CG.ACCT_NBR = DA.ACCT_NBR AND CG.OFFERED LIKE '%ABC%'
    ) AS SRC
SET OFFR_ABCO = 'Y'
WHERE SRC.ACCT_NBR = DA.ACCT_NBR ;

 
But as you only want to set a flag you better use EXISTS:

UPDATE DATABASENAME.DIG_ADOPT
SET OFFR_ABCO = 'Y'
WHERE EXISTS
  ( SELECT
    FROM DATABASENAME.SRT_CG AS CG
    WHERE CG.ACCT_NBR = DATABASENAME.DIG_ADOPT.ACCT_NBR AND CG.OFFERED LIKE '%ABC%'
  );

 

Dieter

31_resu 38 posts Joined 07/13
23 Jun 2016

They both work, I see what you're saying about using defined alias and not original table name, I like the EXISTS as it's cleaner. Thank you very much Dieter

You must sign in to leave a comment.