Otimizando SQLs com comparações de negação

Olá pessoal,

     No post de hoje quero apenas compartilhar um dica simples, mas muito útil, que mostro nos meus treinamentos de SQL Tuning: Evite comparações de negação NOT EQUAL (!= ou <>) ou NOT IN para viabilizar o uso de índices b-tree. Se não for possível evitá-los avalie o uso de índices BITMAP. 

     De um modo geral devemos evitar comparações de negação sempre que possível, e por que devemos evitá-las? Porque se você tiver um índice do tipo b-tree na coluna em que a comparação de negação for utilizada, normalmente ele será ignorado, e por que isso ocorre? Porque segundo a Oracle (consultar última versão do Oracle SQL Tuning Guide) índices do tipo B-tree são ótimos para serem criados em colunas com alta cardinalidade visando otimizar consultas que retornam poucos dados, em média quando a seletividade é de até 4% do total de linhas das tabelas envolvidas. Mais que isso normalmente o índice b-tree deixará de ser eficiente, então o otimizador irá preferir realizar uma operação de FULL TABLE SCAN (ao invés de INDEX SCAN). Quando há uma comparação de negação, normalmente a seletividade será maior que 4%, por esse motivo, "quase sempre" o otimizador irá ignorar índices do tipo B-tree. É importante ressaltar, porém, que existem (pouquíssimos) casos em que o btree pode ser usado até mesmo numa comparação de negação, mas como isso raramente irá acontecer, é necessário pensar em outra solução para otimizar SQLs desse tipo. Uma alternativa é criar um índice do tipo BITMAP, ao invés de B-tree. O Bitmap pode atender perfeitamente essa necessidade, porém ele possui outras desvantagens, como por exemplo, ter um tempo de atualização extremamente alto, o que normalmente inviabiliza o seu uso em ambientes OLTP. Ele é ótimo para ambientes OLAP!

     Veja na "Imagem 01" que criei um índice B-tree na coluna ID_STATUS da tabela ECOMMERCE.PEDIDO, e em seguida verifiquei o plano de execução de um SELECT que filtra dados na coluna ID_STATUS através de uma comparação de negação. Neste caso podemos verificar na "Imagem 02" que o índice foi ignorado.

   

Imagem 01 - Criação de um índice b-tree na coluna ID_STATUS

Imagem 02 - Plano de execução de um SELECT filtrando por comparação de negação na coluna ID_STATUS 

     Agora veja na "Imagem 03" que em seguida eu apaguei o índice B-tree criado anteriormente e recriei-o como BITMAP. A partir de então o otimizador deixou de fazer "FULL TABLE SCAN" e realizou uma operação do tipo "BITMAP INDEX FULL SCAN". Podemos deduzir analisando o custo do SQL em um plano de execução estimado, antes e depois da criação do índice bitmap, que mesmo utilizando índice o SQL não foi otimizado, pois o custo que era 258 (ver Imagem 02) aumentou para 1.439 (ver Imagem 04).

Imagem 03 - Recriando o índice como bitmap

Imagem 04 - Plano de execução do SELECT utilizando o índice bitmap

CONCLUSÃO

O índice Bitmap pode ser utilizado em SQLs que utilizam comparação de negação, mas isso não significa que o SQL será "sempre" otimizado com o uso desse índice. Muitas vezes a otimização irá ocorrer, mas no caso apresentado nesse post ela não ocorreu. Outra solução que poderia ser utilizada nesse caso seria reescrever o SQL utilizando a cláusula IN (ao invés de NOT IN), pois a coluna ID_STATUS possui apenas 5 valores, então poderíamos mudar a cláusula WHERE para ficar igual a:   WHERE  ID_STATUS IN (3,4).

 

  

 

Por hoje é só!

Se você tiver dúvidas deixe um comentário que responderei o mais breve possível. Caso você queira aprender mais sobre este assunto, sugiro participar de uma turma dos meus treinamentos de SQL Tuning.

[]s