Parâmetros Opcionais em consultas no Oracle e no SQL Server
Vou compartilhar uma técnica para extrairmos máximo desempenho na elaboração das nossas procedures com parâmetros opcionais no Oracle e SQL Server.
Quantas vezes você elaborou uma procedure para retornar um cursor (recordset / resultset) que recebe parâmetros opcionais, ou seja apenas as condições na cláusula WHERE se alteram e o restante da query permanece intocado?
Penso que a solução mais adotada seja semelhante a esta abaixo, certo?
Nesta abordagem, o otimizador aplica o filtro de predicados desnecessariamente em qualquer combinação – Observe abaixo o plano de acesso de uma consulta sem filtros informados:
Há uma abordagem muito superior que permite que o código referencie qualquer combinação de variáveis informadas, mas de uma forma que o otimizador do banco de dados trabalhe para nós com máximo desempenho!
Vamos a prática:
Note que sempre fazemos o Bind das variáveis na consulta. Esta é a primeira parte do truque—Isto permite usar SQL dinâmico nativo (da maneira correta!), porque nós temos um conjunto estático de variáveis para realizar bind, em tempo de execução.
Supondo que eu dispare uma consulta sem filtros – Observe que o plano de acesso da consulta está sem filtro de predicados desnecessários:
Se eu informar o parâmetro p_hiredate – Observe que o otimizador avaliou apenas o filtro informado – novamente ignorando os filtros desnecessários:
O otimizador ignora os demais filtros de predicados quando identifica o “1=1 or …” – segunda parte do truque!
Para consultas em tabelas pouco densas, você não perceberá a diferença no custo e no tempo de resposta. Porém, na vida real é possível que você se depare com estruturas contendo milhares de registros e a necessidade de muitos filtros opcionais – E é ai que você verá a diferença!
getting the right answer: good!
getting the right answer right now: best!
Dica para construir um PL/SQL disciplinado: Largue o vício em querer resolver tudo no banco de dados – desconfie de procedures com mais de 50 linhas!
