Pourtant il s’agit bien ici d’une bonne pratique Oracle et je vais essayer d’en expliquer les raisons dans cet article.
Eviter les locks:
La première raison pour laquelle il est recommandé d’indexer les Foreign Keys est que lorsqu’on modifie une table « PARENT » (modification de la PK ou suppression d’une ligne) et qu’il n’existe pas d’index sur la FK de la table « ENFANT », alors Oracle effectue un lock de toute la table « ENFANT », ceci afin d’empêcher toute modification de cette table le temps de la mise à jour de la table « PARENT ».
La preuve en exemple :
(SESSION1)
SQL> create table t1 (c1 number primary key); Table created. SQL> create table t2 (c1 references t1 on delete cascade); Table created. SQL> insert into t1 values (1); 1 row created. SQL> insert into t1 values (2); 1 row created. SQL> insert into t1 values (3); 1 row created. SQL> commit; Commit complete. SQL> insert into t2 values (1); 1 row created. SQL> insert into t2 values (2); 1 row created. SQL> commit; Commit complete.
J’ai donc 3 lignes dans la table T1 et 2 dans la table T2.
Maintenant dans la même session, j’update une ligne dans la table T2 (la table « ENFANT ») sans commiter (ma transaction reste donc ouverte) :
(SESSION 1)
SQL> update t2 set c1=3 where c1=2; 1 row updated.
Dans une autre session je tente de supprimer une ligne dans la table « PARENT » T1 :
(SESSION 2)
SQL> delete t1 where c1=1;
La session est bloquée car comme je le disais précédemment Oracle tente de locker la table T2 avant d’effectuer le DELETE sur T1. Or la table T2 est déjà lockée au niveau ROW par ma première session.
On peut confirmer cela en regardant dans V$SESSION l’évènement d’attente pour la session 2 :
SQL> select sid,event, p2,blocking_session from v$session where status='ACTIVE' and program like '%sqlplus%';
SID EVENT P2 BLOCKING_SESSION
---------- ---------------------------------------------------------------- ---------- ----------------
135 enq: TM - contention 75248 143
143 SQL*Net message from client 1
SQL> select object_name from user_objects where object_id=75248;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------
T2
On a la confirmation que notre session 2 (sid 135) est bloquée par la session 1 (sid 143) et en attente sur l’évènement « enq: TM – contention ». La 2ème requête nous confirme que l’objet locké est bien la table T2.
En ajoutant un index à la Foreign key Oracle ne fera plus de lock au niveau table mais au niveau ligne uniquement, ce qui améliore nettement les accès concurrents.
Tout d’abord on effectue un rollback dans nos 2 sessions précédentes, puis on crée un index sur T2.C1 :
SQL> create index idx_t2_c1 on T2(C1); Index created.
On effectue ensuite le même test que précédemment.
(session 1)
SQL> update t2 set c1=3 where c1=2; 1 row updated.
(session 2)
SQL> delete t1 where c1=1; 1 row deleted.
Cette fois ci le DELETE sur T1 n’est plus bloquée par l’update de T2 de la session 1.
Améliorer les perfs des DELETE:
La 2ème raison pour laquelle il peut être préférable de créer un index sur une Foreign Key c’est lorsque la FK est définie en mode ON DELETE CASCADE. Ce mode indique que lorsqu’une PK est supprimée dans la table « PARENT » alors on laisse à Oracle le soin de supprimer en même temps les clés étrangères qui référencent les PK supprimées. Pour supprimer les lignes correspondantes dans la table « ENFANT », Oracle doit d’abord les localiser. S’il n’existe pas d’index pour la FK concernée alors Oracle est obligé d’effectuer un Full Table Scan de la table « ENFANT » pour chaque ligne supprimée dans la table « PARENT ». D’où l’intérêt d’avoir un index sur la FK.
Pour en avoir la preuve on peut tracer une commande DELETE et regarder ce que donnent les requêtes récursives impliquées par ce DELETE dans le cas où il n’existe pas d’index sur la clé étrangère :
SQL> drop index idx_t2_c1; Index dropped. SQL> ALTER SESSION SET events '10046 trace name context forever, level 12'; Session altered. SQL> delete t1 where c1=1; 1 row deleted. SQL> ALTER SESSION SET events '10046 trace name context off'; Session altered.
Un petit TKPROF sur la trace générée avec l’option SYS=YES (pour afficher les requêtes récursives) nous donne :
SQL ID: 7q7vx5nwhv6wu
Plan Hash: 897490302
delete t1
where
c1=1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.03 0 2 7 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.04 0 2 7 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE T1 (cr=9 pr=0 pw=0 time=0 us)
1 INDEX UNIQUE SCAN SYS_C0011644 (cr=1 pr=0 pw=0 time=0 us cost=0 size=3 card=1)(object id 75250)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 1.29 1.29
********************************************************************************
SQL ID: 04w09dv7av2y5
Plan Hash: 1451993194
delete from "TOTO"."T2"
where
"C1" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 2 0
Execute 1 0.01 0.01 0 7 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 7 3 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE T2 (cr=7 pr=0 pw=0 time=0 us)
1 TABLE ACCESS FULL T2 (cr=7 pr=0 pw=0 time=0 us cost=3 size=3 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
********************************************************************************
Le plan pour le DELETE utilise l’index unique de la PK, ce qui est logique.
Par contre on se rend compte que pour la requête récursive qui va d’abord supprimer la ligne dans la table « ENFANT » T2, un TABLE ACCESS FULL a été effectué.
Alors imaginons qu’on souhaite supprimer plusieurs lignes dans la table « PARENT » et que la table « ENFANT » est beaucoup plus grosse que notre table T2 alors ce FULL SCAN peut valoir très cher en termes de performances.
Maintenant si on crée un index sur la FK on se rend compte que la requête récursive utilise l’index :
SQL ID: 04w09dv7av2y5
Plan Hash: 1995814670
delete from "TOTO"."T2"
where
"C1" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 3 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE T2 (cr=1 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN IDX_T2_C1 (cr=1 pr=0 pw=0 time=0 us cost=1 size=3 card=1)(object id 75428)
Améliorer les perfs des requêtes impliquant la FK:
Dans la même logique il est très fréquent qu’au niveau applicatif on soit amené à effectuer des requêtes en filtrant sur la FK ou bien en joignant la table PARENT avec la table ENFANT. Dans ce cas un index est clairement le bienvenu :
SQL> explain plan for
2 select * from t1,t2 where t1.c1=t2.c1 and t1.c1=1;
Explained.
SQL> select * from table(dbms_xplan.display);
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T2"."C1"=1)
SQL> create index idx_t2_c1 on T2(C1);
Index created.
SQL> explain plan for
2 select * from t1,t2 where t1.c1=t2.c1 and t1.c1=1;
Explained.
SQL> select * from table(dbms_xplan.display);
Plan hash value: 43022878
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T2_C1 | 1 | 3 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."C1"=1)
Conclusion:
Les index sur les FK sont très utiles pour éviter les problèmes de lock (voire de Dead Lock), améliorer les performances sur les requêtes effectuant des DELETE sur les tables « PARENT » et sur les requêtes impliquant les FK.
Bien sûr si dans votre application vous ne supprimez jamais de lignes dans une table « PARENT », que vous ne modifiez jamais la clé primaire de cette table et que vous n’effectuez jamais de requêtes impliquant la FK de la table « ENFANT » alors "OUI" vous pouvez vous abstenir de créer un index pour la FK de la table « ENFANT ».
Explication très claire .
RépondreSupprimerBravo
Merci beaucoup pour votre article clair et concis .
RépondreSupprimer