CREATE [ UNIQUE ] INDEX nome_do_índice ON tabela [ USING método ] ( { coluna | ( expressão ) } [ classe_de_operadores ] [, ...] ) [ TABLESPACE espaço_de_tabelas ] [ 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 possa resultar em uma degradação de desempenho). [1] [2] [3] [4]
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 [5] . O método de índice R-tree implementa R-trees padrão utilizando o algoritmo de divisão quadrática (quadratic split) de Guttman [6] . O método de índice hash é uma implementação do hash linear de Litwin [7] [8] . Os usuários também podem definir seus próprios métodos de índice, mas é muito complicado.
Quando a cláusula WHERE está presente, é criado um índice parcial. Um índice parcial é um índice contendo entradas para apenas uma parte da tabela, geralmente uma parte mais útil para indexar do 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 é bastante 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 impor a unicidade de um subconjunto dos dados da tabela. Para obter informações adicionais deve ser consultada a Seção 11.7 .
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.
Faz o sistema verificar valores duplicados na tabela quando o índice é criado, se existirem dados, e toda vez que forem adicionados dados. A tentativa de inserir ou de atualizar dados que resultem em uma entrada duplicada gera um erro.
O nome do índice a ser criado. O nome do esquema não pode ser incluído aqui; o índice é sempre criado no mesmo esquema da tabela que este pertence.
O nome (opcionalmente qualificado pelo esquema) da tabela a ser indexada.
O nome do método de índice a ser utilizado. Pode ser escolhido entre btree, hash, rtree e gist. O método padrão é btree.
O nome de uma coluna da tabela.
Uma expressão baseada em uma ou mais colunas da tabela. Geralmente a expressão deve ser escrita entre parênteses, conforme mostrado na sintaxe. Entretanto, os parênteses podem ser omitidos se a expressão tiver a forma de uma chamada de função.
O nome de uma classe de operadores. Veja os detalhes abaixo.
O espaço de tabelas onde o índice será criado. Se não for especificado, será utilizado o default_tablespace, ou o espaço de tabelas padrão do banco de dados se default_tablespace for uma cadeia de caracteres vazia.
A expressão de restrição para o índice parcial.
Consulte 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 construção do PostgreSQL). Também atualmente somente B-tree suporta índices únicos.
Pode ser especificada uma classe de operadores 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 31.14.
Para remover um índice deve ser utilizado o comando DROP INDEX.
Por padrão não é utilizado índice para a cláusula IS NULL. A melhor forma para utilizar índice nestes casos é a criação de um índice parcial usando o predicado IS NULL.
Para criar um índice B-tree para a coluna titulo na tabela filmes:
CREATE UNIQUE INDEX unq_titulo ON filmes (titulo);
Para criar um índice para a coluna codigo da tabela filmes e fazer o índice residir no espaço de tabelas espaco_indices:
CREATE INDEX idx_codigo ON filmes(codigo) TABLESPACE espaco_indices;
O comando CREATE INDEX é uma extensão do PostgreSQL à linguagem. O padrão SQL não trata de índices.
| [1] |
Oracle — O comando CREATE INDEX é utilizado para criar um índice em: 1) Uma ou mais colunas de uma tabela, de uma tabela particionada, de uma tabela organizada pelo índice, de um agrupamento (cluster = objeto de esquema que contém dados de uma ou mais tabelas, todas tendo uma ou mais colunas em comum. O banco de dados Oracle armazena todas as linhas de todas as tabelas que compartilham a mesma chave de agrupamento juntas); 2) Um ou mais atributos de objeto tipado escalar de uma tabela ou de um agrupamento; 3) Uma tabela de armazenamento de tabela aninhada para indexar uma coluna de tabela aninhada. Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B10759-01 (N. do T.) |
| [2] |
Oracle — A tabela organizada pelo índice é um tipo especial de tabela que armazena as linhas da tabela dentro de segmento de índice. A tabela organizada pelo índice também pode ter um segmento de estouro (overflow) para armazenar as linhas que não cabem no segmento de índice original. Hands-On Oracle Database 10g Express Edition for Linux — Steve Bobrowski, pág. 350 (N. do T.) |
| [3] |
SQL Server — O comando CREATE INDEX cria um índice relacional em uma tabela ou visão especificada, ou um índice XML em uma tabela especificada. O índice pode ser criado antes de existir dado na tabela. Podem ser criados índices para tabelas e visões em outros bancos de dados especificando um nome de banco de dados qualificado. O argumento CLUSTERED cria um índice em que a ordem lógica dos valores da chave determina a ordem física das linhas correspondentes da tabela. A criação de um índice agrupado (clustered) único em uma visão melhora o desempenho, porque a visão é armazenada no banco de dados da mesma maneira que a tabela com um índice agrupado é armazenada. Podem ser criados índices em colunas calculadas. No SQL Server 2005 as colunas calculadas podem ter a propriedade PERSISTED. Isto significa que o Mecanismo de Banco de Dados armazena os valores calculados, e os atualiza quando as outras colunas das quais a coluna calculada depende são atualizadas. O Mecanismo de Banco de Dados utiliza os valores persistentes quando cria o índice para a coluna, e quando o índice é referenciado em uma consulta. SQL Server 2005 Books Online — CREATE INDEX (Transact-SQL) (N. do T.) |
| [4] |
DB2 — O comando CREATE INDEX é utilizado para: Definir um índice em uma tabela do DB2 (o índice pode ser definido em dados XML ou dados relacionais); Criar uma especificação de índice (metadados que indicam ao otimizador que a tabela de origem possui um índice). A cláusula CLUSTER especifica que o índice é o índice agrupador da tabela. O fator de agrupamento de um índice agrupador é mantido ou melhorado dinamicamente à medida que os dados são inseridos na tabela associada, tentando inserir as novas linhas fisicamente próximas das linhas para as quais os valores chave deste índice estão no mesmo intervalo. DB2 Version 9 for Linux, UNIX, and Windows (N. do T.) |
| [5] |
Lehman, Yao 81 - Philip L. Lehman , s. Bing Yao, Efficient locking for concurrent operations on B-trees, ACM Transactions on Database Systems (TODS), v.6 n.4, p.650-670, Dec. 1981 (N. do T.) |
| [6] |
Antonin Guttman: R-Trees: A Dynamic Index Structure for Spatial Searching. SIGMOD Conference 1984. (N. do T.) |
| [7] |
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.) |
| [8] |
Witold Litwin: Linear Hashing: A new Tool for File and Table Addressing - Summary by: Steve Gribble and Armando Fox. (N. do T.) |
