Olá a todos. Nesta quarta parte da série falarei um pouco sobre otimização de índices fornecendo algumas dicas e conceitos sobre esse que é uma das peças mais importantes de uma base de dados Oracle.

 

1) DICAS GERAIS PARA UM BOM USO DE ÍNDICES

* Criar índices em tabelas que quando consultadas, retornam de 5% a 10% do total de linhas da tabela, ou seja, se há uma tabela de 1000 linhas, e as consultas em cima dessa tabela retornam no máximo 100 linhas, essa coluna usada como filtro é uma candidata a criação de um índice. Índices não tornam a query mais eficiente se esta vai retornar a maioria dos dados da tabela. Neste caso é mais eficiênte um Full Table Scan do que uma busca indexada.

* Criar índices B-Tree em colunas com boa seletividade. A seletividade é o percentual de linhas em uma coluna que possuem o mesmo valor (valor repetido). Uma coluna com seletividade de 5% (densidade de 0.5) é uma coluna com boa seletividade, pois apenas 5% dos valores dessa coluna são valores repetidos. Para colunas com baixa seletividade, recomenda-se criar índices BITMAP, mas isto não é uma regra geral.

* Recomenda-se, quando possível, não indexar colunas que sofrem muitas operações DML, pois nesse caso, tanto os dados nos índices quando nas tabelas devem ser modificados, gerando mais UNDO e REDO.

* Criar índices explicitamente em colunas que possuam Foreign Keys. Criar o índice na coluna que referencia (tabela-filha) e não na referenciada (tabela-pai).

* É mais eficiente criar um índice de várias colunas (Índice Composto) do que um índice para cada coluna usada, pois isso pode aumentar a seletividade do índice composto. Por exemplo:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIExBU1RfTkFNRSwgRklSU1RfTkFNRQpGUk9NIENVU1QKV0hFUkUgTEFTVF9OQU1FID0gJ1NNSVRIJwpBTkQgRklSU1RfTkFNRSA9ICdTVEVWRSc7″ hover_enabled=”0″]U0VMRUNUIExBU1RfTkFNRSwgRklSU1RfTkFNRQpGUk9NIENVU1QKV0hFUkUgTEFTVF9OQU1FID0gJ1NNSVRIJwpBTkQgRklSU1RfTkFNRSA9ICdTVEVWRSc7[/et_pb_dmb_code_snippet]

– Tendo como base a query acima, para otimiza-la, seria melhor criar um índice composto das colunas “last_name” e “first_name” do que um índice para “last_name” e um índice para “first_name”, pois geralmente toda a informação necessária está contida nos blocos do índice. Isso evitaria que a tabela fosse acessada.

* Criar índices com a opção COMPRESS. Caso as colunas indexadas apresentem uma grande quantidade de dados repetidos, pode-se criar indices com taxas de compressão, diminuindo o número de blocos acessados e melhorando a performance geral, mas possívelmente pode prejudicar operações DML (para ter certeza, só testando!!!). É útil em índices compostos onde uma ou mais colunas que fazem parte do índice apresentam um alto grau de repetição de seus valores. Sua sintaxe é:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”Q1JFQVRFIElOREVYIG5vbWVfaW5kaWNlIE9OIHRhYmVsYShjb2x1bmFzWy4uLl0pIENPTlBSRVNTIE47″ hover_enabled=”0″]Q1JFQVRFIElOREVYIG5vbWVfaW5kaWNlIE9OIHRhYmVsYShjb2x1bmFzWy4uLl0pIENPTlBSRVNTIE47[/et_pb_dmb_code_snippet]

OBS1: O N indica o grau de compressão

* Pode-se maximizar a criação de índices em grandes tabelas, desabilitando a geração de REDO, criando-os com a clausula NOLOGGING. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”Q1JFQVRFIElOREVYIGlkeF90ZXN0ZXMgT04gdGFiX3Rlc3Rlcyhjb2x1bmFfdGVzdGUpIE5PTE9HR0lORzs=” hover_enabled=”0″]Q1JFQVRFIElOREVYIGlkeF90ZXN0ZXMgT04gdGFiX3Rlc3Rlcyhjb2x1bmFfdGVzdGUpIE5PTE9HR0lORzs=[/et_pb_dmb_code_snippet]

OBS1: REDO não é gerado, impossibilitando e recuperação do índice no caso de uma falha da mídia logo após a criação deste. Caso o índice seja crítico para o negócio, recomenda-se gerar um backup após a criação daquele.

2) VERIFICANDO SE UMA COLUNA É UMA BOA CANDIDATA A RECEBER UM ÍNDICE

