Otimização de queries com funções analíticasQuery tuning with analytic functions

Ao longo de mais de duas décadas administrando Oracle e SQL Server — passando por Data Warehouse com Business Objects, migrações do Arbor e, mais recentemente, a gestão de banco de dados no ACME — uma das atividades mais recorrentes continua sendo a mesma: tornar uma consulta lenta em uma consulta aceitável sem reescrever a aplicação inteira.

Across more than two decades administering Oracle and SQL Server — from Data Warehouse work with Business Objects, through Arbor migrations, to more recently managing databases at ACME — one of the most recurring activities is still the same: turning a slow query into an acceptable one without rewriting the whole application.

Por que funções analíticasWhy analytic functions

Grande parte dos relatórios "lentos" que investiguei ao longo da carreira usava agregações e subconsultas correlacionadas onde uma função analítica (window function) resolveria com uma única passada nos dados — ROW_NUMBER(), RANK(), SUM() OVER (PARTITION BY ...) e afins.

Most of the "slow" reports I've investigated over the years used aggregations and correlated subqueries where an analytic (window) function would solve it in a single pass over the data — ROW_NUMBER(), RANK(), SUM() OVER (PARTITION BY ...) and similar.

-- exemplo: top 3 consultas mais custosas por instância, sem subconsulta correlacionada
SELECT instancia, sql_id, custo,
       RANK() OVER (PARTITION BY instancia ORDER BY custo DESC) AS posicao
FROM   estatisticas_execucao
QUALIFY posicao <= 3;

Checklist práticoPractical checklist

• Revisar o plano de execução antes de qualquer reescrita.Review the execution plan before any rewrite.
• Substituir subconsultas correlacionadas por funções analíticas quando o objetivo for ranking, acumulados ou comparação entre linhas.Replace correlated subqueries with analytic functions when the goal is ranking, running totals, or row-to-row comparison.
• Validar estatísticas de otimizador atualizadas antes de tirar conclusões sobre custo.Confirm optimizer statistics are up to date before drawing conclusions about cost.
• Medir antes e depois com o mesmo volume de dados de produção.Measure before and after using the same production data volume.

Resultado observadoObserved result

Nos casos mais recorrentes, a troca de subconsultas correlacionadas por funções analíticas reduziu o tempo de execução de relatórios críticos de minutos para segundos, sem qualquer mudança na camada de aplicação.

In the most recurring cases, replacing correlated subqueries with analytic functions cut execution time for critical reports from minutes to seconds, with no changes to the application layer.

Baseado na experiência profissional do autor (fonte: cv.pdf). Based on the author's professional experience (source: cv.pdf).