UNION vs UNION ALL no Oracle: a diferença que custa uma madrugada

Quando uma consulta que rodava em dois segundos passa a levar quarenta, a primeira coisa que eu olho não é o índice. É se alguém escreveu UNION onde deveria ter escrito UNION ALL. Essa troca de uma palavra é uma das causas mais frequentes de degradação silenciosa que já encontrei em ambientes Oracle, e o padrão se repete: a query nasce em homologação com trinta mil linhas, passa nos testes, vai para produção onde a mesma consulta devolve oito milhões de linhas e aí o banco começa a gravar arquivo temporário em disco. Entender a diferença entre UNION e UNION ALL é um daqueles assuntos de union sql que parecem básicos demais para merecer atenção, até o dia em que custam uma madrugada.

o que cada operador realmente faz

Os dois combinam o resultado de duas ou mais consultas em um conjunto só. A diferença está no que acontece depois de combinar.

SELECT cod_cliente FROM vendas_2024
UNION ALL
SELECT cod_cliente FROM vendas_2025;

O UNION ALL empilha os resultados e devolve. Se o cliente 4711 aparece nas duas tabelas, ele vem duas vezes. É uma concatenação pura.

SELECT cod_cliente FROM vendas_2024
UNION
SELECT cod_cliente FROM vendas_2025;

O UNION faz a mesma concatenação e depois elimina linhas duplicadas do conjunto final. O cliente 4711 vem uma vez só. Repare que a deduplicação considera todas as colunas do SELECT, não apenas uma chave. Duas linhas só são consideradas iguais se cada coluna for igual.

Até aqui é a definição de manual. O que o manual não enfatiza é o preço.

union vs union all: onde mora o custo escondido

Para saber que uma linha é duplicata de outra, o Oracle precisa comparar essa linha com todas as demais. Ele não tem mágica para isso. Ou ordena o conjunto inteiro e compara vizinhos, ou constrói uma tabela hash em memória com todas as linhas já vistas. As duas estratégias aparecem no plano de execução com nomes bem reconhecíveis: SORT UNIQUE e HASH UNIQUE.

Vamos ver na prática. Primeiro o UNION ALL:

EXPLAIN PLAN FOR
SELECT cod_cliente, dt_venda, vlr_total FROM vendas_2024
UNION ALL
SELECT cod_cliente, dt_venda, vlr_total FROM vendas_2025;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

O plano sai assim:

------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Cost (%CPU) |
------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |  8000K|   12450  (1)|
|   1 |  UNION-ALL          |              |       |             |
|   2 |   TABLE ACCESS FULL | VENDAS_2024  |  4000K|    6220  (1)|
|   3 |   TABLE ACCESS FULL | VENDAS_2025  |  4000K|    6230  (1)|
------------------------------------------------------------------

Duas leituras e uma concatenação. O custo total é praticamente a soma das duas partes.

Agora a mesma consulta trocando por UNION:

EXPLAIN PLAN FOR
SELECT cod_cliente, dt_venda, vlr_total FROM vendas_2024
UNION
SELECT cod_cliente, dt_venda, vlr_total FROM vendas_2025;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Cost (%CPU) |
------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |  8000K|   38900 (32)|
|   1 |  SORT UNIQUE        |              |  8000K|   38900 (32)|
|   2 |   UNION-ALL         |              |       |             |
|   3 |    TABLE ACCESS FULL| VENDAS_2024  |  4000K|    6220  (1)|
|   4 |    TABLE ACCESS FULL| VENDAS_2025  |  4000K|    6230  (1)|
------------------------------------------------------------------

O UNION-ALL continua lá embaixo, porque internamente o Oracle sempre concatena primeiro. O que mudou foi o passo 1. Aquele SORT UNIQUE de oito milhões de linhas triplicou o custo e adicionou 32% de CPU ao plano. Em versões mais recentes você pode ver HASH UNIQUE no lugar, o que costuma ser mais barato em CPU, mas ainda assim precisa segurar o conjunto inteiro em memória.

Em ambientes de verdade eu prefiro olhar o plano real de execução em vez do estimado, com DBMS_XPLAN.DISPLAY_CURSOR:

SELECT /*+ GATHER_PLAN_STATISTICS */ cod_cliente
FROM   vendas_2024
UNION
SELECT cod_cliente FROM vendas_2025;

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')
);

Com ALLSTATS LAST aparecem as colunas que contam a história completa: A-Rows mostra quantas linhas passaram de verdade por cada operação, Used-Mem mostra quanta memória o SORT UNIQUE consumiu, e o sufixo na coluna de memória diz se rodou tudo em memória ou se houve passes em disco.

quando a PGA acaba e o TEMP entra na conversa

Aqui está o motivo pelo qual a query passa nos testes e cai em produção. O SORT UNIQUE acontece na PGA, a memória privada da sessão. O Oracle aloca por sessão uma fatia limitada pelo PGA_AGGREGATE_TARGET. Se o conjunto a ordenar cabe nessa fatia, a operação roda inteira em memória e você nem percebe.

Se não cabe, o Oracle grava blocos parciais na tablespace TEMP, ordena em pedaços e depois faz merge. Isso se chama one-pass ou multi-pass, dependendo de quantas rodadas forem necessárias. Cada rodada é I/O de disco que não existiria com UNION ALL. Em uma base de varejo que eu atendia, uma consulta de fechamento que ordenava quase quarenta milhões de linhas gerava dez gigabytes de TEMP por execução, e quando três usuários rodavam o mesmo relatório ao mesmo tempo a tablespace estourava.

Dá para confirmar se está acontecendo:

SELECT s.sid, s.username, t.blocks * 8192 / 1024 / 1024 AS mb_temp,
       t.segtype, sq.sql_text
FROM   v$session s
JOIN   v$tempseg_usage t ON t.session_addr = s.saddr
JOIN   v$sql sq ON sq.sql_id = s.sql_id
WHERE  t.segtype = 'SORT'
ORDER BY t.blocks DESC;

Se segtype é SORT e o volume em megabytes é alto, alguém está ordenando muito mais do que a memória comporta. Vale checar também v$sql_workarea_active durante a execução para ver se a operação foi para ONE PASS ou MULTI PASS.

O detalhe cruel é que homologação quase nunca reproduz isso. Com trezentas mil linhas o SORT UNIQUE cabe na PGA, o plano é o mesmo, o tempo é aceitável e ninguém desconfia. O comportamento muda de categoria quando o volume cresce, e muda de uma vez.

quando UNION ALL é a escolha correta, não só a mais rápida

Existe um cenário em que usar UNION chega a ser um erro conceitual: quando os conjuntos são comprovadamente disjuntos. Se não pode haver interseção, deduplicar é trabalho por definição inútil.

SELECT nro_pedido, vlr_total
FROM   pedidos PARTITION (p_2024)
UNION ALL
SELECT nro_pedido, vlr_total
FROM   pedidos PARTITION (p_2025);

Partições diferentes de uma tabela particionada por período não compartilham linha nenhuma. O mesmo vale para filtros mutuamente exclusivos:

SELECT cod_contrato, situacao FROM contratos WHERE situacao = 'ATIVO'
UNION ALL
SELECT cod_contrato, situacao FROM contratos WHERE situacao = 'ENCERRADO';

Um contrato não pode estar em duas situações ao mesmo tempo. Colocar UNION aqui faz o banco pagar por uma verificação cujo resultado já é conhecido antes de executar.

a armadilha do UNION por segurança

O padrão que mais vejo em código legado é o desenvolvedor que escreve UNION por precaução. O raciocínio é sempre parecido: se por acaso vier duplicata, o UNION resolve. Só que na maioria dessas consultas o modelo de dados já garante unicidade, seja por chave primária, por constraint ou pela própria lógica dos filtros. O custo é integral e o benefício é zero.

Quando encontro isso em revisão de código, a pergunta que eu faço ao autor é direta: você consegue me mostrar um caso em que essa consulta produz linha repetida? Se a resposta for hesitante, o operador está ali por hábito. E se realmente puder vir duplicata, quase sempre o problema está em um join mal escrito que deveria ser corrigido na origem, com o UNION funcionando como curativo caro sobre um bug.

detalhes de sintaxe que derrubam a consulta

As colunas precisam bater em quantidade, posição e tipo compatível entre os blocos. O Oracle casa por posição, não por nome. Isto compila e devolve lixo:

SELECT cod_cliente, nome FROM clientes_pf
UNION ALL
SELECT nome, cod_cliente FROM clientes_pj;

Se os tipos forem compatíveis o banco aceita e você recebe nomes na coluna de código. Se não forem, vem ORA-01790: expression must have same datatype as corresponding expression. Prefiro sempre listar as colunas explicitamente e na mesma ordem em todos os blocos, nunca SELECT *.

O ORDER BY vale para o resultado combinado e aparece uma única vez, no fim de tudo. Não dá para ordenar cada bloco separadamente. Se precisar ordenar por uma coluna, use o nome do primeiro bloco ou a posição:

SELECT cod_cliente, dt_venda FROM vendas_2024
UNION ALL
SELECT cod_cliente, dt_venda FROM vendas_2025
ORDER BY 2 DESC;

Os nomes das colunas do resultado vêm sempre do primeiro SELECT, então é nele que os apelidos importam.

a regra que eu sigo

Comece escrevendo UNION ALL. Depois pergunte se existe um caso concreto em que a mesma linha pode aparecer nos dois conjuntos e se essa repetição estraga o resultado para quem consome a consulta. Só troque para UNION se a resposta for sim para as duas coisas. Essa inversão de padrão poupa mais tempo de CPU do que boa parte dos índices que eu já criei.

E se você está começando em Oracle e quer entender plano de execução, uso de PGA e escrita de SQL que não desmonta quando o dado cresce, esse tipo de raciocínio é o que trabalho no curso Oracle Fundamentals.