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

• Como utilizar WITH para otimizar e aumentar a perfomance do seu banco de dados