Pero este comportamiento por defecto puede resultar molesto en situaciones donde debemos realizar búsquedas sobre información ingresada en forma manual, donde la misma puede tener diferencias en Mayúsculas/Minúsculas o en la acentuación. Por ejemplo, si buscamos clientes utilizando esta sintaxis
WHERE Apellido LIKE '%García%'
la sentencia no devolverá aquellos clientes que fueron ingresados en nuestra base de datos como "GARCIA", "Garcia" o "garcía". Este comportamiento puede observarse en operaciones lógicas (ya sea por igualdad, rango o similitud), en agregaciones (GROUP BY) y en el ordenamiento de los datos devueltos por la consulta (ORDER BY).
Es por ello que en muchos sistemas se optaba por alguno de estos dos enfoques al momento de manejar este tipo de información:
1) Se "normalizaba" la información ingresada por los usuarios, convirtiendo todo a mayúsculas o minúsculas, y reemplazando los acentos. De esta forma, los siguientes datos se almacenaban así:
Garcia --> GARCIA
García --> GARCIA
garcia --> GARCIA
2) Se mantenía la información en la base de datos como la ingresaba el usuario, pero se creaba un indice basado en funciones que indexaba por los valores convertidos a mayúsculas/minúsculas y sin acentos.
En ambos casos, al momento de realizar una búsqueda se requiere que el texto a buscar se encuentre en el mismo "formato" (mayúsculas o minúsculas, acentos eliminados) que como se ha almacenado en la tabla o en el índice basado en funciones.
¿Qué es "COLLATION" y para que sirve?
Existen dos tipos básicos de COLLATION:
- Binario: Las comparaciones y el ordenamiento de los datos se basan en el valor numérico de cada caracter de una cadena.
- Lingüístico: El ordenamiento y las comparaciones se basan en secuencias alfabéticas de los caracteres, independiente del valor numérico de los mismos.
Los distintos tipos de COLLATION linguisticos pueden consultarse en el sitio de Oracle. Las distintas COLLATION poseen tres sufijos que definen el comportamiento de las mismas al momento de realizar comparaciones y ordenamiento:
- "_CS": Sensible a acentos y mayúscula/minúscula. Es la opcion por defecto, y si no se especifica el sufijo se asume el mismo.
- "_CI": Case Insensitive, significa que no se tiene en cuenta diferencias entre mayúsculas y minúsculas, pero si a los acentos.
- "_AI": No tiene en cuenta mayúsculas y minúsculas ni acentos.
Si no se especifica una COLLATION en particular, Oracle utiliza un pseudo-valor "USING_NLS_COMP", el cual significa que se tienen en cuenta los valores de los parámetros "NLS_SORT" y "NLS_COMP" para determinar la collation a utilizar.
¿Como se define la COLLATION de una columna?
Al momento de crear una tabla, cada columna puede incluir la definición del COLLATION que se va a utilizar. Asimismo, se puede definir una COLLATION por defecto a nivel de tabla, esquema, sesión y base de datos. A continuación veremos algunos ejemplos:
A nivel Columna
CREATE TABLE t_Tabla1 (
ID_Tabla1 NUMBER,
Texto VARCHAR2(20 CHAR),
TextoBinarioCI VARCHAR2(20 CHAR) COLLATE BINARY_CI,
CONSTRAINT pk_Tabla1 PRIMARY KEY (ID_Tabla1)
);
Para probar como se comporta la columna "TextoBinarioCI", vamos a insertar algunos datos y ejecutar algunas consultas:
INSERT INTO t_Tabla1
SELECT 1, 'Opción', 'Opción' FROM DUAL UNION ALL
SELECT 2, 'Opcion', 'Opcion' FROM DUAL UNION ALL
SELECT 3, 'opción', 'opción' FROM DUAL UNION ALL
SELECT 4, 'opcion', 'opcion' FROM DUAL;
4 rows inserted.
SELECT * FROM t_Tabla1 t
WHERE t.Texto = 'opcion';
ID_TABLA1 TEXTO TEXTOBINARIOCI
---------- -------------------- --------------------
4 opcion opcion
SELECT * FROM t_Tabla1 t
WHERE t.TextoBinarioCI = 'opcion';
ID_TABLA1 TEXTO TEXTOBINARIOCI
---------- -------------------- --------------------
2 Opcion Opcion
4 opcion opcion
A nivel Tabla
Podemos crear una tabla con una COLLATION por defecto, o modificar una tabla existente. Hay que tener en cuenta que modificar la COLLATION por defecto de una tabla existente, no modifica las columnas existentes, sino que el nuevo valor se aplica a las columnas que se creen en la misma posteriormente.
ALTER TABLE t_Tabla1 DEFAULT COLLATION BINARY_AI;
Table T_TABLA1 altered.
ALTER TABLE t_Tabla1 ADD (
TextoBinarioAI VARCHAR2(20 CHAR) );
Table T_TABLA1 altered.
A continuación, actualizamos los datos existente en la tabla, repitiendo los valores de las dos columnas ya existentes en la nueva columna. Y luego realizamos una búsqueda similar a la anterior, pero sobre la nueva columna.
UPDATE t_Tabla1
SET TextoBinarioAI = CASE ID_Tabla1 WHEN 1 THEN 'Opción'
WHEN 2 THEN 'Opcion'
WHEN 3 THEN 'opción'
WHEN 4 THEN 'opcion'
END;
4 rows updated.
SELECT * FROM t_Tabla1 t
WHERE t.TextoBinarioAI = 'opcion';
ID_TABLA1 TEXTO TEXTOBINARIO TEXTOBINARIOAI
---------- -------------------- -------------------- --------------------
1 Opción Opción Opción
2 Opcion Opcion Opcion
3 opción opción opción
4 opcion opcion opcion
Podemos ver que al crear la nueva columna, como la tabla había sido configurada con la opción DEFAULT COLLATION BINARY_AI, la misma no distingue entre mayúsculas y minúsculas ni acentos al momento de realizar comparaciones.
A nivel Esquema
Para definir una COLLATION por defecto a nivel de esquema, se utiliza la clausula "DEFAULT COLLATION {COLLATION}" al momento de crear un usuario, o podemos modificarlo posteriormente:
CREATE USER usr1 IDENTIFIED BY pwd1
DEFAULT TABLESPACE users
DEFAULT COLLATION BINARY_CI;
ALTER USER usr2 DEFAULT COLLATION BINARY_AI;
Es importante recordar que estos cambios afectan a las columnas creadas con posterioridad, no modificando las columnas ya existentes.A nivel Sesión
La forma mas sencilla es utilizando una sentencia "ALTER SESSION SET DEFAULT_COLLATION", como se muestra a continuación:
ALTER SESSION SET DEFAULT_COLLATION=SPANISH_CI;
También es posible modificar la COLLATION con la que se crearan las nuevas columnas en forma implícita, al configurar los valores de "NLS_SORT" y "NLS_COMP" y luego crear columnas sin explicitar ninguna COLLATION (ni haber configurado una COLLLATION por defecto para la tabla, esquema o sesión).
ALTER SESSION SET NLS_SORT=SPANISH_CI;
ALTER SESSION SET NLS_COMP=LINGUISTIC;
CREATE TABLE t_Tabla2 (
Texto VARCHAR2(10)
);
Para ver la COLLATION con la que fue creada una columna, podemos utilizar la siguiente consulta:
SELECT column_id, column_name, collation
FROM user_tab_columns
WHERE table_name = 'T_TABLA2'
COLUMN_ID COLUMN_NAME COLLATION
---------- --------------------------- --------------------------------
1 TEXTO USING_NLS_COMP
A nivel Base de Datos
Para cambiar la COLLATION por defecto de toda una base de datos, se puede hacer el cambio a los parámetros "NLS_SORT" y "NLS_COMP" a nivel de la CDB, (y el mismo impacta esta base de datos y a todas las PDB conectadas a la misma) o en cada PDB.
ALTER SYSTEM SET NLS_SORT=BINARY_CI SCOPE=SPFILE;
ALTER SYSTEM SET NLS_COMP=LINGUISTIC SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
A nivel de Sentencia
Podemos utilizar la clausula "COLLATE {COLLATION}" al momento de hacer agrupaciones, ordenamientos o comparaciones, como podemos ver en los siguientes ejemplos:SELECT ID_Tabla1, Texto
FROM t_Tabla1
WHERE Texto COLLATE SPANISH_CI LIKE 'O%o%';
ID_TABLA1 TEXTO
---------- --------------------------------------------------
2 Opcion
4 opcion
SELECT ID_Tabla1, Texto
FROM t_Tabla1
WHERE Texto COLLATE SPANISH_AI LIKE 'O%o%';
ID_TABLA1 TEXTO
---------- --------------------------------------------------
1 Opción
2 Opcion
3 opción
4 opcion
SELECT TextoBinarioAI COLLATE SPANISH_CI, COUNT(*)
FROM t_Tabla1
GROUP BY TextoBinarioAI COLLATE SPANISH_CI;
TEXTOBINARIOAICOLLAT COUNT(*)
-------------------- ----------
Opcion 2
Opción 2
SELECT TextoBinarioAI COLLATE SPANISH_AI, COUNT(*)
FROM t_Tabla1
GROUP BY TextoBinarioAI COLLATE SPANISH_AI;
TEXTOBINARIOAICOLLAT COUNT(*)
-------------------- ----------
Opción 4
SELECT ID_Tabla1, TextoBinarioAI
FROM t_Tabla1
ORDER BY TextoBinarioAI;
ID_TABLA1 TEXTOBINARIOAI
---------- --------------------
1 Opción
4 opcion
3 opción
2 Opcion
SELECT ID_Tabla1, TextoBinarioAI
FROM t_Tabla1
ORDER BY TextoBinarioAI COLLATE BINARY;
ID_TABLA1 TEXTOBINARIOAI
---------- --------------------
2 Opcion
1 Opción
4 opcion
3 opción
No hay comentarios.:
Publicar un comentario