de Martin LE TARNEC.

Sin distinción entre mayúscula y minúscula usando UPPER o LOWER


Sin distinción entre mayúscula y minúscula

Ignorar la distinción entre mayúscula y minúscula en un filtro where es muy simple. Por ejemplo, se puede, convertir ambos lados de la comparación a mayúsculas:

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE UPPER(last_name) = UPPER('winand')

Sin tener en cuenta si se ha usado mayúscula o minúscula para la búsqueda o la columna LAST_NAME, la función UPPER las empareja como se desea.

Nota

Otra manera para emparejar es usar otra intercalación (en ingles “collation”). La intercalación por defecto usada por SQL Server y MySQL no distingue diferencias entre mayúscula y minúscula.

La lógica para la sentencia es perfectamente entendible pero el plan de ejecución no lo es:

DB2
Explain Plan
------------------------------------------------------
ID | Operation         |                   Rows | Cost
 1 | RETURN            |                        |  690
 2 |  TBSCAN EMPLOYEES | 400 of 10000 (  4.00%) |  690

Predicate Information
 2 - SARG ( UPPER(Q1.LAST_NAME) = 'WINAND')
Oracle
----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |   10 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |   10 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("LAST_NAME")='WINAND')
PostgreSQL
                     QUERY PLAN
------------------------------------------------------
 Seq Scan on employees
   (cost=0.00..1722.00 rows=50 width=17)
   Filter: (upper((last_name)::text) = 'WINAND'::text)

Esto es el regreso de nuestro viejo amigo FULL TABLE SCAN. Aunque existe un índice sobre la columna LAST_NAME, es inutilizable -porque la búsqueda no es sobre LAST_NAME sino sobre UPPER(LAST_NAME). Desde el punto de vista de la base de datos, es algo completamente diferente.

Es una trampa en la cual es fácil caer. Reconocemos al instante la relación entre LAST_NAME y UPPER(LAST_NAME) y esperemos que la base de datos “vea” lo mismo. En realidad, la visión del optimizador es más como esto otro:

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE BLACKBOX(...) = 'WINAND'

La función UPPER es solamente una caja negra. Los parámetros hacia la función no son pertinentes porque no existe una relación general entre los parámetros de las funciones y el resultado.

Sugerencia

Se puede reemplazar el nombre de la función por una CAJA NEGRA para entender el punto de vista del optimizador.

Evaluación en el momento de compilar

El optimizador puede evaluar la expresión que se encuentra en el lado derecho durante el momento de la compilación porque tiene todos los parámetros de entrada. El plan de ejecución de Oracle (“en la sección de los predicados”), por lo tanto, muestra la anotación UPPER sobre la expresión de búsqueda. Ese comportamiento es muy similar a una compilación evaluando las expresiones contantes en el momento de compilar.

Para soportar esta sentencia, se requiere un índice adicional para cubrir la expresión de la búsqueda actual. Eso significa que no se necesita un índice sobre LAST_NAME sino uno sobre UPPER(LAST_NAME):

CREATE INDEX emp_up_name 
    ON employees (UPPER(last_name))

Un índice cuya definición contiene funciones o expresiones se denomina índices sobre expresiones (del inglés “function-based index” FBI). En lugar de copiar los datos de las columnas directamente dentro del índice, un índices sobre expresiones aplica la función primero y pone el resultado dentro del índice. Como resultado, el índice almacena los apellidos en mayúscula.

La base de datos puede usar un índices sobre expresiones si la expresión exacta de la definición del índice aparece en la sentencia SQL, como en el ejemplo anterior. El plan de ejecución lo confirma:

DB2
Explain Plan
-------------------------------------------------------
ID | Operation            |                 Rows | Cost
 1 | RETURN               |                      |   13
 2 |  FETCH EMPLOYEES     |     1 of 1 (100.00%) |   13
 3 |   IXSCAN EMP_UP_NAME | 1 of 10000 (   .01%) |    6

Predicate Information
 3 - START ( UPPER(Q1.LAST_NAME) = 'WINAND')
      STOP ( UPPER(Q1.LAST_NAME) = 'WINAND')

La sentencia se cambió por WHERE UPPER(last_name) = 'WINAND' (no hay UPPER en el lado derecho) Cuando usamos UPPER('winand'), el optimizador utiliza una estimación incorrecta y espera seleccionar el 4% de los registros de la tabla. Eso causa que el optimizador ignore el índice y realice un TBSCAN. Ver FULL TABLE SCAN para ver por qué podría tener sentido.

Oracle
--------------------------------------------------------------
|Id |Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT            |             |  100 |   41 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |  100 |   41 |
|*2 |  INDEX RANGE SCAN          | EMP_UP_NAME |   40 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("LAST_NAME")='WINAND')
PostgreSQL
                       QUERY PLAN
------------------------------------------------------------
Bitmap Heap Scan on employees
  (cost=4.65..178.65 rows=50 width=17)
  Recheck Cond: (upper((last_name)::text) = 'WINAND'::text)
  -> Bitmap Index Scan on emp_up_name
     (cost=0.00..4.64 rows=50 width=0)
     Index Cond: (upper((last_name)::text) = 'WINAND'::text)

