SQL TUNING: UNION X CASE

Olá!

Não é fato extraordinário encontrar códigos SQLs com querys complexas fazendo uso do operador UNION. Esta forma de codificação pode gerar graves problemas de performance.

Uma alternativa para estes casos, é substituir o operador UNION pela expressão CASE.

Vamos ver um exemplo típico.

O SQL abaixo utiliza o operador UNION para exibir dados de parcelas com seus respectivos STATUS.

select P.NM_CONTRAT CONTRATO,
P.NM_LIBERAC LIBERACAO,
P.NM_PARCELA PARCELA,
'ABERTA' SITUACAO
from PARCELAS P
where P.LG_BAIXADO = 'F'
and P.DT_CADASTR between '02/01/2010' and '05/01/2010'
union
select P.NM_CONTRAT CONTRATO,
P.NM_LIBERAC LIBERACAO,
P.NM_PARCELA PARCELA,
'BAIXADA' SITUACAO
from PARCELAS P
where P.LG_BAIXADO = 'T'
and P.DT_CADASTR between '02/01/2010' and '05/01/2010';

Olhando-se para o plano de execução abaixo nota-se que, apesar do uso de um índice sobre a coluna DT_CADASTR para acessar os dados, a tabela PARCELAS é acessada 2 vezes por causa do operador UNION.


Decorrido: 00:00:02.37

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=224 Card=9856 Bytes=236544)
1 0 SORT (UNIQUE) (Cost=224 Card=9856 Bytes=236544)
2 1 UNION-ALL
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'CG_PARCE' (Cost=88 Card=4928 Bytes=118272)
4 3 INDEX (RANGE SCAN) OF 'CG_PARCE_DT_CADASTR' (NON-UNIQUE) (Cost=29 Card=4928)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'CG_PARCE' (Cost=88 Card=4928 Bytes=118272)
6 5 INDEX (RANGE SCAN) OF 'CG_PARCE_DT_CADASTR' (NON-UNIQUE) (Cost=29 Card=4928)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
1628 bytes sent via SQL*Net to client
536 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
24 rows processed

Agora, vamos reescrever o SQL substituindo o UNION pela expressão CASE:

select P.NM_CONTRAT CONTRATO,

P.NM_LIBERAC LIBERACAO,
P.NM_PARCELA PARCELA,
CASE
WHEN P.LG_BAIXADO = 'F' THEN
'ABERTA'
WHEN P.LG_BAIXADO = 'T' THEN
'BAIXADA'
ELSE
'NAO DEFINIDA'
END SITUACAO
from PARCELAS P
where P.DT_CADASTR between '02/01/2010' and '05/01/2010';

O plano de execução abaixo mostra que a reescrita do SQL reduziu 50% o uso de recursos do banco de dados acessando a tabela PARCELAS uma única vez. Além disso, ela executa apenas 30% dos sorts em memória em relação ao comando escrito com UNION.

Decorrido: 00:00:02.62


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=88 Card=9856 Bytes=236544)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CG_PARCE' (Cost=88 Card=9856 Bytes=236544)
2 1 INDEX (RANGE SCAN) OF 'CG_PARCE_DT_CADASTR' (NON-UNIQUE) (Cost=29 Card=9856)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
2096 bytes sent via SQL*Net to client
536 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
24 rows processed
 
Portanto, o CASE é uma ótima alternativa ao uso do UNION para codificar querys com melhor performance.

Até a próxima.