Muitos Índices em uma Tabela

Fala pessoal vou falar de um assunto que sempre gera alguma discussão nos ambientes em que esse assunto foi levantado

Ultimamente tenho percebido certo padrão dizendo que nenhuma tabela pode ter mais de seis índices ou sete índices em alguns clientes. Então algumas instruções SQL podem executar bem, mas algumas podem não executar tão bem assim e se precisarmos de mais índices não podemos adicionar porque já existem 6 índices então algum teria que morrer para criar mais.

Entendendo que muitas vezes alguns índices podem ser redundantes, podemos pensar no índice IDX_OMT_NAME_ID_01 em (COL1, COL2), IDX_OMT_NAME_ID_02 em (COL1, COL2,COL3), e IDX_OMT_NAME_ID_03 em (COL1, COL2,COL3,COL4).

Nesses casos é comum que alguns DBAs pensem em dropar os primeiros dois índices porque eles são redundantes, ou seja, eles têm as mesmas colunas principais e na mesma ordem, como IDX_OMT_NAME_ID_03. 

Mas dropar índices redundantes pode causar problemas com a seleção de uma tabela de condução de um join. (No post sobre métodos de join eu falo sobre tabelas de condução ou driving tables).

Quando temos muitos índices em uma tabela, não vamos obter um impacto grande em questões de desempenho isso se estamos falando de sistemas OLTP, porque apenas algumas linhas são processadas ​​uma única transação, e o impacto de vários índices de em uma atualização geralmente não é tão grande se comparado a ao desempenho de uma consulta.

Agora um número alto de índices em uma tabela pode ser extremamente prejudicial para ambientes com grande número de processos batch de atualização, ou seja com um número alto de insets, updates e deletes.

Veja um leve comparativo:

Quantidade Linhas	Quantidade de Índices	Tempo
1000	0	00:00:00.41
10000	0	00:00:02.99
10000	1	00:00:05.88
10000	3	00:00:07.82
20000	5	00:00:59.69

Da para imaginar o problema quando temos uns 8 índices de uma tabela com alguns GB de tamanho, e inserindo mais alguns GB em um processo batch.

Um workaround para esse problema é dropar on índices antes da execução da batch e recriar os mesmos após a execução . Com opções como NOLOGGING e PARALLEL é possível reconstruir um índice com mais velocidade, porém mesmo com esses recursos o trabalho de reconstruir um índice pode demorar as vezes mais que o próprio processo batch se for o caso.

Minha experiência me diz que o melhor é não criar regras em relação a um número especifico de índices que uma tabela possa ter, porque literalmente cada caso é um caso.O melhor é sempre analisar a relação de custo e beneficio de um novo índice e o Oracle nos ajuda bastante porque podemos usar recursos como o ALTER INDEX MONITORING USAGE  para monitorar e verificar a eficiência e uso dos índices.