fr en

Consulter les recommandations de création d'index avec QSYS2.CONDENSEDINDEXADVICE

Objectifs :

Sur IBMi, pour optimiser les temps de traitement, SQL peut se créer ses propres index si ceux-ci n’existent pas déjà dans la base de données.
Les index créés par SQL (MTI ou Maintained Temporary Indexes) sont temporaires et donc supprimés à chaque IPL.

Il est important d’analyser les index temporaires créés par SQL pour éventuellemment créer des index permanents.

Pour cela il est possible de visuliser la liste des index recommandés avec ACS :

  • Run SQL SCRIPT
  • Onglet : Outils
  • Option : SCHEMAS
  • Clic droit sur la base de données concernée
  • Outil de conseil à la gestion des index
  • Regroupement des index recommandés

Mais il est également possible de récupérer les mêmes informations directement par SQL grâce à la table QSYS2.CONDENSEDINDEXADVICE.

La table QSYS2.CONDENSEDINDEXADVICE retourne la liste des Index recommandés, c’est à dire la liste des index que SQL a créé de façon temporaire pour répondre aux différentes requêtes faites sur la base de données.

Les informations principales contenues dans QSYS2.CONDENSEDINDEXADVICE sont :

  • TABLE_NAME Nom long de la table sur laquelle pointe l’index recommandé
  • TABLE_SCHEMA Nom long de la bibliothèque de la table sur laquelle pointe l’index recommandé
  • SYSTEM_TABLE_NAME Nom court de la table sur laquelle pointe l’index recommandé
  • SYSTEM_TABLE_SCHEMA Nom court de la bibliothèque de la table sur laquelle pointe l’index recommandé
  • KEY_COLUMNS_ADVISED Liste des clefs formant le chemin d’accès recommandé
  • LAST_ADVISED Date de la dernière recommandation de cet index
  • TIMES_ADVISED Nombre de fois ou l’index a été recommandé
  • MOST_EXPENSIVE_QUERY Durée de la requête la plus longue
  • AVERAGE_QUERY_ESTIMATE Moyenne de la durée de la requête
  • TABLE_SIZE Taille de la table
  • MAX_ROW Nombre d’enregistrement maximum de la table
  • MTI_CREATED Nombre de créations de l’index temporaire
  • MTI_USED Nombre d’utilisations de l’index temporaire
  • LAST_MTI_USED Date de dernière utilisation de l’index temporaire
  • ESTIMATED_CREATION_TIME Estimation du temps de création de l’index

Syntaxe :

Pour récupérer les 25 index recommandés sur les tables contenant le plus d’enregistrements :

SELECT   TABLE_NAME                                       AS TABLE_NOM_LONG, 
               TABLE_SCHEMA                                   AS BIBLIOTHEQUE, 
               SYSTEM_TABLE_NAME                        AS TABLE_NAM, 
               KEY_COLUMNS_ADVISED                   AS CLEFS_SUGGEREES, 
               INDEX_TYPE                                         AS TYPE_INDEX, 
               LAST_ADVISED                                    AS DERNIERE_RECOMMANDATION,
               TIMES_ADVISED                                  AS NB_RECOMMANDATIONS,
               MOST_EXPENSIVE_QUERY                  AS REQUETE_LA_PLUS_LONGUE,
               AVERAGE_QUERY_ESTIMATE              AS DUREE_MOYENNE_ESTIMEE_REQUETE,
               MTI_CREATED                                      AS NB_CREATION_INDEX_TEMPORAIRE,
               MTI_USED                                            AS NB_UTILISATION_INDEX_TEMPORAIRE,
               LAST_MTI_USED                                  AS DATE_DERNIERE_UTILISATION,
               MAX_ROW                                           AS NB_LIGNES,
               ESTIMATED_CREATION_TIME             AS TEMPS_CREATION_ESTIME      
FROM QSYS2.CONDENSEDINDEXADVICE
WHERE MTI_USED <> 0
ORDER BY MAX_ROW DESC
LIMIT 25;

Pour récupérer les 25 index les plus souvent recommandés :

SELECT   TABLE_NAME                                       AS TABLE_NOM_LONG, 
               TABLE_SCHEMA                                   AS BIBLIOTHEQUE, 
               SYSTEM_TABLE_NAME                        AS TABLE_NAM, 
               KEY_COLUMNS_ADVISED                   AS CLEFS_SUGGEREES, 
               INDEX_TYPE                                         AS TYPE_INDEX, 
               LAST_ADVISED                                    AS DERNIERE_RECOMMANDATION,
               TIMES_ADVISED                                  AS NB_RECOMMANDATIONS,
               MOST_EXPENSIVE_QUERY                  AS REQUETE_LA_PLUS_LONGUE,
               AVERAGE_QUERY_ESTIMATE              AS DUREE_MOYENNE_ESTIMEE_REQUETE,
               MTI_CREATED                                      AS NB_CREATION_INDEX_TEMPORAIRE,
               MTI_USED                                            AS NB_UTILISATION_INDEX_TEMPORAIRE,
               LAST_MTI_USED                                  AS DATE_DERNIERE_UTILISATION,
               MAX_ROW                                           AS NB_LIGNES,
               ESTIMATED_CREATION_TIME             AS TEMPS_CREATION_ESTIME      
FROM QSYS2.CONDENSEDINDEXADVICE
WHERE MTI_USED <> 0
ORDER BY NB_RECOMMANDATIONS DESC
LIMIT 25;

Pour récupérer les 25 index les plus souvent utilisés :

SELECT   TABLE_NAME                                       AS TABLE_NOM_LONG, 
               TABLE_SCHEMA                                   AS BIBLIOTHEQUE, 
               SYSTEM_TABLE_NAME                        AS TABLE_NAM, 
               KEY_COLUMNS_ADVISED                   AS CLEFS_SUGGEREES, 
               INDEX_TYPE                                         AS TYPE_INDEX, 
               LAST_ADVISED                                    AS DERNIERE_RECOMMANDATION,
               TIMES_ADVISED                                  AS NB_RECOMMANDATIONS,
               MOST_EXPENSIVE_QUERY                  AS REQUETE_LA_PLUS_LONGUE,
               AVERAGE_QUERY_ESTIMATE              AS DUREE_MOYENNE_ESTIMEE_REQUETE,
               MTI_CREATED                                      AS NB_CREATION_INDEX_TEMPORAIRE,
               MTI_USED                                            AS NB_UTILISATION_INDEX_TEMPORAIRE,
               LAST_MTI_USED                                  AS DATE_DERNIERE_UTILISATION,
               MAX_ROW                                           AS NB_LIGNES,
               ESTIMATED_CREATION_TIME             AS TEMPS_CREATION_ESTIME      
FROM QSYS2.CONDENSEDINDEXADVICE
WHERE MTI_USED <> 0
ORDER BY MTI_USED DESC
LIMIT 25;