lunes, 27 de noviembre de 2017

Oracle 12c R2 - Column Level COLLATION

Para quienes estamos acostumbrados a trabajar con Oracle, nos resulta normal que la base de datos considere a los caracteres "a", "á", "A" y "Á" como distintos, ya que es conocido que Oracle por defecto es una base de datos donde los valores de tipo carácter son almacenados, comparados y ordenados en forma binaria, teniendo en cuenta el valor ASCII de cada uno de ellos.

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?




Collation es un nuevo atributo de las columnas de tipo caracter, el cual define como se interpretan las distintas variaciones (por mayúscula/minúscula, acentuación) de las letras del abecedario.

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