PrimaryKey et Index

Version imprimable

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:

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.

23/11/2016, 17h27
mnitu

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.
23/11/2016, 17h30
Tonii

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.
24/11/2016, 10h02
SQLpro

Citation:

Envoyé par Tonii

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 +
24/11/2016, 10h38
mnitu

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.

Citation:

Envoyé par mnitu

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:

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 +

24/11/2016, 18h58
mnitu

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