CREATE [ UNIQUE ] INDEX nome_do_índice ON tabela [ USING método ]
( { coluna | ( expressão ) } [ classe_de_operadores ] [, ...] )
[ WHERE predicado ]
O comando CREATE INDEX constrói o índice nome_do_índice na tabela especificada. Os índices são utilizados, principalmente, para melhorar o desempenho do banco de dados (embora a utilização não apropriada cause uma degradação de desempenho).
Os campos chave para o índice são especificados como nomes de coluna ou, também, como expressões escritas entre parênteses. Podem ser especificados vários campos, se o método de índice suportar índices multicolunas.
O campo de um índice pode ser uma expressão computada a partir dos valores de uma ou mais colunas da linha da tabela. Esta funcionalidade pode ser utilizada para obter acesso rápido aos dados baseado em alguma transformação dos dados básicos. Por exemplo, um índice computado como upper(col) permite a cláusula WHERE upper(col) = 'JIM' utilizar um índice.
O PostgreSQL fornece os métodos de índice B-tree, R-tree, hash e GiST. O método de índice B-tree é uma implementação das B-trees de alta concorrência de Lehman-Yao. O método de índice R-tree implementa R-trees padrão utilizando o algoritmo de divisão quadrática (quadratic split) de Guttman. O método de índice hash é uma implementação do hash linear de Litwin [1] [2] . Os usuários também podem definir seus próprios métodos de índice, mas é muito complicado.
Quando a cláusula WHERE está presente, um índice parcial é criado. Um índice parcial é um índice contendo entradas apenas para uma parte da tabela, geralmente uma parte mais útil para indexar que o restante da tabela. Por exemplo, havendo uma tabela contendo tanto pedidos faturados quanto não faturados, onde os pedidos não faturados ocupam uma pequena parte da tabela, mas que é a parte mais usada, o desempenho pode ser melhorado criando um índice apenas para esta parte da tabela. Outra aplicação possível é utilizar a cláusula WHERE junto com UNIQUE para garantir 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 usar todas as colunas, e não apenas as que estão sendo indexadas. Atualmente não são permitidas subconsultas e expressões de agregação na cláusula WHERE. As mesmas restrições se aplicam aos campos do índice que são expressões.
Todas as funções e operadores utilizados na definição do índice devem ser "imutáveis" (immutable), ou seja, seus resultados devem depender somente de seus argumentos, 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 na expressão do índice ou na cláusula WHERE, a função deve ser marcada como IMMUTABLE na sua criação.
Veja o Capítulo 11 para obter informações sobre quando os índices podem ser utilizados, quando não são utilizados, e em quais situações particulares podem ser úteis.
Atualmente somente os métodos de índice B-tree e Gist suportam índices com mais de uma coluna. Por padrão podem ser especificadas até 32 colunas (este limite pode ser alterado na geração do PostgreSQL). Também atualmente somente B-tree suporta índices únicos.
Uma classe de operadores pode ser especificada para cada coluna de um índice. A classe de operadores identifica os operadores a serem utilizados pelo índice para esta coluna. Por exemplo, um índice B-tree sobre inteiros de quatro bytes usaria a classe 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 da coluna é 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 classificar o tipo de dado do número complexo tanto pelo valor absoluto quanto pela parte real, o que pode ser feito definindo duas classes de operadores para o tipo de dado e, então, selecionando a classe apropriada na construção do índice. Mais informações sobre classes de operadores estão na Seção 11.6 e na Seção 33.13 .
Deve ser utilizado o comando DROP INDEX para remover um índice.
Para criar um índice B-tree para a coluna titulo na tabela filmes:
CREATE UNIQUE INDEX unq_titulo ON filmes (titulo);
O comando CREATE INDEX é uma extensão do PostgreSQL à linguagem. O padrão SQL não trata de índices.
| [1] |
Litwin, W. Linear hashing: A new tool for file and table addressing. In Proceedings of the 6th Conference on Very Large Databases, (New York, 1980}, 212-223. (N. do T.) |
| [2] |
Witold Litwin: Linear Hashing: A new Tool for File and Table Addressing - Summary by: Steve Gribble and Armando Fox. (N. do T.) |