SQL cours

Instruction SQL CREATE INDEX

L’instruction CREATE INDEX est utilisée pour créer des index dans les tables.

Les index sont utilisés pour récupérer les données de la base de données plus rapidement qu’autrement. Les utilisateurs ne peuvent pas voir les index, ils sont juste utilisés pour accélérer les recherches/requêtes.

La mise à jour d’une table avec des index prend plus de temps que la mise à jour d’une table sans (car les index ont également besoin d’une mise à jour). Donc, ne créez des index que sur les colonnes qui seront fréquemment recherchées.

CREATE [UNIQUE] INDEX nom-index
        ON nom-table (nom-colonne [ASC | DESC])
        [CLUSTER | NOT CLUSTER]
        [PARTITIONED]
        [PADDED | NOT PADDED]
        [using-specification]
        [free-specification]
        [DEFINE YES | NO]
        [COMPRESS YES | NO]
        [PARTITION BY RANGE partition-element
                      using-specification
                      free-specification]
        [BUFFERPOOL nom-bp]
        [CLOSE YES | NO]
        [DEFER YES | NO]
        [PIECESIZE integer]
        [COPY YES | NO]


using-specification
        USING
              VCAT nom-catalogue |
              STOGROUP nom-stogroup
                  PRIQTY integer
                  SECQTY integer
                  ERASE YES | NO

free-specification
        FREEPAGE integer
        PCTFREE integer

partition-element
        PARTITION integer
        ENDING AT (constant/MAXVALUE/MINVALUE)

UNIQUE:

Cela empêche la table de contenir deux lignes ou plus avec la même valeur de la clé d’index.

ASC :

  • Spécifie que les entrées d’index doivent être conservées dans l’ordre croissant des valeurs de colonne.
  • Ce sont les paramètres par défauts.

DESC :

Spécifie que les entrées d’index doivent être conservées dans l’ordre décroissant des valeurs de colonne.

CLUSTER ou NOT CLUSTER :

Spécifie si l’index est l’index de clustering de la table ou non.

PARTITIONED :

Spécifie que l’index est des données partitionnées

PADDED ou NOT PADDED :

Spécifie comment les colonnes de chaîne de longueur variable doivent être stockées dans l’index.

  • PADDED : spécifie que les colonnes de chaîne de longueur variable dans l’index sont toujours remplies avec le caractère de remplissage par défaut jusqu’à leur longueur maximale.
  • NOT PADDED : spécifie que les colonnes de chaîne de longueur variable ne doivent pas être remplies à leur longueur maximale dans l’index.

Clause USING :

  • Pour les index non partitionnés, la clause USING indique si les ensembles de données de l’index doivent être gérés par l’utilisateur ou gérés par DB2.
  • VCAT : spécifie que le premier ensemble de données pour l’index est géré par l’utilisateur et que les ensembles de données suivants, si nécessaire, sont également gérés par l’utilisateur.
  • STOGROUP : Spécifie que DB2 définira et gérera les ensembles de données d’index. 
  • PRIQTY : spécifie l’allocation d’espace primaire minimum pour l’ensemble de données gérées DB2.
  • SECQTY : spécifie l’allocation d’espace secondaire minimum pour l’ensemble de données gérées DB2.
  • ERASE : Indique si les ensembles de données gérés par DB2 doivent être effacés lors de la suppression de l’index.

FREEPAGE :

Spécifie la fréquence à laquelle laisser une page d’espace libre lors de la création d’entrées d’index.

PCTFREE :

Détermine le pourcentage d’espace libre à laisser dans chaque page non-feuille et page feuille lorsque des entrées sont ajoutées à l’index.

DEFINE :

Spécifie quand les ensembles de données sous-jacents pour l’index sont physiquement créés.

  • OUI : les ensembles de données sont créés lors de la création de l’index. OUI est la valeur par défaut.
  • NON : les ensembles de données ne sont pas créés tant que les données ne sont pas insérées dans l’index.

COMPRESS :

Spécifie si la compression des données d’index sera utilisée.

  • NO : spécifie qu’aucune compression d’index ne sera utilisée. C’est la valeur par défaut.
  • YES : spécifie que la compression d’index sera utilisée.

PARTITION BY RANGE :

Spécifie que l’index est l’index de partitionnement.

  • PARTITION integer : une clause PARTITION spécifie la valeur la plus élevée de la clé d’index dans une partition d’un index de partitionnement.
  • ENDING AT : Spécifie qu’il s’agit de l’index de partitionnement et indique comment les données seront partitionnées.
    CONSTANT : spécifie une valeur constante avec un type de données qui doit être conforme aux règles d’attribution de cette valeur à la colonne.
    MAXVALUE : spécifie une valeur supérieure à la valeur maximale pour la clé de limite d’une limite de partition.
    MINVALUE : spécifie une valeur inférieure à la valeur minimale de la clé de limite d’une limite de partition.

BUFFERPOOL :

Identifie le pool de mémoire tampon à utiliser pour l’index.

CLOSE :