* Pode-se usar a query abaixo para ajudar a determinar as colunas candidatas a um índice:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIENTLkNPTFVNTl9OQU1FLApDUy5OVU1fRElTVElOQ1QsCkNTLk5VTV9OVUxMUywKQ1MuTlVNX0JVQ0tFVFMsCkNTLkRFTlNJVFksCihTRUxFQ1QgTlVNX1JPV1MKRlJPTSBEQkFfVEFCTEVTIFQKV0hFUkUgVC5UQUJMRV9OQU1FID0gQ1MudGFibGVfbmFtZQpBTkQgICBULk9XTkVSID0gQ1Mub3duZXIpIEFTIFRPVF9MSU5IQVMsCigoQ1MuTlVNX0RJU1RJTkNUICogMTAwKSAvCihTRUxFQ1QgTlVNX1JPV1MKRlJPTSBEQkFfVEFCTEVTIFQKV0hFUkUgVC5UQUJMRV9OQU1FID0gQ1MudGFibGVfbmFtZQpBTkQgVC5PV05FUiA9IENTLm93bmVyKSkgYXMgIiUgdG90YWwiCkZST00gREJBX1RBQl9DT0xfU1RBVElTVElDUyBDUwpXSEVSRSBDUy5UQUJMRV9OQU1FID0gJ25vbWVfdGFiZWxhX2Fsdm8nCk9SREVSIEJZIENTLkNPTFVNTl9OQU1FOw==” hover_enabled=”0″]U0VMRUNUIENTLkNPTFVNTl9OQU1FLApDUy5OVU1fRElTVElOQ1QsCkNTLk5VTV9OVUxMUywKQ1MuTlVNX0JVQ0tFVFMsCkNTLkRFTlNJVFksCihTRUxFQ1QgTlVNX1JPV1MKRlJPTSBEQkFfVEFCTEVTIFQKV0hFUkUgVC5UQUJMRV9OQU1FID0gQ1MudGFibGVfbmFtZQpBTkQgICBULk9XTkVSID0gQ1Mub3duZXIpIEFTIFRPVF9MSU5IQVMsCigoQ1MuTlVNX0RJU1RJTkNUICogMTAwKSAvCihTRUxFQ1QgTlVNX1JPV1MKRlJPTSBEQkFfVEFCTEVTIFQKV0hFUkUgVC5UQUJMRV9OQU1FID0gQ1MudGFibGVfbmFtZQpBTkQgVC5PV05FUiA9IENTLm93bmVyKSkgYXMgIiUgdG90YWwiCkZST00gREJBX1RBQl9DT0xfU1RBVElTVElDUyBDUwpXSEVSRSBDUy5UQUJMRV9OQU1FID0gJ25vbWVfdGFiZWxhX2Fsdm8nCk9SREVSIEJZIENTLkNPTFVNTl9OQU1FOw==[/et_pb_dmb_code_snippet]

* A query acima possui 2 informações úteis:

– NUM_DISTINCT: Indica o numero de valores distintos em uma coluna. Caso o valor dessa coluna seja muito baixo,por exemplo 3, em relação ao seu total de registros, isso quer dizer que essa coluna possui uma seletividade baixa, tornando-a uma fraca candidata para a criacao de um indice. Caso uma coluna possua uma quantidade alta de valores distintos, em relação ao seu total de registros, como por exemplo 1.000.000, isso torna essa coluna uma forte candidata a criacao de um indice.

– NUM_NULL: Indica o numero de valores nulos para determinada coluna. Se uma coluna possui poucos valores NULL, ela é uma boa candidata a criacao de um indice.

* Segue um resuminho sobre o que foi abordado acima:

– Se a coluna possui uma quantidade elevada de valores distintos E se for usada como predicado (WHERE coluna) e se apenas algumas linhas da tabela (<=10%) forem retornadas, então essa coluna é uma boa candidata.

– Se a coluna não for usada como predicado (WHERE coluna …) E/OU um grande número de linhas foram retornados, então essa coluna não é uma boa candidata a receber um índice

OBS1: Colunas usadas em condições de JOIN tem as mesmas caracteristicas que predicados, portanto o que cabe a um predicado, cabe a uma coluna usada em JOIN, para a criação ou não de indices.

 

3) CHECANDO SE UM ÍNDICE PRECISA SER RECONSTRUÍDO

* Esse é um assunto que gera muita polêmica, pois uns dizem que o REBUILD é desnecessário nas releases mais atuais, outros dizem que um REBUILD faz milagres, e outros chegam até a automatizar o REBUILD dos índices mais críticos em intervalos regulares de tempo. Eu, particularmente, já vi resultados muito bons em REBUILDs de índices, mas não em todos os casos. As vezes, a operação de REBUILD pode ser muito traumática.

* Para ajudar a verificar se um índice precisa ser reconstruído:

3.1) Analisar o indice

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”QU5BTFlaRSBJTkRFWCBub21lX2luZGV4IFZBTElEQVRFIFNUUlVDVFVSRTs=” hover_enabled=”0″]QU5BTFlaRSBJTkRFWCBub21lX2luZGV4IFZBTElEQVRFIFNUUlVDVFVSRTs=[/et_pb_dmb_code_snippet]

3.2) Executar a seguinte query:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIEhFSUdIVCwKREVMX0xGX1JPV1MsCkxGX1JPV1MsCkxGX0JMS1MKKERFTF9MRl9ST1dTL0xGX1JPV1MpCkZST00gSU5ERVhfU1RBVFM7″ hover_enabled=”0″]U0VMRUNUIEhFSUdIVCwKREVMX0xGX1JPV1MsCkxGX1JPV1MsCkxGX0JMS1MKKERFTF9MRl9ST1dTL0xGX1JPV1MpCkZST00gSU5ERVhfU1RBVFM7[/et_pb_dmb_code_snippet]

OBS1: Caso o valor de DEL_LF_ROWS/LF_ROWS seja maior que 2, ou LF_ROWS seja menor que LF_BLKS, ou HEIGHT seja 4, então o índice deveria ser reconstruído.

OBS2: Recomenda-se realizar o rebuild e computar as estatísticas na mesma operação. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”QUxURVIgSU5ERVggbm9tZV9pbmRleCBSRUJVSUxEIENPTVBVVEUgU1RBVElTVElDUzs=” hover_enabled=”0″]QUxURVIgSU5ERVggbm9tZV9pbmRleCBSRUJVSUxEIENPTVBVVEUgU1RBVElTVElDUzs=[/et_pb_dmb_code_snippet]

Por enquanto é isso meus caros DBAs/desenvolvedores/curiosos… até a próxima.

Keep Tuning!