No more spool space in Teradata while trying Update

Question

I'm trying to update a table with to many rows 388.000.

This is the query:

update DL_RG_ANALYTICS.SH_historico
from 
(
SELECT
    CAST((MAX_DIA - DIA_PAGO) AS INTEGER) AS DIAS_AL_CIERRE_1
FROM

(SELECT * FROM DL_RG_ANALYTICS.SH_historico A
        LEFT JOIN 
            (SELECT ANO||MES AS ANO_MES, MAX(DIA) AS MAX_DIA FROM DL_RG_ANALYTICS.SH_CALENDARIO
            GROUP BY 1) B
        ON A.ANOMES = B.ANO_MES
) M) N

SET DIAS_AL_CIERRE = DIAS_AL_CIERRE_1;

Any help is apreciate.

primary index on DL_RG_ANALYTICS.SH_historico (dias_al_cierre) might help. — access_granted, Jan 08 '20 at 17:26

ravioli · Accepted Answer · 2020-01-08T17:13:46.703

This first thing I'd do is replace the SELECT * with only the columns you need. You can also remove the M derived table to make it easier to read:

UPDATE DL_RG_ANALYTICS.SH_historico
FROM (
  SELECT CAST((MAX_DIA - DIA_PAGO) AS INTEGER) AS DIAS_AL_CIERRE_1
  FROM DL_RG_ANALYTICS.SH_historico A
  LEFT JOIN (
    SELECT ANO || MES AS ANO_MES, MAX(DIA) AS MAX_DIA 
    FROM DL_RG_ANALYTICS.SH_CALENDARIO
    GROUP BY 1
  ) B ON A.ANOMES = B.ANO_MES
) N
SET DIAS_AL_CIERRE = DIAS_AL_CIERRE_1;

What indexes are defined on the SH_CALENDARIO table? If there is a composite index of (ANO, MES) then you should re-write your LEFT JOIN sub-query to GROUP BY these two columns since you concatenate them together anyways. In general, you want to perform joins, GROUP BY and OLAP functions on indexes, so there will be less row re-distribution and they will run more efficiently.

Also, this query is updating all rows in the table with the same value. Is this intended, or do you want to include extra columns in your WHERE clause?

No more spool space in Teradata while trying Update

1 Answers1