Bonjour,
Je suis plus familier de DB2 que de SQL Server, mais je soupçonne ce dernier d’avoir recopié son grand cousin, qui permet la mise en oeuvre des index "cluster" depuis déjà 25 ans. Dans ce qui suit, je me situe dans un contexte DB2.
Un tel index mérite une attention toute particulière, puisque, tant qu’on n’est pas dans un univers quantique, il ne peut y en avoir qu’un par table. Là réside le secret de la performance des traitements transactionnels lourds (si je puis dire) et surtout batch.
En effet, considérez la composition de la clé d’un tel index : {Col1, Col2, ..., Coln}. Appelons XC cet index. Considérez encore que je réorganise la table T sur laquelle est branché XC : DB2 range physiquement les lignes de la table T selon la séquence des clés de XC.
Illustration. Considérez maintenant les deux tables suivantes des commandes et des lignes de commande (clés primaires soulignées) :
Commande {CdeId, NumeroCde, Date, CLientId, ...}
LigneCommande {LigneId, CdeId, ProduitId, Quantité, ...}
CdeId étant clé étrangère au sein de LigneCommande.
Supposons encore, au niveau physique cette fois-ci, qu’il y ait un index XLCdeCde branché sur la colonne CdeId de la table LigneCommande et que cet index soit déclaré cluster :
Si au cours d’une transaction on récupère les données de la commande NumeroCde = 1234, y-compris les lignes de commande correspondantes, DB2 ira chercher directement ces lignes dans la page physique où ces lignes ont été regroupées. Coût de l’opération relativement aux lignes de commande : de l’ordre de deux lectures "physiques" pour l’index XLCdeCde et une seule lecture pour récupérer l’ensemble les lignes de commande (en admettant qu’elles tiennent toutes dans une page physique).
Au contraire, si l’index XLCdeCde n’est pas cluster, vous pouvez en théorie avoir autant de lectures physiques que de lignes de commande, puisque celles-ci peuvent être éparpillées dans des pages distinctes. A 10 millisecondes la lecture de page, cela peut finir par coûter cher.
Maintenant, vous pouvez avoir intérêt (et je vous engage vivement à procéder ainsi) à identifier LigneCommande relativement à Commande, car au fond, la ligne de commande n’est jamais qu’une propriété (multivaluée) de la commande. Le jeu de clés devient le suivant :
Commande {CdeId, NumeroCde, Date, TauxTVA, ...}
LigneCommande {CdeId, LigneId, ProduitId, Quantité, ...}
La clé primaire de LigneCommande est alors la suivante : {CdeId, LigneId} et devient la clé de l’index XLCdeCde (toujours cluster).
Vous me direz : quelle est la valeur ajoutée par rapport à l’organisation précédente ? Elle existe et, à titre d’exercice, je vous demande de la découvrir, quand par exemple chaque ligne de commande se décompose elle-même en engagements sur ligne de commande et que transactions et batch doivent engendrer un minimum de lectures à 10 millisecondes.
Ce qui vaut pour les lectures vaut bien entendu pour les écritures (création, modification, suppression de commandes).
Si quelqu’un connaît bien SQL Server, merci de confirmer que le comportement des index cluster est comparable à celui que je viens de décrire.
Partager