Pour améliorer les performances, c'est-à-dire accélérer les recherches de données que l'on va faire dans cette table, on a la possibilité d'agir sur l'organisation de la table, donc en fait sur le stockage physique des données, de façon à minimiser le nombre d'entrées sorties faites sur le disque. Par exemple, on peut chercher, d'une part, à regrouper dans les mêmes pages de stockage des données qui peuvent être fréquemment recherchées ensemble, d'autre part à se donner des moyens d'accès par clés qui permettront de retrouver l'adresse des données sur disque, en fonction d'une clé, sans avoir à parcourir toute la table. Ces méthodes pour accélérer les recherches de données dans une table sont fournies par INGRES selon deux types :
On ne peut optimiser globalement toutes les recherches possibles sur une table, mais on a toujours un ensemble de recherches types pour lesquelles une bonne organisation de la table apportera un gain de performances considérable. Pour définir cet ordre, on va choisir, parmi les colonnes de la table, celles qui servent le plus souvent de critères de recherche et organiser la table par rapport à ces colonnes qui constitueront la "clé primaire" de la table. Si par exemple on considère la table suivante : personne (nom, prénom, date-naissance, ville, département) Lorsqu'on cherche les personnes habitant une ville V donnée, avec une organisation en heap il faudra balayer séquentiellement toute la table personne pour trouver les réponses. Ceci peut être acceptable si la table est de petite taille, mais devient pénalisant avec des tables volumineuses, s'étendant sur un grand nombre de pages de disque. Si la table est structurée sur une clé primaire "ville", on accélère la recherche d'une part parce que les personnes d'une même ville seront regroupées dans les mêmes pages, d'autre part parce que pour la valeur V de ville, l'organisation va donner l'adresse des personnes ayant cette valeur de ville. Les organisations de tables qu'INGRES permet de créer sont les trois organisations classiques de fichiers :
Les enregistrements de la table d'origine sont stockés, après cette organisation, dans les pages correspondant à leur clé. Si on reprend l'exemple précédent, une organisation de la table personne en hash sur la clé "ville" va restructurer la table de façon que toutes les personnes d'une même ville soient enregistrées dans la même page. Lorsqu'on recherche les personnes sur une valeur V de ville, la valeur de hachage correspondant à V donne le numéro de page où elles se trouvent. Dans ce cas, on a la réponse avec une seule entrée/sortie effectuée. Toutefois, il est possible que, pour une clé donnée, tous les enregistrements ne tiennent pas dans une seule page. On a alors des pages de débordement, chaînées à la première page pointée par la clé, ce qui pénalise la recherche. L'organisation en hash est particulièrement bien adaptée à des recherches où le critère porte sur l'égalité de la clé primaire à une valeur donnée. Il faut noter que le critère doit porter sur toute la clé pour tirer parti du hachage. Si la clé est formée de plusieurs colonnes, la recherche sur la valeur d'un sous-ensemble de ces colonnes ne présente plus d'intérêt. De même, toute autre recherche, par exemple sur plage de valeurs telle que les personnes dont la ville commence par "PA", ne peut tirer profit de la clé de hachage. Un autre inconvénient de la structure en hash est son encombrement, car les pages de données ne sont remplies par défaut qu'à 50%. Mais ceci peut être très facilement modifié et il est recommandé d'augmenter ce taux de remplissage pour des tables stables, rarement mises à jour. schéma d'une structure hash :
¯
¯
¯
¯
¯
¯
Dans INGRES, l'index est géré par deux niveaux : la racine et les pages d'index; une page d'index contient les clés maximales de chaque page de données, avec leur adresse; la racine pointe elle-même les pages d'index selon leur clé maximale. schéma de l'ISAM :
¯
¯
¯
¯
¯
¯
¯
¯
¯
Cette organisation est très bien adaptée aux recherches sur valeur exacte de la clé, ou sur plage de valeurs, ou sur la valeur de la partie gauche de la clé quand elle est formée de plusieurs colonnes, puisque les enregistrements sont triés sur la clé. Son inconvénient majeur est la difficulté des mises à jour et les effets de débordement qu'elles peuvent produire : les nouveaux enregistrements doivent être placés dans les pages pointées par leur index et, quand une page est pleine on crée une page de débordement. On peut ainsi, après avoir fait des insertions massives dans la table, avoir à parcourir un grand nombre de pages pour trouver les données. Il faut, pour éviter cela, réorganiser souvent la table.
On constitue ainsi, avec les différents niveaux d'index, un arbre d'index. Cet arbre est dit balancé car tous les noeuds terminaux (ou feuilles) sont au même niveau. Les noeuds terminaux pointent sur les pages de données, tandis que les noeuds intermédiaires pointent sur les pages d'index du niveau inférieur. Au départ, lorsque la table est structurée en Btree, les données sont triées. Ensuite, les données insérées sont stockées dans les pages suivantes, sans avoir à les réorganiser : seuls les index vont être réorganisés dynamiquement. Cette propriété du Btree utilisé dans INGRES tient dans le fait que les noeuds terminaux contiennent toutes les clés des données de la table : on dit que l'index est "dense". Donc, la table peut grossir sans qu'il y ait de problèmes de débordements comme en ISAM, mais en contre partie, le fichier d'index sera grand et grossira en même temps que la table. L'exemple suivant illustre un Btree d'une table personne dont la clé
est le nom.
¯
¯
feuilles
Les adresses donnent la page de la donnée et son emplacement dans la page. Cette organisation est bien adaptée à la recherche sur une plage de valeurs de la clé, sur la partie gauche de la clé, ainsi que sur sa valeur exacte. Elle pose moins de problèmes que l'ISAM si on a beaucoup de mises à jour ou d'insertions à faire. Mais elle prend beaucoup de place pour gérer les index (dans les feuilles, on a un index dense).
organiser la table personne en hash sur les colonnes "nom", "prénom" : organiser la table personne en isam sur la colonne "ville" : organiser la table personne en btree sur la colonne "departement" : Elle peut aussi être faite avec un tri, par la structure "heapsort" au lieu de heap, ce qui permettra de remettre les données dans un ordre trié (mais sans clé d'accès) sur le disque. Ceci est intéressant si on a, par ailleurs, un accès par clé secondaire (voir plus loin) car les données accédées seront regroupées. exemple : réorganisation en heapsort de la table personne, sur le nom : D'autres options de la commande modify permettent de régler le remplissage des pages, les nombres de pages index, etc..., mais les paramètres par défaut sont généralement suffisants. On peut, pour limiter le nombre de pages à lire, remplir à 100 % une table ne subissant pas de modifications :
Mais il arrive fréquemment que l'on ait besoin d'accélérer les accès à une table sur d'autres colonnes que sur la clé primaire. Par exemple, on a pris "ville" comme clé primaire de la table personne parce que l'on a souvent à faire une jointure sur les villes, mais si on a à chercher les personnes sur date-naissance, notre clé ne sert plus. On peut utiliser un deuxième type d'accès privilégié, soit seul, soit combiné avec une structure de table : c'est la création d'un index secondaire. Cet index secondaire consiste à créer un fichier d'index sur une clé "secondaire", formée par une ou plusieurs colonnes de la table, sans réorganisation des données : le fichier d'index va donner, pour chaque clé secondaire, l'adresse de l'enregistrement correspondant à cette clé. Le fichier d'index est traité par INGRES comme une table qui contient, au lieu des données, des couples (clé, adresse de donnée). Il est créé par la commande : Remarque : une clé secondaire peut être formée de colonnes faisant partie de la clé primaire.
Que prendre comme clé ?
Ingres, depuis la version 6.4, permet de créer automatiquement une clé unique qui sera mise à jour par le système à chaque nouvelle insertion de tuple. Cette possibilité est offerte dans la commande de création de table. exemple : ident table_key with system_maintained, nom char(30), details char(100) ); Lorsqu'on traite des données hiérarchisées, on a des clés "en cascade", par exemple du type code_pays, code_region, code_ville. Supposons que ces codes soient numériques et représentés par idpays, idregion et idville : pays (idpays,.. region (idpays, idregion,... ville (idpays, idregion, idville,.. Pour trouver 2 villes de la même région, on a 2 tests à faire, sur idpays et idregion : ... and v1.idregion = v2.idregion; idpaysreg = idpays * 100 + idregion La recherche précédente peut alors se faire avec un seul test :
On peut également conseiller la structure HEAP pour une table très petite (moins de 5 pages) ou HEAP avec des index secondaires pour une table très grosse (plus de 2000 pages)
Ces informations peuvent être estimées par l'optimiseur ou générées par un utilitaire "optimizedb" qui fournit des statistiques : valeurs uniques et histogrammes de valeurs des colonnes. Pour obtenir des plans d'exécution corrects, on a intérêt à fournir à l'optimiseur des statistiques à jour, en lançant, au niveau UNIX, la commande : and v.ancstr <81 and f.revenu > 90000 Pour choisir quel index utiliser en premier, l'optimiseur va utiliser les statistiques fournies par optimizedb : ou bien : L'idéal est de lancer optimizedb non seulement sur les clés principales ou secondaires, mais aussi sur toutes les colonnes référencées par les clauses where. Il faut éviter de lancer la commande optimizedb sur toutes les colonnes de toutes les tables de la base, en faisant : optimizedb nom_base , car la création de toutes les statistiques est extrêmement longue et inutile. Après chaque passage de la première commande "optimizedb", il est conseillé de lancer la commande Unix "sysmod" qui réorganise les tables systèmes : Si certaines statistiques sont devenues inutiles, on peut les détruire avec la commande Unix
Par exemple, pour obtenir l'ensemble des foyers ayant au moins un véhicule, on peut écrire 3 formes de requêtes : 1ère requête (jointure simple) : from foyer f, vehicule v where f.ident = v.ident; from foyer f where exists (select * from vehicule v where f.ident = v.ident); from foyer f where f.ident in (select v.ident from vehicule v); En général, l'utilisation des formes exists ou in pourrait être évitée par l'usage d'une "jointure externe", c'est-à-dire une jointure entre 2 tables où on garde à la fois les lignes répondant au critère de jointure et les lignes de la 2ème table ne répondant pas au critère. On peut donc éviter la forme exists ou in en réalisant une jointure externe avant la sélection. Exemple : on veut trouver les foyers n'ayant pas de véhicule. La requête avec exists s'écrit : where not exists (select * from vehicule v where f.ident = v.ident);
Dans la table foyer, les foyers f1 ont au moins un véhicule, ce qui correspond à l'ensemble des véhicules v1. Les foyers f2 n'ont pas de véhicule; on cherche cet ensemble de foyers. Les véhicules v2 ne correspondent à aucun foyer de la table foyer. Faire une jointure externe gauche entre foyer et vehicule consiste à prendre tous les 'ident' de foyer et de vehicule, existant dans au moins une de ces 2 tables : select f.ident, v.ident as vehic from foyer f left join vehicule v on f.ident = v.ident;
Pour trouver les foyers correspondant à l'ensemble f2 :
or (couleur = 'bleu' and an = 73) or (couleur = 'jaune' and an = 88) or .... insert into temp values('vert',83); .......................('bleu',73); ..................... ('jaune',88); ..... where f.cnaiss < 40 union select ident from foyer f, vehicule v where f.ident = v.ident; moyenne des âges de parents et d'enfants : select age from parent ; insert into temp select age from enfant ; select avg(age) from temp ; count(*) as nombre, sum(age) as somme from enfant ; insert into temp count(*), sum(age) from parent ; select float4 (sum(somme)) / sum(nombre) as moyage from temp ; from foyer group by cprof order by cprof ;
En effet, le fichier logfile, qui garde les résultats de toutes les sessions en cours, est partagé par tous les usagers Ingres. Avec SQL
Utiliser l'appel direct plutôt que par ingmenu. Ces outils écrans sont gros consommateurs de balayage de table : essayer de limiter et écourter leur emploi. Lors d'insertions ou mises à jour fréquentes, revenir à l'écran des fonctions QBF (idem lors d'une interruption ...). Éviter d'utiliser TableField ou des jointures trop grosses, utiliser des vues ou des tables temporaires pour restreindre l'information fournie au minimum utile. Problème d'interblocage lors de mises à jour : Des usagers simultanés de la même table peuvent se bloquer. Si cela arrive trop fréquemment, revoir la structure de la table. HEAP est à changer en HASH ou en BTREE, qui doit donner de bons résultats. Dans ce cas, penser aussi à réduire aussi les facteurs de remplissage de la table ET des index secondaires. Si trop de problèmes en interactif : avant de passer à des requêtes en applicatif, différer vos requêtes en "heures creuses" grâce à l'outil ingnuit (voir cours outils Ingres)
(réorganise les tables système Ingres) et, également, de temps à autre : $ verifydb -mruninteractive -sdbname "nom_base" -opurge qui élimine les informations périmées.
|