decode function
A reader, November 23, 2009 - 7:39 am UTC
Hi Tom,
I have a materialized view with the query a),
I try to change this query with the query b)
the only difference is the elimination of the union all
ad the use of the decode function.
With the query b) I have
the error
ORA-01652: unable to extend temp segment by 8192 in tablespace TEMP
the question is:
does the decode function impact on TEMP consuming?
thanks and best regards
Mauro
b)
SELECT
output.NU_ANNOMM AS NU_ANNOMM,
.......
output.CO_TP_REND AS CO_TP_REND,
decode(output.CO_VOCE,'ST_ATRNDIS',output.CO_PIVA, 'CT_CONS_B' ,output.CO_PIVA,null) AS CO_PIVA,
SUM (output.VA_IMPORTO) AS VA_IMPORTO,
SUM (output.VA_IMPORTO_ALLOC) AS VA_IMPORTO_ALLOC
FROM
TBRC_DATI_COMPENSI output
WHERE
output.FL_COMPENSO = 'S'
GROUP BY
output.NU_ANNOMM,
.......
output.CO_TP_REND,
decode(output.CO_VOCE,'ST_ATRNDIS',output.CO_PIVA, 'CT_CONS_B' ,output.CO_PIVA,null)
a)
SELECT
output.NU_ANNOMM AS NU_ANNOMM,
........
output.CO_TP_REND AS CO_TP_REND,
output.CO_PIVA AS CO_PIVA,
SUM (output.VA_IMPORTO) AS VA_IMPORTO,
SUM (output.VA_IMPORTO_ALLOC) AS VA_IMPORTO_ALLOC
FROM
VMRC_COMPENSO_ABI output
WHERE
output.FL_COMPENSO = 'S'
and co_voce in ('ST_ATRNDIS', 'CT_CONS_B')
GROUP BY
output.NU_ANNOMM,
....
output.CO_TP_REND,
output.CO_PIVA,
union all
SELECT
output.NU_ANNOMM AS NU_ANNOMM,
output.CO_TP_REND AS CO_TP_REND,
null AS CO_PIVA,
SUM (output.VA_IMPORTO) AS VA_IMPORTO,
SUM (output.VA_IMPORTO_ALLOC) AS VA_IMPORTO_ALLOC
FROM
VMRC_COMPENSO_ABI output
WHERE
output.FL_COMPENSO = 'S'
and not(co_voce in ('ST_ATRNDIS', 'CT_CONS_B'))
GROUP BY
output.NU_ANNOMM,
output.CO_TP_REND
November 23, 2009 - 4:38 pm UTC
you are comparing a union all query against tables VMRC_COMPENSO_ABI and VMRC_COMPENSO_ABI to a query against a third table TBRC_DATI_COMPENSI ??????
Well, the union all query is done sequentially - one after the other really - so the temp needs for it would be whatever the first bit needs AND THEN whatever the second bit needs
where as the second query does everything in one pass.
I doubt it has anything to do with decode
It has everything to do with the size of TBRC_DATI_COMPENSI versus the other two, it'll be larger or having many more distinct values for the group by list
follow up
Mauro, November 24, 2009 - 3:44 am UTC
ops! excuse me, I wrong coping an other snapshot query,
the source table is the same (TBRC_DATI_COMPENSI),
the 2 correct query are:
b)
SELECT
output.NU_ANNOMM AS NU_ANNOMM,
output.CO_VOCE AS CO_VOCE,
output.CO_ABI AS CO_ABI,
output.CO_GRUPPO AS CO_GRUPPO,
output.DT_MESE AS DT_MESE,
output.DT_ANNO AS DT_ANNO,
output.CO_CAB AS CO_CAB,
output.CO_PRODOTTO AS CO_PRODOTTO,
output.CO_PERSONALIZ AS CO_PERSONALIZ,
output.CO_SCAGLIONE AS CO_SCAGLIONE,
output.CO_CIRCUITO AS CO_CIRCUITO,
output.CO_OPE AS CO_OPE,
output.CO_TECNO AS CO_TECNO,
output.FL_COMPENSO AS FL_COMPENSO,
output.CO_PRES_CARTA AS CO_PRES_CARTA,
output.CO_TP_CER AS CO_TP_CER,
output.CO_TP_RISK AS CO_TP_RISK,
output.CO_TP_REND AS CO_TP_REND,
decode(output.CO_VOCE,'ST_ATRNDIS',output.CO_PIVA, 'CT_CONS_B' ,output.CO_PIVA,null) AS CO_PIVA,
output.CO_PROP_POS AS CO_PROP_POS,
decode(FL_CHIP_MOV, '2', 'S', '4', 'S', '5', 'S', 'N') AS FL_CHIP_MOV,
SUM (output.VA_DRIVER) AS VA_DRIVER,
output.VA_PARAM AS VA_PARAM,
SUM (output.VA_IMPORTO) AS VA_IMPORTO,
SUM (output.VA_IMPORTO_ALLOC) AS VA_IMPORTO_ALLOC
FROM
TBRC_DATI_COMPENSI output
WHERE
output.FL_COMPENSO = 'S'
GROUP BY
output.NU_ANNOMM,
output.CO_VOCE,
output.CO_ABI,
output.CO_GRUPPO,
output.DT_MESE,
output.DT_ANNO,
output.CO_CAB,
output.CO_PRODOTTO,
output.CO_PERSONALIZ,
output.CO_SCAGLIONE,
output.CO_CIRCUITO,
output.CO_OPE,
output.CO_TECNO,
output.FL_COMPENSO,
output.CO_PRES_CARTA,
output.CO_TP_CER,
output.CO_TP_RISK,
output.CO_TP_REND,
decode(output.CO_VOCE,'ST_ATRNDIS',output.CO_PIVA, 'CT_CONS_B' ,output.CO_PIVA,null),
output.CO_PROP_POS,
decode(FL_CHIP_MOV, '2', 'S', '4', 'S', '5', 'S', 'N'),
output.VA_PARAM;
a)
SELECT output.nu_annomm AS nu_annomm, output.co_voce AS co_voce,
output.co_abi AS co_abi, output.co_gruppo AS co_gruppo,
output.dt_mese AS dt_mese, output.dt_anno AS dt_anno,
output.co_cab AS co_cab, output.co_prodotto AS co_prodotto,
output.co_personaliz AS co_personaliz,
output.co_scaglione AS co_scaglione,
output.co_circuito AS co_circuito, output.co_ope AS co_ope,
output.co_tecno AS co_tecno, output.fl_compenso AS fl_compenso,
output.co_pres_carta AS co_pres_carta, output.co_tp_cer AS co_tp_cer,
output.co_tp_risk AS co_tp_risk, output.co_tp_rend AS co_tp_rend,
output.co_piva AS co_piva,
output.co_prop_pos AS co_prop_pos,
DECODE (fl_chip_mov,
'2', 'S',
'4', 'S',
'5', 'S',
'N'
) AS fl_chip_mov,
SUM (output.va_driver) AS va_driver, output.va_param AS va_param,
SUM (output.va_importo) AS va_importo,
SUM (output.va_importo_alloc) AS va_importo_alloc
FROM tbrc_dati_compensi output
WHERE output.fl_compenso = 'S' AND co_voce IN ('ST_ATRNDIS', 'CT_CONS_B')
GROUP BY output.nu_annomm,
output.co_voce,
output.co_abi,
output.co_gruppo,
output.dt_mese,
output.dt_anno,
output.co_cab,
output.co_prodotto,
output.co_personaliz,
output.co_scaglione,
output.co_circuito,
output.co_ope,
output.co_tecno,
output.fl_compenso,
output.co_pres_carta,
output.co_tp_cer,
output.co_tp_risk,
output.co_tp_rend,
output.co_piva,
output.co_prop_pos,
DECODE (fl_chip_mov, '2', 'S', '4', 'S', '5', 'S', 'N'),
output.va_param
UNION ALL
SELECT output.nu_annomm AS nu_annomm, output.co_voce AS co_voce,
output.co_abi AS co_abi, output.co_gruppo AS co_gruppo,
output.dt_mese AS dt_mese, output.dt_anno AS dt_anno,
output.co_cab AS co_cab, output.co_prodotto AS co_prodotto,
output.co_personaliz AS co_personaliz,
output.co_scaglione AS co_scaglione,
output.co_circuito AS co_circuito, output.co_ope AS co_ope,
output.co_tecno AS co_tecno, output.fl_compenso AS fl_compenso,
output.co_pres_carta AS co_pres_carta, output.co_tp_cer AS co_tp_cer,
output.co_tp_risk AS co_tp_risk, output.co_tp_rend AS co_tp_rend,
NULL AS co_piva,
output.co_prop_pos AS co_prop_pos,
DECODE (fl_chip_mov,
'2', 'S',
'4', 'S',
'5', 'S',
'N'
) AS fl_chip_mov,
SUM (output.va_driver) AS va_driver, output.va_param AS va_param,
SUM (output.va_importo) AS va_importo,
SUM (output.va_importo_alloc) AS va_importo_alloc
FROM tbrc_dati_compensi output
WHERE output.fl_compenso = 'S' AND co_voce NOT IN ('ST_ATRNDIS', 'CT_CONS_B')
GROUP BY output.nu_annomm,
output.co_voce,
output.co_abi,
output.co_gruppo,
output.dt_mese,
output.dt_anno,
output.co_cab,
output.co_prodotto,
output.co_personaliz,
output.co_scaglione,
output.co_circuito,
output.co_ope,
output.co_tecno,
output.fl_compenso,
output.co_pres_carta,
output.co_tp_cer,
output.co_tp_risk,
output.co_tp_rend,
output.co_prop_pos,
DECODE (fl_chip_mov, '2', 'S', '4', 'S', '5', 'S', 'N'),
output.va_param
the single query with the decode seems more temp consuming.
thanks
Mauro
November 24, 2009 - 11:23 am UTC
I gave you enough for you to be able to guess at what might be happening.
if you build two small sets one at a time... versus one set all at once.
question to you, if you size your temp correctly (make it larger) which one is more efficient?
I'll guess that one that does the single pass.