Problème d'inclusion de l'index

Version imprimable

25/05/2008, 16h20
Nicolimero

Problème d'inclusion de l'index

Bonjour tout le monde,

je suis actuellement en train de travailler sur la modélisation d'une base de données pour une entreprise dans le batiment à l'aide de Power AMC 12.1. J'ai fait toute l'analyse préparatoire, mon MCD a l'air de tenir la route. La génération du modèle physique se passe bien aussi.
Par contre, au moment de générer la base (option "SGBD" -> "Générer la base de données..."), le programme me sort un avertissement:

Catégorie : Index de table
Vérifier : Inclusion de l'index
Objet : Index 'lot projet.LOT_PROJET_PK' inclut 'LOT_PROJET_FK'
Emplacement : <Modèle>::lot projet

Le contexte est le suivant:
j'ai notamment les 2 tables en question:
- table des projets: identifiés par des numéros uniques (autoincrémentés) et leurs attributs (ex: un projet de rénovation)
- tables des lots : identifiés par des numéros uniques (autoincrémentés) et un attribut, leur nom (ex: lot éléctricité, lot plomberie, lot maçonnerie...).

Un projet doit avoir au minimum un lot et peut avoir plusieurs lots.
Un lot peut ne pas appartenir à un projet mais peut aussi appartenir à plusieurs projet.

Les cardinalités sont donc:
"table projet" --- 1/n ------ assoc lot/projet ------ 0/n --- "table lot"

Au moment de la génération du MPD, une table lot/projet est créée dont la clé primaire est la concaténation des clés primaires de la table projet et de la table lot. Ce qui donne:

Lot_Projet
pr_id smallint <pk,fk1> (identifiant du projet)
lo_id smallint <pk,fk2> (identifiant du lot)

Je voulais donc savoir quels pourraient être les problèmes futurs d'un tel avertissement ? Comment faire pour l'empécher car il me semble qu'il s'agit d'un cas d'école sans réel problème ?

Je vous remercie d'avance. Et si je n'ai pas été assez clair ou s'il vous faut plus de précision, n'hésitez pas, je vous répondrai.
26/05/2008, 02h14
fsmrel

Citation:

Envoyé par Nicolimero

le programme me sort un avertissement:

Catégorie : Index de table
Vérifier : Inclusion de l'index
Objet : Index 'lot projet.LOT_PROJET_PK' inclut 'LOT_PROJET_FK'
Emplacement : <Modèle>::lot projet

AMC vous prévient qu’il a généré (inutilement) l’index LOT_PROJET_FK mais il préfère manifestement que vous preniez l’initiative de le supprimer.

AMC a dû générer 3 index pour la table LOT_PROJET :

1) Un index PROJET_PK pour la clé primaire de la table, index ayant pour clé (LotId, PrId) ou (PrId, LotId), peu importe.

2) Un index LOT_PROJET_FK ayant pour clé soit (LotId), soit (PrId).

3) Un index Lot_Projet_FK2 (ou un nom de la même farine), ayant pour clé (PrId) si l’index LOT_PROJET_FK a pour clé (LotId) ou ayant pour clé (LotId) si l’index LOT_PROJET_FK a pour clé (PrId).

Supposons que la génération ait donné lieu à ceci :

— Clé de l’Index PROJET_PK : (LotId, PrId)
— Clé de l’Index PROJET_FK : (LotId)
— Clé de l’Index PROJET_FK2 : (PrId)
Il est d'usage pour la plupart des SGBD d’exiger un index de type UNIQUE, pour garantir l’unicité des clés primaires, d’où la production par AMC de l’index PROJET_PK.
AMC produit aussi un index pour chaque clé étrangère, ce qui est parfois utile pour garantir la performance de certaines requêtes, d’où l’existence des index PROJET_ FK et PROJET_ FK2.
Toutefois, vous reconnaîtrez que pour garantir la performance concernant la clé étrangère {LotId}, l’index PROJET_PK suffit largement et donc que l’index PROJET_ FK "inclus dans l'autre" est strictement inutile et doit disparaître, car en plus il freine les opérations lors des opérations de mise à jour (INSERT, UPDATE, DELETE), engendre des surcoûts de maintenance (sauvegardes, réorganisations, etc.), pollue les logs, j’en passe et des meilleures.

Pour savoir si c’est PROJET_FK ou PROJET_FK2 qu’il faut supprimer :

