CREATE INDEX

Name

CREATE INDEX  --  define um índice novo

Synopsis

CREATE [ UNIQUE ] INDEX nome_do_índice ON tabela
    [ USING método_de_acesso ] ( coluna [ nome_do_operador ] [, ...] )
    [ WHERE predicado ]
CREATE [ UNIQUE ] INDEX nome_do_índice ON tabela
    [ USING método_de_acesso ] ( nome_da_função( coluna [, ... ]) [ nome_do_operador ] )
    [ WHERE predicado ]
  

Entradas

UNIQUE

Faz com que o sistema procure por valores duplicados na tabela quando o índice é criado, se existirem dados na tabela, e sempre que novos dados forem adicionados. A tentativa de inserir ou de atualizar dados, que produza um valor duplicado, gera um erro.

nome_do_índice

O nome do índice a ser criado.

tabela

O nome da tabela a ser indexada.

método_de_acesso

O nome do método de acesso a ser utilizado pelo o índice. O método de acesso padrão é o BTREE. O PostgreSQL implementa quatro métodos de acesso para os índices:

BTREE

uma implementação das "B-trees" de alta concorrência de Lehman-Yao.

RTREE

implementa "R-trees" padrão, utilizando o algoritmo de partição quadrática de Guttman.

HASH

uma implementação das dispersões lineares de Litwin.

GIST

Generalized Index Search Trees (Árvores de Procura de Índice Generalizadas).

coluna

O nome de uma coluna da tabela.

nome_do_operador

Uma classe de operador associada. Veja abaixo para obter mais detalhes.

nome_da_função

Uma função que retorna um valor que pode ser indexado.

predicado

Define a expressão da restrição (constraint) para o índice parcial.

Saídas

CREATE

Mensagem retornada se o índice for criado com sucesso.

ERROR: Cannot create index: 'nome_do_índice' already exists.

Este erro ocorre se for impossível criar o índice.

Descrição

O comando CREATE INDEX constrói o índice nome_do_índice na tabela especificada.

Tip: Os índices são utilizados, principalmente, para melhorar o desempenho do banco de dados, mas a utilização não apropriada causa uma degradação do desempenho.

Na primeira sintaxe exibida acima, os campos chave para o índice são especificados como nomes de coluna. Vários campos podem ser especificados, se o método de acesso do índice suportar índices com múltiplas colunas.

Na segunda sintaxe exibida acima, o índice é definido sobre o resultado da função definida pelo usuário nome_da_função aplicada sobre uma ou mais colunas de uma única tabela. Estes índices funcionais podem ser utilizados para obter acesso rápido aos dados baseado em operadores que normalmente iriam requerer alguma transformação para aplicá-los aos dados base.

O PostgreSQL implementa os métodos de acesso B-tree, R-tree, hash e GiST para os índices. O método de acesso B-tree é uma implementação das B-trees de alta concorrência de Lehman-Yao. O método de acesso R-tree implementa R-trees padrão utilizando o algoritmo de partição quadrática de Guttman. O método de acesso hash é uma implementação das dispersões lineares de Litwin. Os algoritmos utilizados são mencionados apenas para informar que todos estes métodos de acesso são inteiramente dinâmicos, não necessitando de otimização periódica (como no caso de, por exemplo, métodos de acesso hash estáticos).

Quando a cláusula WHERE está presente, um índice parcial é criado. Um índice parcial é um índice que contém entradas apenas para uma parte da tabela, geralmente uma parte mais interessante do que o resto da tabela. Por exemplo, havendo uma tabela contendo tanto pedidos faturados quanto não faturados, onde os pedidos não faturados ocupam uma pequena fração da tabela, mas é a parte mais consultada, o desempenho pode ser melhorado criando-se um índice apenas para esta porção da tabela. Uma outra aplicação possível é a utilização da cláusula WHERE juntamente com UNIQUE para exigir a unicidade de um subconjunto dos dados da tabela.

A expressão utilizada na cláusula WHERE pode referenciar apenas as colunas da tabela subjacente (mas pode referenciar qualquer coluna, e não apenas as que estão sendo indexadas). Na forma atual, subconsultas e expressões de agregação não são permitidas na cláusula WHERE.

Todas as funções e operadores utilizados na definição de um índice devem ser possíveis de serem armazenados na memória intermediária (cachable), ou seja, seus resultados devem depender apenas de seus argumentos de entrada e nunca de uma influência externa (como o conteúdo de outra tabela ou a hora atual). Esta restrição garante que o comportamento do índice é bem definido. Para utilizar uma função definida pelo usuário em um índice deve ser utilizado o atributo 'Iscachable' na cláusula WITH.

Use o DROP INDEX para excluir um índice.

Notas

O otimizador de consultas do PostgreSQL vai considerar o uso de um índice B-tree sempre que um atributo indexado estiver envolvido em uma comparação utilizando um dos seguintes operadores: <, <=, =, >=, >

O otimizador de consultas do PostgreSQL vai considerar o uso de um índice R-tree sempre que um atributo indexado estiver envolvido em uma comparação utilizando um dos seguintes operadores: <<, &<, &>, >>, @, ~=, &&

O otimizador de consultas do PostgreSQL vai considerar o uso de um índice hash sempre que um atributo indexado estiver envolvido em uma comparação utilizando o operador =.

Atualmente somente os métodos de acesso B-tree e Gist suportam índices com mais de uma coluna. Por padrão, até 16 chaves podem ser especificadas (este limite pode ser alterado na geração do PostgreSQL). Na implementação atual, somente o B-tree suporta índices únicos.

Uma classe de operador pode ser especificada para cada coluna de um índice. A classe de operador identifica os operadores a serem utilizados pelo índice desta coluna. Por exemplo, um índice B-tree sobre inteiros de quatro bytes vai utilizar a classe de operadores int4_ops; esta classe de operadores inclui funções de comparação para inteiros de quatro bytes. Na prática, a classe de operadores padrão para o tipo de dado do campo é normalmente suficiente. O ponto principal em haver classes de operadores é que, para alguns tipos de dado, pode haver mais de uma ordenação que faça sentido. Por exemplo, pode se desejar ordenar o tipo de dado do número complexo tanto pelo valor absoluto, quanto pela parte real, o que pode ser feito definindo-se duas classes de operadores para o tipo de dado e, então, selecionando-se a classe apropriada para a construção do índice. Também existem algumas classes de operadores com finalidades especiais:

A seguinte consulta exibe todas as classes de operadores:

SELECT am.amname AS metodo_de_acesso,
       opc.opcname AS nome_do_operador,
       opr.oprname AS op_comparação
FROM   pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr
WHERE  opc.opcamid = am.oid AND
       amop.amopclaid = opc.oid AND
       amop.amopopr = opr.oid
ORDER BY método_de_acesso, nome_do_operador, op_comparação;
    

Utilização

Para criar um índice B-tree para a coluna titulo na tabela filmes:

CREATE UNIQUE INDEX unq_titulo
    ON filmes (titulo);
  

Compatibilidade

SQL92

O comando CREATE INDEX é uma extensão do PostgreSQL à linguagem.

Não existe o comando CREATE INDEX no SQL92.