Spécifie si l’ensemble de données peut ou non être fermé lorsque l’index n’est pas utilisé et que la limite du nombre d’ensembles de données ouverts est atteinte.

  • OUI : Admissible à la fermeture. C’est la valeur par défaut
  • NON : Non éligible à la fermeture.

DEFER :

Indique si l’index est créé lors de l’exécution de l’instruction CREATE INDEX.

  • NON : L’index est construit. C’est la valeur par défaut.
  • OUI : L’index n’est pas construit.

PIECESIZE :

Spécifie l’adressabilité maximale de chaque ensemble de données pour un index.

COPY :

Indique si l’utilitaire COPY est autorisé pour l’index.

  • NON : N’autorise pas l’image complète ou les copies simultanées. NON est la valeur par défaut.
  • OUI : Autorise l’image complète ou les copies simultanées.

Exemple 1:

Créez un index sur la colonne TAB1_COL2 d’une table TB_TAB1.

CREATE INDEX IX_TAB1_COL2
       ON TB_TAB1(TAB1_COL2);

Créez un index nommé NOM_IX_PROJET sur la table TB_PROJET. Le but de l’index est de s’assurer qu’il n’y a pas deux entrées dans la table avec la même valeur pour le nom du projet (NOM_PROJET). Les entrées d’index doivent être dans l’ordre croissant.

CREATE UNIQUE INDEX NOM_IX_PROJET
       ON TB_PROJET(NOM_PROJET);

Créez un index nommé IX_EMPLOYE_JOB_DEPT sur la table TB_EMPLOYE. Organisez les entrées d’index dans l’ordre croissant par intitulé de poste (EMPLOYE_JOB) au sein de chaque département (EMPLOYE_DEPT).

CREATE INDEX IX_EMPLOYE_JOB_DEPT
       ON TB_EMPLOYE (EMPLOYE_DEPT, EMPLOYE_JOB);

Exemple 2 :

Créez un index unique, nommé DSN8910.IX_DEPT, sur la table DSN8910.TB_DEPT. Les entrées d’index doivent être dans l’ordre croissant par la colonne unique DEPT_NO. DB2 doit définir les ensembles de données pour l’index, à l’aide du groupe de stockage DSN8G910.

Chaque ensemble de données doit contenir au maximum 1 Mégaoctet de données. Utilisez 512 Ko comme allocation d’espace primaire pour chaque ensemble de données et 64 Ko comme allocation d’espace secondaire. Remplir l’index.

Les ensembles de données peuvent être fermés lorsque personne n’utilise l’index et n’ont pas besoin d’être effacés si l’index est supprimé.

CREATE UNIQUE INDEX DSN8910.IX_DEPT
        ON DSN8910.TB_DEPT
              (DEPT_NO ASC)
        PADDED
        USING STOGROUP DSN8G910
                       PRIQTY 512
                       SECQTY 64
                       ERASE NO
       BUFFERPOOL BP1
       CLOSE YES
       PIECESIZE 1M;

Explication:

Les ensembles de données sous-jacents pour l’index seront créés immédiatement, ce qui est la valeur par défaut (DEFINE YES). En supposant que la table DSN8910.TB_DEPT est vide, si nous voulions différer la création des ensembles de données jusqu’à ce que les données soient insérées pour la première fois dans l’index, nous spécifierions DEFINE NO au lieu d’accepter le comportement par défaut.

La spécification de PADDED garantit que les colonnes de chaînes de caractères de longueur variable dans l’index sont complétées par des blancs.

Exemple 3 :

Créez un index de cluster, nommé IX_EMP, sur la table TB_EMP dans la base de données DSN8910. Mettez les entrées dans l’ordre croissant par la colonne EMP_NO. Laissez DB2 définir les ensembles de données pour chaque partition à l’aide du groupe de stockage DSN8G910. Faites en sorte que l’allocation d’espace principal soit de 36 kilo-octets et autorisez DB2 à utiliser la valeur par défaut pour SECQTY. Si l’index est supprimé, les ensembles de données n’ont pas besoin d’être effacés.

Il doit y avoir 4 partitions, avec des entrées d’index réparties entre elles comme suit :

  • Partition 1 : entrées jusqu’à H99
  • Partition 2 : entrées au-dessus de H99 jusqu’à P99
  • Partition 3 : entrées au-dessus de P99 jusqu’à Z99
  • Partition 4 : entrées au-dessus de Z99

Associez l’index au pool de mémoire tampon BP1 et autorisez la fermeture des ensembles de données lorsque personne n’utilise l’index. Activez l’utilisation de l’utilitaire COPY pour l’image complète ou les copies simultanées.

CREATE INDEX DSN8910.IX_EMP
        ON DSN8910.TB_EMP
            (EMP_NO ASC)
        USING
            STOGROUP DSN8G910
                PRIQTY 36
                ERASE NO
        CLUSTER
        PARTITION BY RANGE
            (PARTITION 1 ENDING AT('H99'),
             PARTITION 2 ENDING AT('P99'),
             PARTITION 3 ENDING AT('Z99'),
             PARTITION 4 ENDING AT('999'))
        BUFFERPOOL BP1
        CLOSE YES
        COPY YES;