Administrando Índices (INDEX)

Um índice executa o mesmo trabalho que um índice típico em um livro. Por exemplo, o Manual de Referência da Linguagem SQL da Oracle Corporation para a versão R1 do Oracle 12c tem mais de 1.908 páginas. E se você estivesse procurando informações sobre a cláusula DISTINCT da instrução SELECT? Você tem algumas maneiras de encontrar essas informações no livro. Uma maneira é sentar e começar a ler o livro na primeira página e continuar lendo até encontrar os dados que está procurando. Uma maneira muito mais eficiente é voltar para o final do livro e encontrar o índice, que contém um resumo de tópicos importantes em ordem alfabética. Dentro de alguns segundos, você pode procurar a palavra DISTINCT, anotar o número da página em que ela é mencionada e virar diretamente para ela. Essa é uma abordagem muito melhor.

O objeto SQL INDEX executa da mesma maneira. Ao criar um objeto INDEX, você está identificando uma ou mais colunas em uma tabela que acredita ser frequentemente usada para procurar dados. Em seguida, você cria o índice com base nessa coluna – ou conjunto de colunas – e o Oracle literalmente cria um objeto separado que obtém uma lista exclusiva de todos os dados atualmente naquela coluna, os classifica adequadamente de acordo com o tipo de dados e armazena informações de endereçamento interno que vincula o índice à tabela de origem e às linhas contidas nela. O resultado é que qualquer consulta futura na tabela que faça referência a qualquer dado indexado fará com que o seguinte ocorra automaticamente:

Executa uma análise para determinar se a consulta será beneficiada usando o índice.
Se sim, redirecione o foco temporariamente para o índice, pesquise no índice qualquer dado desejado identificado pela consulta e obtenha localizações diretas das linhas apropriadas

Um índice é um objeto que você pode criar no banco de dados e armazena um subconjunto de dados de uma tabela. O índice é seu próprio objeto; ele é armazenado separadamente da tabela e é constantemente mantido com todos os INSERT, UPDATE e DELETE executados na tabela.
Por exemplo, se você tiver uma tabela SHIPS com uma coluna CAPACITY e criar um índice para a tabela usando a coluna CAPACITY, a tabela e o índice armazenarão os valores CAPACITY separadamente. Sempre que uma instrução DML altera um valor na coluna CAPACITY da tabela SHIPS, o índice também é alterado de acordo.

O objetivo de um índice é oferecer suporte a consultas mais rápidas em uma tabela. Os valores do índice são pré-classificados. Um índice pode ser criado para apenas uma tabela e pode ser criado em uma ou mais das colunas dessa tabela – colunas que você designar. Para cada coluna, o índice também armazena o endereço desses dados da tabela de origem. O SQL pode usar o objeto de índice para acelerar a consulta das cláusulas WHERE e ORDER BY. Por exemplo, se uma cláusula WHERE fizer referência a qualquer coluna (ou colunas) indexada, o SQL (ou mais especificamente, o otimizador) considerará automaticamente o índice, pois determina a estratégia de consulta ideal para a instrução SQL. O resultado é que as consultas podem ser significativamente mais rápidas, dependendo da quantidade de dados envolvidos e do número de índices que podem ser aplicados a uma tabela.

Você pode criar quantos índices quiser, mas não pode usar em colunas LOB e RAW, quem dita se irá usar ou não um índice criado é o sistema otimizador de banco de dados, que veremos mais adiante.

Criação Implícita de Índice

Se você criar uma restrição em uma tabela do tipo PRIMARY KEY ou UNIQUE, como parte da criação da restrição, o SQL criará automaticamente um índice para dar suporte a essa restrição na coluna ou colunas, se esse índice ainda não existir. Por exemplo, considere a seguinte instrução SQL:

CREATE TABLE SEMINARIOS ( SEMINARIO_ID NUMBER(11) PRIMARY KEY, SEMINARIO_NOME VARCHAR2(30) UNIQUE );

