Geneesya: Oracle: Índices (1)

Introdução

De forma análoga ao SQL Server, também no Oracle, a utilização de Índices é essencial para obter alto desempenho numa base de dados (BD).

Num artigo anterior, fizemos uma introdução à utilização de índices no SQL Server. Vamos agora fazer trabalho análogo para os índices do Oracle. Não vamos repetir a explicação detalhada de alguns conceitos base, que já foi efectuada nesse artigo. Em cada tópico, vão ser incluídos links para os tópicos equivalentes no artigo do SQL Server, onde os detalhes podem ser consultados.

Vamos então começar a analisar como podemos usar os índices no Oracle, para obter uma base de dados que tenha uma boa performance, integridade de dados e, ao mesmo tempo, mantenha a sobrecarga associada aos índices, num mínimo.

Conteúdo

O que é um Índice?
Estrutura de um Índice
Tipos de Índices
Limites dos Índices
Como o Oracle usa os Índices?
Considerações para a criação de Índices
Referências

O que é um Índice?

Um índice corresponde a uma estrutura em disco, associada a uma tabela ou view, que agiliza a pesquisa de linhas nessa tabela ou view.

A estrutura do índice é separada da tabela e não afecta a forma como os dados são guardados fisicamente. Afecta sim a forma como são lidos a partir da tabela.

Ver exemplos e detalhe aqui.

Estrutura de um Índice

À semelhança do SQL Server, os índices no Oracle são implementados através de uma estrutura de Árvore-B.

Ver detalhe aqui.

Apesar da estrutura ser idêntica, existe uma diferença essencial, entre o Oracle e o SQL Server, nos dados que são armazenados na estrutura da árvore-B e, consequentemente, na forma como são usados os índices.

Bloco
Um bloco é a menor unidade de disco que o Oracle consegue ler ou escrever. Todos os dados do Oracle - tabelas, índices, clusters - são armazenados em blocos. O tamanho do bloco é configurável para cada BD, normalmente 4Kb, 8Kb, 16Kb ou 32Kb. O tamanho de uma linha de uma tabela é, geralmente, mais pequeno que o tamanho do bloco, por isso o mesmo bloco pode acomodar várias linhas.

A estrutura de árvore-B dos índices do Oracle, guarda o endereço de blocos de BD e não o endereço de linhas de tabela. Então, o Oracle não lê "apenas uma linha", vai ler todo o bloco e ignorar as linhas que não precisa. Minimizar este desperdício é um dos fundamentos da Oracle Performance Tuning.

Tipos de Índices

No Oracle, existem dois tipos principais de índices: B-Tree (nonclustered) e Bitmap.

Existem mais tipos de índices, como cluster, bitmap join, function-based, reverse key e text, mas acabam por ser variantes daqueles dois.

Convém aqui enfatizar que os índices cluster do Oracle não têm nada de semelhante com os índices clustered do SQL Server. No Oracle aquilo que se assemelha às tabelas clustered do SQL Server são as Index-Organized Tables, que também são guardadas numa estrutura árvore-B (em vez de heap), ordenada pela Chave Primária.

Mais informação sobre índices clustered do SQL Server aqui.

Limites dos Índices

No Oracle, também existem algumas limitações internas aos índices.

Tamanho da chave
O tamanho de uma chave de índice está limitado a um máximo de 32 colunas, para índices B-Tree e 30 colunas para índices Bitmap.

Número de índices
Não existe limite para o número de índices numa tabela.

Qualquer destes limites é muito elevado e há poucos casos em que um sistema bem projectado se aproxime sequer deles. Existem duas razões fundamentais para isso não acontecer:

À medida que aumenta o número de índices também aumenta o tamanho total ocupado pela tabela (com todos os seus índices). Claro que os discos rígidos são baratos e abundam as soluções de armazenamento, mas o aumento do tamanho duma BD tem outros efeitos: as operações de manutenção (backups, restauros, verificações de consistência e reconstrução de índices) vão levar mais tempo, à medida que o tamanho da BD aumenta;
Os índices têm que ser actualizados em sincronismo com as alterações dos dados. Quantos mais índices existirem numa tabela, mais os locais e maior o número de operações necessárias para actualizar. Se existirem 10 índices numa tabela, a inserção duma nova linha tem que ser feita em 11 locais (na tabela e em cada um desses índices).
Nas BDs que são principalmente de leitura (de suporte à decisão, ou datawarehouses) isto pode ser aceitável. Mas nas BDs em que são frequentes as operações de alteração de dados - inserir, alterar, eliminar (sistemas OLTP), a sobrecarga imposta pela existência de muitos índices pode não ser aceitável.

