Índices

Os índices existem para melhorar o tempo de resposta das pesquisas, mas a sua utilização atrasa as operações de INSERT, DELETE e UPDATE, porque a alteração da tabela implica uma actualização dos dados do índice. Como consequência, o programador deve minimizar a criação de índices, utilizando-os apenas quando são mesmo necessários.

• Introdução aos índices
• Como funcionam os índices b-tree?
• Criação de um índice
• A selectividade de um índice

Introdução aos índices

Os índices existem para melhorar o tempo de resposta nas pesquisas e estas são efectuadas nas seguintes situações:

• De forma explícita num comando SELECT;
• De forma implícita num INSERT, porque é preciso verificar a não duplicação da PRIMARY KEY e das UNIQUE KEYs;
• De forma implícita num UPDATE ou num DELETE porque é preciso encontrar a linha que vai ser alterada ou eliminada;
• De forma implícita quando o comando SQL inclui uma suboperação que requer a pesquisa, por exemplo um JOIN entre duas tabelas executado com o algoritmo NESTED LOOPS;

A aceleração provocada por um índice pode ser enorme, por exemplo horas convertidas em segundos, mas a sua utilização tem um custo. Esse custo manifesta-se nas seguintes situações:

• As operações de INSERT, UPDATE e DELETE obrigam a uma actualização do índice, o que as torna mais pesadas;
• O índice ocupa espaço em disco que em algumas situações ocupa tanto quanto a tabela a que se refere;
• Os índices têm que ser reconstruídos com alguma frequência, e esta operação consome recursos e demora tempo;

O uso de índices é uma decisão de compromisso entre a degradação de algumas operações e a maior velocidade noutras, devendo ser ponderada e reflectida. A base de dados Oracle suporta vários tipos de índices, cada um com vantagens e inconvenientes. Neste curso vamos analisar os índices do tipo "b-tree", que são os de utilização mais flexível, com velocidade muito aceitável.

Topo

Como funcionam os índices b-tree?

Os índices "b-tree" do Oracle têm uma estrutura em árvore. No topo está o bloco Raiz que contém apontadores para vários blocos Ramo. Estes apontam para blocos Folha ou para outro bloco Ramo, caso o índice seja muito grande. Os blocos Folha contêm o valor usado no índice, por exemplo uma chave primária, e o respectivo ROWID. Este permite a localização directa da linha da tabela que assim fica associada ao valor usado como entrada no índice.

Um bloco Folha tem ligações para o próximo bloco Folha e para o anterior. Isto permite percorrer sem grande esforço os valores armazenados no índice, por ordem crescente e decrescente, o que dá suporte às pesquisas que requerem:

• Resultado ordenado pela coluna que se usou no índice;
• Comparação de valores usando “maior que” ou “menor que” (RANGE) tanto nos intervalos fechados como nos abertos;

Uma pesquisa feita através de um índice b-tree necessita de 3, eventualmente 4, leituras de blocos do índice, antes de encontrar o valor desejado. Depois disto requer mais uma leitura do bloco que contem a linha da tabela, que é encontrada via ROWID. Se o índice é muito utilizado é provável que o bloco Raiz e os blocos Folha estejam na SGA, o que evita as leituras de blocos a partir do disco.

A alteração dos dados da tabela requer a manutenção do índice, que é uma operação com algum peso. Suponha que pretende inserir uma nova entrada num bloco folha e não tem espaço livre. O sistema terá que criar um novo bloco folha e redistribuir as entradas do bloco anterior pelo novo. Depois terá que ser adicionada uma entrada no bloco ramo. Se este também estiver cheio, então teremos que criar um novo bloco Ramo, dividir as entradas pelos dois e actualizar os apontadores no bloco Raiz ou no bloco Ramo de nível acima.

A utilização de uma sequência de números como chave primária reduz a manutenção do respectivo índice, porque os novos valores são sempre números superiores aos existentes, o que significa que a árvore cresce só para a direita, não sendo necessário alterar os dados antigos nem reordenar as suas ligações.

Topo

Criação de um índice

Para criar um índice usamos a sintaxe genérica a seguir indicada:

