Les contraintes référentielles assurent la cohérence des données. Si le bénéfice est évident pour prévenir des erreurs des applications, les développeurs ont beau jeu d’expliquer, aujourd’hui, que ce n’est pas un problème pour eux ; la logique métier assure cette cohérence de manière bien plus riche encore…
Et pourtant, les plans d’exécution et donc les performances sont impactées par les contraintes, y compris les contraintes référentielles. Vous fournissez des informations à Oracle sur les relations qui existent entre les données, il en tire parti. Il en paie également le prix s’il doit valider cette cohérence…
Cet article illustre ces points à travers un exemple simple dans la perspective des systèmes transactionnels. Il montre également comment éviter qu’Oracle paie le prix de la validation avec la clause RELY DISABLE. Vous découvrirez enfin qu’un produit cartésien peut parfois avoir un coût de 0 ; étonnant ?
Dans le contexte des systèmes décisionnels, les perspectives sont très différentes. L’article précédent intitulé Mon petit Data Mart (Partie 4) : Clés étrangères et Query Rewrite discutait déjà de ce sujet avec un exemple mettant en oeuvre une vue matérialisée. Cela étant, la nature des développements est très différente. La logique objet et la modèlisation des systèmes transactionnels également.
Schéma exemple
Pour cette démonstration, vous allez créer 2 tables MASTER et DETAIL, à l’aide du script suivant :
drop table master purge;
drop table detail purge;
create table master(id number,
text varchar2(10),
constraint master_pk primary key(id));
create table detail(id number,
master number not null,
text varchar2(10),
constraint detail_pk primary key(id));
insert into master
(select rownum, rpad('X',10,'Y')
from dual
connect by level <=10);
insert into detail
(select rownum, mod(rownum,10)+1, rpad('X',10,'Y')
from dual
connect by level <=1000000);
commit;
exec dbms_stats.gather_table_stats(user,'detail');
exec dbms_stats.gather_table_stats(user,'master');
Une requête avec et sans contrainte
Pour vous persuader de l’intérêt de l’existence d’une contrainte, il suffit de trouver une requête simple dont l’information associée à la contrainte pourrait être utilisée à Oracle et de comparer les plans d’exécution associés. Voici qui parait évident que l’on commence à utiliser sans contrainte :
set autotrace on statistics
select count(detail.text)
from detail
where master not in (select id from master);
COUNT(DETAIL.TEXT)
------------------
0
Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
3321 consistent gets
0 physical reads
0 redo size
431 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autotrace off
explain plan for select count(detail.text)
from detail
where master not in (select id from master);
select * from table(dbms_xplan.display(format=>'basic +predicate'));
PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 3118352298
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | HASH JOIN RIGHT ANTI| |
| 3 | INDEX FULL SCAN | MASTER_PK |
| 4 | TABLE ACCESS FULL | DETAIL |
-------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MASTER"="ID")
Maintenant, nous allons créer une contrainte référentielle et, comme vous allez voir, ça n’arrive pas tous les jours, vous pouvez améliorer les performances de la requête de 100% et passant le nombre de buffer gets de 3321 à 0 (sic!)
alter table detail
add constraint detail_master
foreign key (master) references master(id);
set autotrace on statistics
select count(detail.text)
from detail
where master not in (select id from master);
COUNT(DETAIL.TEXT)
------------------
0
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
431 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autotrace off
explain plan for select count(detail.text)
from detail
where master not in (select id from master);
select * from table(dbms_xplan.display(format=>'basic +predicate'));
PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 2102150436
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | FILTER | |
| 3 | TABLE ACCESS FULL| DETAIL |
--------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NULL IS NOT NULL)
Au passage, vous remarquez que l’étape 3 du plan qui est un accès de toute la table n’est pas exécuté puisque les statistiques montre que 0 (zéro) blocs de données sont manipulés lors de l’exécution; c’est logique puisque le prédicat NULL IS NOT NULL est faux, j’imagine, la plupart du temps ;-).
RELY ou comment éviter le coût de la validation
Petit problème qui n’apparait pas forcément dans notre exemple précédent, maintenir une contrainte a un coût puisque chaque valeur dans la table de détail est « validée » sur la table maitre. Pour éviter ce problème, on peut désactiver la contrainte et malgré tout dire à Oracle de lui faire confiance. Il suffit pour cela d’utiliser la clause RELY sur la contrainte comme ci-dessous :
alter table detail drop constraint detail_master;
alter table master modify constraint master_pk rely;
alter table detail
add constraint detail_master
foreign key (master) references master(id)
rely disable;
Le plan utilise de nouveau le prédicat NULL IS NOT NULL. Pourtant, il se pourrait tout à fait qu’il existe une ligne pour laquelle la contrainte d’intégrité référentielle n’est pas valide et les insertion/update ne font pas l’objet de validation sur la table maitre :
set autotra ce on statistics
select count(detail.text)
from detail
where master not in (select id from master);
COUNT(DETAIL.TEXT)
------------------
0
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
431 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autotrace off
explain plan for select count(detail.text)
from detail
where master not in (select id from master);
select * from table(dbms_xplan.display(format=>'basic +predicate'));
PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 2102150436
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | FILTER | |
| 3 | TABLE ACCESS FULL| DETAIL |
--------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NULL IS NOT NULL)
Produit Cartésien & Coût de 0
Et le coût de la requête dans tout ça ? Une variante de l’exemple précédent qui introduit volontairement un produit cartésien est très instructif. Vous voyez, entre autre que :
- le coût d’une requête peut être plus faible que le coût de ses étapes élémentaires
- ou qu’une requête peut afficher un produit cartésien et être extrêmement optimisée puisqu’elle manipule 0 blocs malgré les 1,000,000 de lignes concernées
set autotrace on statistics
select count(detail.text)
from detail, master
where not exists (select 1 from master where id=master);
Statistics
----------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
431 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autotrace off
explain plan for select count(detail.text)
from detail, master
where not exists (select 1 from master where id=master);
select * from table(dbms_xplan.display(format=>'basic +predicate +cost'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------
Plan hash value: 1412041905
---------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 (0)|
| 1 | SORT AGGREGATE | | |
|* 2 | FILTER | | |
| 3 | MERGE JOIN CARTESIAN| | 9067 (1)|
| 4 | INDEX FULL SCAN | MASTER_PK | 1 (0)|
| 5 | BUFFER SORT | | 9066 (1)|
| 6 | TABLE ACCESS FULL | DETAIL | 907 (1)|
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NULL IS NOT NULL)
Conclusion
Evidemment cet exemple est grossier. Néanmoins, il met en évidence une fois encore que si vous arrivez à écrire des algorithmes de manipulation de données plus efficace qu’Oracle, c’est souvent parce que le moteur n’a accès à certaines informations dont vous vous disposez ; les contraintes d’intégrité à commencer par les contraintes NOT NULL mais aussi les contraintes référentielles sont de cette nature ; les distributions de données ou les corrélations entre tables et colonnes en font partie également. Avant d’en terminer, supprimez vos tables d’exemple :
drop table detail purge;
drop table master purge;
