Cuando se usan los indices en una consulta con LIKE

Posted on por

Hoy vamos ha hacer una pequeña entrada de tunning.

Muchas veces vemos a la hora de hacer ajustes de rendimiento como las consultas que se ejecutan la base de datos tienen la clausula LIKE, pero su comportamiento es distinto segun como se ejecuta.
¿Cuando se hace un INDEX RANGE y cuando un FULL SCAN? , esto es principalmente lo que veremos hoy

Lo primero,¿ que es lo que hace la claúsula LIKE ?
La clausula LIKE mira si una cadena determinada coincide con un determinado patrón. Mediante el carácter % podemos hacer búsquedas genéricas con esa cadena .
Hemos de destacar que, LIKE contempla la búsqueda de longitud cero, es decir ,que la búsqueda de LIKE ‘PEPE% ‘ incluye tanto PEPESI como PEPE.

Una vez tenemos claro que hace la clausula LIKE, veamos de que manera podemos utilizarlo:

  • CADENA%
  • %CADENA
  • %CADENA%
  • CADENA1%CADENA2

Vamos a ver el comportamiento en cada una de las consultas. Tenemos una tabla llamada Imagen con un campo varchar NOMBRE 

SQL> desc imagen
 Name                 Null?    Type
 ------------------ -------- ---------------
 ID                  NOT NULL NUMBER
 NOMBRE                       VARCHAR2(256)
 RUTA                         VARCHAR2(256)

Sobre este campo NOMBRE hemos creado un y un UNIQUE INDEX llamado IMAGEN_NOMBRE_UNI, vamos ha hacer los 4 tipos de búsqueda y comparar los planes:

CADENA%

Si ejecutamos una búsqueda en la que miramos el final de la cadena, la base de datos intentará utilizar primero el índice 

set autotrace traceonly;
SQL> select * from  imagen where nombre like 'D80%';
|Id | Operation                  | Name           |Rows |Bytes| 
------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                  | 1 |143|3(0)|
| 1 |  TABLE ACCESS BY INDEX ROWID| IMAGEN           | 1 |143|3(0)|
|*2 |   INDEX RANGE SCAN          | IMAGEN_NOMBRE_UNI| 1 |   |2(0)|
-------------------------------------------------------------------

El optimizador conoce como comienza la cadena, con lo que llevará a cabo un INDEX RANGE SCAN para llevar a cabo la consulta con el menor coste posible.

%CADENA

EN este caso estamos buscando el final de la cadena entre el contenido de nuestra columna NOMBRE, si miramos el resultado del plan de ejecución

set autotrace traceonly;
SQL> select * from  imagen where nombre like '%009';
----------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes |
-------------------------------------------------
| 0| SELECT STATEMENT  |       |778 |108K| 93 (2)
|*1|  TABLE ACCESS FULL| IMAGEN|778 |108K| 93 (2)
-------------------------------------------------

El optimizador desconoce como comienza la cadena, con lo que llevará a cabo un TABLE ACCESS FULL de toda la tabla.En este caso no usa el índice ya que no le sirve de nada el indice si debe de comparar la parte final de la cadena

%CADENA%

Que ocurrirá si buscamos la parte central de una cadena?

set autotrace traceonly;
SQL> select * from  imagen where nombre like '%009%';
----------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes |
-------------------------------------------------
| 0| SELECT STATEMENT  |       |778 |108K| 93 (2)
|*1|  TABLE ACCESS FULL| IMAGEN|778 |108K| 93 (2)
-------------------------------------------------

El optimizador desconoce como comienza la cadena, con lo que llevará a cabo un TABLE ACCESS FULL de toda la tabla.
En este caso no usa el índice ya que no le sirve de nada el indice si debe de comparar la parte final de la cadena , es el mismo comportamiento que cuando lanzamos la consulta con %CADENA

CADENA1%CADENA2

Que ocurrirá si buscamos la parte central de una cadena?

set autotrace traceonly;
SQL>  select * from imagen where nombre like 'D80_10%G';

|Id | Operation                  | Name           |Rows |Bytes| 
------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                  | 1 |143|3(0)|
| 1 |  TABLE ACCESS BY INDEX ROWID| IMAGEN           | 1 |143|3(0)|
|*2 |   INDEX RANGE SCAN          | IMAGEN_NOMBRE_UNI| 1 |   |2(0)|
-------------------------------------------------------------------

Este caso es muy similar al de CADENA%, el optimizador si que conoce el comienzo de la cadena, con lo que llevará a cabo un INDEX RANGE SCAN para llevar a cabo la consulta en el menor.
Hay que tener en cuenta que, contra mas restrictiva sea la búsqueda mas rápido será y tendrá menos coste para la base de datos. es decir que, aunque en los dos casos use el índice, el coste de buscar CAD%CADENA2 será mayor que el de buscar CADENA1%CADENA2