Jointure entre varchar et numeric

Bonjour,

Dans le cadre d'une migration de Oracle 10 vers Oracle 11, j'ai découvert que l'une des applications ciblée par la migration utilisait des jointures entre un champ Numeric et un champ de Varchar.

Dans une base en 10, la jointure se fait entre le champ numérique et les valeurs numérique du champ alphanumérique. Sous Oracle 11, la requête est en erreur.

Je n'ai pas la possibilité de modifier l'application, aussi je me demande s'il est possible de maintenir le comportement d'Oracle 10 avec Oracle 11 afin que la jointure continue a fonctionner le temps que l'appli soit remplacée.

Si vous avez la moindre info.

La jointure implique une conversion implicite : la colonne de type varchar est converti en numérique. Si cette colonne contient des valeurs non-numériques une erreur sera levée.
Dans votre cas soit les données entre les deux bases ne sont pas les mêmes soit le plan d’exécution a changé. Donc commencez par examiner et comparer les plans.
Mais je pense qu’il sera plus sage de nettoyer la table pour garder que les valeurs numériques. Est-ce que ces valeurs sont que des entiers ou bien aussi des réels ?

Suite à la migration de la bdd en 11 (par Datapump), j'ai vérifié à la main les tables/champs qui posent problème.

La colonne en Varchar comprends des données numériques et des données non-numérique.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Select DISTINCT ChpNUM from table1 1 5 9   Select DISTINCT ChpVarchar from table2 01 1 04 99 TR SR AS C4 etc..

---

La différence c'est le comportement en cas de jointure :
J'exécute cette requête sur les deux bases

Code:

---

1 2 3 4 5

select count(X) from d, p where X=1 and p.Y='toto' and d.X=p.X'

---

En Oracle 10 => 22
En Oracle 11 => ORA-01722: invalid number

Les données sont les mêmes.


Je ne peux pas toucher aux données de la base, les effets de bords sur l'application seraient innombrables.

Edit, un peu plus d'explications : On est bien d'accord qu'on est loin des "bests practices", mais je n'y peux pas grand chose, je n'ai pas écrit l'appli. Le fait est que cela fonctionne dans une base en 10, mais pas dans une base en 11.
Du coup je suppose, un peu naïvement, qu'un paramètre de la base en oracle 11 devrait pouvoir me permettre de conserver la flexibilité de la version précédente, mais jusqu'ici, je ne trouve rien.

La partie Predicate du plan d'exécution peut s’avérer ici (et toujours aussi) d'une grande utilité.

Pourriez-vous donc poster les deux plans d'exécution correspondant dans les deux bases de données. Il se peut qu'en 10g, les clauses where ont été appliquées dans l'ordre de leur apparition alors qu'en 11g le CBO a transformé cet ordre si bien que des to_number sont appliqués à des varchar2 alors qu'avant les filtres appliqués dans l'ordre éliminaient toutes les valeurs en varchar2 de la colonne de la jointure avant d'arriver à la jointure. Si c'est le cas, cela apparaîtra dans la partie Predicate.

En attendant vous pouvez essayer votre requête en 11g en altérant la session pour utiliser le CBO 10g

Code:

---

1 2	alter session set optimizer_features_enable='10.2.0.3'; -- mettre votre version

---

Et voir si cela évite l'erreur ou pas

Merci pour votre aide.

Concernant les plans d'exécution, cela dépasse mes compétences, je ferai un point avec mon dba la semaine prochaine pour explorer cette piste.


Par contre en modifiant la session, je retombe sur le comportement de la base en 10. Pas avec ma version actuelle (10.2.0.4) mais 9.2.0.8 et antérieures.
Cette base avait déjà été migrée de 8 à 10, c'est peut être l'explication de ce comportement.

Par contre dans ma base en version 10 :

Code:

---

1 2 3

Select Name, Value from v$parameter where NAME like 'optim%' optimizer_features_enable 10.2.0.4

---

Étrange.
Enfin ça laisse une solution alternative en attendant une revue de l'application.

Bonjour,

Citation:

---

Du coup je suppose, un peu naïvement, qu'un paramètre de la base en oracle 11 devrait pouvoir me permettre de conserver la flexibilité de la version précédente, mais jusqu'ici, je ne trouve rien.

---

Ce n'est pas une question de flexibilité, mais de l'ordre dans lequel les opérations sont exécutées:
Si les prédicats qui ne filtre que les nombres sont appliqués avant la jointure, alors pas d'erreur. Si la jointure est faite avant application de ces prédicats, alors erreur lorsqu'on on tombe sur un non-numérique.

C'est en fait un bug de l'appli qui, par hasard, ne se voyait pas avant.

La bonne solution c'est de modifier la requête en mettant le to_number dans un CASE WHEN predicats qui filtrent les nombres THEN TO_NUMBER(...) END

Sinon, le workaround c'est de retrouver le plan d'exécution d'avant en jouant sur les paramètres, les hints, des baselines, etc.

Cordialement,
Franck.