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.
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 hayUPPERen el lado derecho) Cuando usamosUPPER('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 unTBSCAN. 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”).
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.
Nota
Las llamadas “estadísticas extendidas” sobre expresiones y grupos de columna se presentaron con la versión 11g de Oracle.
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, “Planes de ejecución”, 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.