Cliquez sur la table Lot_Projet, puis sur l’onglet Index de la fenêtre "Propriétés de la table". Vous devez voir les 3 noms d’index.
Pour connaître les colonnes figurant dans la clé d’un index, sélectionnez la ligne correspondante puis faites ALT-ENTRÉE (ou cliquez sur l’cône "Afficher les propriétés", celle où il y a une main indiquant une feuille de papier).
Ensuite, en toute connaissance de cause, sélectionnez la ligne correspondant à l’index inutile et, par clic droit, sélectionnez "Supprimer", puis faites feu.
31/01/2009, 21h27
soft_angel

dsl fsmrel, mais j'ai pas compris une chose:
pourquoi vous avez choisi de supprimer l'index concernant la clé étrangère {LotId} au lieu de l'autre??
cad quels sont les critères qui permettent de déterminer l'index à supprimer?
01/02/2009, 00h50
fsmrel
1) Ceux qui développent les SGBD exigent que chaque clé primaire (ou alternative) soit dotée d’un index, de type UNIQUE. Un tel index sert à garantir l’unicité des clés. Disons qu’en imposant la présence d’un tel index, ça leur fait du code en moins à développer, c'est toujours ça de pris. Tel est le cas par exemple de DB2, SQL Server et vraisemblablement Oracle.

En revanche, les clés étrangères ne sont pas soumises à ce genre de diktat. Si donc l’index PROJET_PK est obligatoire, l’index PROJET_FK fait double emploi.

2) A supposer que ceux qui développent les SGBD permettent qu’une clé primaire ne soit pas nécessairement dotée d’un index et que l’on supprime l’index PROJET_PK.
Si l’on exécute une requête permettant de savoir si la paire {LotId=123, PrId=456} existe dans la table Lot_Projet :
Code:

1 2 3 4 5 SELECT 'OK' From Lot_Projet where LotId = 123 and PrId = 456 ;
alors le SGBD mettra vraisemblablement à profit l’index PROJET_FK pour accélérer la recherche, ainsi que l’index PROJET_FK2. Quand je dis vraisemblablement, cela veut dire que le SGBD choisira la tactique la plus rentable selon les données statistiques stockées dans son catalogue. Plus la table sera volumineuse et le nombre de valeurs distinctes élevé, plus le SGBD jugera nécessaire l’utilisation des deux index, afin d’éviter les crapahuts interminables dans la table. Si le nombre de niveaux de chaque index est égal à n, le nombre d'entrées/sorties sera au moins égal à 2*n.

Maintenant, si l’on dispose de l’index PROJET_PK (et que l’on ait supprimé ou non l’index PROJET_FK), la réponse est contenue dans cet index et l’efficacité est maximale, puisque le SGBD ne cherchera pas à accéder à la table tandis que le nombre d'entrées/sorties sera égal à n.
01/02/2009, 16h50
soft_angel

donc pour la clé primaire il faut avoir d'index pour améliorer les performances. et ainsi il faut supprimer l'index de la clé étrangère.
mais il ya 2 clés étrangères : {poID} et {loID}. quelle clé doit-on supprimer? est-ce qu'il ya un critère pour choisir?
01/02/2009, 20h14
fsmrel

Hum...

Citation:

Envoyé par soft_angel

donc pour la clé primaire il faut avoir d'index pour améliorer les performances. et ainsi il faut supprimer l'index de la clé étrangère.

L’index affecté à la clé primaire est bien sûr déterminant pour la performance si la table contient de centaines de milliers ou de millions de lignes et qu’il s’agit d’en récupérer une ou quelques unes par le biais d’une requête. Mais si la table ne contient que quelques dizaines de lignes, cet index est parfaitement inutile. Je répète que si c’est index est obligatoire, c’est parce que les éditeurs de SGBD ont jugé pratique de l’utiliser pour résoudre le problème de l’unicité des clés.

Quant à l’index défini pour la clé étrangère, vous pouvez le conserver, mais il ne servira à rien. Autant s’en débarrasser.

Citation:

Envoyé par soft_angel

mais il ya 2 clés étrangères : {poID} et {loID}. quelle clé doit-on supprimer? est-ce qu'il ya un critère pour choisir?

J’ai l’impression que vous mélangez les concepts. Les clés, qu’elles soient primaires ou étrangères, sont des concepts du niveau logique. Leur rôle est lié à l’intégrité des données, c'est-à-dire leur validité, leur cohérence.

Les index sont des fichiers, donc du niveau physique et leur rôle est fondamentalement d’assurer une bonne performance pour l’accès aux données.

