Les contraintes CHECK, comme les autres contraintes donnent des informations à l’optimiseur pour résoudre les plans d’exécutions. Cependant, du fait de la manière dont Oracle estime les cardinalités avec des fonctions cela peut poser des problèmes. Jonathan Lewis donne un exemple intéressant sur son blog. De ce fait, en fonction des versions et des cas d’utilisation, Oracle tire partie ou pas de ces contraintes…
Autremennt dit, la manière dont les prédicats associés aux contraintes CHECK sont poussés dans les opérations des plans d’exécution dépend d’un certain nombre de facteurs et notamment :
- Si la colonne testée implique directement la colonnes sur laquelle il y a une contrainte
CHECKou, par transitivité, une autre colonne - Si la condition de test est contradictoire avec la contrainte
- Si le propriétaire de la table incriminée est
SYSou un autre utilisateur
Cet article présente plusieurs exemples de plans mettant en oeuvre une contrainte CHECK pour accompagner vos réflexions. Et si vous n’êtes pas satisfait du comportement d’Oracle, vous pouvez toujours utiliser la Note [ID 271999.1] « Disable generation of predicates from CHECK constraints »
Comme vous le verrez ci-dessous, il est essentiel d’utiliser un autre utilisateur que
SYSdans vos exemples. Ces tests ont été réalisés avec une version 11.2.0.2.2 sous Linux 32 bits. Il est possible que vous ayez des comportements différents avec des versions différentes
Schéma exemple
Pour commencer, créez un schéma d’exemple spécifique :
create user demo
identified by demo
default tablespace users
temporary tablespace temp;
grant connect, resource, dba to demo;
connect demo/demo
drop table T1 purge;
create table t1 (id number,
id2 number,
lib varchar2(100));
insert into T1
(select 1, rownum, rpad(to_char(rownum),100)
from dual connect by level <=10000);
alter table T1 add constraint T1_IDIS1 check (id=1);
commit;
create index T1_ID2 on T1(ID2);
exec dbms_stats.gather_table_stats(user,'T1')
Exemple simple où la contrainte n’apparait pas
Nous allons commencer par un exemple simple. Comme vous le voyez ci-dessous, la contrainte T1_IDIS1 implique que la valeur de la colonne ID est toujours 1 ou NULL et pourtant, si vous exécutez une requête avec un prédicat plus large, la contrainte n’est pas prise en compte :
connect demo/demo
explain plan for
select *
from T1
where id>0;
select *
from table(
dbms_xplan.display(
format=>'basic +cost +predicate +note'));
PLAN_TABLE_OUTPUT
-----------------------------------------------
Plan hash value: 3617692013
-----------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------
| 0 | SELECT STATEMENT | | 68 (0)|
|* 1 | TABLE ACCESS FULL| T1 | 68 (0)|
-----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">0)
On pourrait s’attendre à avoir un filtre du genre "ID"=1 dans ce cas par exemple. Et bien non, puisque de toute façon toutes les valeurs (la contrainte est validée et immédiate) vérifient cette condition, Oracle choisit de ne pas pousser le prédicat. Ca ne changera pas le plan de toute façon…
Conditions contradictoires
En revanche si la condition de recherche est contradictoire avec la clause CHECK, Oracle saura en tirer partie ; en témoigne la condition NULL IS NOT NULL dans le plan ci-dessous qui ne sera même pas exécuté si nécessaire :
explain plan for
select *
from T1
where id=2;
select *
from table(
dbms_xplan.display(
format=>'basic +cost +predicate +note'));
PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 3332582666
------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
------------------------------------------------
| 0 | SELECT STATEMENT | | 0 (0)|
|* 1 | FILTER | | |
|* 2 | TABLE ACCESS FULL| T1 | 68 (0)|
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("ID"=2)
Propagation de la contrainte par transitivité
En revanche, si la contrainte peut être utile, par transitivité par exemple, Oracle sait en tirer partie, comme vous pouvez vous en rendre compte à l’étape 2 du plan suivant :
explain plan for
select *
from T1
where id=id2;
select *
from table(
dbms_xplan.display(
format=>'basic +cost +predicate +note'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
Plan hash value: 2649869177
-----------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | T1_ID2 | 1 (0)|
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"="ID2")
2 - access("ID2"=1)
Le schéma SYS n’obéit pas aux mêmes règles
Pour terminer, il est intéressant de noter que le schéma SYS n’obéit pas aux mêmes règles, en effet, si vous supprimez le schéma DEMO et reproduisez votre table dans SYS comme ci-dessous :
connect / as sysdba
drop user demo cascade;
drop table T1 purge;
create table t1 (id number,
id2 number,
lib varchar2(100));
insert into T1
(select 1, rownum, rpad(to_char(rownum),100) from dual connect by level <=10000);
alter table T1 add constraint T1_IDIS1 check (id=1);
commit;
create index T1_ID2 on T1(ID2);
exec dbms_stats.gather_table_stats(user,'T1')
Vous constaterez que la condition de la contrainte check n’est pas propagée par transitivité et que ce qui aurait pu être traité par un index rapidement effectue un accès FULL à la table créée :
explain plan for
select *
from T1
where id=id2;
select *
from table(
dbms_xplan.display(
format=>'basic +cost +predicate +note'));
PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 3617692013
-----------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------
| 0 | SELECT STATEMENT | | 44 (0)|
|* 1 | TABLE ACCESS FULL| T1 | 44 (0)|
------------- ----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"="ID2")
Remarquez que c’est également le cas en cas de conditions contradictoires :
explain plan for
select *
from T1
where id=2;
select *
from table(
dbms_xplan.display(
format=>'basic +cost +predicate +note'));
PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 3617692013
-----------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------
| 0 | SELECT STATEMENT | | 2 (0)|
|* 1 | TABLE ACCESS FULL| T1 | 2 (0)|
-----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=2)
Conclusion
Pour ce qui est du dernier exemple, il ne s’agit pas d’un bug, c’est bien voulu ! Ces petits tests montrent, une fois encore l’importance d’utiliser des contraintes. Il montre également que de réaliser des tests dans le schéma SYS peut être une mauvaise idée.
Pour terminer, supprimez la table exemple :
drop table T1 purge;
