Introdução às Common Table Expressions (CTEs)
As CTEs são uma maneira poderosa de organizar e simplificar consultas SQL complexas. Usando a cláusula WITH, você pode criar expressões temporárias que melhoram a legibilidade, manutenção e, em alguns casos, o desempenho das consultas.
Elas são especialmente úteis em cenários onde a mesma subconsulta é utilizada repetidamente, ou quando uma consulta possui muitos níveis de aninhamento. Além disso, CTEs são mais legíveis do que subconsultas tradicionais e podem reduzir o overhead computacional, dependendo do otimizador do banco de dados.
Exemplos de uso
WITH table_example AS (
-- Subconsulta
SELECT coluna1, coluna2
FROM tabela
WHERE condição
)
SELECT *
FROM table_example;No próximo exemplo é possível ver uma query sem o uso do WITH e outra adotando a solução:
SELECT cod_cliente, COUNT(*) AS tot_vendas
FROM vendas
WHERE status = 'FINALIZADO'
GROUP BY cod_cliente;
SELECT cod_cliente, SUM(vl_pedido) AS tot_valor
FROM vendas
WHERE status = 'FINALIZADO'
GROUP BY cod_cliente;Solução aplicando o WITH:
WITH vendas_realizadasAS (
SELECT cod_cliente, vl_pedido
FROM vendas
WHERE status = 'FINALIZADO'
)
SELECT cod_cliente, COUNT(*) AS tot_vendas, SUM(vl_pedido) AS tot_valor
FROM vendas_realizadas
GROUP BY cod_cliente;Nos exemplos acima vemos como a consulta fica mais clara e legível com o uso do CTE, permitindo assim um fácil entendimento bem como uma melhoria de perfomance e evirando repetição de queries, que são vantagens que buscamos com a utilização do CTE.
Assim como no caso abordado acima, existem outros exemplos do uso da clausula WITH fazendo JOIN com outras tabelas e criando assim consultas mais complexas.
Um exemplo de utilização fazendo JOIN com outras tabelas é possível ver abaixo onde listamos os funcionários que não são gerentes, ou seja, que não possuem subordinados:
WITH subordinados AS (
SELECT id_gerente AS gerente_id
FROM funcionarios
WHERE id_gerente IS NOT NULL
)
SELECT f.id_funcionario, f.nome
FROM funcionarios f , subordinados s
WHERE f.id_funcionario = s.gerente_id
AND s.gerente_id IS NULL;Para finalizar, um ponto importante no banco de dados Oracle, é que as CTEs são frequentemente materializadas (inline view). Isso significa que a consulta é executada uma vez e reutilizada, reduzindo o overhead. Podemos usar o hint /*+ MATERIALIZE */ para forçar a materialização se necessário.
WITH /*+ MATERIALIZE */ cte_table_name AS (
SELECT ...
)
SELECT ...
FROM cte_table_name;Benefícios do uso do WITH
Melhoria na Legibilidade e Manutenção
- Ao nomear subconsultas, o SQL se torna mais legível e mais fácil de ajustar.
Reuso de Resultados
- Você pode referenciar a mesma CTE múltiplas vezes em uma consulta.
Simplificação de Consultas Complexas
- Quebre consultas aninhadas ou cálculos complicados em blocos lógicos.
Redução de Carga Computacional (em alguns casos)
- CTEs materiais podem ser calculadas uma vez e reutilizadas, economizando esforço computacional.
Compatibilidade com Recursão
- CTEs permitem consultas recursivas, que são úteis para hierarquias e estruturas de dados como árvores.
Um dos pontos de ateção é evitar o uso para queries simples, uma vez que isso poderá ocasionar o efeito contrário do desejado e adicionar uma complexidade desnecessário reduzindo a eficiência.
Conclusão
O uso de CTEs é uma boa abordagem para simplificar e organizar consultas SQL complexas, reduzir duplicação de código e, em alguns casos, melhorar a performance. Apesar disso, é importante testar sua aplicação em diferentes cenários, pois a performance pode variar dependendo do otimizador do banco de dados e do tamanho dos dados envolvidos, e assim não gerar os ganhos inicialmente esperados.
Espero que assim como nos demais posts, o tópico abordado tenha contribuído no entendimento de mais essa opção existente dentre as inúmeras outras relacionados ao contexto de banco de dados.
Referências
