Discussion: PrimaryKey et Index

  1. #1
    Membre régulier
    Profil pro
    Inscrit en
    novembre 2009
    Messages
    143
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : novembre 2009
    Messages : 143
    Points : 77
    Points
    77

    Par défaut PrimaryKey et Index

    Bonjour tout le monde,

    Je me permets de vous écrire car je viens de reprendre une base de données Oracle et une chose m'interpelle.
    Sur pas mal de tables, il y a la création d'un index sur la primary key. Quel est l'intérêt puisqu'une PK est automatiquement un index unique ?

    Exemple :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
     
      CREATE TABLE "SCHEMA"."PROFIL" 
       (	"PRO_ID" VARCHAR2(6 BYTE) NOT NULL ENABLE, 
    	"PRO_LIB" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
    	"PRO_STATUT" CHAR(1 BYTE) NOT NULL ENABLE, 
    	"PRO_MODIF_LOGIN" VARCHAR2(120 BYTE), 
    	"PRO_MODIF_DATE" DATE, 
    	 CONSTRAINT "PROFIL_STATUT_CK" CHECK (PRO_STATUT IN ('0', '1')) ENABLE, 
    	 CONSTRAINT "PROFIL_PK" PRIMARY KEY ("PRO_ID")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "TBS_IDX_M"  ENABLE
       ) SEGMENT CREATION IMMEDIATE 
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "TBS_DATA_M" ;
     
      CREATE UNIQUE INDEX "SCHEMA"."IDX_PROFIL_PK" ON "SCHEMA"."PROFIL" ("PRO_ID") 
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "TBS_IDX_M" ;
    Sur certaines tables d'ailleurs le nom de l'index créé est le même que la PK...

    Quel est donc l'intérêt de créer l'index IDX_PROFIL_PK sur le champ PRO_ID alors que ce champ est une PK ?
    C'est pour une question de performance ?

    Merci d'avance à toutes et tous.

  2. #2
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    5 461
    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 461
    Points : 11 152
    Points
    11 152

    Par défaut

    D'une manière générale une contrainte de type primary key nécessite un index mais elle peut réutiliser un index existent y compris si celui ci n'est pas unique!

    Si le create table se contente de préciser juste la clé primaire alors Oracle va créer lui même un index unique qui prendra un nom automatique. Or dans pas mal des cas les DBA aiment définir des normes de nommages des divers entités d'Oracle et des contrôler divers paramètres au lieux de se baser sur les valeurs implicites, ce qui par ailleurs est une bonne pratique!
    De plus si je ne m'abuse pas les indexes crées automatiques sont supprimées automatiquement par Oracle lors des manipulations de contraintes de ce type comme la désactivation, ce qui parfois est fâcheux. En résume vous avez plus de contrôle de cette manière.

  3. #3
    Membre régulier
    Profil pro
    Inscrit en
    novembre 2009
    Messages
    143
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : novembre 2009
    Messages : 143
    Points : 77
    Points
    77

    Par défaut

    Merci mnitu !

    Donc c'est principalement pour une convention de nommage. Sauf que dans mon cas elle est mal faite.
    Et merci pour l'autre précision

    Bonne fin de journée.

  4. #4
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    16 643
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 16 643
    Points : 38 571
    Points
    38 571

    Par défaut

    Citation Envoyé par Tonii Voir le message
    Sur pas mal de tables, il y a la création d'un index sur la primary key. Quel est l'intérêt puisqu'une PK est automatiquement un index unique ?
    Non ! pas du tout...

    Vous confondez les notions logique (contrainte PK) et physique (index).
    Une base de données n'a pas besoin d'index pour fonctionner. La vérification de l'unicité d'une valeur lors de l'insertion ou de l'update peut se faire en bloquant la table et en parcourant séquentiellement toute les lignes à la recherche d'un doublon potentiel avant d'autoriser l'insertion.
    Néanmoins cette approche étant très pénalisante, tous les éditeurs de SGBDR ont réagit en proposant la création systématique d'index pour les contraintes PK et UNIQUE. Mais en vertu de l'indépendance entre couche logique (la contrainte) et couche physique (l'index) il y aura deux entrées différentes dans les tables systèmes, l'une signalant la PK et l'autre l'index.

    Certains SGBD sont allés plus loin en proposant de manière stupide et imbécile, de mettre systématiquement des index sous les contraintes FOREIGN KEY... (c'est le cas de MySQmerde...) Je vous invite à méditer la raison pour laquelle je prétend que cela est stupide !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  5. #5
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    5 461
    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 461
    Points : 11 152
    Points
    11 152

    Par défaut

    Je trouve toujours très valorisant de rappeler les différences entre la notion de contrainte et celle d'index pour lesquelles on prend souvent des raccourcis un peu trop simplificatrice ce qui contribue à l'augmentation de la confusion.

    En pratique il est drôle aussi de constater combien des anciens applications tournent encore en se basant juste sur un index unique au lieux d'une contrainte Primary Key; les contraintes existe chez Oracle depuis la version 6 or certaines applications datent d'avant cette version. En pratique leur utilisation a commencée à partir de la version 7.3 une fois que divers problèmes les concernant on été résolues et que les utilisateurs on commencé à comprendre leur bénéfices.

    Oracle ne crée pas d'index automatique pour les Foreign Key mais en pratique c'est presque systématique de les ajouter sous pénalisation des souvenirs douloureuses à l'exploitation de l'application. Comme d'habitude il y a toujours des exceptions assez bien identifiées que tout utilisateur averti prend en considération. Je suis donc intéressé par vos explications concernant "l'imbécilité" de l'approche.

  6. #6
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    16 643
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 16 643
    Points : 38 571
    Points
    38 571

    Par défaut

    Citation Envoyé par mnitu Voir le message
    Je suis donc intéressé par vos explications concernant "l'imbécilité" de l'approche.
    Il existe différents cas ou l'index de FK existe à l'état naturel et par conséquent serait redondant et d'autre ou la création de ce dernier serait inutile car inclus.

    L'exemple typique est l'héritage des données.
    Exemple, une classe de véhicule générique, comportant les sous catégories suivantes : bateau, avion. Voici le modèle physique :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    CREATE TABLE T_VEHICULE_VHC 
    (
    VHC_ID                   INT PRIMARY KEY,
    VHC_PUISSANCE            SMALLINT,
    VHC_IMMATRICULATION      VARCHAR(16));
     
    CREATE TABLE T_AVION_AVN
    (
    VHC_ID                   INT PRIMARY KEY 
                                 FOREIGN KEY 
    							 REFERENCES T_VEHICULE_VHC (VHC_ID),
    AVN_ALTITUDE_CROISIERE   INT);
     
    CREATE TABLE T_BATEAU_BTO
    (
    VHC_ID                   INT PRIMARY KEY 
                                 FOREIGN KEY 
    							 REFERENCES T_VEHICULE_VHC (VHC_ID),
    BTO_TIRANT_EAU           INT);
    Aucune des tables fille n'a besoin d'index pour les FK !

    Les autres cas classiques sont la table de jointure et la table ayant des liens identifiants. Ce qui conduit à des index inclus...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  7. #7
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    5 461
    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 461
    Points : 11 152
    Points
    11 152

    Par défaut

    Merci pour vos explications, je vois mieux de quoi vous parlez.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Message 'Duplicate index entry'
    Par Poulou dans le forum Administration
    Réponses: 13
    Dernier message: 04/05/2004, 14h57
  2. Index n'est pas a jour
    Par touhami dans le forum Paradox
    Réponses: 5
    Dernier message: 11/12/2002, 14h47
  3. [Technique] Intérêt des index
    Par ddams dans le forum Décisions SGBD
    Réponses: 10
    Dernier message: 04/11/2002, 15h11
  4. [Technique] Index, comment font les moteurs de recherche ?
    Par bat dans le forum Décisions SGBD
    Réponses: 4
    Dernier message: 25/10/2002, 15h41
  5. Requête imbriquée et indexes INTERBASE
    Par vadim dans le forum InterBase
    Réponses: 2
    Dernier message: 06/09/2002, 16h15

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