Artigo SQL Magazine 1 - 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 sequencial, 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 das tabelas, pois além da colocação do registro em sequê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 sequencial 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 sequencial (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:

• MAX ou MIN devem ser apenas expressões da lista de seleção;
• A lista de seleção não deve ter qualquer outro operador de concatenação ou adição a não ser somente MAX ou MIN;
• A lista de seleção não deve conter qualquer outra coluna a não ser uma simples ocorrência da coluna como agrupamento de MAX ou MIN;
• A sentença SQL não pode ser um JOIN;
• A cláusula WHERE e group by não podem ser utilizadas;
• A coluna alvo do MAX ou MAX deve ser indexada ou fazer parte das primeiras colunas de um índice concatenado.

A seguinte sentença SQL utilizará um índice sobre a coluna SAL:

SELECT (max(e.sal)*2) + 10000 FROM emp e;

7) Sentenças SQL recuperam informações sobre uma simples tabela

Uma sentença SQL pode ter vários índices únicos e/ou não únicos à disposição do otimizador. A escolha do índice a ser usado depende da presença de:

• Índices únicos e não únicos: os índices únicos serão favorecidos sobre os índices não únicos.
• Vários índices não únicos : os registros identificados pelo caminho de acesso do índice condutor (o primeiro nome na sentença SQL) serão unidos com aquele identificado por outro índice. A meta é identificar os registros pertencentes a todos os índices.

Se o otimizador não tiver clara escolha concernente a qual índice usar, então irá arbitrariamente escolher o primeiro mencionado dentro da sentença SQL como índice condutor (Driving Index).

8) Caso de sentença SQL com interseção de vários predicados de igualdade dentro da cláusula WHERE com índice não único.

O índice não único será sorteado pela coluna rowid a fim de minimizar o número de comparações necessárias.

Se nós temos índice não único sobre JOB e DEPTNO:

SELECT e.ename FROM emp e
 WHERE e.job = ‘manager’ and e.deptno = 20;

a. Procura a primeira ou próxima referência do índice sobre job (driving index) contendo ‘manager’.

b. Procura a primeira ou a próxima referência do índice deptno contendo valor 20.

Compara as duas rowid:

• Se as duas forem iguais, registro encontrado. Vai a próxima referência de "a." e compara com a próxima referência de "b.".
• Se forem diferentes guarda a referência de "a." e passa a próxima referência de "b.".

9) Caso de sentença SQL com interseção de um predicado de igualdade com um predicado sem limite, dentro da cláusula WHERE com índice não único.

Somente o índice sobre a igualdade será usado. No exemplo que segue, se índices não únicos existirem sobre as colunas JOB e DEPTNO, o índice sobre JOB será utilizado, os registros correspondentes serão recuperados e verificada a validade do segundo predicado.

SELECT e.ename FROM emp e
 WHERE e.job = ‘manager’ and e.deptno > 10

10) Caso de índice Concatenado

Índices concatenados são índices formados por várias colunas representados como se fosse simplesmente uma única coluna.

a) Interseção de vários predicados de igualdade.

Se um índice concatenado existir sobre DEPTNO e JOB, somente uma referência será necessária para localizar a rowid correspondente:

SELECT e.ename FROM emp e
 WHERE e.job = ‘manager’ and e.deptno = 10

b) Interseção de predicados sem limite com predicados de igualdade.

Se um índice concatenado existir sobre DEPTNO e JOB, será utilizado para executar a seguinte sentença SQL :

SELECT e.ename FROM emp e
 WHERE e.job = ‘manager’ and e.deptno > 10

c) Interseção de predicado sem limite.

Se um índice concatenado existir sobre DEPTNO e JOB, será utilizado para executar a seguinte sentença SQL:

SELECT e.ename FROM emp e
 WHERE e.job > ‘manager’ and e.deptno > 10

d) Utilização parcial ou total dos índices concatenados.

O otimizador pode somente usar a(s) primeira(s) parte(s) do índice concatenado.

No seguinte caso, um índice concatenado foi criado sobre as colunas EMPNO, ENAME e DEPTNO:

A seguinte sentença SQL usa plenamente o índice concatenado:

