23 Jan 2015
A quick solution is to change the UNION to UNION all, this avoids the DISTINCT processing.
In best case the table is partitioned by I.SETL_DT.
You might also try to combine all three selects into one using CASEs:
SEL I.DLVR_POINT_NO ,SUM(CASE WHEN I.SETL_DT <= (ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),0)-1) AND I.SETL_DT >= ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1,-12) THEN I.SALES end) AS InvoiceAmtLast12 ,SUM(CASE WHEN I.SETL_DT <= ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),-12)-1 AND I.SETL_DT >= ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1,-24) THEN I.SALES end) AS InvoiceAmtLast24 ,MIN(CASE WHEN I.SETL_DT < ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1,-24) THEN 1 end) AS CalcGrowth FROM VOLUME_TABLE I WHERE I.SETL_DT <= (ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),0)-1) GROUP BY 1 ,CASE WHEN I.SETL_DT <= (ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),0)-1) AND I.SETL_DT >= ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1,-12) THEN 1 WHEN I.SETL_DT <= ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),-12)-1 AND I.SETL_DT >= ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1,-24) THEN 2 ELSE 3 END
You should runt some tests which one is more efficient on this big table, it will depend on the actual data...
Dieter
Hi All,
The below Query is facing Spool space issue while executing ...
SEL I.DLVR_POINT_NO, SUM(I.SALES) AS InvoiceAmtLast12, CAST(NULL AS DECIMAL(18,2)) AS InvoiceAmtLast24,
CAST(NULL AS INTEGER) AS CalcGrowth FROM
VOLUME_TABLE I
WHERE I.SETL_DT <= (add_months((CURRENT_DATE - extract(day from CURRENT_DATE)+1),0)-1)
AND I.SETL_DT >= add_months(CURRENT_DATE - extract(day from CURRENT_DATE)+1,-12)
GROUP BY 1
UNION
SEL I.DLVR_POINT_NO, NULL AS InvoiceAmtLast12, SUM(I.SALES) AS InvoiceAmtLast24, NULL AS CalcGrowth FROM
VOLUME_TABLE I
WHERE I.SETL_DT <= add_months((CURRENT_DATE - extract(day from CURRENT_DATE)+1),-12)-1
AND I.SETL_DT >= add_months(CURRENT_DATE - extract(day from CURRENT_DATE)+1,-24)
GROUP BY 1
UNION
SEL DISTINCT I.DLVR_POINT_NO, NULL AS InvoiceAmtLast12, NULL AS InvoiceAmtLast24, 1 AS CalcGrowth FROM
VOLUME_TABLE I
WHERE I.SETL_DT < add_months(CURRENT_DATE - extract(day from CURRENT_DATE)+1,-24)
I tried creating an volatile table to load all the SELECT part of data first but that is also taking longer time and ending up with spool issue.Can anyone please suggest other alternate approach or help in tuning the Query..
P.S : The Volume table contains around 5billion rows..
Thanks..