Esse artigo faz parte da revista SQL Magazine edição 01. Clique aqui para ler todos os artigos desta edição
Otimização de Comandos SQL
Mesmo um aplicativo bem projetado poderá experimentar problemas de desempenho se a frase SQL que usa for mal construída. Falhas no projeto do aplicativo e na construção do SQL causam a maioria dos problemas de desempenho em bancos de dados projetados adequadamente. Veremos neste artigo como melhorar o comando SQL, para garantir maior performance em sua execução, tendo como base o ORACLE.
A forma como o banco de dados se organiza para responder a requisição de um aplicativo é diretamente influenciada pela maneira como a frase SQL é construída. Existem vários pontos a serem observados nesta construção. O caminho mais curto é o uso de índices para auxiliar a pesquisa mas, é importante observar, a simples criação de um índice nem sempre é o melhor caminho para otimização de comandos SQL.
Alguns conceitos importantes
Antes de partir para como e quais as melhores formas de se aplicar e se escrever comandos SQL é importante relembrarmos alguns conceitos importantes.
Existem dois métodos básicos utilizados pelos SGDB´s para localizar dados, também conhecidos como caminhos de acesso. São eles:
- Full Table Scan: Leitura seqüencial, bloco por bloco, de toda a tabela. Neste método, são verificados registro a registro os critérios de seleção.
- Index Search: Procura de um índice sobre a coluna definida no critério de seleção, identificando a localização dos registros qualificados.
O ORACLE possui seu próprio mecanismo de otimização, chamado otimizador, para executar comandos SQL e dinamicamente determinar que caminho de acesso seguir, dependendo das informações disponíveis. O melhor caminho de acesso é, geralmente, o uso de índices, mas isso nem sempre é verdadeiro. Por exemplo, se existir uma forma de identificar diretamente um registro pelo seu endereço físico, a resposta será mais rápida. (neste caso estamos falando do rowid, endereço físico do registro em uma tabela ORACLE).
Ex.:
SELECT rowid INTO :emp_rowid FROM EMP
WHERE emp.empno = 5353 FOR UPDATE OF emp.ename;
UPDATE emp SET emp.ename = "OTIMIZADOR"
WHERE rowid = :emp_rowid;
Excluindo registros duplicados:
DELETE FROM emp e
WHERE e.rowid > (SELECT MIN(x.rowid) FROM emp x
WHERE x.empno = e.empno);
Algumas informações para auxiliar o acesso do otimizador:
Verificar nas colunas dentro da cláusula WHERE:
- Que colunas são índices;
- Que colunas estão definidas como NOT NULL;
- Que índices podem ser usados.
É possível ajudar o otimizador a procurar o melhor caminho de acesso através da criação de índices e clusters, o que será considerado a partir de agora.
Regras de Utilização de Índices
Um índice será usado se :
- o índice existir;
- a coluna índice for referenciada na cláusula WHERE;
Um índice pode ser usado para testes de:
- Igualdade:
SELECT e.empno, e.ename FROM emp e
WHERE e.job = ‘CLERK’;
- Intervalo ilimitado:
SELECT e.empno, e.ename FROM emp e
WHERE e.job > ‘CLERK’;
- Intervalo limitado:
SELECT e.empno, e.ename FROM emp e
WHERE e.job BETWEEN ‘CLERK’ AND ‘JONES’;
A criação de índices deve ser feita com critério. A atualização (inserts, updates e deletes) em tabelas de dados gera uma ação equivalente nos índices, sendo que o tempo de atualização dos índices é superior ao de atualização da tabelas, pois além da colocação do registro em seqüência dentro do índice é necessário a atualização dos ramos das árvores do índice.
Considerando os detalhes acima, índices devem ser criados somente quando necessário, pois a sua simples existência não implica em melhoria de performance, podendo muitas vezes ser a origem de perda de performance.
Condições de não utilização de Índices
1) Funções ou Operadores Aritméticos
Se a coluna índice for modificada por uma função ou por operadores aritméticos (+,-,*,/), o índice não será utilizado.
Um índice sobre SAL ou ENAME não será utilizado nos seguintes casos:
SELECT e.ename, e.empno FROM emp e
WHERE e.sal*12 = 2400;
SELECT e.ename, e.empno FROM emp e
WHERE SUBSTR(e.ename,1,3) = ‘JON’
Nos casos acima, as seguintes modificações permitem o uso de índice:
SELECT e.ename, e.empno FROM emp e
WHERE e.sal = 2400/12
SELECT e.ename, e.empno FROM emp e
WHERE e.ename LIKE ‘JON%’
2) Conversão de Dados
A conversão de dados pode inibir a utilização do índice da coluna em questão se usado inadvertidamente.
Na seguinte sentença, um índice sobre a coluna HIREDATE não será usado:
SELECT e.ename, e.empno FROM emp e
WHERE TO_CHAR(e.hiredate, ‘month dd,yyyy’) = ‘january 23,1982’
Esta mesma sentença SQL, modificada, usará o índice como segue:
SELECT e.ename, e.empno FROM emp e
WHERE e.hiredate = to_date(‘january 23,1982’ , ’mounth dd,yyyy’)
Se na cláusula WHERE os predicativos utilizam dados de tipos diferentes, o ORACLE automaticamente converte um deles sem a preocupação sobre a melhor escolha para a utilização dos índices. A conversão por default é escolhida, pelo caso mais comum (Ex.: O valor de uma coluna, onde o tipo é number, rowid ou date, comparado com uma constante de tipo char).
Em qualquer caso é muito recomendado o controle da conversão dos dados de maneira explícita.
3) Caso de colunas com valores NULL
Os índices não contêm referência para valores do tipo null. Isto quer dizer que os valores null podem ser recuperados somente através de uma leitura seqüencial completa da tabela (Full Table Scan).
No comando abaixo o índice sobre a coluna COMM não será utilizado:
SELECT e.ename, e.empno FROM emp e
WHERE e.comm is null
Neste código o otimizador considera que a maioria dos registros dentro da tabela são valores not null e irá escolher a leitura seqüencial (Full Table Scan):
SELECT e.ename, empno FROM emp e
WHERE e.comm > = 100
A velocidade de execução dependerá dos dados da tabela. Se a maioria dos registros contiver valores não nulos, a cláusula "comm is not null" será mais rápida.
Os índices concatenados não terão referência para os registros onde todas as colunas que o compõem tiverem valores nulos.
4) Caso de predicados com a condição "NOT EQUAL"
Os índices sobre colunas referenciadas pela condição not equal não serão usadas. O otimizador considera que o número de registros que irão satisfazer a condição será maior do que os registros que não a satisfazem.
O índice sobre DEPTNO não será usado na seguinte sentença SQL:
SELECT e.ename, e.empno FROM emp e
WHERE e.deptno !=10
No comando abaixo, o índice sobre deptno será utilizado:
SELECT e.ename, e.empno FROM emp e
WHERE not e.deptno > 20
O ORACLE executará a sentença SQL como e.deptno <= 20
5) Casos de sentenças SQL com cláusula "ORDER BY"
A cláusula ORDER BY aumenta consideravelmente a necessidade de recursos para execução de uma sentença SQL. Em geral, tabelas temporárias serão necessárias para operação de sort.
É possível evitar a utilização de tabelas temporárias se um índice sobre a coluna alvo do order by existir. As seguintes condições devem ser cumpridas:
- A coluna order by deve ser definida com not null;
- A coluna order by deve ser uma simples coluna índice ou as primeiras colunas de índice concatenado;
- A cláusula order by deve conter somente colunas e não expressões;
- Não deve haver qualquer cláusula group by, distinct ou for update;
- Não deve haver nenhum outro acesso alternativo que seja prioritário sobre o índice da coluna order by;
A seguinte sentença SQL será executada via índice em DNAME se os requisitos mencionados acima forem respeitados:
SELECT d.dname FROM dept d order by d.dname;
SELECT d.dname, d.deptno FROM dept d
WHERE d.loc = ‘dallas’ order by d.dname;
SELECT d.dname, d.loc FROM dept d
WHERE d.dname != ‘accouting’ order by d.dname;
Na sentença abaixo, se um índice sobre a coluna LOC existir, tornar-se-á prioritário e o índice sobre dname não será usado:
SELECT d.dname, d.deptno FROM dept d
WHERE d.loc = ‘dallas’ order by d.dname;
6) Casos de sentenças SQL contendo funções MAX ou `MIN
Um índice será usado para executar uma sentença SQL com MAX ou MIN se as seguintes condições forem satisfeitas:
-
...
23/02/2010 13:55:00
2010
2010
115
7
