Boas práticas para escrever instruções SQL

 

Olá pessoal,

    A pedido de um colega de trabalho, estou disponibilizando neste post um guia de boas práticas com 10 dicas para escrever intruções SQL em Banco de Dados Oracle. É importante ressaltar que apesar do foco ser Oracle, a maior parte das dicas se aplicam em outros SGBDs também. Outro ponto importante, é que de um modo geral, essas dicas se aplicam somente aos SQLs que são executados inúmeras vezes, em BDs de produção (e não para queries ad-hoc, de testes ou que serão executadas apenas 1 vez). 

     A maioria das dicas abaixo foram retiradas do treinamento de SQL Tuning que ministro na Oramaster, e são apresentadas em mais detalhes por lá. A ideia aqui é ser sucinto, mas caso você queira um pouco mais de informações sobre algum item específico, deixe uma pergunta no campo de comentários.

 

 

1- INDENTAÇÃO e ORGANIZAÇÃO

A primeira dica para escrever instruções SQL é um item que também deve ser aplicado em qualquer  outra linguagem de programação: indente o código que você escrever e separe-o/distribua-o em linhas diversas. - Mas isso dá mais trabalho, demora muito! Sim, demora um pouco mais para escrever o código, porém ele oferece alguns benefícios: fica mais fácil de ler e entender, facilitando inclusive, futuras manutenções. Veja abaixo 2 exemplos de código SQL e tire suas próprias conclusões sobre qual é mais fácil de ler e entender (e visualmente mais bonito): 

     a) Código não indentado e dividido em menos linhas:

    SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, E.SALARY,

    D.DEPARTMENT_NAME FROM HR.EMPLOYEES E JOIN

    HR.DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID JOIN

    HR.JOBS J ON J.JOB_ID = E.JOB_ID WHERE E.SALARY > 5000;  

     b) Código indentado e dividido em mais linhas:

    SELECT  E.FIRST_NAME,

            E.LAST_NAME,

            J.JOB_TITLE,

            E.SALARY,

            D.DEPARTMENT_NAME

    FROM    HR.EMPLOYEES E

    JOIN    HR.DEPARTMENTS D

      ON    E.DEPARTMENT_ID = D.DEPARTMENT_ID

    JOIN    HR.JOBS J

      ON    J.JOB_ID = E.JOB_ID

    WHERE   E.SALARY > 5000;  

 

 

2- ESPECIFIQUE O NOME DAS COLUNAS

 

Em vez de escrever * para retornar todas as colunas de uma (ou mais tabelas), especifique SEMPRE e SOMENTE o nome das colunas desejadas (ver Imagem 01). Desse modo você poderá obter os seguintes benefícios:

     - Reduzir a qtde. de bytes que serão carregados na memória do Oracle (especificamente na Buffer Cache); 
     - Reduzir a qtde. de dados que irão trafegar pela rede;

     - Melhorar o Plano/Tempo de Execução como um todo (ver Imagem 02).

Imagem 01 - SELECT reescrito especificando somente as colunas desejadas

Imagem 02 - Plano de Execução dos SQLs antes e depois de substituir * pelo nome das colunas

 
 
3- ESCREVA SQLs IDÊNTICOS

  

Escreva os SQLs e garanta a sua reutilização. Códigos SQLs iguais, mesmo se forem executados por usuários e/ou máquinas diferentes, terão sempre o mesmo identificador (SQLId). Tendo o mesmo SQLId eles serão alocados uma única vez em memória (na Shared Pool) e terão seu(s) plano(s) de execução(ões) reutilizado(s), ou seja, economizarão memória e serão executados mais rapidamente. Uma forma de garantir a reutilização de SQLs é escrevê-los dentro de Stored Procedures e chamá-las dentro das aplicações (eu sempre trabalhava dessa forma quando era Desenvolvedor), em vez das aplicações submeterem SQLs para o BD . 

 

 

4- UTILIZE APELIDOS

Utilize apelidos curtos para cada tabela referenciada no SQL, e especifique esses apelidos junto com os nomes de cada coluna inclusa no SQL. Isso ajudará a otimizar o "parse" e também ajudará a evitar o erro "ORA-00918: column ambiguously defined". Veja que no código SQL do item 1, as tabelas possuem apelidos curtos (uma letra apenas) e todas as colunas são especificadas previamente com esses apelidos.

 

 

5- UTILIZE O PADRÃO ANSI

Ao escrever uma instrução SQL, utilize o padrão ANSI SQL. Ele permite simplificar algumas instruções SQL complexas, e também permite organizar melhor o código. Para mais informações sugiro a leitura do artigo SQL padrão ANSI X padrão Oracle: Qual é mais rápido?

 

