IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
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
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut 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é
    Homme Profil pro
    Inscrit en
    Février 2013
    Messages
    79
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Février 2013
    Messages : 79
    Points : 170
    Points
    170
    Par défaut
    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
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    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
    Avatar de pachot
    Homme Profil pro
    Developer Advocate YugabyteDB
    Inscrit en
    Novembre 2007
    Messages
    1 821
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : Suisse

    Informations professionnelles :
    Activité : Developer Advocate YugabyteDB
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2007
    Messages : 1 821
    Points : 6 443
    Points
    6 443
    Billets dans le blog
    1
    Par défaut
    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 - Developer Advocate Yugabyte 🚀 Base de Données distribuée, open source, compatible PostgreSQL
    🗣 twitter: @FranckPachot - 📝 blog: blog.pachot.net - 🎧 podcast en français : https://anchor.fm/franckpachot

  5. #5
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    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 expérimenté

    Homme Profil pro
    Inscrit en
    Mars 2010
    Messages
    536
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 536
    Points : 1 359
    Points
    1 359
    Par défaut
    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/12615758#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
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252

  8. #8
    Rédacteur

    Homme Profil pro
    Consultant / formateur Oracle et SQL Server
    Inscrit en
    Décembre 2002
    Messages
    3 460
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant / formateur Oracle et SQL Server

    Informations forums :
    Inscription : Décembre 2002
    Messages : 3 460
    Points : 8 073
    Points
    8 073
    Par défaut
    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

Discussions similaires

  1. [INDEX] - ordre des colonnes d'un index composite
    Par PpPool dans le forum Administration
    Réponses: 2
    Dernier message: 25/07/2011, 16h21
  2. Ordre des colonnes dans in Index
    Par dev-man dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 20/09/2007, 16h22
  3. Réponses: 1
    Dernier message: 02/05/2007, 16h16
  4. Ordre des Colonnes d'une requête
    Par dlh1222 dans le forum Access
    Réponses: 4
    Dernier message: 15/09/2005, 00h43
  5. Rajout colonne - changer l'ordre des colonnes ?
    Par Coptere dans le forum PostgreSQL
    Réponses: 3
    Dernier message: 13/09/2005, 11h56

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo