HINT - IGNORE_ROW_ON_DUPKEY_INDEX - ORA-38912

Hello;
Je vais parler aujourd'hui d'un hint qui même si il ne devrait pas être une finalité pourrait s'avérer bien pratique dans certains cas.

SQL>

SQL>

SQL> create table lamidba as select object_id,object_name,object_type from dba_objects where owner='LAO';

Table created.

SQL>

SQL>

On y ajoute une clé primaire.

SQL>

SQL> alter table lamidba add constraint pk_lami primary key (object_id,object_name);

Table altered.

SQL>

Je crée maintenant une nouvelle table sur mon schema LAO.

SQL>

SQL> create table newtable (i number);

Table created.

La nuit arrive, et le traitement sensé mettre à jour ma table lamidba s'execute.

SQL>

SQL> insert into lamidba select object_id,object_name,object_type from dba_objects where owner='LAO';

insert into lamidba select object_id,object_name,object_type from dba_objects where owner='LAO'

ERROR at line 1:

ORA-00001: unique constraint (LAO.PK_LAMI) violated

SQL>

Et patatra ! Dans la mesure ou il n'y a pas de critère, la requête essaye d'inserer des lignes qui sont déjà présente.. et notre clé primaire joue son rôle de proteger entre autre l'unicité des données.
Bien evidemment, l'exception levée a mis fin au traitement et ma table "NEWTABLE" n'a pas été inseré dans lamidba

SQL>

SQL> select count(*) from lamidba where object_name='NEWTABLE';

  COUNT(*)

----------

         0

SQL>

Le plus simple serait evidemment de repenser la logique et de s'assurer qu'on ne va pas inserer des valeurs en double. Dans notre cas, cela serait très simple, mais dans la vraie vie les traitement peuvent être bien plus compliqué et en attendant une mep applicative, on aimerait bie que notre traitement de nuit fonctionne. C'est ici que le hint Ignore_Row_On_Dupkey_index  va rentrer en action. Let's go !

SQL> insert /*+ ignore_row_on_dupkey_index(lamidba)*/ into lamidba select object_id,object_name,object_type from dba_objects where owner='LAO';

insert /*+ ignore_row_on_dupkey_index(lamidba)*/ into lamidba select object_id,object_name,object_type from dba_objects where owner='LAO'

                                                      

ERROR at line 1:

ORA-38912: An index must be specified in the index hint

SQL>

Plus de violation de contrainte, mais une autre erreur qui a le mérite d'être explicite. Il faut préciser dans le hint aussi bien le nom de la table ou comme pour tout hint l'alias utilisé dans la requête, que l'index. Soit, recommencons en précisant le nom de ma clé primaire.

SQL>  insert /*+ ignore_row_on_dupkey_index(lamidba,pk_lami)*/ into lamidba select object_id,object_name,object_type from dba_objects where owner='LAO';

3 rows created.

SQL> commit;

Commit complete.

Nous n'avons plus d'erreur et trois lignes ont été ajouté à ma table lamidba. Nous pouvons facilement vérifier que cela inclu ma table "NEWTABLE"

SQL>

SQL> select count(*) from lamidba where object_name='NEWTABLE';



  COUNT(*)

----------

         1

Pour infos les deux autres lignes correspondent à ma table lamidba elle même et sa clé primaire.

Remarque 1: Il faut evidemment privilégier la réécriture du bloc applicatif pour eviter l'utilisation du hint.

Remarque 2: Dans le cas d'une table complexe avec clé, primaire et plusieurs indexes uniques cela ne fonctionnera pas. On ne peut passer qu'un seul indexe dans le hint

Remarque 3: Dans le cas de fortes volumétries à traiter, il faut s'attendre à une dégradation des performances.
Enjoy.