Browse Category: Sql

10gR2: Ignorar mayúsculas, minúsculas y acentos

Una de las cuestiones que los desarrolladores muchas veces me hacen es la siguiente:

“Cómo habría que hacer para que esta sucesión aparezca ordenada alfabéticamente?
  • A
  • b
  • C
  • d
  • FrontPage
El orden es “A, C, FrontPage, b, d” cuando el esperado sería “A, b, C, d, FrontPage””
 

Crearé la siguiente tabla, con sus respectivos datos para reproducir la cuestión que nos afecta:

En lo primero que nos vamos a fijar es en los valores de las variables NLS_SORT y NLS_COMP para mi sesión:

Como vemos, el valor de NLS_SORT es SPANISH, con lo cual aplicará la ordenación alfabética de España:

Ahora vamos a comprobar que si le asginamos el valor BINARY a NLS_SORT, hará una ordenación en base al valor binario de los caracteres (y por tanto la ordenación alfabética NO es la que nosotros esperamos):

En el ejemplo anterior hemos jugado simplemente con mayúsculas y minúsculas, pero podemos ver el comportamiento con los acentos:

Vemos como dependiendo del valor de la variable NLS_SORT vamos a ignorar o no tanto las mayúsculas, minúsculas y acentos para la ordenación de la tabla.

Expresiones Regulares (REGEXP)

A partir de la versión 10g, con la introducción de las llamadas funciones REGEXP (REGular EXPressions), Oracle nos ofrece una batería de sofisticadas y potentes herramientas nativas que permiten al desarrollador centralizar el procesamiento intensivo de cadenas de caracteres dentro de la base de datos Oracle y con los lenguajes SQL y PL/SQL.

¿Qué son las Expresiones Regulares?

Sin embargo, un buen manejo de las expresiones regulares es algo tan poderoso que vale la pena que nos tomemos un tiempo para aprender a utilizarlas. Además, saber manejar expresiones regulares nos servirá no solamente en el mundo de las bases de datos Oracle; las podremos usar también en otros lenguajes de programación como Perl, Java, .Net, Php y Unix Shell Scripting, entre otros.

Una expresión regular como una cadena de caracteres que definen un patrón de búsqueda. En una expresión regular encontramos literales y metacaracteres. Los literales se leen al pie de la letra. Los metacaracteres son caracteres que tienen un significado especial.

Tomemos por ejemplo la siguiente expresión regular:

[p|m]apa

Los metacaracteres son los corchetes y el símbolo pipe (|). El resto son literales.

Los corchetes agrupan a varios caracteres en un solo carácter.

El símbolo pipe indica un símbolo u otro; es decir la ‘p’ o la ‘m’.

Luego, la expresión regular:

[p|m]apa

Coincide con las cadenas:

papa
mapa

¿Para qué sirven las Expresiones Regulares?

En una base de datos Oracle existen diversos escenarios en que la implementación de expresiones regulares constituye una herramienta de gran utilidad:

  • Búsqueda de texto. Las expresiones regulares nos permiten hacer búsquedas complejas de cadenas de caracteres dentro de las columnas de nuestras tablas.
  • Formateo de datos. Podemos modificar los datos proyectados en las sentencias SELECT, invirtiendo palabras, agregando o quitando caracteres, etc.
  • Definición de constraints. A fin de forzar el contenido de una columna para que se ajuste a un formato determinado: casilla de correo, número telefónico, etc.
  • Manipulación de datos. Por ejemplo, regularizando datos en procesos de migración desde aplicaciones legacy y aplicando reglas de búsqueda y reemplazo (Search & Replace).
# Operador Descripción
1 () Considera a toda la expresión entre paréntesis como una unidad. La expresión puede ser una simple cadena de literales o una expresión compleja conteniendo otros metacaracteres.
2 […] Debe coincidir cada carácter de la lista entre corchetes. Ej. ma[pms]a encuentra (mapa, masa, mama) y no encuentra (mata)
3 [^…] No debe coincidir ningún caracter de la lista entre corchete. Ej. ma[^pms]a (mata, mala) y no encuentra (mapa)
4 […..] POSIX collation element.
5 [:…:] POSIX character class. Ver las dos siguientes tablas.
6 [=…=] POSIX character equivalence class. Ej. [=e=] representa e, é, è, ë, …
7 . El metacaracter punto coincide con cualquier carácter. Ej. ma.a (mapa,masa,mata,…)
8 ? Coincide con cero o una  ocurrencia de la subexpresión que le precede al asterisco. Ej. map?a (mapa, maa)
9 * Coincide con cero o más ocurrencias de la subexpresión que le precede al asterisco. Ej. map*a (mapa, mappa, mappppa, maa)
10 + El metacaracter suma coincide con una o más ocurrencias de la subexpresión que lo precede. Ej. ma+a (mapa, mapppa, mappppppa)
11 {n1} Empareja exactamente n1 ocurrencias de la subexpresión precedente. Ej. a{5} (aaaaa)
12 {n1,} Empareja al menos n1 ocurrencias de la subexpresión precedente. Ej. a{5,} (aaaaa, aaaaaa, aaaaaaa, …)
13 {n1,n2} Empareja entre n1 y n2 ocurrencias de la subexpresión precedente. Ej. a{3,5} (aaa, aaaa, aaaaa)
14 Precediendo a un metacaracter con el símbolo de escape, el metacaracter será interpretado como un literal. El doble carácter de escape (\) permite considerar al carácter de escape como literal. Ej. *hola* (*hola*) Ej. \hola (hola)
15 n Debe coincidir la n-ésima subexpresión que le precede.
16 | Logical OR. Ej. [v|b]aca (vaca, baca)
17 ^ Coincide con el principio de línea y el operador está representado con el carácter sombrerito (^). Ej. ^hola (hola, holamundo, …)
18 $ Coincide con el final de línea y el operador está representado con el carácter pesos ($). Ej. hola$ (hola, mundohola, …)

El item 5 son entradas para character classes, los cuales son independientes del character set y su descripción será mostrada en la tabla siguiente .

Character Class All Characters of type
[:alnum:] Representa un carácter alfanumérico. Incluye letras y números. Omite símbolos de puntuación.
[:alpha:] Incluye letras solamente.
[:blank:] Espacio en blanco.
[:cntrl:] Carácter de control.
[:digit:] Carácter Numérico.
[:graph:] Toda la combinación de character class [:punct:] , [:upper:], [:lower:], [:digit:]
[:lower:] Representa un carácter en minúscula.
[:print:] Representa un carácter que se puede imprimir.
[:punct:] Punctuation characters.
[:space:] Representa un espacio.
[:upper:] Representa un carácter en mayúscula.
[:xdigit:] Valid hexadecimal characters.

La siguiente tabla muestra Character Ranges.

Range All Characters of Type
[A-Z] All upercase alphabetic characters.
[a-z] All lowercase characters.
[0-9] All numeric digits.
[1-9] All numeric digits excluding zero.

Regular Expression Function

Para poder hacer uso de todo lo visto hasta ahora Oracle SQL incluye un grupo de nuevas  funciones y condiciones para poder manejar expresiones regulares a partir de la versión 10g :

Regular Expression Funciton Parámetros Descripción
REGEXP_SUBSTR (s1, pattern, p1, n1, m1)s1 – cadena de caracteresPattern – expresión regularp1 – numérico opcional (default 1)n1 – numérico opcional (default 1)m1 – 1 o más parámetros (ver tabla siguiente). Opcional Función para extraer de una cadena una subcadena de caracteres que coincidan con un patrón especificado en una expresión regular.
REGEXP_INSTR (s1, pattern, p1, n1, opt1, m1)s1 – cadena de caracteresPattern – expresión regularp1 – numérico opcional (default 1)n1 – numérico opcional (default 1)opt1 – numérico (0 o 1). Opcional (Default 0)m1 – 1 o más parámetros (ver tabla siguiente). Opcional Función que permite determinar la posición de inicio de un patrón específico en una cadena de caracteres.
REGEXP_REPLACE (s1, pattern, rep1, p1, o1, m1)s1 – cadena de caracteresPattern – expresión regularRep1 – cadena, opcional.(Default NULL)p1 – numérico opcional (default 1)o1 – numérico opcional (default 0)m1 – 1 o más parámetros (ver tabla siguiente). Opcional Función que permite hacer búsqueda y reemplazo en una cadena de caracteres utilizando expresiones regulares para la búsqueda.