Esta instrução criará a tabela SEMINARIOS, com duas CONSTRAINTs e dois objetos INDEX. Esses objetos INDEX serão nomeados automaticamente pelo sistema SQL. A seguinte consulta no dicionário de dados confirma a criação do índice:

SELECT TABLE_NAME, INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'SEMINARIO'

A consulta acima traz o seguinte resultado:

TABLE_NAME	INDEX_NAME
SEMINARIO	SYS_C009932
SEMINARIO	SYS_C009931

Na saída deste exemplo, você pode ver que o sistema atribuiu os nomes SYS_C009932 e SYS_C009931 aos índices. No exemplo dado ao criar PRIMARY KEY ou UNIQUE, o SGBD da Oracle criará automaticamente os índices com nomes automáticos, podemos criar índices manualmente e dar um nome apropriado também.

Como alternativa, você pode consultar em outra parte do dicionário de dados para ver as colunas envolvidas nos índices:

SELECT INDEX_NAME, COLUMN_NAME FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'SEMINARIO'

INDEX_NAME	COLUMN_NAME
SYS_C009931	SEMINARIO_ID
SYS_C009932	SEMINARIO_NOME

Nestes exemplos, aplicamos cada restrição a uma única coluna. Se tivéssemos criado uma PRIMARY KEY composta ou uma restrição UNIQUE composta, todas as colunas envolvidas na restrição também seriam indexadas como um índice composto e os nomes das colunas seriam listados na saída desta segunda consulta no dicionário de dados.

Veja o exemplo a seguir, foi criado a tabela X com duas colunas ID, C, e em seguida definido chave primária composta das duas colunas, dessa forma o Oracle cria automático como vimos o índice, mas observe que apenas um índice foi criado, pois há apenas um PRIMARY KEY para esta tabela, caso tivesse um ou mais UNIQUE então o Oracle criaria automático um ou mais índices, ou seja sempre um índice para cada PRIMARY KEY / UNIQUE.

Apenas um índice criado: SYS_C0017737061

Criando Índice manualmente em uma coluna

Vimos no tópico anterior para que serve um índice e as formas de criação automática de índice. Veremos agora como criar manualmente um índice em uma coluna.

CREATE INDEX IDX_DATA_FATURA ON FATURA(DATA_FATURA);

A criação de um índice não é garantia de que será usado sempre. Quem toma a decisão de usar ou não o índice é o otimizador SQL, ele analisa se a coluna tem dados muito repetitivos ou não, se por exemplo uma coluna possuir dados bastante repetitivos provavelmente não irá usar o índice para filtrar, quem toma a decisão do uso de um índice é o otimizador.

O otimizador de banco de dados Oracle considerará o uso de um índice em qualquer consulta que especifique uma coluna indexada na cláusula WHERE ou ORDER BY, dependendo de como a coluna de índice for especificada. Existem algumas diretrizes para estruturar sua instrução SQL para que o otimizador tenha maior probabilidade de aplicar o índice. As diretrizes são as seguintes:

Operador de diferença (<>, !=, ^=) não irá fazer uso do índice em uma busca, igual ou maior/menor poderá utilizar o índice.

LIKE '%NOME' não irá usar índice.
LIKE 'NOME%' pode usar índice.
Se o LIKE começar a buscar pela inicial do nome então o índice poderá ser utilizado, da outra forma mostrada o índice não será usado.

Uma função em uma coluna impedirá o uso de um índice – a menos que o índice seja um índice baseado em função.

Manutenção de Índices

Os índices são mantidos automaticamente em segundo plano pelo SQL e não exigem nenhum esforço de sua parte para manter os dados de pesquisa consistentes com a tabela. No entanto, observe que isso significa que cada instrução INSERT, UPDATE e DELETE futura que você executar precisará trabalhar mais. Cada instrução DML que modifica os dados em uma tabela indexada também executará a manutenção do índice, conforme necessário. Em outras palavras, cada índice adicionado a uma tabela coloca mais carga de trabalho em cada instrução DML que afeta os dados indexados.

