Oracle busquedas like (fuzzy, contains, soundex)

Necesito hacer una busqueda por nombres, pero quiero que el sistema me diga si hay un nombre "parecido" en el sistema. Es decir, quiero buscar un cliente llamado Carlos Castañeda pero en la base de datos tengo varios similares, aunque ninguno igual:
Juan Carlos Castañeda
Carlos Castañeda Perez
Carlos Castaneda

La verdad estuve divagando bastante en el tema y no lograba encontrar el camino correcto.

Lo primero que busqué fue la funcion Soundex. Soundex es un algoritmo que hace equivalencias foneticas de 2 palabras. Por ejemplo ait con eight. Cada palabra tiene un valor soundex y si los valores son iguales, entonces, tengo un acierto.
select soundex('eigt'), soundex('eight') from dual
Ambos registros me devuelven E230 por lo que deben ser equivalentes.

Pero.... este algoritmo solo funciona en ingles. No para español.

Seguí buscando y encontre un blog con una implementacion plsql del soundex: http://oraclenotepad.blogspot.com/2008/03/soundex-en-espaol.html

Pero el soundex me compara solamente una palabra y yo quería comparar 2 nombres completos. Modifiqué un poco el codigo e hize una funcion que sacaba el soundex de cada palabra del nombre_a_buscar en el campo_nombre y si cumplía mas del N porciento entonces devolvía ok. Estaba bien, pero...

el performance se fue al suelo cuando quería buscar en una tabla con mas de medio millon de registros.

De dedique entonces a buscar la funcion CONTAINS de Oracle. Esta funcion me permite buscar en textos grandes. Separa las palabras de un texto y las indexa. Hay que crear un indice para utilizarla:

create index clientes_idx on temp (nombre) indextype;

ya ahi podía hacer consultas:
SELECT score(1),nombre FROM clientesWHERE CONTAINS (nombre, 'castañeda',1) > 0;

El contains devuelve un score con el que se evalúa el resultado. Mientras mas alto sea el resultado es mas exacta la busqueda.

Cuando busco con 2 nombres, este query me devuelve los resultados que tengan los 2 nombres, por ejemplo juan Carlos Castañeda o Carlos Castañeda Perez
SELECT score(1),nombre FROM clientesWHERE CONTAINS (nombre, 'carlos castañeda',1) > 0;

pero... no me devuelve nombres como Carlos Alberto Castañeda Perez.

agregué un OR a la consulta
SELECT score(1),nombre FROM clientesWHERE CONTAINS (nombre, 'carlos or castañeda',1) > 0;
pero... me devuelve todos los carlos y todos los castañedas. Carlos Perez, Carlos Lopez, Juan Castañeda, Carlos Castañeda, etc.

Cambié la consulta a un AND y mejoró
SELECT score(1),nombre FROM clientesWHERE CONTAINS (nombre, 'carlos or castañeda',1) > 0;

Pero me posicionaba de igual manera el Carlos Castañeda del Carlos Jose Castañeda y del Carlos Castañeda Perez.

Necesitaba diferenciar las busquedas exactas.

Busque entonces:
SELECT score(1),nombre FROM clientesWHERE CONTAINS (nombre, 'carlos or castañeda or (carlos and castaneda)',1) > 0;

El query se empezaba a complicar un poco. Además estaba buscando tambien que me diera busquedas Fuzzy, es decir, busquedas que me devolvieran palabras que contienen "casi" todos los caracteres de la palabra a buscar.

en ese caso debía buscar
SELECT score(1),nombre FROM clientesWHERE CONTAINS (nombre, '?carlos or ?castañeda',1) > 0;

Encontré también algo que no había utilizado que es el "query template" que me permitiría tener un query un poco más sencillo, con esto deje mi query así:










Lo que hace la plantilla es partir la cadena que envio en textquery como si fuesen varios tokens, y además va evaluando progresivamente cada una de las opciones que le envío. Es decir, primero busca con AND para ver si estan todas las palabras:
transform((TOKENS, "{", "}", "AND"))

Luego busco con AND pero con palabras "fuzzy" o parecidas, añadiendo el "?"
transform((TOKENS, "?{", "}", "AND"))

Si quisiera que hiciera un soundex pondria un "!" antes del token. Pero el soundex es en ingles y no me sirve.
transform((TOKENS, "!{", "}", "AND"))

Luego hago lo mismo pero con OR para que busque alguna de las 2 palabras.

pero...
también estuve leyendo que el query no estaba sincronizado por default, habría que sincronizarlo.

begin
  CTX_DDL.SYNC_INDEX('clientes_idx','50K');
end;
/

pero.. yo no quería sincronizarlo cada ves que ejecuto el query... así que cambie el indice para que se sincronice al hacer commit:

create index clientes_idx on temp (nombre) indextype is ctxsys.context parameters('sync (on commit)');

ahora sí, cuando  quise buscar CASTANEDA o JOSE en lugar de josé no me devolvia los mismo valores. Consideras las tildes como caracteres distintos.

Encontre que el indice hay que indicarle que utilice un LEXER_BASIC.

Para esto me conecte como sys y me di grants: grant execute on CTX_DDL to scott;

ya en mi esquema, creé una preferencia con el LEXER que necesito:
begin
  CTX_DDL.CREATE_PREFERENCE ('LEXER_SINTILDES', 'BASIC_LEXER');
  CTX_DDL.SET_ATTRIBUTE ('LEXER_SINTILDES', 'BASE_LETTER', 'YES');
  end;
  /

recreé el indice:

create index temp_nombre_idx on temp (nom_contacto) indextype is ctxsys.context parameters('sync (on commit) LEXER LEXER_SINTILDES');

Con eso ya logré los resultados que esperaba. mmm... no del todo. No encontré un soundex que me diera mejores resultados sin sacrificar el performance. Pero tengo buenos resultados.

saludos!

Algunos de los links que leí... solo algunos:

http://hugoracle.blogspot.com/2008/07/mejora-de-desempeo-con-oracle-text.html
http://www.orafaq.com/forum/t/95509/2/
http://www.oracle.com/technology/products/text/pdf/10gtext_features_overview.pdf
http://www.oracle.com/technology/products/text/htdocs/prog_relax.html?_template=/ocom/print