SELECT * FROM emp e
 WHERE e.empno = 7369
 and e.ename = ‘smith’ and e.depnto = 20
  
 SELECT * FROM emp e 
 WHERE e.ename = ‘smith’
 and e.empno = 7369
 and e.deptno = 20

As seguintes sentenças SQL utilizam parcialmente o índice concatenado:

Uso parcial do índice usando somente EMPNO e ENAME:

SELECT * FROM emp e
 WHERE e.empno = 7369 and e.ename = ‘smith’

Uso parcial do índice usando somente EMPNO e ENAME :

SELECT*FROM emp e
 WHERE e.empno = 7369 and e.deptno = 20

As seguintes sentenças SQL não usam o índice :

SELECT * FROM emp e
 WHERE e.ename = ‘smith’ and e.deptno = 20
 SELECT * FROM emp e WHERE e.ename = ‘smith’
 SELECT * FROM emp e WHERE e.deptno = 20

11) Casos de Índice único e não único na mesma sentença SQL.

O otimizador favorecerá a utilização do índice único.

No seguinte caso o índice não único existe em SAL e um índice único em EMPNO:

SELECT e.ename FROM emp e
 WHERE e.sal = 3000 and e.empno = 7902

O índice único será usado para procurar a rowid correspondente ao empno = 7902.

Nos registros recuperados serão verificados o valor de sal.

12) Caso de vários índices na mesma sentença SQL.

Se vários índices únicos estão disponíveis, o otimizador irá escolher o primeiro mencionado na sentença SQL. Por exemplo, se índices únicos existirem sobre ENAME e EMPNO, e um índice não único existir sobre SAL, a seguinte sentença SQL será executada sobre o índice ENAME

SELECT e.deptno FROM emp e
 WHERE e.sal = 3000
 and e.ename = ‘scott’ and e.empno = 7602

Otimização da cláusula OR (União de predicados)

O caminho de acesso é determinado como se a cláusula OR e suas ramificações estivessem ausentes. O caminho é determinado considerando cada ramificação da cláusula OR separadamente.

Exemplo A

Índice existente sobre SAL e JOB.

SELECT e.ename FROM emp e
 WHERE e.deptno = 10
 and (e.sal = 3000 or e.job = ‘clerk’)

Análise:

1. Para DEPTNO = 10 - Pesquisa sequencial na tabela;
2. Para SAL = 3000 - Índice não único sobre SAL;

Para JOB = ‘clerk’ - Índice não único sobre JOB;

Neste caso, o caminho de acesso identificado por "2" é melhor que o identificado por "1", então a cláusula OR é otimizada para utilização dos índices.

Exemplo B

Somente índice sobre SAL utilizando a mesma sentença do exemplo A.

Análise:

1. Para DEPTNO = 10 - Pesquisa sequencial na tabela
2. Para SAL = 3000 - Índice não único sobre SAL

Para JOB = ‘clerk’ - Pesquisa sequencial na tabela

Os dois caminhos de acesso identificado por "2" não são melhores que o identificado por "1", então os índices não serão utilizados e ocorrerá uma pesquisa sequencial na tabela. O otimizador na Cláusula OR exige a presença dos índices utilizáveis dentro de cada coluna referenciada dentro da cláusula OR.

Verificando o “Caminho de Acesso” do otimizador

Podemos verificar o caminho de acesso utilizado pelo SGDB para executar uma determinada frase SQL. Chamaremos esse caminho de “Acesso de Plano de Execução”. No plano de execução são verificados todos os procedimentos feitos pelo SGDB, como utilização ou não de índices, clusters, rowid, além da verificação da quantidade de passos realizados pelo banco para execução do comando. Este procedimento é muito útil, pois pode ser utilizado como recurso para verificarmos se as alterações efetuadas em um comando realmente otimizam a execução do mesmo. A verificação do plano de execução é feita no Oracle através do comando EXPLAIN PLAN. A sintaxe do comando é a seguinte:

EXPLAIN PLAN 
 SET STATEMENT_ID = 'COMAND_ID' 
 INTO OUTPUT
 FOR COMANDOSQL;

Onde:

