Social Icons

14 de jul. de 2011

Otimizando consultas com índices BITMAP


Olá Pessoal,
  
     Estive bastante atarefado nas últimas semanas (terminando de montar o material do treinamento de SQL Tuning) e por este motivo demorei um pouco mais para postar um novo artigo aqui no blog. Hoje retomei as atividades e irei apresentar o que é um índice BITMAP e para quê ele serve.
    
     O índice bitmap é um tipo de índice que foi introduzido no Oracle Database 7 e que pode ser utilizado para otimizar consultas que utilizam como filtro de dados, colunas que possuem baixa cardinalidade, ou seja, colunas que possuem pouca variação de valores nas linhas de uma tabela. Ao criar um índice bitmap em uma coluna, o Oracle monta um mapa de bits para todas as linhas da tabela, contendo todos os valores possíveis para a coluna. Para cada linha há uma mapa de todos os valores possíveis da coluna indexada. O Oracle grava um bit 1 onde o valor existe em uma determinada linha e 0 para os valores que não existem nesta linha.
  
     Para exemplificar uma coluna candidata para a criação de um índice bitmap, podemos citar uma coluna que armazena o sexo de registros de clientes. Em uma tabela que armazena dados de clientes, na coluna sexo só podemos armazenar 1 de 2 valores distintos: masculino ou feminino.
  
     No exemplo da Figura 1 temos a representação de um índice bitmap que foi criado na coluna sexo de uma tabela que possui 19 linhas. Veja na figura que temos 19 linhas representadas horizontalmente (com cabeçalho em cor amarela) e os valores possíveis para a coluna SEXO (Masculino e Feminino), representados verticalmente (coluna com título VALOR). Neste mapa de bits podemos observar que as linhas 3, 4, 5, 7, 12, 14, 15, 16 e 18 da tabela possuem o valor Masculino gravado na coluna SEXO. Nas linhas 1, 2, 6, 8, 9, 10, 11, 13, 17 e 19, temos o valor Feminino.

Figura 1 - Estrutura do índice bitmap na coluna "sexo" de uma tabela que possui 19 linhas
 
     É importante ressaltar que um índice só será utilizado pelo Otimizador de Query do Oracle quando ele verifica que é mais rápido fazer um Index Scan do que realizar um Full Table Scan (FTS). Em geral, índices B-tree são utilizados quando uma consulta retorna até 4% dos dados de uma tabela, mas não estamos falando de índice B-tree, e sim de índices bitmap. No caso de índices bitmap, eles serão utilizados e são mais eficientes, quando as colunas indexadas possuem baixa cardinalidade. Quanto menor a cardinalidade, melhor o benefício de utilizá-lo.

Obs.: O exemplo da Figura 1 foi utilizado apenas para fins didáticos, pois o índice bitmap não seria utilizado naquela tabela porque ela possui somente 19 linhas. Índices não são utilizados para pesquisar dados em tabelas pequenas, pois nestes casos, um FTS é mais rápido.

  
    Índices BITMAP podem ser criados sem restrições em ambientes de Bancos de Dados (BD) OLAP, porém em BD´s OLTP eles não são recomendados e se forem utilizados, devem ser criados com muito cuidado, pois há um custo de processamento (e consequentemente de tempo) alto para  atualizações linha-a-linha (característica comum em sistemas OLTP).
   
     Em BD´s OLTP índices bitmap podem gerar locks, deadlocks e um alto tempo de espera nas transações (INSERT, UPDATE ou DELETE) que envolvem as tabelas indexadas. Isso chama-se contenção de linhas e pode causar sérios problemas de performance em sistemas que sofrem atualizações frequentes. Crie índices bitmap somente se a tabela sofre poucas atualizações!
    
     Para criar um índice bitmap com o nome IX_SEXO na coluna SEXO da tabela CLIENTE, utilize o seguinte comando:
           CREATE BITMAP INDEX IX_SEXO ON CLIENTE (SEXO);

       O índice do exemplo acima poderia otimizar uma consulta na tabela CLIENTE, que retorna somente registros de clientes do SEXO feminino, como no exemplo abaixo:
    SELECT   *
    FROM     CLIENTE
    WHERE    SEXO = 'FEMININO';
  
        
Obs.: No treinamento de SQL Tuning eu ensino como e quando criar este e outros tipos de índices do Oracle Database. É muito importante saber quando um índice pode ser criado, qual tipo de índice criar e quando ele realmente irá te ajudar, pois um índice mal projetado poderá não trazer ganho algum de performance nas consultas desejadas.
  
  
CONCLUSÃO
     Índices bitmap são ótimos para otimizar consultas em colunas com baixa cardinalidade. Jamais crie índices bitmap em colunas que possuem alta cardinalidade, como por exemplo, colunas que possuem os valores da chave primária (PK´s) de uma tabela, pois além do Oracle não permitir criar índices bitmap em PK´s, nestes casos eles seriam extremamente ineficientes. Para colunas com alta cardinalidade utilize índices b-tree (tipo de índice padrão do Oracle Database).  
     Não crie índices bitmap em colunas de tabelas que sofrem atualizações frequentes (o que ocorre normalmente em BD´s OLTP). Isso irá degradar demasiadamente a performance das atualizações, podendo gerar locks e erros nas aplicações (que podem ocorrer como consequência de deadlocks).
  
  
Por hoje é só!
[]s
  
  
  
Referências
   - Livro Aprendendo SQL, Editora Novatec, Alan Beaulieu;
   - Treinamento Oracle Database 10g: SQL Fundamentals II;
   - Treinamento Oracle Database 10g: Performance Tuning;
   - Oracle Bitmap Index Techniques: http://www.dba-oracle.com/oracle_tips_bitmapped_indexes.htm.

6 comments:

  1. Artigo bastante esclarecedor. Parabéns!
    Mas fiquei com uma dúvida: você falou que índice bitmap é aconselhado para colunas com baixa cardinalidade, o exemplo que você usou foi sexo (M/F), o BD gravou 0 e 1, um valor representa M e outro representa F.
    No entanto, quando tivermos uma cardinalidade maior que 2 (maior que M/F) e ainda sendo baixa, devemos utilizar bitmap também correto? Como que o banco gravaria? Por exemplo, como o banco gravaria uma coluna com índice bitmap contendo as notas de alunos (nota de aluno pode ser os inteiros de 1 a 10)?

    ResponderExcluir
  2. Guido, obrigado pelo comentário!

    Sempre que uma coluna tiver baixa cardinalidade vc pode usar índices bitmap para otimizar consultas, porém tome cuidado com a contenção de linhas. Se a tabela sofre muitas alterações concorrentes vc poderá ter sérios problemas de bloqueios e performance nas atualizações.

    A estrutura interna dos índices bitmap é sempre composta de valores 0 e 1 que são gravados no mapeamento das linhas e valores distintos da coluna. Vou tentar explicar atráves de outro exemplo. Se vc tem uma tabela chamada CARROS que contém milhares de carros de uma montadora, com as colunas marca, modelo e cor e se vc criar um índice bitmap na coluna cor, supondo que existem somente 4 cores (branco, preto, vermelho e prata), a estrutura interna do índice poderia ficar assim:

    ------------------------------------
    LINHA
    ------------------------------------
    VALOR | 1 | 2 | 3 | 4 | 5 | ...
    ------------------------------------
    Branco | 0 | 0 | 0 | 0 | 1 | ...
    Preto | 0 | 0 | 0 | 1 | 0 | ...
    Vermelho | 1 | 0 | 0 | 0 | 0 | ...
    Prata | 0 | 1 | 1 | 0 | 0 | ...
    ------------------------------------

    Traduzindo... o carro da linha 1 é Vermelho, linhas 2 e 3 são Prata, linha 4 é Preto e linha 5 é Branco!

    Será que ficou claro agora?


    []s

    ResponderExcluir
  3. Muito bom o post !! Sobre a duvida Guido, eu entendi com o novo exemplo.
    Parabens.

    ResponderExcluir
  4. Fabio eu tenho uma coluna com dois valores somente, porem a tabela sofre muitas alterações, neste caso qual seria o melhor indice?

    ResponderExcluir
    Respostas
    1. O melhor índice depende de uma série de fatores, não é tão simples assim a resposta. Sugiro que você assista aos vídeos do link https://www.fabioprado.net/2016/04/indices-no-oracle-database-tudo-o-que.html

      []s

      Excluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)