Discussion :

[Snipah]

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Waldar]

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

[Snipah]

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.

[Waldar]

Vous pouvez envisager une vue peut-être.

[Snipah]

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

[mnitu]

Restrictions for Function-Based Indexes

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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