6- FAÇA CONVERSÕES EXPLÍCITAS

 

Faça conversões de tipos de dados explicitamente para evitar conversões indesejadas e também para otimizar performance. Quando você escreve no SQL uma comparação de 2 valores com tipos de dados diferentes, o Oracle converte 1 dos valores para que ambos fiquem com o mesmo tipo de dado e depois efetua a comparação. Não existe no Oracle (e também na maioria das linguagens de programação) comparações com tipos de dados diferentes. Se você escrever um código fazendo, por exemplo, uma comparação entre um valor númerico e um valor alfanúmerico, o Oracle vai tentar fazer uma conversão implícita no valor alfanumérico para numérico, o que resulta em maior tempo de parse (em relação à conversão explícita) e pode gerar erro se o valor contiver algum caractere não-numérico (uma letra qualquer, por exemplo). 

Para que você entenda melhor o que fazer, vou escrever abaixo como você deve, e não deve, realizar uma comparação entre datas, filtrando dados de uma tabela de CLIENTE, cuja data de nascimento seja igual a '20/01/2000':

     1- Como fazer a comparação entre datas:

           WHERE  DT_NASC = TO_DATE('20/01/2000', 'dd/mm/yyyy');

    

     2- Como NÃO fazer a comparação entre datas:

           WHERE  DT_NASC = '20/01/2000';

Se você escrever o código como no exemplo 2, o Oracle tentará converter a string contendo o valor 20/01/2000 em data, e sem a função TO_DATE com máscara informando qual é o formato de data que está sendo utilizado, pode ocorrer erro na conversão (para entender melhor este assunto sugiro a leitura do artigo Configurando National Language Support (NLS) no Oracle). 

Para aprender mais sobre conversões de tipos de dados sugiro a leitura do artigo The Importance of Data Types.

7- CUIDADO COM O USO DE FUNÇÕES NAS COLUNAS

  

Evite, sempre que possível, o uso de funções em colunas para efetuar ligações, filtrar ou transformar dados, pois elas aumentam o uso de CPU, podem eliminar o uso de índices, e consequentemente, aumentam o tempo de execução da instrução SQL. O uso de funções implica também em maior tempo na execução do SQL devido ao processamento linha por linha entre "SQL Engine" e "PL/SQL Engine". Resumindo, antes de incluir uma função em uma coluna na cláusula WHERE (algo muito comum) pense bem se não tem como evitá-la (nos treinamentos de SQL Tuning a gente vê algumas opções de como evitá-las), pois se já existe um índice (b-tree ou bitmap simples, não baseado em função) nessa coluna, ele será ignorado, forçando o Otimizador a realizar uma operação de Full Table Scan (que normalmente demora muito mais que Index Scan). Se não tiver como evitá-la, considere a criação de um "índice baseado em função"!

  

  

8- CUIDADO COM O USO DE DISTINCT

  

Avalie cuidadosamente a necessidade do uso da cláusula DISTINCT. Muitas vezes ela é utilizada indevidamente como "gambiarra" para filtrar linhas repetidas de um CROSS JOIN, nos casos em que a instrução SQL não contém todas as colunas necessárias para efetuar as devidas ligações (já tive que arrumar SQLs assim várias vezes). Esses SQLs tem sua performance prejudicada, pois devido à ausência de uma ou mais colunas necessárias para efetuar o JOIN, o Oracle retorna linhas a mais (sobrecarregando desnecessariamente a Buffer Cache) e depois gasta CPU para eliminar as linhas repetidas. Tudo isso tem um impacto negativo no desempenho. 

  
  
9- EVITE COMPARAÇÕES COM VALORES NULOS E OPERADORES DE NEGAÇÃO

 

Evite comparações de negação NOT EQUAL (!= ou <>) ou NOT IN e também com valores nulos para viabilizar o uso de índices b-tree, que são os mais utilizados em ambientes OLTP. 
  

 

10- EVITE A CLÁUSULA "ORDER BY"

 

Evite o uso da cláusula ORDER BY, pois ela aumenta consideravelmente o consumo de CPU. Prefira fazer a ordenação dentro da aplicação (e não no BD). Eu já fiz isso inúmeras vezes quando era Desenvolvedor e normalmente fica muito mais rápido ordenar os dados na aplicação. Tire o ORDER BY dos SQLs e faça o teste! 

   

 

Por hoje é só!

Deixe o seu comentário se este artigo lhe foi útil ou se você tiver qualque dúvida.

Extra: Sugiro também a leitura do artigo Disciplined PL/SQL.

 

 

[]s