Es un INDEX RANGE SCAN normal como el descrito en el Capítulo 1. La base de datos atraviesa el índice B-tree y sigue la cadena de los nodos hoja. No existen operaciones dedicadas o palabras claves para los índices basados en funciones.

Aviso

A veces, las herramientas ORM utilizan UPPER y LOWER sin dar a conocer al desarrollador. Hibernate, por ejemplo, inyecta un LOWER implícito para las búsquedas sin distinciones entre mayúscula y minúscula.

El plan de ejecución ya no es el mismo como era en la sección anterior sin el UPPER; la estimación del número de registros es demasiada alta. Es particularmente extraño cuando el optimizador espera devolver más registros desde la tabla de los que un INDEX RANGE SCAN traería. ¿Cómo puede traer 100 registros desde una tabla si el escaneo del índice devuelve solamente 40 registros? La respuesta es que no puede ser. Contradecir las estimaciones indica generalmente un problema con las estadísticas. En este caso particular, es porque la base de datos Oracle no ha actualizado sus estadísticas cuando se ha creado el nuevo índice (ver también el siguiente apartado Estadísticas de Oracle para los índices basados en funciones).

Estadísticas de Oracle para los índices basados en funciones

La base de datos Oracle mantiene la información acerca del número de valores distintos como parte de la estadísticas de la tabla. Esa cifra es reutilizada si una columna es parte de varios índices.

También se conservan estadísticas para un índices sobre expresiones (FBI) a nivel de las tablas como columnas virtuales. Aunque la base de datos Oracle recolecta las estadísticas de los índices de manera automática para los nuevos índices (desde la versión 10g), este cambio no se propaga hasta las estadísticas de la tabla. Por este motivo, la documentación de Oracle recomienda volver a generar las estadísticas de las tablas después de crear un índices sobre expresiones:

Después de crear un índices sobre expresiones, recolectar sobre ambos, índice y su tabla referencia, usando el paquete DBMS_STATS. Esas estadísticas se habilitarán en la base de datos Oracle para decidir correctamente cuándo usar un índice.

Oracle Database SQL Language Reference

Mi consejo personal es el siguiente: después de cada cambio sobre un índice, actualiza las estadísticas de su tabla referencia y de todos sus índices. De todas maneras, eso podría, originar efectos no deseados. Por tanto, coordina esta actividad con los administradores de base (DBA) y genera un respaldo de las estadísticas originales.

Después de actualizar las estadísticas, el optimizador calcula estimaciones más certeras:

Oracle
--------------------------------------------------------------
|Id |Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT            |             |    1 |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |    1 |    3 |
|*2 |  INDEX RANGE SCAN          | EMP_UP_NAME |    1 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("LAST_NAME")='WINAND')
PostgreSQL
                      QUERY PLAN
----------------------------------------------------------
 Index Scan using emp_up_name on employees
   (cost=0.00..8.28 rows=1 width=17)
   Index Cond: (upper((last_name)::text) = 'WINAND'::text)

Como la estimación del número de registro ha bajado (desde 50 en el siguiente ejemplo hacia 1 en este plan de ejecución) el optimizador prefiere usar la operación sencilla Index Scan.

Aunque la actualización de las estadísticas no ha mejorado el rendimiento de ejecución en este caso, el índice se usó correctamente de todos modos, es siempre una buena idea validar las estimaciones del optimizador. El número de registros procesados por cada operación (estimación de la cardinalidad) es una cifra particularmente importante que está también en los planes de ejecuciones de SQL Server y PostgreSQL.

Sugerencia

Apéndice A, “Execution Plans, describe las estimaciones del número de registros en los planes de ejecuciones de SQL Server y PostgreSQL.

SQL Server y MySQL no soportan los índices basados en funciones como se ha descrito pero ambos dan la posibilidad de usar, en su lugar, las columnas virtuales. Para utilizarlas, se debe agregar primero una columna virtual a la tabla para poder indexarla más tarde:

MySQL

Desde MySQL 5.7 puedes indexar columnas generadas de la siguiente forma:

ALTER TABLE employees
  ADD COLUMN last_name_up VARCHAR(255) AS (UPPER(last_name));
CREATE INDEX emp_up_name ON employees (last_name_up);
SQL Server
ALTER TABLE employees ADD last_name_up AS UPPER(last_name)
CREATE INDEX emp_up_name ON employees (last_name_up)

SQL Server y MySQL son capaces de usar este índice aunque la expresión aparezca en la sentencia. En casos muy sencillos, SQL Server y MySQL pueden utilizar el índice aunque la sentencia no se haya cambiado. Sin embargo, a veces, la sentencia debe cambiarse con el fin de hacer referencia al nombre de la nueva columna para poder usar el índice. Verifica siempre el plan de ejecución en caso de tener dudas.

Si te gusta mi manera de explicar, te encantará mi libro.

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia