Create and Maintain Indexes

Index já foi mostrado a sua definição e falado um pouco sobre ele. Sabemos que é um objeto do banco de dados que ajuda a acelerar as pesquisas no banco de dados quando utilizado no WHERE ou ORDER BY, algo muito parecido com um índice de um livro, em que não precisamo procurar página por página por algo, basta apenas olhar o índice e saber a página exata onde se encontra.

O index armazena um subconjunto ordenado de uma ou mais colunas de acordo com a configuração do index no momento da sua criação, juntamente com a localização exata desta linha na tabela. Mas o Oracle nem sempre irá utilizar o index conforme veremos mais a frente algo chamado de OPTIMIZER. Vejamos agora os dois modos de criar um Index.

Implícito

Como já foi dito em posts passados, quando configuramos uma constraint UNIQUE ou PK, caso não exista um Index para a(s) coluna(s), o Oracle irá criar um index automaticamente. Vejam o exemplo abaixo:

CREATE TABLE pessoas
(pessoa_id NUMBER PRIMARY KEY,
nome VARCHAR2(50) UNIQUE);

Ao criar a tabela acima em questão, será criada duas constraints, uma PK e uma UNIQUE, e também será criado dois INDEX, que terá um nome criado de forma automática pelo Oracle, já que não especificamos um.

Explícito

Vejam agora como criar um Index de modo explícito.

CREATE INDEX ix_pessoas_data_nascimento ON pessoas(data_nascimento)

Será criado um Index para a coluna data_nascimento da tabela pessoas. Então em querys que utilizam essa coluna nas cláusulas WHERE r/ou ORDER BY, poderá mais rápida, mas nem sempre, a escolha de se utilizar ou não é do próprio Oracle, usando algo que se chama de optimizer. Vejamos o que seria isto então.

Optimizer

Não só as query que tenham alguma coluna com Index passa pelo Optimizer, mas sim todos os comandos SQL. É o Optimizer que determina qual o melhor caminho para chegar em um determinado resultado desejado. Então quando temos alguma query que tenha pelo menos no WHERE ou no ORDER BY alguma coluna que tenha um Index, o Optimizer vai avaliar se o uso do Index vai melhorar a performance ou não, ou seja, se vai usar o Index ou não. Então, apenas existir o Index não é garantia que ele será usado.

Vejam que no exemplo acima, criamos um Index em uma coluna do tipo DATE. Se essa coluna tiver vários valores repetidos, é muito provável que o Optimizer não ira utilizar o Index, mas caso contrário é muito provável que sim. Quando uma coluna não possui muitos valores repetitivos e sim únicos, é dito que essa coluna tem um alto grau de seletividade. Caso contrário seria um baixo grau de seletividade.

Como já vimos, para poder se beneficiar do Index, a coluna que está indexada tem que aparecer nas cláusulas WHERE ou ORDER BY, mas vimos que nem sempre ele pode ser usado. Existem alguns outros fatores que podem contribuir para o seu uso. Veja quais são alguns:

  • A comparação dessa coluna indexada tem que ser um igualdade
  • O operador “Maior Que” ou outro, talvez use o Index.
  • O operador “Não Igual” não irá usar o Index.
  • O operador LIKE talvez use o Index, desde o o WILDCARD não esteja no início, ou seja, no primeiro carácter.
  • Tudo parece ótimo correto? Acelerar as nossas consultas parece algo maravilhoso, mas isso nem sempre é verdade. Se criarmos uma tabela em que a maioria das operações serão INSERT e/ou DELETE e/ou UPDATE, as operações não irão se beneficiar do Index, pelo contrário, essas operações serão mais lentas, pois além de modificar a tabela, terão que modificar também o Index. Então, usem com moderação.

    Composite Index

    Um Index composto é aquele feito por mais de uma coluna, veja como podemos criar isto:

    CREATE INDEX ix_pessoas_nome_nascimento ON pessoas(nome, nascimento);

    Criamos então um Index composto por duas colunas da tabela pessoas, nome e nascimento. O index armazena um conjunto de valores ordenados das duas colunas, ordenada primeiro pela primeira coluna, no caso a coluna nome, depois pela segunda coluna, no caso a coluna nascimento, e assim por diante.´

    Se utilizarmos em um query na cláusula WHERE as duas colunas, o Optimizer muito provavelmente irá escolher utilizar o Index. Mas se utilizarmos apenas a primeira coluna, que no nosso caso é nome, o Optimizer poderá usar também o Index, pois conforme já mencionamos, o Index armazena os valores primeiro os valores da primeira coluna e depois da segunda coluna.

    E se utilizarmos apenas a segunda coluna na nossa query? O Optimizer também ira considerar utilizar o Index, por causa de algo chamado Skip Scanning. E o que seria isto? Com o Skip Scanning, o Oracle trata uma Index composta como a combinação de várias Index simples, claro que a performance não será a mesma. E a quantidade de Index simples será calculado pelo número de valores únicos da primeira coluna.

    Unique

    Um Unique Index nos ajuda a garantir que as informações da coluna serão únicas, ou seja, não irão se repetir. A criação de uma Index assim é bem similar ao que já vimos, adicionamos apenas a palavra reservada UNIQUE conforme podemos verificar abaixo:

    CREATE UNIQUE INDEX ix_pessoas_cpf ON pessoas(cpf);

    Cuidado não confundam a UNIQUE INDEX com a Constraint UNIQUE. São coisas diferentes, apenas de que quando adicionamos as constraints PRIMARY KEY ou UNIQUE, o Oracle automaticamente cria uma UNIQUE INDEX.

    Drop

    E agora para finalizar, vejamos como podemos remover uma Index.

    DROP INDEX ix_pessoas_nome;

    Bem simples não é? Claro que se dermos um drop na tabela o index também será removido. Com isto finalizamos mais este post. No próximo post veremos um pouco mais de Synonym.

    Anúncios

    Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE Associate por estar sempre contribuindo para a comunidade com um bom nível de expertise.

    Marcado com: , , , ,
    Publicado em SQL Expert
    2 comentários em “Create and Maintain Indexes
    1. Tiago disse:

      Tércio, gostaria de parabeniza-lo pelo excelente trabalho mostrado neste blog. Com certeza acrescentará muito na rotina de estudos de muitos (como eu) que desejam tirar a certificação.

      Curtir

    Deixe um comentário

    Preencha os seus dados abaixo ou clique em um ícone para log in:

    Logotipo do WordPress.com

    Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

    Foto do Google

    Você está comentando utilizando sua conta Google. Sair /  Alterar )

    Imagem do Twitter

    Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

    Foto do Facebook

    Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

    Conectando a %s

    Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

    Esse Blog é reconhecido pela
    Certificações
    Sou articulista

    Clique para seguir este blog e receber notificações via email de novos posts.

    Tércio Costa

    Tércio Costa

    Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE Associate por estar sempre contribuindo para a comunidade com um bom nível de expertise.

    Links Pessoais

    Serviços verificados

    Visualizar Perfil Completo →

    Total de Visualizações da Página
    • 138.077 Visualizações
    %d blogueiros gostam disto: