VALIDATE vs NOVALIDATE

Une contrainte peut-être activée/non activée. Elle peut être validée/non validée. Elle peut bien d'autre chose mais arrêtons nous sur un exemple avec une contrainte NOT NULL...

• Activé (ENABLED) signifie que la contrainte est vérifiée lorsque vous exécutez des ordres DML
• Validé (VALIDATE) signifie qu'Oracle sait que la contrainte est vrai.

Par défaut quand vous créez une contrainte, elle est activée et validée... Mais pour plein de raisons vous pouvez vouloir activer une contrainte sur une table sans vérifier qu'elle est valide : soit parce que vous voulez vous passer de la vérification et de la charge associée soit parce que, par exemple, elle n'est pas valide...

Pour une raison que j'expliquerai dans un prochain Post, me voilà avec une contrainte que je sais valide mais qu'Oracle considère comme "NOT VALIDATED". Vous me direz, on s'en fout ! Non, pas exactement, je vais illustrer que dans certains cas, ça peut vous emmener à quelque chose que vous n'attendiez pas forcement quoiqu'à postériori tellement évident.

Etape 1 - Créer une table et un index pour notre exemple

create table gark
            (a number not null);

create unique index gark_uk
             on gark(a);

ACCEPT TABLE_NAME
GARK

select CONSTRAINT_NAME,
         CONSTRAINT_TYPE,
         STATUS,
         VALIDATED,
         RELY
  from user_constraints
  where table_name='&&TABLE_NAME';

CONSTRAINT_NAME C STATUS   VALIDATED     RELY
--------------- - -------- ------------- ----
SYS_C0010658    C ENABLED  VALIDATED

Etape 2 - Vérifier le plan de notre requête

explain plan for 
 select * from gark
   where a=1 or a is null;

select * from
  table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------
Plan hash value: 300359500

----------------------------------------------------
| Id  | Operation         | Name    | Rows  | Cost |
----------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    1 |
|*  1 |  INDEX UNIQUE SCAN| GARK_UK |     1 |    1 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("A"=1)

Remarquez que la requête utilise notre index !

Etape 3 - Invalider la contrainte NOT NULL en la laissant active

alter table gark
 modify constraint SYS_C0010658
   ENABLE NOVALIDATE;

select CONSTRAINT_NAME,
         CONSTRAINT_TYPE,
         STATUS,
         VALIDATED,
         RELY
  from user_constraints
  where table_name='&&TABLE_NAME';

CONSTRAINT_NAME C STATUS   VALIDATED     RELY
--------------- - -------- ------------- ----
SYS_C0010658    C ENABLED  NOT VALIDATED

Etape 4 - Regardez le plan de la même requête.

explain plan for 
 select * from gark
   where a=1 or a is null;

select * from
  table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------
Plan hash value: 2884726894

----------------------------------------------------
| Id  | Operation         | Name    | Rows  | Cost |
----------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    2 |
|*  1 |  TABLE ACCESS FULL|    GARK |     1 |    2 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("A" IS NULL OR "A"=1)

Bon le plan change et alors ? Je ne vous raconterai pas la suite, ça ne vaut pas la peine !

Avant de terminer...
Si vous pensez pouvoir forcer le plan avec la clause "RELY" de la contrainte (J'y ai cru), vous pouvez jeter un oeil sur cette erreur :

ORA-25127: RELY not allowed in NOT NULL constraint