Criando índices filtrados no Oracle Database

Olá pessoal,

     No post de hoje vou falar sobre índices filtrados no Oracle Database. Você sabe o que é isso? Já ouviu falar deles?

     Se não ouviu, não se assuste, isso é normal! Eu descobri que eles existem no começo deste ano, ao ser indagado sobre como criá-los no Oracle por uma ex-aluna. No SQL Server, a partir da versão 2008, existe um recurso para criar índices filtrados e ela queria saber se dava para criar algo similar no Oracle. Eu já tinha lido em algum lugar que isso era possível no SQL Server, mas não sabia, até aquele momento, que dava para fazer algo similar no Oracle, foi aí que ela mesma descobriu como fazer, e me ensinou (essa é a vantagem de ser instrutor... a gente acaba aprendendo sempre mais com os alunos)! É muito simples a criação de índices filtrados no Oracle Database e me espantei por nunca ter lido algo relacionado a este assunto pelos sites e blogs que costumo navegar, por isso, resolvi escrever este artigo. É importante ressaltar que no SQL Server isso é uma feature (por isso o funcionamento lá é melhor), e no Oracle é uma espécie de workaround através do uso de índices baseados em função (IBF) + cláusula CASE, mas funciona bem!

     Agora vem a pergunta: O que são índices filtrados? Os índices b-tree comuns (não filtrados) contém sempre os dados de todas as linhas (exceto nulas) da coluna indexada. Já os índices filtrados, contém apenas uma parte das linhas de uma tabela (não confundir com índices parciais em tabelas particionadas), que é definida a partir de uma condição na coluna indexada.

     Vantagens do índices filtrados:
     1- Indexam uma parte das linhas da tabela, portanto são menores e consomem menos espaço em disco;
     2- Por serem menores, podem ser mais eficientes;
     3- Tarefas de manutenção, como por exemplo, um REBUILD, gastam menos tempo;

     4- Podem ser utilizados como solução para otimizar consultas que precisam retornar linhas que possuem valor nulo em uma determinada coluna da tabela (exemplo que veremos abaixo).

     Para demonstrar o seu uso, iremos criar um índice filtrado em uma coluna chamada NU_CELULAR da tabela CLIENTE do schema ECOMMERCE (schema que utilizo nos meus treinamentos de SQL Tuning). Essa coluna permite valores nulos, a tabela tem 64.073 linhas e apenas 4.074 linhas com valores nulos na coluna NU_CELULAR (ver Imagem 01).

Imagem 01: Total de linhas da tabela CLIENTE e total de linhas
com valores nulos na coluna NU_CELULAR

     Em nosso caso, vamos partir do princípio que precisamos otimizar SQLs que façam consultas nessa tabela, filtrando por linhas que tenham NU_CELULAR com valor nulo. Como índices b-tree não indexam valores nulos, criaremos um índice baseado em função que irá substituir o valor nulo por zero, e indexaremos somente estas linhas com valor zero, ou seja, criaremos um índice filtrado nas colunas que contém valor zero, ao invés de nulo. Segue abaixo um roteiro para demonstrar isso:

1- Antes de criar o "índice filtrado" veja na Imagem 02 o plano de execução de uma consulta que irá retornar o total de linhas na tabela que possuem valor nulo na coluna NU_CELULAR:

Imagem 02 - Plano de execução da consulta, fazendo FTS na tabela CLIENTE

     Observe que o Otimizador nos diz que irá executar um FTS na tabela CLIENTE e que o custo total deste SQL é 276.

2- Crie o índice filtrado executando o comando abaixo:
    create index ecommerce.ix_cliente_nucelular on ecommerce.cliente (case when nu_celular is null then 0 end);
  
3- Após criação de um IBF é necessário sempre efetuar a coleta de estatísticas da tabela (isso é necessário pois IBF cria um coluna virtual na tabela e é importante para o Otimizador, ter estatísticas atualizadas dessa coluna após sua criação):
     exec dbms_stats.gather_table_stats('ECOMMERCE','CLIENTE');

4- Execute novamente a mesma consulta do item 1 e veja o "novo" plano de execução Imagem 03:

Imagem 03 - Plano de execução da consulta, fazendo IRS

      Repare que agora o Otimizador irá fazer Index Range Scan (ao invés de FTS) para retornar o resultado, e o custo do SQL caiu para 4, ou seja, o plano nos mostra que conseguimos otimizar o SQL. 

     Um detalhe muito importante que deve ser seguido para que a gente obtenha o resultado esperado do SQL, utilizando o índice filtrado, é que ao invés de escrever um SQL tradicional para filtrar linhas com valores nulos (WHERE COLUNA IS NULL), neste caso, temos que escrever uma condição no filtro que seja exatamente igual aquela que utilizamos na criação do índice (veja na Imagem 04 a diferença entre os SQLs e note que o resultado deles é o mesmo):

Imagem 04 - SQLs tradicional X SQL com CASE para retornar
quantidade de linhas que possuem valores nulos na coluna NU_CELULAR.

Bom pessoal, por hoje é só!

Espero que você tenha gostado. Se tiver qualquer dúvida é só deixar um comentário!

[]s
      

Referências:
     - Filtered index equivalent in Oracle?