La siguiente tabla muestra los valores posibles del parámetro m1 en las anteriores funciones:

Parameter Value Descripción
‘c’ utiliza una correspondencia sensible a mayúsculas/minúsculas (por defecto)
‘i’ utiliza una correspondencia no sensible a mayúsculas/minúsculas
‘n’ permite el operador de correspondencia con cualquier carácter
‘m’ trata la cadena de origen como varias líneas
‘x’ Ignora caracteres espacios en blanco

Junto con las regular expression functions tenemos una regular expression condition:

Regular Expression Condition Parámetros Descripción
REGEXP_LIKE (s1, pattern, m1)s1 – cadena de caracteresPattern – expresión regularm1 – 1 o más parámetros (ver tabla anterior). Opcional Condición que se puede utilizar en la cláusula WHERE de una sentencia SQL SELECT y que permite retornar aquellas filas que coinciden con el patrón especificado en una expresión regular.

Vamos a ver algunos ejemplos (pulsa sobre la imagen para verlo mejor):

reg_exp1

reg_exp2

Expresiones Regulares y CHECK Constraints

Se pueden utilizar expresiones regulares para filtrar los datos que se permiten entrar en un tabla mediante restricciones. El siguiente ejemplo muestra cómo podría ser configurado una columna para permitir sólo caracteres alfabéticos dentro de una columna VARCHAR2. Esto le denegará signos de puntuación, dígitos, elementos de separación, …

CREATE TABLE t1 (
c1 VARCHAR2(20), CHECK
(REGEXP_LIKE(c1, ‘^[[:alpha:]]+$’)));

INSERT INTO t1 VALUES (‘newuser’);
-> 1 row created.
INSERT INTO t1 VALUES (‘newuser1’);
-> ORA-02290: check constraint violated

Join Method II

CHOOSING THE RIGHT JOIN METHOD

En muchas ocasiones dejamos al optimizador que decida que join utilizar. Sin embargo, en muchas ocasiones, ya sea por la mala elección del optimizador o por nuestros conocimientos de los datos nos permite una mejor toma de decisiones.

SORT-MERGE/HASH VERSUS NESTED LOOPS