Índice composto

CREATE INDEX idx_vendedor_data ON fatura(vendedor_id, data_venda)

Neste exemplo, o resultado é um único índice que combina as duas colunas(vendedor_id, data_venda). Um índice composto copia e classifica dados de ambas as colunas no objeto de índice composto. Seus dados são classificados primeiro pela coluna da primeira posição, segundo pela coluna da segunda posição e assim por diante, para todas as colunas que formam o índice composto. A consulta abaixo excita o otimizador a usar o índice.

SELECT * FROM fatura
WHERE vendedor_id = 10 and data_venda = sysdate

Observe que a cláusula WHERE no exemplo anterior faz referência às colunas que compõem o objeto INDEX composto. Em seguida, considere esta consulta:

SELECT * FROM fatura WHERE vendedor_id = 10

Essa consulta, que faz referência à primeira coluna no índice composto, também chama o índice. O motivo é que o índice composto classifica internamente os dados pela primeira coluna primeiro e pela segunda coluna em sequência e assim por diante. Dado que, os dados copiados e classificados internamente da tabela indexada são comparáveis em estrutura a um índice de coluna única com base – neste exemplo – na coluna VENDEDOR_ID. Mas agora considere esta consulta:

SELECT * FROM fatura WHERE data_venda = '25-SEP-2019'

Esta consulta faz referência à segunda coluna do índice composto, mas não a primeira. A estrutura de índice composta é classificada principalmente na primeira coluna de sua estrutura, que no exemplo com o qual estamos trabalhando, é VENDEDOR_ID. Mas esta consulta não faz referência a VENDEDOR_ID. No entanto, o otimizador de SQL ainda pode considerar a aplicação do índice devido a um recurso conhecido como SKIP SCANNING.

Skip Scanning

Graças ao Skip Scanning (ignorar varredura), uma cláusula WHERE que faça referência a qualquer coluna em um índice composto pode chamar o índice em seu processamento. No entanto, o benefício de desempenho não é idêntico. No Skip Scanning, o SQL trata um índice composto como uma combinação de vários índices, a quantidade depende do nível de seletividade que resulta na indexação da primeira coluna (ou inicial) do objeto INDEX.

Se a primeira coluna contiver apenas alguns valores exclusivos em um grande número de linhas, o índice, se usado, poderá ser aplicado um número relativamente pequeno de vezes. Por outro lado, se a coluna principal contiver dados relativamente únicos nas linhas, o índice – se usado – poderá ser revisado com frequência. Em outras palavras, um skip scan fará uma verificação de índice uma vez para cada valor exclusivo na primeira coluna. Isso não é tão benéfico quanto um simples índice de uma coluna e seu benefício varia, dependendo da singularidade dos valores na primeira coluna. Mas a cláusula WHERE obtém algum benefício de qualquer maneira.

O ponto é que uma cláusula WHERE que faz referência a algumas, mas não todas, as colunas em um índice composto pode invocar o índice, mesmo que a coluna principal não seja referenciada na cláusula WHERE. No entanto, a inclusão da coluna principal pode resultar em uma aplicação mais eficiente do índice composto e, portanto, em um resultado mais rápido para a consulta.

Unique – Criando uma restrição no Índice

Um índice exclusivo é aquele que ajuda a garantir que uma coluna em uma tabela contenha informações exclusivas. A sintaxe para criar um índice exclusivo é a seguinte:

CREATE UNIQUE INDEX IX_EMP_CPF ON FUNCIONARIOS(CPF)

Esta instrução SQL cria um índice que garante que os dados inseridos na coluna CPF da tabela FUNCIONARIOS sejam ÚNICOS. Se tentar inserir valores repetidos nessa coluna vai dar erro, a mensagem de erro vai ser parecida com uma mensagem de violação de CONSTRAINT:

ORA-00001: unique constraint(SQL_XENQZYEECQUXPVYPPYZSODJZB.IX_EMP_CPF) violated ORA-06512: at "SYS.DBMS_SQL", line 1

