Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Oracle Discussion :

Index : ordre des colonnes


Sujet :

Oracle

  1. #1
    Expert éminent
    Index : ordre des colonnes
    Bonjour,

    Je viens vers vous car depuis hier j'ai une question qui trotte dans ma tête...

    Chez un client, nous avons une base de données assez volumineuse, qui offre des performances très médiocres.

    La faute avant tout à la modélisation, mais c'est un modèle éditeur, donc faut se faire une raison : faut faire avec.

    En revanche, j'ai lu il y a quelques jours un article de SQLpro qui date un peu (à l'époque où 4 Go de RAM pour un serveur était le bout du monde) et orienté SQL Server, sur l'optimisation des bases de données, et notamment sur les index.

    Il dit que l'ordre des colonnes doit être du plus discriminant vers le moins discriminant.

    Hors, dans la base, les colonnes dans les index proposés par l'éditeur sont dans l'ordre... "fonctionnel".

    A savoir, par exemple pour la table des "tiers" :

    1/ Code société
    2/ Type de tiers
    3/ Code du tiers

    Selon le contexte, on a entre 1 et 100 code sociétés différents, une dizaine de type de tiers différentes, et quelques centaines de milliers de codes différentes.

    Pour un même numéro de société, on retrouve plus ou moins l'ensemble des types de tiers, et chaque type de tiers dans chaque société à sa numérotation propre, donc avec des doublons.

    Si j'écoute l'arcicle de SQLpro, je suis tenté de recréer l'article avec les colonnes suivantes :

    1/ Code du tiers
    2/ Code société
    3/ Type du tiers

    Est-ce que je vais vraiment y gagner quelque chose, ou si c'est vraiment qu'une optimisation valable (à l'époque) pour SQL Server ?

    J'ai d'autres tables où c'est plus gros encore :

    Colonne1 : 100 valeurs uniques
    Colonne2 : 10 valeurs uniques
    Colonne3 : Plusieurs centaines de millions (milliards ?) de valeurs uniques
    Colonne4 : 100 valeurs uniques
    Colonne5 : 1 unique valeur (fonctionnalité pas utilisée)

    => Est-ce que si je passe colonne3 en premier je peux m'attendre à un miracle ? Au moins une petite amélioration ?
    On ne jouit bien que de ce qu’on partage.

  2. #2
    Membre habitué
    Bonjour,

    Je n'avais pas entendu parler de cette règle du plus discriminant au moins discriminant, donc je veux bien moi aussi des avis d'expert

    Ceci dit, attention à l'ordre des colonnes dans les index: si vous remplacez votre index comme vous l'indiquez:
    1/ Code du tiers
    2/ Code société
    3/ Type du tiers

    Alors le résultat peut être catastrophique selon l'appli. En effet, si l'appli a besoin de faire disons un select code_societe from table WHERE type_du_tiers=123 alors le moteur sera incapable d'utiliser l'index. Pour qu'un tel index soit utilisable, il faut une requête qui se base sur:
    - seulement le Code du tiers, ou
    - le code du tiers ET le Code société, ou
    - le Code du tiers ET le Code société ET le type du tiers

    Bref, ca dépend vraiment comment l'appli est codée.

  3. #3
    Expert éminent
    L'index est l'index unique de la clé.
    Donc on fait systématiquement les trois.
    Ou alors tous sauf le code (tiens, faudra faire un second index en effet )

    Mais on ne requête jamais société et type indépendamment.
    On ne jouit bien que de ce qu’on partage.

  4. #4
    Expert éminent
    Bonjour,

    L'ordre des colonnes dépend de l'utilisation qui en est faite (donc les requêtes) et aucune règle arbitraire n'est bonne si elle ne tient pas compte des requêtes.
    L'idée, c'est que un index sur A,B,C set à des critères sur A, ou sur A,B, ou sur A,B,C.
    Il peut aussi servir pour B ou B,C dans le cas où il y a peu de valeurs distinctes sur A, grâce à l'index skip scan. C'est donc le contraire de la règle.

    Donc on fait systématiquement les trois.
    Ou alors tous sauf le code (tiens, faudra faire un second index en effet )
    Donc il faut mettre ce code à la fin et l'index sera utilisé dans tous les cas.

    Il y a autre chose à voir. Si cest colonnes sont des foreign key vers des tables qui peuvent avoir des delete, il faut avoir un index qui commence par la foreign key, pour des raisons de verrous. Mais ici je suppose qu'on ne supprime pas des code tiers ou des societes.

    Sinon, la meilleure ressource que je conaisse sur les index c'est: http://use-the-index-luke.com/fr/sql...dex-concatenes

    Cordialement,
    Franck.
    Franck Pachot - dbi services - Oracle ACE Director - OCM 12c - Oak Table member - twitter: @FranckPachot - blog: blog.pachot.net

  5. #5
    Expert éminent
    D'accord, donc soit j'ai mal compris l'article de SQLpro, soit il s'agissait d'une spécificité de SQL Server.

    Bon, fausse piste pour les mauvaises performances

    Sinon, la base n'a pas de relation entre les tables (pas de FK explicites). C'est à mon avis la raison principale des mauvaises performances, même si c'est loin de tout expliquer Mais bon, base éditeur, on peut rien changer à la structure sans risquer des bugs du programme...
    On ne jouit bien que de ce qu’on partage.

  6. #6
    Membre éprouvé
    Bonjour,

    Comme l’a si bien indiqué Franck, ce sont primordialement les clauses where de vos requêtes qui dictent le ‘design’ de vos indexes. Par exemple on s’efforcera à mettre les colonnes sur lesquelles une égalité est appliquée en première position car c’est grâce aux premières colonnes de l’index que la partie scannée (de cet index) par Oracle est déterminée (index range scan). Ensuite il faudrait contextualiser la création d’un index servant à couvrir une requête avec la possibilité que cet index soit capable de couvrir une autre requête : faire d’une pierre deux coups :
    Par exemple trois requêtes distinctes:

    Q1 : where clause (a,b,c)
    Q2 : where clause (b,c)
    Q3: where clause (c)

    En me limitant strictement à ce qui précède je créerai l’index suivant: Index (b,c,a)

    Qui aura l’avantage de couvrir Q1 et Q2. Et si b est souvent dupliquée je pourrai voir ma requête Q3 honorée par un index skip scan qui en passant n’est pas un accès très performant et trop souvent représente une indication d’un manque d’un index précis.

    https://community.oracle.com/message...15758#12615758

    Souvent aussi (comme signalé par Frank) lors de la création d’un indexe je pense à la possibilité (d’une pierre deux coups également) de couvrir la menace du lock et deadlock introduite par une foreign key non indexée lorsqu’on supprime des données de la table mère.

    Par exemple, si ma FK du modèle qui précède est FK(c,b) alors dans ce cas l’index que j’ai créé plus haut index (b,c,a) couvre aussi cette FK parce qu’il commence par les deux colonnes de la FK (même si ce n’est pas dans le bon ordre de la FK)

    http://www.toadworld.com/platforms/o...gy-part-i.aspx

    Enfin, j’arrive à votre question. Vous devriez déjà comprendre par cet ‘Enfin’ que la décision de placer les colonnes de l’indexe selon leur critères de discrimination (leur nombre de distinct values) arrive en dernière position (du point de vue Oracle au moins). Par exemple, lorsque mes autres critères me garantissent l’efficacité et la bonne utilisation d’un index (a,b,c) aussi bien que l’index (b,a,c) ou peut être aussi (c,b,a) alors uniquement dans ce cas, je commencerai à analyser la colonne la mieux adaptée pour être en première position. Et le critère le plus important pour moi dans ce cas concerne la compressibilité de l’index et la possible utilisation de l’index skip scan (quoique cet accès est moins performant et souvent à éviter). Si vous compressez votre index (a,b,c) vous avez beaucoup de chance d’avoir un taux de compression optimal si vous positionnez la colonne qui contient le moins de valeur distinctes (la plus souvent répétée) en première position.

    Conclusion

    Lors de la création d’un index il faudrait dans l’ordre selon moi:

    1. Essayer de couvrir le plus de requêtes possibles
    2. Mettre les colonnes sur lesquelles une égalité est appliquée en première(s) position(s)
    3. Pensez à la possibilité de couvrir en même temps la contrainte d’intégrité FK (au besoin)
    4. Enfin, pour une compression optimale pensez à mettre les colonnes les plus souvent répétées en première position
    Bien Respectueusement
    www.hourim.wordpress.com

    "Ce qui se conçoit bien s'énonce clairement"

  7. #7

  8. #8
    Rédacteur

    Citation Envoyé par Mohamed.Houri Voir le message
    ..faire d’une pierre deux coups ...
    Belle antistrophe rabelaisienne !
    Consultant / formateur Oracle indépendant
    Certifié OCP 12c, 11g, 10g ; sécurité 11g

    Ma dernière formation Oracle 19c publiée sur Linkedin : https://fr.linkedin.com/learning/oracle-19c-l-administration