Il est hors de question de supprimer une clé étrangère, car l’intégrité de la base données ne serait plus assurée. Par exemple, si l’on supprimait la clé étrangère {LotId} on pourrait trouver dans la table Lot_Projet une valeur de l’attribut LotId qu’on ne trouverait pas en tant que valeur de clé primaire dans la table référencée, à savoir Lot.

De même, si l’on supprimait la clé étrangère {PrId} on pourrait trouver dans la table Lot_Projet une valeur de l’attribut PrId qu’on ne trouverait pas en tant que valeur de clé primaire dans la table référencée, à savoir Projet.

Comme on l’a vu, la suppression l’index PROJET_FK ne pose aucun problème, puisque si problème il y avait, ça ne serait que la performance qui pourrait être affectée, or il n’en est rien, puisque l’index PROJET_PK suffit pour garantir cette performance.

Par contre, si l’on supprime l’index PROJET_FK2, l’intégrité de la clé étrangère {PrId} est toujours préservée, tandis que la performance de l’accès à une ligne de la table Lot_Projet en fonction d’une valeur de l’attribut PrId peut être sensiblement dégradée.
02/02/2009, 18h48
soft_angel

aaah j'ai compris maintenant. merci beaucoup pour votre aide :D
06/06/2011, 01h37
elbakai

Merci bien fsmrel pour votre explication, et Nicolimero pour le lancement du probleme,moi aussi j'ai trouvé le même problème avec la PowerAMC version 15.1 et la solution ça marche très bien
12/10/2011, 14h14
god4us

errer lors de la generation de la BD Power AMC 15.1

Bonjour ,

j'ai le meme problème dans un autre projet , et j'ai fait une comparaison en reproduisant cet eexple mais je constate le probleme:

Catégorie Vérifier Objet Emplacement
Package Unicité des noms de contrainte Colonne 'Lot.lot_id' <Modèle>::Lot
Package Unicité des noms de contrainte Colonne 'lot/proj.lot_id' <Modèle>::lot/proj
Colonne de table Divergence au niveau des contraintes de colonne de clé étrangère Colonne 'lot/proj.lot_id' <Modèle>::lot/proj
Index de table Inclusion de l'index Index 'lot/proj.LOT_PROJ_PK' inclut 'LOT_PROJ_FK' <Modèle>::lot/proj

Je note que j'ai creé 2 tables :
table projet(pr_id comme identifiant clé primaire)
table lot (lot_id comme cle primaire )

http://Bureau/erreur_generation-BD

Merci boucoup.
12/10/2011, 15h29
fsmrel

Bonjour,

Votre image ne s’affiche pas. Pour ne pas perdre de temps à conjecturer, le mieux serait de nous faire parvenir votre modèle.
25/11/2011, 22h45
SUHOKAWA

Merci beaucoup pour ces enrichissantes informations:ccool:
09/09/2014, 17h28
DaCoolG

Bonjour,

Désolé de relancer le sujet mais je ne comprends pas. Je fais bien comme indiqué plus haut en supprimant l'index d'une des clés étrangères dans ma table. Par contre cela supprime également la clé étrangère. C'est gênant non ?

J'utilise PowerAMC 15.1

Merci d'éclairer ma lanterne.

Cordialement

Bonjour DaCoolG,

C’est pour le moins étrange, il n’y a aucune raison pour que les choses se passent ainsi, à moins de la valeur prise par un paramètre subtil et violent dont nous ignorons l'existence...

Pour en avoir le coeur net, commencez par créer par exemple ce MCD :

http://www.fsmwarden.com/developpez_...roduit_mcd.png

Dérivez-le en MPD :

http://www.fsmwarden.com/developpez_...roduit_mpd.png

La situation des clés étrangères est la suivante :

http://www.fsmwarden.com/developpez_...t_fk_avant.png

En ce qui concerne les index :

http://www.fsmwarden.com/developpez_...ndex_avant.png

Après suppression de l’index ESTDETYPE_FK :

http://www.fsmwarden.com/developpez_...ndex_apres.png

Suite à cette suppression d’index, la situation concernant les clés étrangères ne doit pas avoir évolué (en tout cas c’est bien ce qui se passe chez moi) :

http://www.fsmwarden.com/developpez_...t_fk_apres.png

Ce que confirme le code SQL généré :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE TABLE TYPE_PRODUIT ( TypeProduitId INT NOT NULL, TypeProduitNom VARCHAR(64) NOT NULL, CONSTRAINT TYPE_PRODUIT_PK PRIMARY KEY (TypeProduitId) ) ; CREATE TABLE PRODUIT ( ProduitId INT NOT NULL, TypeProduitId INT NOT NULL, ProduitNom VARCHAR(64) NOT NULL, CONSTRAINT PRODUIT_PK PRIMARY KEY (ProduitId), CONSTRAINT PRODUIT_TYPE_PRODUIT_FK FOREIGN KEY (TypeProduitId) REFERENCES TYPE_PRODUIT ) ;
Que donne ce scénario chez vous ?