Importante entender que ao criar uma CONSTRAINT UNIQUE criará automaticamente um UNIQUE INDEX, no entanto ao criar um UNIQUE INDEX não irá criar automaticamente uma CONSTRAINT UNIQUE. Fique ligado!

Droping – Removendo um INDEX

Para remover um índice use o seguinte comando:

DROP INDEX nome_do_indice

Observe que, se você excluir uma tabela que tem um índice, o índice será descartado automaticamente. Se você recriar a tabela, precisará recriar o índice.

Índice Visível e Invisível

Um índice pode ser criado para ficar visível ou invisível para o otimizador. Somente um índice visível é reconhecido pelo otimizador e, portanto, disponível para o otimizador usar no processamento de uma instrução SQL. Se um índice for invisível, o otimizador omitirá o índice da consideração ao criar o plano de execução para a instrução SQL.

Um índice é visível por padrão, pode se testar se é realmente necessário o uso de um índice criado, para isso basta torná-lo invisível e executar algumas consulta observando se a performance irá cair drasticamente, caso não, você poderá então dropar o índice, deletá-lo, outra forma é se instruções DML estão sendo prejudicadas pelo índice, para testar primeiro torna invisível e testa, se ver que o índice não é muito útil daí sim faça a exclusão dele.

Por padrão um índice é criado como VISIBLE, mas podemos especificar ao criar um índice:
CREATE INDEX idx1 ON tabela_teste(id) VISIBLE;

Vamos ver como criar um índice invisível:
CREATE INDEX idx1 ON tabela_teste(id) INVISIBLE;

Se o índice já tiver sido criado e desejar alterar um índice VISÍVEL para INVISÍVEL use o seguinte comando:
ALTER INDEX idx1 INVISIBLE

Você pode ver o status de um índice fazendo a seguinte consulta:
SELECT VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = 'IDX1'

Múltiplos Índices

É possível criar mais de um índice para uma coluna ou conjunto de colunas e para isso deverá o outro índice estar como invisível e que seja uma abordagem diferente, por exemplo, ao criar um índice por padrão ele é do tipo B TREE (árvore B) mas podemos criar BITMAP usando a sintaxe (CREATE BITMAP INDEX…), se já foi criado um índice do tipo B TREE então se formos criar mais um índice pra mesma coluna então não pode ser do tipo B TREE e esse índice já existente tem que estar como invisible, ou se o índice for UNIQUE podemos criar um índice NONUNIQUE.

Para a finalidade do exame, o ponto que você precisa lembrar é que, embora esses vários índices em um determinado conjunto de colunas possam existir simultaneamente, apenas um pode estar visível no banco de dados a qualquer momento. Por exemplo, ao criar vários índices em um determinado conjunto de colunas, todos os índices preexistentes no mesmo conjunto de colunas já devem estar invisíveis ou a tentativa de criar um novo índice visível no mesmo conjunto de colunas falhará.

Uma razão para alternar índices entre visibilidade e invisibilidade é ajustar o desempenho do aplicativo para vários propósitos. Lembre-se de que a existência de um índice não garante que o otimizador utilize o índice. No entanto, tornar um índice invisível garante que o otimizador ignore o índice. Considere a seção que acabamos de revisar. Você pode achar vantajoso tentar diferentes tipos de índices em um determinado tipo para otimizar o desempenho de aplicativos baseados em transações, em comparação a consultas e análises pesadas. Os detalhes de tais considerações estão além do escopo do exame; para nossos propósitos, é útil reconhecer que o ajuste do aplicativo é um motivo útil para empregar índices invisíveis.

Mas lembre-se de que, mesmo que um índice seja invisível, ele ainda será mantido. Qualquer instrução INSERT, UPDATE ou DELETE processada na tabela também atualizará o índice, que é um custo de desempenho. Isso é verdade tanto para um índice visível como para índice invisível.

Referência

OCA Oracle Database SQL Exam 1Z0-071. Páginas 523 – 574.