Sort-merge join y hash join pueden ser considerados de la misma “familia” de joins (proporcionan un buen rendimiento bajo condiciones similares, mientras que los nested loops se adaptan a una categoría diferente de queries. Por lo tanto cuando determines que tipo de join usar, hay que plantearse si un nested loop es apropiada.

La decisión entre hash/sort-merge y nested loop debería estar basada en:

  • Necesidad de rendimiento vs necesidad de tiempo de respuesta. Nested Loop habitualmente ofrece mejor tiempo de respuesta, pero hash/sort-merge pueden ofrecer mejor rendimiento.
  • La proporción de tablas en el join. Un mayor subconjunto de filas procesadas puede ser más rápido con hash/sort-merge.
  • Disponibilidad de índices para soportar el join. Un nested loop es más efectivo cuando un índice puede ser usado en las tablas del join.
  • Memoria y CPU disponible para ordenaciones. Grandes ordenaciones pueden consumir una cantidad excesiva de recursos y puede ralentizar la ejecución.
  • Hash join podría conseguir grandes beneficios en ejecuciones paralelas y operaciones orientadas a partición. Aunque nested loop y sort-merge también pueden ser usadas en paralelización.

Table 1 Determining the Optimal Join Method

WHEN JOINING A TO B (IN THAT ORDER)  CONSIDER SORT-MERGE OR HASH JOIN?  CONSIDER NESTED LOOPS USING AN INDEX ON B?
Both A and B are small. Yes. Maybe, depending on the selectivity of the index and the size of the outer table.
Only selecting a small subset of rows from B (and B has an index). No. Performing a table scan of B will be cost-inefficient. Yes. The index will reduce the number of IOs on B.
Want the first row as quickly as possible. No. The first row won’t bereturned until both A and B are scanned, sorted, and merged or until the hash table has been built. Yes. Rows can be returned as soon as they are fetched using the index.
Want to get all rows as quickly as possible. Maybe. Nested loops might still get all rows before sort-merge or hash join if other conditions apply. Maybe. Nested loops might still get all rows before sort merge if other conditions apply.
Doing a full table scan of A and want to use parallel query. Yes. Yes. Nested loops can be resolved in parallel if the outer (first) table in the join is retrieved via a full table scan.
Memory is limited, especially PGA memory. Maybe not. Large sorts can have significant overhead, especially if memory for sorts is limited. Hash joins can also require more memory than nested loops. Yes. The nested loops join avoids sorting and is, therefore, less affected by memory limitations.

 SORT-MERGE VERSUS HASH JOINS
Sort-merge está disponible desde versiones antiguas de Oracle, mientras que el hash join es más reciente. Hash join puede ser empleado en muchos, pero no todos los caso donde puede ser empleado el sort-merge. Cuando ambos son directamente comparados, el hash join tiende a un mejor rendimiento que un sort-merge join.

Sin embargo, sort-merge join tiene una más amplia aplicabilidad. Hash join puede ser ejecutado sólo cuando la condición join es una condición de igualdad, y los sort-merge pueden ser utilizados para resolver condiciones de no igualdad.

El sort-merge requiere más memoria y para ejecutar eficientemente y más CPU para durante las ordenaciones.

Aunque el hash join habitualmente tiene un rendimiento superior que el sort-merge, si los datos de entrada ya están ordenados, las operaciones de ordenación serán menos costosas y el sort-merge será más efectivo.

Referencias: Oracle Performance Survival Guide (Guy Harrison)

Notas: La Table 1 he preferido no traducirla, ya que los conceptos pueden ser más sencillos de entender.

Join Methods I

Esta es la primera de una serie de entradas en las que quiero hablar sobre los métodos Join de acceso a datos.  Independientemente de la categoría lógica de join, Oracle tiene tres tipos de algoritmos join: nested loops join, short-merge join y hash join. Cada join es óptimo en diferentes circunstancias.

NESTED LOOPS JOIN

Oracle realiza una búsqueda de la segunda tabla (o inner) por cada fila encontrada en la primera tabla (o outer). Sin un índice en la tabla inner, se necesitará escanear la inner table una vez por cada fila en la outer table. Esto puede provocar que nested table scan tenga un incremento exponencial en cuanto a sobrecarga y tiempo de ejecución dependiendo del crecimiento de la tabla.

nestedloop

El optimizador habitualmente seleccionará una operación nested loops sólo si hay un índice en la inner table. Si se debe acceder a la mayoría de los datos de la inner table un nested loop suele ser menos efectivo que un sort-merge o hash join.

SORT- MERGE JOIN

Cuando se ejecuta un merge-join, Oracle ordena cada tabla (o result set) por el valor de la columna join. Una vez ordenadas, los dos conjuntos de datos son fusionados.

sortmerge

En este tipo de join se suelen leer la mayoría de datos de las tablas o no hay disponible un acceso por índice en la inner table. El hash join (lo veremos más adelante) suele ser más efectivo en los casos donde pueden ser considerados ambos. Sin embargo hay casos donde podemos emplear sort-merge join y no hash join (non-equi join).

HASH JOIN

Cuando ejecutamos un hash join Oracle construye una hash table para una de las dos tablas comprendidas en el join. Esta hash table es utilizada para encontrar registros coincidentes en una forma algo similar a la utiliza en los nested loop con índices.

hashjoin

Como comentamos anteriormente el hash join es habitualmente más efectivo que sort-merge join en circunstancias en las que ambos son aplicables y pudiera ser más efectivo que nested loop donde una gran proporción de registros de la tabla son incluidos.

Referencias: Oracle Performance Survival Guide (Guy Harrison)