CREATE [UNIQUE] INDEX nomeIndice ON
nomeTabela(coluna1 [, coluna2...])
[TABLESPACE nomeTablespace];

• O nome do índice é obrigatório e distingue este objecto de outros. Alguns comandos em Oracle criam índices de forma implícita, por exemplo a definição de uma restrição Primary Key ou Unique Key. Nestes casos o índice recebe o nome que for atribuído à restrição. Estes índices materializam a regra imposta pela restrição.
• A cláusula UNIQUE é opcional. Na sua ausência o índice suporta valores repetidos, enquanto na sua presença é obrigatório definir valores diferentes. As restrições PRIMARY KEY e UNIQUE KEY forçam a existência de um índice do tipo UNIQUE. No caso da restrição PRIMARY KEY ainda é forçado que todos os valores sejam NOT NULL.
• Um índice está sempre associado a uma tabela e é removido de forma automática quando a respectiva tabela é removida.
• Um índice usa no mínimo uma coluna da tabela. Quando utiliza mais que uma coluna é um índice concatenado ou composto. Quando temos um índice concatenado e UNIQUE significa que as colunas do índice, consideradas de forma isolada, podem ter valores repetidos, mas a concatenação dos valores das colunas, considerada como um grupo, não pode ter valores repetidos. Se além disso o índice fizer parte da PRIMARY KEY, então todas as colunas têm que ter valores NOT NULL.
• A palavra reservada TABLESPACE indica onde o índice será guardado. Quando é omitida o índice é armazenado no TABLESPACE definido por omissão para esse utilizador. Faz parte das boas práticas do SGBD Oracle que as tabelas e os índices sejam guardados em TABLESPACES diferentes.

O comando listado abaixo permite criar um índice com o nome EMP_DEPARTMENT_IX sobre a coluna DEPARTMENT_ID da tabela EMPLOYEES. Este índice aceita valores repetidos:

CREATE INDEX EMP_DEPARTMENT_IX ON EMPLOYEES(DEPARTMENT_ID);

O comando abaixo permite criar o índice com o nome EMP_NAME_IX sobre a tabela EMPLOYEES e cujos valores resultam da concatenação das colunas FIRST_NAME e LAST_NAME. Este índice aceita valores repetidos:

CREATE INDEX EMP_NAME_IX ON EMPLOYEES (LAST_NAME, FIRST_NAME);

O comando listado abaixo permite criar um índice com o nome EMP_EMAIL_UK sobre a coluna EMAIL da tabela EMPLOYEES. Este índice não aceita valores repetidos e será armazenado no tablespace USERS:

CREATE UNIQUE INDEX EMP_EMAIL_UK ON EMPLOYEES(EMAIL) TABLESPACE USERS;

O comando apresentado a seguir permite saber que índices estão criados sobre a tabela EMPLOYEES que pertence ao utilizador HR:

select i.table_owner,i.table_name,i.index_name,i.index_type,i.uniqueness,
       c.column_position,c.column_name
from
    dba_indexes I,
    dba_ind_columns c
where
    i.index_name = c.index_name
    and i.table_owner = c.table_owner
    and i.owner = c.index_owner
    and i.table_name = 'EMPLOYEES'
    and i.owner='HR'
order by i.table_owner,i.index_name,c.column_position;

Topo

A selectividade de um índice

Um índice é muito selectivo quando todos os seus valores são diferentes, sendo pouco selectivo quando possui muitos valores repetidos. Se calcularmos o ratio entre número de valores diferentes do índice e número total de linhas da tabela, o índice será muito selectivo se o ratio der 1, enquanto pouco selectivo se der próximo de zero. Um índice criado sobre a coluna NUMERO_CLIENTE (identificador numérico e único) é mais selectivo que outro criado sobre a coluna DATA_ANIVERSARIO, que por sua vez é mais selectivo que outro criado sobre a coluna ESTADO_CIVIL.

A selectividade do índice é medida pela selectividade dos seus valores, o que significa que, no caso de índices compostos, não conta a coluna individual mas sim a concatenação de todas as colunas.

Topo

Realizado por Turtle Learning ®. Última alteração em 2011-08-15