CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] nome_do_índice ON tabela [ USING método ] ( { coluna | ( expressão ) } [ classe_de_operadores ] [, ...] ) [ WITH ( parâmetro_de_armazenamento = valor [, ... ] ) ] [ 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, hash, GiST e GIN. 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.
Quando esta opção é utilizada, o PostgreSQL constrói o índice sem obter bloqueios para evitar inserções, atualizações e exclusões na tabela enquanto o índice está sendo construído; a construção padrão de índices bloqueia escritas (mas não leituras) na tabela até seu término. Existem muitos cuidados a serem tomados quando se utiliza esta opção — consulte Construção de índice CONCURRENTLY.
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, gist e gin. 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 nome de um parâmetro de armazenamento específico do método de índice. 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.
A cláusula WITH pode especificar parâmetros de armazenamento para os índices. Cada método de índice pode possuir seu próprio conjunto de parâmetros de armazenamento permitidos. Todos os métodos de índice nativos aceitam um único parâmetro:
O fator de preenchimento para um índice é a percentagem que determina quão cheia o método de índice vai tentar deixar as páginas de índice. Para as B-trees, as páginas folha são preenchidas até esta percentagem durante a construção inicial do índice, e também ao estender o índice para a direita (valores de chave maiores). Se em seguida as páginas se tornarem completamente cheias, elas serão divididas, levando a uma degradação gradual da eficiência do índice. As B-trees utilizam um fator de preenchimento padrão de 90, mas pode ser selecionado qualquer valor entre 10 e 100. Se a tabela for estática então o fator de preenchimento igual a 100 será o melhor para minimizar o tamanho físico do índice, mas para tabelas muito atualizadas um fator de preenchimento menor é melhor porque minimiza a necessidade de divisão de páginas. Os outros métodos de índice utilizam o fator de preenchimento de forma diferente mas bem análoga a esta; o fator de preenchimento padrão varia entre os métodos.
A criação do índice pode interferir na operação regular do banco de dados. Normalmente o PostgreSQL bloqueia a tabela a ser indexada com relação a escritas, e realiza toda a construção do índice em uma única varredura da tabela. As outras transações continuam podendo ler a tabela, mas se tentarem inserir, atualizar ou excluir linhas da tabela serão bloqueadas até a construção do índice terminar. Isto pode ter um efeito sério se o sistema for um banco de dados de produção ativo. Tabelas grandes podem levar muitas horas para serem indexadas, e mesmo para tabelas menores a construção do índice pode bloquear quem escreve por um tempo inaceitavelmente longo para um sistema em produção.
O PostgreSQL suporta a construção de índices sem bloquear a escrita. Este método é chamado especificando a opção CONCURRENTLY do comando CREATE INDEX. Quando se utiliza esta opção, o PostgreSQL deve realizar duas varreduras na tabela, e além disso deve aguardar todas as transações existentes terminarem. Portanto, este método requer mais trabalho total do que a construção de índice padrão, e leva muito mais tempo para terminar. Entretanto, uma vez que permite a continuação das operações normais enquanto o índice é construído, este método é útil para adição de novos índices em um ambiente de produção. Obviamente a carga extra de CPU e de E/S imposta pela criação do índice pode tornar as outras operações mais lentas.
Se acontecer algum problema durante a segunda varredura da tabela, como a violação da unicidade em um índice único, o comando CREATE INDEX irá falhar, mas deixará para trás um índice "inválido". Este índice será ignorado com relação às finalidades dos comandos, porque poderá estar incompleto; entretanto, ainda irá acarretar sobrecarga de atualização. Para estes casos o método de recuperação recomendado é remover o índice e tentar executar novamente o comando CREATE INDEX CONCURRENTLY (Outra possibilidade é tentar reconstruir o índice com REINDEX. Entretanto, uma vez que REINDEX não suporta construções CONCURRENTLY, esta opção dificilmente parecerá atrativa).
Outro problema ao se construir um índice único CONCURRENTLY, é que a restrição de unicidade já estará sendo imposta para as outras transações quando a segunda varredura da tabela iniciar. Isto significa que poderão ser relatadas violações da restrição por outros comandos antes do índice se tornar disponível para uso, ou mesmo nos casos em que a construção do índice venha a falhar. Também, caso ocorra uma falha na segunda varredura, o índice "inválido" continuará a impor sua restrição de unicidade após a falha.
São suportadas construções CONCURRENTLY para índices com expressões e para índices parciais. Erros ocorridos na avaliação das expressões podem causar um comportamento semelhante ao descrito acima para violações da restrição de unicidade.
As construções de índice regulares permitem que ocorram ao mesmo tempo outras construções de índice regulares na mesma tabela, mas somente pode ocorrer uma construção de índice CONCURRENTLY na tabela de cada vez. Nos dois casos não pode ocorrer nenhum outro tipo de modificação no esquema da tabela durante a construção. Outra diferença é que o comando CREATE INDEX regular pode ser executado dentro de um bloco de transação, enquanto CREATE INDEX CONCURRENTLY não pode.
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. Podem ser encontradas mais informações sobre classes de operadores na Seção 11.8 e na Seção 33.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.
As versões anteriores do PostgreSQL também possuíam o método de índice R-tree. Este método foi removido porque não possuía vantagens significativas sobre o método GiST. Se for especificado USING rtree, o comando CREATE INDEX irá interpretá-lo como USING gist, para simplificar a conversão dos bancos dados antigos para GiST.
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 expressão lower(titulo), para permitir procuras eficientes sem diferenciar letras minúsculas e maiúsculas:
CREATE INDEX idx_titulo_min ON filmes ((lower(titulo)));
Para criar um índice com um fator de preenchimento diferente do padrão:
CREATE UNIQUE INDEX idx_titulo ON filmes (titulo) WITH (fillfactor = 70);
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;
Para criar um índice sem bloquear as escritas na tabela:
CREATE INDEX CONCURRENTLY idx_qtd_vendas ON tbl_vendas (quantidade);
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.) |