10/09/2014, 08h31
DaCoolG

Bonjour,

Merci de votre réponse.

Je viens de comprendre. La confusion vient du fait que lorsque je génère la base de données il ne me créé pas les clés étrangères pour aucune table. Il me créé bien les indexs, les clés primaires mais pas les clés étrangères dans mon fichier sql. J'ai modifié dans les options de génération le champ "création de clé étrangère" à interne mais ça ne change rien.

Je note aussi que dans les propriétés de ma référence j'ai une légère différence puisque pour le champ mise en œuvre vous êtes en "déclarative" alors que moi j'ai "trigger". Je ne connais pas la différence et si c'est pertinent.

Cordialement

Bonjour DaCoolG,

Citation:

Envoyé par DaCoolG

Je note aussi que dans les propriétés de ma référence j'ai une légère différence puisque pour le champ mise en œuvre vous êtes en "déclarative" alors que moi j'ai "trigger". Je ne connais pas la différence et si c'est pertinent.

Par définition, le choix de passer par un trigger fait que le contrôle de l’intégrité référentielle n’est pas contrôlé par une contrainte de clé étrangère au sein du CREATE TABLE (ou d’un ALTER TABLE), mais par des triggers, c'est-à-dire des procédures prenant automatiquement la main lors d’un insert dans la table fille (table PRODUIT dans mon exemple), et lors de la suppression d’une ligne dans la table parente ou de la modification de sa clé primaire (table TYPE_PRODUIT) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
CREATE TABLE PRODUIT 
(
   ProduitId            INT NOT NULL,
   TypeProduitId        INT NOT NULL,
   ProduitNom           VARCHAR(64) NOT NULL,
   CONSTRAINT PRODUIT_PK PRIMARY KEY (ProduitId)
) ;
 
create trigger CLR_TRIGGER_PRODUIT on PRODUIT  insert as
external name %Assembly.GeneratedName%.
 
 
create trigger TI_PRODUIT on PRODUIT for insert as
begin
    declare
       @numrows  int,
       @numnull  int,
       @errno    int,
       @errmsg   varchar(255)
 
    select  @numrows = @@rowcount
    if @numrows = 0
       return
 
    /*  Le parent "TYPE_PRODUIT" doit exister à la création d'un enfant dans "PRODUIT"  */
    if update(TypeProduitId)
    begin
       if (select count(*)
           from   TYPE_PRODUIT t1, inserted t2
           where  t1.TypeProduitId = t2.TypeProduitId) != @numrows
          begin
             select @errno  = 50002,
                    @errmsg = 'Le parent n''''existe pas dans "TYPE_PRODUIT". Impossible de créer un enfant dans "PRODUIT".'
             goto error
          end
    end
 
    return
 
/*  Traitement d'erreurs  */
error:
    raiserror @errno @errmsg
    rollback  transaction
end
 
 
create trigger TU_PRODUIT on PRODUIT for update as
begin
   declare
      @numrows  int,
      @numnull  int,
      @errno    int,
      @errmsg   varchar(255)
 
      select  @numrows = @@rowcount
      if @numrows = 0
         return
 
      /*  Le parent "TYPE_PRODUIT" doit exister à la mise à jour d'un enfant dans "PRODUIT"  */
      if update(TypeProduitId)
      begin
         if (select count(*)
             from   TYPE_PRODUIT t1, inserted t2
             where  t1.TypeProduitId = t2.TypeProduitId) != @numrows
            begin
               select @errno  = 50003,
                      @errmsg = 'TYPE_PRODUIT" n''''existe pas. Impossible de modifier l''''enfant dans "PRODUIT".'
               goto error
            end
      end
 
      return
 
/*  Traitement d'erreurs  */
error:
    raiserror @errno @errmsg
    rollback  transaction
end
 
create trigger CLR_TRIGGER_TYPE_PRODUIT on TYPE_PRODUIT  insert as
external name %Assembly.GeneratedName%.

Il est évident qu’il faut vous dépêcher de remplacer l’option « Trigger » par « Declarative » et faire en sorte que ce soit à l'avenir l’option par défaut :

Outils > Options du modèle > Référence > Mise en œuvre par défaut :

http://www.fsmwarden.com/developpez_...par_defaut.png