Index et fonctions : ORA-01450

Bonjour,

J'essaie de créer un index sous Oracle 10g portant sur des fonctions de ce type :

Code:

---

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

CREATE OR REPLACE FUNCTION fonction1 (param VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS retour VARCHAR2(32); BEGIN   IF INSTR(param, '-') <> 0 THEN retour := substr(param, 1,INSTR(param, '-')-1); ELSE retour := param; END IF;   RETURN lower(retour); END;

---

La seconde fonction utilisée dans mon index est très similaire.

J'ai une table TABLETEST ayant un champs1 et un champs2 de type VARCHAR2(32).

Si je crée un index avec une seule fonction, ça passe.

Lorsque j'essaie de créer un index sur cette table en utilisant deux fonctions, j'obtiens l'erreur suivante ORA-01450: longueur maximum de clé (6398) depassée.
De ce que j'ai compris et cherché sur le net, je suis en théorie très loin de cette longueur maximum.

J'ai essayé de remplacer le RETURN lower(retour) par RETURN SUBSTRB(lower(retour),1,32), ça ne change rien.

Voici le code utilisé pour la création de l'index :

Code:

---

1 2 3 4

CREATE INDEX IDX_TEST ON TABLETEST (fonction1(champs1),fonction2(champs2));

---

Quelqu'un aurait-il une idée? Quelque chose doit m'echapper...
Merci d'avance.

En aparté : ça m'étonne que vous ayiez créé une fonction pour du code aussi trivial, vous pouvez faire une ou plusieurs colonnes virtuelles et les indexer.

Sur le sujet : aucune idée ;)

Oui, deux colonnes virtuelles auraient fait l'affaire.
Malheureusement, il me semble que ça n'est disponible qu'a partir d'Oracle 11, or je travaille sur 10g.

Soit dit en passant, si je remplace par des CASE ... WHEN dans mon index, ça fonctionne. Seulement, ça oblige à utiliser le même CASE dans les requetes, et cette table est utilisée pour en charger beaucoup d'autres. Si la chaine à extraire change, j'aimerai n'avoir à changer que la fonction, pas toutes les requetes et l'index.

Vous pouvez envisager une vue peut-être.

Une vue materialisée? C'est une bonne idée, mais j'aurai préféré éviter, ou au moins comprendre pourquoi mon index ne veut pas passer.
Merci pour tes suggestions :D

Restrictions for Function-Based Indexes

Citation:

---

Function-based indexes cannot use expressions that return VARCHAR2 or RAW data types of unknown length from PL/SQL functions. A workaround is to limit the size of the function's output by indexing a substring of known length:

-- INITIALS() might return 1 letter, 2 letters, 3 letters, and so on.
-- We limit the return value to 10 characters for purposes of the index.
CREATE INDEX func_substr_index ON
emp_tab(substr(initials(ename),1,10);

-- Call SUBSTR both when creating the index and when referencing
-- the function in queries.
SELECT SUBSTR(initials(ename),1,10) FROM emp_tab;

---

Essaie donc comme ça:

Code:

---

1 2 3 4

CREATE INDEX IDX_TEST ON TABLETEST (SubStr(fonction1(champs1)1,32),SubStr(fonction2(champs2)1,32));

---

Bien vu Mnitu, ça passe bien comme ça :king:
J'avais pensé au SUBSTR dans le RETURN de la fonction, mais pas dans la définition de l'index, merci bien.

Par contre, mes requêtes n'ont pas l'air décidées à l'utiliser, mais ça c'est une autre histoire...
Merci encore pour ton aide :mrgreen: