| > > > | |
 CREATE GROUP
|
CREATE LANGUAGE
|
CREATE [ UNIQUE ] INDEX nom ON table [ USING méthode ]
( { colonne | ( expression ) } [ classeop ] [, ...] )
[ TABLESPACE nom_tablespace ]
[ WHERE prédicat ]
CREATE INDEX construit un index nom_index sur la table spécifiée. Les index sont principalement utilisés pour améliorer les performances de la base de données (bien qu'une utilisation inappropriée puisse produir l'effet inverse).
Les champs clé pour l'index sont spécifiés à l'aide de noms des colonnes ou par des expressions écrites entre parenthèses. Plusieurs champs peuvent être spécifiés si la méthode d'indexation supporte les index multi-colonnes.
Un champ d'index peut être une expression calculée à partir des valeurs d'une ou plusieurs colonnes de la ligne de table. Cette fonctionnalité peut être utilisée pour obtenir un accès rapide à des données obtenues par transformation des données basiques. Par exemple, un index calculé sur upper(col) autorise la clause WHERE upper(col) = 'JIM' à utiliser un index.
PostgreSQL™ fournit les méthodes d'indexation B-tree (NDT : arbres balancés), R-tree (NDT : arbres réels), hash (NDT : hachage) et GiST (NDT : arbres de recherche généralisés). La méthode B-tree est une implantation des arbres-B à haute concurrence de Lehman-Yao. La méthode R-tree implante les arbres-R à l'aide de l'algorithme divisé quadratique de Guttman. La méthode hash est une implémentation du hachage linaire de Litwin. Il est possible, bien que compliqué, de définir des méthodes d'indexation utilisateur.
Lorsque la clause WHERE est présente, un index partiel est créé. Un index partiel est un index ne contenant des entrées que pour une portion d'une table, habituellement la portion sur laquelle l'indexation est la plus utile. Par exemple, si une table contient des ordres facturés et d'autres qui ne le sont pas, et que les ordres non facturés n'occupent qu'une petite fraction du total de la table, qui plus est fréquemment utilisée, les performances sont améliorées par la création d'un index sur cette portion. Une autre application possible est l'utilisation de la clause WHERE en combinaison avec UNIQUE pour assurer l'unicité sur un sous-ensemble d'une table. Voir Section 11.7, « Index partiels » pour plus de renseignements.
L'expression utilisée dans la clause WHERE peut ne faire référence qu'à des colonnes de la table sous-jacente, mais elle peut utiliser toutes les colonnes, pas uniquement celles indexées. Actuellement, les sous-requêtes et les expressions d'agrégats sont aussi interdites dans la clause WHERE. Les mêmes restrictions s'appliquent aux champs d'index qui sont des expressions.
Toutes les fonctions et opérateurs utilisés dans la définition d'index doivent être « immutable » (NDT : immuable), c'est-à-dire que leur résultat ne doit dépendre que de leurs arguments et jamais d'une influence externe (telle que le contenu d'une autre table ou l'heure). Cette restriction permet de s'assurer que le comportement de l'index est strictement défini. Pour utiliser une fonction utilisateur dans une expression d'index ou dans une clause WHERE, cette fonction doit être marquée immutable lors de sa création.
Le système vérifie la présence de valeurs dupliquées dans la table à la création de l'index (si des données existent déjà) et à chaque fois qu'une donnée est ajoutée. Les tentatives d'insertion ou de mises à jour qui résultent en des entrées dupliquées engendrent une erreur.
Le nom de l'index à créer. Aucun nom de schéma ne peut être inclus ici ; l'index est toujours créé dans le même schéma que sa table parent.
Le nom de la table à indexer (éventuellement qualifié du nom du schéma).
Le nom de la méthode à utiliser pour l'index. Les choix sont btree, hash, rtree et gist. La méthode par défaut est btree.
Le nom d'une colonne de la table.
Une expression basée sur une ou plusieurs colonnes de la table. L'expression doit habituellement être écrite entre parenthèses, comme la syntaxe le précise. Néanmoins, les parenthèses peuvent être omises si l'expression a la forme d'un appel de fonction.
Le nom d'une classe d'opérateur. Voir plus bas pour les détails.
Le tablespace dans lequel créer l'index. S'il n'est pas précisé, default_tablespace est utilisé ou le tablespace par défaut de la base de données si default_tablespace est une chaîne vide.
L'expression de la contrainte pour un index partiel.
Chapitre 11, Index présente des informations sur le moment où les index peuvent être utilisés, quand ils ne le sont pas et dans quelles situations particulières ils peuvent être utiles.
Actuellement, seules les méthodes d'indexation B-tree et GiST supportent les index multi-colonnes. Jusqu'à 32 champs peuvent être spécifiés par défaut. (Cette limite peut être modifiée à la compilation de PostgreSQL™.) Seul B-tree supporte actuellement les index uniques.
Une classe d'opérateur peut être spécifiée pour chaque colonne d'un index. La classe d'opérateur identifie les opérateurs à utiliser par l'index pour cette colonne. Par exemple, un index B-tree sur des entiers codés sur quatre octets utilise la classe int4_ops, qui contient des fonctions de comparaison pour les entiers sur quatre octets. En pratique, la classe d'opérateur par défaut pour le type de données de la colonne est généralement suffisant. Les classes d'opérateur trouvent leur intérêt principal dans l'existence, pour certains types de données, de plusieurs ordonnancements significatifs.
Soit l'exemple d'un type de données « nombre complexe » qui doit être classé par sa valeur absolue ou par sa partie réelle. Cela peut être réalisé par la définition de deux classes d'opérateur pour le type de données, puis par la sélection de la classe appropriée lors de la création d'un index.
De plus amples informations sur les classes d'opérateurs sont disponibles dans Section 11.8, « Classes d'opérateurs » et dans Section 32.14, « Interfacer des extensions d'index ».
DROP INDEX est utilisé pour supprimer un index.
Par défaut, les index ne sont pas utilisés pour les clauses IS NULL. La meilleure façon d'utiliser des index dans de tels cas est de créer un index partiel qui utilise un prédicat IS NULL.
Créer un index B-tree sur la colonne titre dans la table films :
CREATE UNIQUE INDEX title_idx ON films (title);
Créer un index sur la colonne code de la table films et donner à l'index l'emplacement du tablespace espaceindex :
CREATE INDEX code_idx ON films(code) TABLESPACE espaceindex;