oracle

Olá pessoal!

Este post inicia uma série de posts com dicas de ajustes de SQL, direcionado principalmente para desenvolvedores. Aos interessados, recomendo também a leitura da série Tuning durante o projeto: boas práticas. Iniciarei com as dicas mais simples – e que podem parecer muito óbvias para todos os DBA’s e também para alguns desenvolvedores mais experientes – mas que muitas vezes não são praticadas por desenvolvedores, seja por falta de conhecimento ou por falta de noção da importância de pequenos detalhes ao se escrever códigos SQL. Esses detalhes podem significar uma significativa diferença no desempenho de uma aplicação – seja pra melhor, seja pra pior.

Uso de JOINS

Vamos começar com 2 casos muito simples, ambos envolvendo o uso de JOINS entre tabelas.

O que pode parecer muito óbvio para um DBA, as vezes não é praticado por um desenvolvedor. Vamos usar o já conhecido schema HR como exemplo. Imaginem que um desenvolvedor quer uma informação muito simples: O nome de um um funcionário, seu código, e o departamento onde trabalha.

SQL> select employee_id, last_name, department_id 
from employees where employee_id = 100;

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID
----------- ------------------------- -------------
        100 King                                 90

Ele conseguiu os dados acima, todos na mesma tabela, de uma forma bem simples e direta. Porém, seu relatório exigia o nome do Departamento (e não apenas seu código), e por isso ele executa OUTRO select:

SQL> select department_id, department_name 
from departments where department_id=90;

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
           90 Executive

Dentro da aplicação, ele guarda os resultados em variáveis de memória, para depois mostrar os resultados, seja na tela do usuário final ou na impressão de um relatório. Ele obteve seu resultado após duas consultas, ao invés de apenas uma consulta utilizando JOIN. Aos olhos do usuário final, tudo OK: ele obteve corretamente a informação que desejava aos seus olhos. Mas e o desempenho da aplicação? Será que esta é a melhor maneira de utilizar o banco de dados?

Não, esta não é a melhor maneira. Se os seus dados podem ser obtidos através de apenas um SELECT  – ao invés de dois (ou mais), então use apenas um SELECT. Pode haver exceções em alguns casos, mas via de regra uma consulta é sempre menos custosa para o banco de dados do que duas consultas.

Assim ficaria a consulta feita maneira correta:

SQL> select e.last_name, d.department_name 
from employees e, departments d
where e.employee_id = 100  
and e.department_id = d.department_id;  

LAST_NAME                 DEPARTMENT_NAME
------------------------- ------------------------------
King                      Executive

Este é um exemplo muito simples e também muito óbvio, porém podemos lembrar que isto não fica assim tão óbvio quando falamos de querys muito mais complexas. As vezes o desenvolvedor pode acabar não conseguindo todas suas informações em apenas um Select. Nestes casos ele pode – e deve – pedir ajuda ao DBA para chegar na melhor solução.

Cláusula WHERE

A segunda dica deste post é em relação a ordenação dos JOINS. Vamos supor que seu SELECT faça JOINs entre 4 tabelas, chamadas T1, T2, T3 e T4. Vamos supor também que as chaves estrangeiras para realizar os JOINs estejam exatamente nesta mesma ordem, ou seja, T1 com T2, T2 com T3, e T3 com T4. Ou seja, o SELECT poderia ficar assim:

SELECT T1.campo1, T4.campo2
  FROM T1, T2, T3, T4
 WHERE T1.fk = T2.id
   AND T2.fk = T3.id
   AND T3.fk = T4.id;

Para obter o melhor desempenho de um SELECT neste caso, uma medida bem simples pode ser tomada: a prioridade dos JOINs deve ser da maior tabela para a menor tabela (considerando o número de registros). Vamos considerar a seguinte quantidade de registro para cada tabela:

T1 = 20
T2 = 80.000
T3 = 500
T4 = 150

Neste caso, o SELECT ficaria com uma melhor performance com o seguinte código:

SELECT T1.campo1, T4.campo2
  FROM T1, T2, T3, T4
 WHERE T2.fk = T3.id
   AND T3.fk = T4.id
   AND T1.fk = T2.id;

O resultado, ou seja, os dados retornados são exatamente os mesmos. Repare que a única mudança foi a ordem com que foram feitos os JOINs. Primeiro devem ser feitos os mais “destrutivos”, ou seja, o JOIN que elimina mais linhas do result set. O objetivo é fazer com o que o Oracle trabalhe com o menor número possível de registros em memória: quanto mais registros são eliminados na primeira condição da cláusula WHERE, mais próximos estarão os dados do resultado final.

Isto não vale apenas para JOIN’s, mas também para outros tipos de condições na cláusula WHERE. Se no SELECT acima fosse possível usar um unique index na tabela T2, que tem 80 mil linhas, eliminaríamos de cara as outras 79.999 linhas do área de memória ocupada pelo Oracle para realizar a operação.

Esta é uma dica para desenvolvedores que vale para qualquer banco de dados: fazer com que o gerenciador trabalhe com o menor número possível de registros “o quanto antes”. A cláusula WHERE deve estar, portanto, ordenada da condição “mais destrutiva” (primeiro) para a “menos destrutiva” (por último).

Milton Bastos é DBA Oracle e Desenvolvedor PL/SQL, dividido entre Apucarana/PR e Curitiba/PR. Certificações: OCA (Oracle 11g DBA Certified Associate), Oracle Database 11g Data Warehousing Certified Implementation Specialist, Oracle Database 11g Sales Specialist Assessment, Oracle Database Appliance PreSales Specialist Assessment, Oracle Database Appliance Sales Specialist Assessment