Optimización de consulta con LIKE

[ib3xn0]

Buenos días.
Quisiera preguntar como poder optimizar una consulta en la cual existen varios "Like" ya que es un formulario de búsqueda por palabra en varios campos. Comentar que la consulta es para una BBDD Oracle 11G.

Gracias anticipadas.

[gnzsoloyo]

Las consultas con LIKE por definición son ineficientes.
Pero sin ver la consulta en si, es difícil decirte qué te conviene hacer.

[ib3xn0]

aquí va:

Código MySQL:

SELECT 
numero,
fecha_inicio,
fecha_fin,
nombre,
apellido,
notas,
color
FROM tabla1
WHERE zona = 'EMEA' AND
fecha_inicio BETWEEN to_date('$FECHA_DESDE','DD/MM/YYYY') AND to_date('$FECHA_HASTA','DD/MM/YYYY')
AND(
UPPER ($nombre) LIKE UPPER ('%$palabra%') OR UPPER ($apellido) LIKE UPPER ('%$palabra%') OR UPPER ($color) LIKE UPPER ('%$palabra%')) 
ORDER BY fecha_inicio DESC
 

gracias!

[gnzsoloyo]

Hay varias consideraciones a tener en cuenta para mejorar lo que tienes.
1) Conviene un índice sobre el campo "zona", y otro sobre "fecha_inicio". Eso ayudará a mejorar la performance.

2) No conviene usar comodines en ambos lados de la palabra buscada. Eso destruye la performance porque en esas condiciones no se puede crear un índice sobre ese campo (que podría ayudar), ya que Oracle los descartaría de entrada. Eso sucederá porque al buscar todo lo que comience o termine con cualquier cosa, pero contenga algo en medio, Oracle se verá forzado a revisar todos los registros de la tabla.

3) Debes tener en cuenta también el factor humano: Muy, pero muy rara vez, una persona busca una cadena de texto contenida dentro de otra. En especial con palabras como esas, ya que las personas suelen recordar el inicio o final de las expresiones mucho mejor, y eso es lo que buscan.
Si quieren encontrar el color Azul Cerúleo, o Azul Ultramar, no buscarán "mar" o "erúle". Buscarán "Azul" y desde allí parten. ¿Se entiende?

4) Para realizar búsquedas de cadenas contenidas en otras, existen métodos mejores que la cláusula LIKE. Es el caso de los índices de clase FULL TEXT, que permiten mejores performances en esos casos.
Un link útil para eso: http://www.oracle-base.com/articles/...le-text-9i.php

En todo caso, ateniendonos a la primera opción (LIKEs restringidos), este sería el caso más abarcativo:

Código SQL:

SELECT   numero, fecha_inicio, fecha_fin, nombre, apellido, notas, color
    FROM tabla1
   WHERE zona = 'EMEA'
     AND fecha_inicio BETWEEN TO_DATE ('$FECHA_DESDE', 'DD/MM/YYYY')
                          AND TO_DATE ('$FECHA_HASTA', 'DD/MM/YYYY')
     AND (   UPPER ($nombre) LIKE UPPER ('$palabra%')
          OR UPPER ($nombre) LIKE UPPER ('%$palabra')
          OR UPPER ($apellido) LIKE UPPER ('$palabra%')
          OR UPPER ($apellido) LIKE UPPER ('%$palabra')
          OR UPPER ($color) LIKE UPPER ('$palabra%')
          OR UPPER ($color) LIKE UPPER ('%$palabra')
         )
ORDER BY fecha_inicio DESC
 

Por otro lado, te recomiendo que a nivel de aplicación le restrinjas a los usuarios la posibilidad de ingresar cualquier cosa, y que las palabras bsucadas tengan al menos cinco caracteres, ya que de lo contrario el nuvel de respuestas será elevado (y mal a la perfromance), por exceso de casos coincidentes.
¿A qué me refiero?
A que si no los obligas a ingresar una cantidad de caracteres mínimo, pueden querer buscar, por ejemplo, "todos los apellidos que contengan con 'AL'".
¿Te puedes imaginar la cantidad de resultados?

La decisión de restringir los datos para mejorar la performance, es una prerrogativa del que diseña el sistema. El cliente puede querer cualquier basura, pero uno como desarrollador debe proponerle y plantearle las cosas que son convenientes para él, aunque no lo sepa o no lo entienda.

[ib3xn0]

muchas gracias por la gran respuesta!
revisaré todo los consejos que me indicas,..GRACIAS NUEVAMENTE.