• COMAND_ID é um String identificador para posterior visualização do plano de execução.
• OUTPUT é tabela onde o plano de execução é gerado. O usuário que executar o EXPLAIN PLAN deve ter direitos de INSERT sobre a mesma. O comando para criação desta tabela é o seguinte:

O Script para criação desta tabela chama-se “UTLXPLAN.SQL” e é instalado juntamente com oracle e a sua localização depende da versão do banco.

• COMANDOSQL é o comando a ser analisado.

Para verificação do plano de execução gerado pelo EXPLAIN PLAN devemos consultar a tabela informada no comando. Este procedimento pode ser efetuado de várias maneiras, uma delas é a que segue:

SELECT SUBSTR(LPAD(' ',2*(LEVEL-1))||operation,1,50) operation, options, 
 object_name, position 
     FROM OUTPUT
     START WITH id = 0 AND statement_id = 'COMAND_ID'
     CONNECT BY PRIOR id = parent_id AND 
     statement_id = 'COMAND_ID';

Onde,

• COMAND_ID é um String identificador do plano de execução informado no EXPLAIN PLAN
• OUTPUT é tabela onde o plano de execução é gerado.

Vamos exemplificar a execução do EXPLAIN PLAIN validando a seguinte informação:

“Um índice é utilizado se ele existir e se a coluna que faz parte deste índice estiver contida na clausula WHERE do comando SQL. Este mesmo índice não é utilizado se existirem funções de conversão de dados envolvendo esta coluna.”

Em outras palavras: a execução do comando “SELECT * FROM EMP WHERE EMPNO = 50” utilizará o índice da chave primária, que contém a coluna EMPNO, e a execução do comando “SELECT * FROM EMP WHERE TO_CHAR(EMPNO) = ‘50’”, não utilizará o índice.

Utilizaremos o comando do quadro em destaque acima para criação da tabela que receberá o plano de execução, com o nome de PLAN_TABLE. Em seguida executaremos o EXPLAIN PLAN para verificação da utilização do índice com o seguinte comando:

EXPLAIN PLAN
     SET STATEMENT_ID = 'SQLMagazine Com Indice'
     INTO PLAN_TABLE
     FOR 
     SELECT * FROM EMP WHERE EMPNO = 50;

A verificação do plano de execução é feita de seguinte forma:

SELECT SUBSTR(LPAD(' ',2*(LEVEL-1))||operation,1,50) operation, options, 
 object_name, position 
     FROM PLAN_TABLE
     START WITH id = 0 AND statement_id = 'SQLMagazine Com Indice'
     CONNECT BY PRIOR id = parent_id AND 
     statement_id = 'SQLMagazine Com Indice';

O resultado com o plano de execução é o seguinte:

Analisando este resultado comprovamos que o índice referente a chave primária foi utilizado. Vamos verificar agora o mesmo comando alterado:

EXPLAIN PLAN
     SET STATEMENT_ID = 'SQLMagazine Sem Indice'
     INTO PLAN_TABLE
     FOR 
     SELECT * FROM EMP WHERE TO_CHAR(EMPNO) = ‘50’;

A verificação do plano de execução é feita de seguinte forma:

SELECT SUBSTR(LPAD(' ',2*(LEVEL-1))||operation,1,50) operation, options, 
 object_name, position 
     FROM PLAN_TABLE
     START WITH id = 0 AND statement_id = 'SQLMagazine Sem Indice'
     CONNECT BY PRIOR id = parent_id AND 
     statement_id = 'SQLMagazine Sem Indice';

Este resultado mostra que a utilização do índice foi anulada pela função aplicada a coluna.

Conclusão

Os cuidados a serem tomados com o acesso a dados da aplicação são parte importante de um projeto. A forma como as consultas são escritas e como o banco de dados está projetado influencia diretamente na performance do produto. Esses fatores não devem andar separados. Na maioria dos casos é necessária a perfeita integração entre a consulta escrita e o estado do banco de dados. Muitas vezes uma simples “arrumação” na consulta, trocando algumas cláusulas ou mudando um pouco a “lógica” das condicionais, garante um ganho na velocidade de execução e resposta da mesma. Não esqueça nunca de analisar se o índice a ser criado é realmente necessário, para não adicionar um mais um processo para o seu banco. Boa sorte e bons projetos!