(por Fábio Nepomuceno)
Na atual configuração do mercado e dos negócios, precisamos de informações de forma imediata. A concorrência agressiva e a velocidade em que as coisas acontecem nos obrigam a tomar decisões rápidas. Por isso faz-se necessário mantermos as informações estratégicas da organização “sempre a mão”.
Diante deste cenário as empresas investem em sistemas que garantem integridade e velocidade no acesso a seus dados. O SGBD Oracle desde 1992 oferece essas vantagens e não pára de evoluir, como bem colocado por Constantino Jacob no último post deste blog (Você ainda está no Oracle 7?).
Mas de nada adianta todo poder deste referido SGBD se a equipe responsável pelo desenvolvimento das consultas não conhecer formas de extrair o máximo de desempenho da ferramenta.
Em seguida são listadas algumas dicas de como utilizar toda a força do SGBD Oracle em favor de consultas mais rápidas:
1) Bind Variables
O uso de variáveis de ligação (Bind variables) em procedures, triggers, functions, packages e na própria aplicação traz o benefício da redução do uso de memória no servidor, pois não é necessária a fase de “parse” durante a execução do comando.
Exemplo:
select * from notas_fiscais where departamento_id = 63;
select * from notas_fiscais where departamento_id = 68;
Usando uma Bind Variable chamada v_departamento_id, a consulta ficaria assim:
select * from notas_fiscais where departamento_id = :v_departamento_id;
Para que os comandos se tornem iguais, você deve sempre usar variáveis no lugar de dados fixos, como mostra o exemplo acima.
2) Use índices nas tabelas cuidadosamente
2.1)Use índices em colunas que são freqüentemente usados na clausula WHERE de consultas da aplicação ou de consultas usadas por usuários finais.
2.2)Indexe as colunas que são freqüentemente usadas para juntar (JOIN) as tabelas nas diferentes consultas. Prefira fazer JOIN pelas chaves primarias e chaves estrangeiras. Use índices únicos em chaves primárias e índices não únicos em chaves estrangeiras (FOREIGN KEY). Índices únicos são melhores que os não únicos devido a melhor seletividade. Portanto dê preferência a seleção de dados através das chaves primárias.
2.3) Use índices apenas em colunas que possuem uma baixa porcentagem de linhas com valores iguais. Quanto menos distintos forem os valores para uma coluna, obtemos mais velocidade no acesso aos dados desta tabela utilizando essa coluna indexada.
2.4) Não use índices em colunas que são usadas apenas com funções e/ou operadores (diferente de “=”) na clausula WHERE.
As seguintes cláusulas no WHERE não farão uso do índice mesmo que ele esteja disponível:
A.COL1 > A.COL2
A.COL1 < A.COL2
A.COL1 >= A.COL2
A.COL1 <= A.COL2
COL1 IS NULL
COL1 IS NOT NULL
COL1 NOT IN (value1, value2)
COL1 != expression
COL1 LIKE ‘%teste’
No último exemplo de cláusula, o uso do “%” no inicio da string acaba por suprimir a parte por onde coluna é indexada e por isso o índice não é usado. Por outro lado, COL1 LIKE ‘teste%’ ou COL1 LIKE ‘teste%teste%’ faz uso do índice resultando em uma busca por faixas limites.
Quaisquer expressões, funções ou cálculos envolvendo colunas indexadas não farão uso do índice se ele existir. No exemplo a seguir, o uso da função SQL UPPER vai impedir a utilização do índice provocando assim uma consulta FULL TABLE SCAN.
SELECT DEPT_NAME
FROM DEPARTMENT
WHERE UPPER(DEPT_NAME) like ‘SALES%’;
2.5) Não indexe colunas que são freqüentemente modificadas ou quando a eficiência ganha através da criação de um índice não valha a pena devido à perda de desempenho em operações de INSERT, UPDATE e DELETE. Com a criação do índice, estas operações perderão em desempenho devido à necessidade de manter o índice correto.
3) Planos de Execução (Explain Plan)
Sempre que possível faça uso do Explain Plan, pois se for indicado que na consulta existe: Table Access (Full). Isso indica que na tabela está ocorrendo leitura sem a utilização de índices. Desta forma a consulta fica “pesada”, dependendo das quantidades de registros que contém na mesma, aumentando o tempo de retorno.
A dica é: Quando temos um plano de acesso de uma query com está situação, devemos observar a quantidade de itens que a tabela possui. É interessante criar um índice para tabelas com mais de 2000 registros.
4) Use o WHERE ao invés de HAVING para filtrar linhas
Evite o uso da clausula HAVING junto com GROUP BY em uma coluna indexada. Neste caso o índice não é utilizado. Além disso, exclua as linhas indesejadas na sua consulta utilizando a clausula WHERE ao invés do HAVING. Se a tabela FUNCIONARIO possuísse um índice na coluna DEPARTAMENTO_ID, a seguinte consulta não faria uso dele:
SELECT DEPARTAMENTO_ID,
SUM(SALARIO)
FROM FUNCIONARIO
GROUP BY DEPARTAMENTO_ID
HAVING DEPARTAMENTO_ID = 100;
Entretanto, a mesma consulta pode ser escrita para explorar o índice:
SELECT DEPARTAMENTO_ID,
SUM(SALARIO)
FROM FUNCIONARIO
WHERE DEPARTAMENTO_ID = 100
GROUP BY DEPARTAMENTO_ID;
5) Especifique as colunas do índice na clausula WHERE
Em um índice composto a consulta apenas utilizará o índice se as colunas do índice estiverem especificadas na clausula WHERE. A seguinte consulta usará o índice composto baseado na chave primária (NOTA_ID, ITEM_ID, PRODUTO_ID) da tabela ITENS_NOTAS_FISCAIS.
SELECT VALOR_ITEM
FROM ITENS_NOTAS_FISCAIS
WHERE NOTA_ID = 27
AND ITEM_ID = 100;
SELECT VALOR_ITEM
FROM ITENS_NOTAS_FISCAIS
WHERE PRODUTO_ID = 5555;
Nenhuma das consultas acima se beneficiará do uso do índice composto.
A mesma consulta pode ser reescrita para tirar vantagem do índice.
SELECT VALOR_ITEM
FROM ITENS_NOTAS_FISCAIS
WHERE NOTA_ID > 0
AND ITEM_ID > 0
AND PRODUTO_ID = 5555;
6) Quando não utilizar índices
Se você estiver selecionando mais de 15 % das linhas de uma tabela, um FULL TABLE SCAN é geralmente mais rápido do que o acesso pelo índice. Quando o acesso por índice causar lentidão ao invés de apresentar um ganho de desempenho pode utilizar algumas técnicas para eliminar o uso do índice:
Para colunas numéricas:
SELECT NOME
FROM FUNCIONARIO
WHERE SALARIO+0 = 50000;
Para colunas alfanuméricas
SELECT NOME
FROM FUNCIONARIO
WHERE DDD || ‘’ = ‘071′;
Um índice também não é usado se o Oracle tiver que realizar uma conversão implícita de dados.
Para o exemplo a seguir, SALARIO é uma coluna numérica na tabela FUNCIONARIO e uma string é convertida num valor numérico:
SELECT NOME
FROM FUNCIONARIO
WHERE SALARIO = ‘50000′;
Quando a porcentagem de linhas acessadas é menor que 15 % da tabela, então o uso do índice será bem mais performático.
A Polo-iT possui os melhores profissionais com grande conhecimento de ambientes reais de alta criticidade poderá lhe auxiliar no seu projeto de melhoria de performance de consultas, desenvolvimento de aplicações e modelagem de rotinas. Possuímos a solução certa, sob medida para a sua empresa e com excelente retorno.
Espero que as dicas sejam úteis. E até o próximo blog.
Fábio Nepomuceno é Analista de Sistemas e atua há 9 anos como especialista em desenvolvimento de aplicações baseadas em Oracle Forms e Reports, PL/SQL e Oracle Applications. Possui vasto conhecimento em criação de consultas complexas utilizando todo o potencial oferecido pelo Oracle para aplicações contábeis, financeiras e administrativas em geral.