Como o Oracle usa os Índices

O Oracle pode executar as seguintes operações relacionadas com índices:

INDEX UNIQUE SCAN
Este tipo de pesquisa, usa a estrutura árvore-B para localizar um valor específico. O Oracle usa esta operação se houver um predicado (filtro pesquisável) que garanta que o resultado da pesquisa não tenha mais que uma linha.

INDEX RANGE SCAN
À semelhança da anterior, este tipo de pesquisa, usa a estrutura árvore-B, mas agora para encontrar um conjunto de valores e não apenas um valor específico.

A operação de pesquisa é executada a partir da página de raiz. A partir das linhas desta página, o mecanismo de consultas vai localizar a página do seguinte nível inferior do índice, que contém a primeira linha que está a ser pesquisada. O mecanismo vai, então, ler essa página. Se a página estiver no nível de folha do índice, a pesquisa termina aí. Se não é o nível de folha, então o mecanismo de consulta identifica novamente a página do nível inferior seguinte, que contém o valor especificado. Este processo continua até que o nível de folha seja atingido.

Quando o mecanismo de consultas localiza a página de folha que contém o valor de chave especificado, ou o início do intervalo especificado de valores-chave, vai lendo ao longo das páginas de folha até que todas as linhas correspondentes ao predicado sejam devolvidas. A figura seguinte mostra um exemplo de como uma pesquisa seria feita, num índice, para devolver as linhas com o valor 4 na chave:

INDEX FULL SCAN
Este tipo de pesquisa corresponde a uma leitura completa de todas as páginas de folha do índice. Dependendo de várias estatísticas do sistema, a BD pode executar esta operação se necessitar de todas as linhas, pela ordem do índice – p.ex., por causa de uma cláusula ORDER BY.

Quando a varredura do índice é feita pelo mecanismo de consultas, é sempre uma leitura completa de todas as páginas de folha do índice, independentemente de todas as linhas serem retornadas ou não. Nunca é uma leitura parcial.

Uma varredura não envolve apenas a leitura dos níveis de folha do índice, as páginas de nível superior também são lidas durante a operação.

INDEX FAST FULL SCAN
Este tipo de pesquisa corresponde a uma leitura completa de todas as páginas de folha do índice, conforme guardadas no disco. Esta operação é geralmente realizada em vez de uma varredura (scan) completa da tabela, se todas as colunas necessárias estão disponíveis no índice.

TABLE ACCESS BY INDEX ROWID (Lookup)
Os lookups ocorrem quando o Oracle usa um índice para localizar as linhas pedidas por uma consulta, mas esse índice não contém todas as colunas necessárias para satisfazer a consulta. Para obter as colunas restantes, o Oracle usa o endereço da linha (ROWID), para ler directamente da tabela.

TABLE ACESS FULL ou FULL TABLE SCAN
Lê a tabela inteira - todas as linhas e colunas – conforme armazenada no disco. Apesar da leitura de multi-blocos melhorar consideravelmente a velocidade duma varredura completa de tabela, esta ainda é uma das operações mais pesadas, em termos de BD. Além de altas taxas de I/O, uma varredura completa da tabela tem que inspecionar todas as linhas da tabela, pelo que também pode consumir uma quantidade considerável de tempo de CPU.

Considerações para a criação de Índices

Por mais benéfica que a utilização de índices possa ser, estes devem ser projectados com cuidado. Como resultado, a criação de índices deve levar em conta algumas considerações:

Os índices existem, principalmente, para aumentar a performance.
Tenha em consideração a relação custo/benefício dos índices.
Considere técnicas não-standard e alternativas de indexação, como Índices Bitmap (mas não para OLTP) ou Índices Funcionais.
Use ferramentas de análise e tuning, em especial o Execution Plan.
Optimize as pesquisas para a utilização de índices. P.ex., um índice não é uma grande ajuda em condições do tipo NOT: <>, NOT IN, NOT LIKE, …