Entendiendo a los conjuntos de caracteres

2 comentarios

Los conjuntos de caracteres, también llamados juegos de caracteres en castellano, y en inglés CHARACTER SET son todos los caracteres que pueden ser guardados en una columna de tipo CHAR o VARCHAR. Dentro de un conjunto de caracteres a cada carácter se le asigna una posición que será siempre la misma en ese conjunto de caracteres. Por ejemplo, en el conjunto de caracteres ASCII la letra A mayúscula siempre se encuentra en la posición 65.

El conjunto de caracteres base es el llamado ASCII (American Standard Code for Information Interchange, código estándar norteamericano para el intercambio de la información), pronunciado áski, está compuesto por todas las letras mayúsculas, y minúsculas del alfabeto inglés, todos los dígitos, y algunos caracteres especiales. Usa los últimos 7 bits de un byte y por lo tanto tiene 128 caracteres (ya que 2 elevado a la 7ª potencia es 128).

CHARACTER_SET_01

Captura 1. Si haces clic en la imagen la verás más grande

Este conjunto de caracteres es muy bueno … si todo el texto estará escrito en inglés moderno. Pero si quieres escribir texto en español, en portugués, en francés, en chino, en vietnamita, etc., no te servirá porque te faltarán caracteres, en el caso de escribir texto en español encontrarás que te faltan las vocales acentuadas, las letras u con diéresis y las letras eñe.

El mismo problema tendrás si quieres escribir texto en portugués, en francés, en alemán, en ruso, en chino, etc., no tendrás todos los caracteres que necesitas.

Por ese motivo se inventaron otros conjuntos de caracteres, hay un montón de ellos, para contemplar todos los casos posibles. Por ejemplo tenemos al BIG_5 que tiene los caracteres chinos, japoneses y coreanos, el CYRL que tiene los caracteres cirílicos (usados en el idioma ruso), el DOS737 que tiene los caracteres griegos, el DOS862 que tiene los caracteres hebreos, el DOS864 que tiene los caracteres árabes, etc.

Como vimos anteriormente, el conjunto de caracteres ASCII usa solamente los últimos 7 bits de cada byte. Como sobra el bit más significativo entonces se pueden codificar 128 caracteres más. Hay por supuesto muchísimas formas de realizar esa codificación, la más usada es la ISO8859, que tiene varias variantes.

ISO8859_1 tiene todos los caracteres que se necesitan en alemán, danés, español, finés, francés, holandés, inglés, islandés, italiano, noruego, portugués, sueco. Por ese motivo normalmente se lo llama “europeo occidental” o también “Latin 1”.

ISO8859_2 tiene todos los caracteres que se necesitan en checo, croata, eslovaco, esloveno, húngaro, polaco, rumano, serbio. Por ese motivo se lo llama “europeo central” o también “Latin 2”.

Hay muchos ISO8859 más, que sirven para introducir texto en muchísimos idiomas, pero no en todos. Los idiomas coreano, chino, japonés, etc., no pueden ser usados con ISO8859, ya que esos idiomas tienen más de 128 caracteres distintos.

Pero tener tantos conjuntos de caracteres era muy problemático cuando se quería intercambiar información entre computadoras ¿por qué? porque con los primeros 128 caracteres no había problemas pero con los demás sí. Por ejemplo, con un conjunto de caracteres el carácter ubicado en la posición 163 era la ú (una letra u minúscula acentuada), y con otro conjunto de caracteres en la posición 163 estaba la letra griega mu. Misma posición, distinto símbolo. Muy complicado.

Por ese motivo se decidió inventar un conjunto de caracteres universal, que sirviera siempre y para todos los casos.

A ese estándar universal se le llamó UNICODE.

Mediante UNICODE se puede escribir texto en cualquier idioma conocido, tanto moderno como antiguo, pero además puedes utilizar símbolos especiales matemáticos, notas musicales, flechas, iconos, etc. Eso es posible porque con UNICODE pueden usarse hasta 4 bytes. Si se usaran todos los bits para representar caracteres entonces podríamos tener 2 elevado a la 32, o sea 4.294.967.296 caracteres distintos pero no es así, algunos caracteres son de control, por lo que en realidad solamente se pueden codificar hasta 1.114.112 caracteres distintos, de todas maneras más que suficientes para todas nuestras necesidades actuales y futuras.

Algo que debes recordar es que UNICODE no está terminado, en cada nueva versión se le agregan más símbolos. Pero no te preocupes, ya tiene todos los caracteres usados en todos los lenguajes conocidos, lo que se le va agregando son símbolos. Por ejemplo el símbolo del euro se le agregó en el año 1998, antes de eso no lo tenía porque no podían adivinar que existiría una moneda llamada euro ni cual sería su símbolo.

Cuando en UNICODE se codifican los bits para representar caracteres, hay 3 formas usuales de hacerlo, todas ellas empiezan con las letras UTF (Unicode Transformation Format), y son: UTF-8, UTF-16, UTF-32.

Los números indican cuantos bits se usan en cada grupo de caracteres, no cuantos bits se guardarán.

¿Y qué tiene que ver todo esto con Firebird?

Que en Firebird todo el texto que se introduce en una columna de tipo CHAR o VARCHAR debe pertenecer sí o sí a un conjunto de caracteres.

Cuando creas una Base de Datos debes especificar cual conjunto de caracteres se usará por defecto en ella.

Si no lo especificas entonces se usará NONE. Lo cual es bueno si todo el texto estará escrito en inglés, pero si no es así entonces tendrás problemas cuando quieras usar la función UPPER(), entre otras cosas.

Ejemplo:

Se creó una Base de Datos y se especificó como conjunto de caracteres a NONE. Luego se creó una tabla llamada TEST con una columna llamada NOMBRE de tipo VARCHAR(40), se le insertaron dos filas a esa tabla, y luego se consultó esa tabla:

INSERT INTO TEST (NOMBRE) VALUES('aeiou AEIOU');
INSERT INTO TEST (NOMBRE) VALUES('áéíóú üÜ ñÑ');

SELECT UPPER(NOMBRE) FROM TEST

El resultado obtenido fue:

CHARACTER_SET_02

Captura 2. Si haces clic en la imagen la verás más grande

y como puedes ver, las letras del alfabeto inglés sí fueron convertidas a mayúsculas, pero las demás letras no.

Por lo tanto, lo que debemos hacer al crear una Base de Datos es especificar un conjunto de caracteres que contemple todas las letras que se pueden usar en español y que también nos permita ordenar correctamente, y convertir a mayúsculas.

Los dos conjuntos de caracteres más utilizados para lograr ese objetivo son ISO8859_1 y UTF8.

¿Y cuál de ellos es preferible usar?

Es mejor usar ISO8859_1 porque cada carácter siempre ocupa exactamente 1 byte. En cambio si usamos UTF8 cada carácter que no sea ASCII ocupará 2 bytes y estaremos desperdiciando mucho espacio en el disco duro. La excepción a esta regla es si sabes que necesitarás intercambiar datos con bases de datos que usen conjuntos de caracteres que no sean europeos occidentales, por ejemplo con algún ucraniano o algún ruso. Pero para la gran generalidad de los casos, tu mejor opción es usar ISO8859_1.

¿Se puede cambiar el conjunto de caracteres de una Base de Datos?

Supongamos que al crear tu Base de Datos especificaste a UTF8 como su conjunto de caracteres y luego quieres cambiarlo a ISO8859_1 porque es más conveniente ya que se ahorra espacio, ¿puedes hacerlo?

No directamente con el Firebird, pero sí con algunos programas utilitarios como el IBEScript.

¿Y por qué no se puede con Firebird?

Podrías pensar que sería conveniente tener una alternativa como:

ALTER DATABASE ALTER DEFAULT CHARACTER SET ISO8859_1;

pero tal cosa no existe. ¿Por qué no? Porque en tu Base de Datos podrías tener millones de filas que tienen columnas de tipo CHAR o VARCHAR cuyo conjunto de caracteres debería ser cambiado. Y eso tomará un montón de tiempo. Podrías pensar: “bueno, que deje a las columnas como están y que use ISO8859_1 para el nuevo texto”, pero eso provocaría inconsistencias. Por ejemplo, al crear la Base de Datos especificaste un conjunto de caracteres que te permite escribir en chino, luego cambiaste de idea y quieres escribir en español. ¿Y las columnas que ya tienen texto escrito en chino? No pueden ser borradas, ni traducidas por el Firebird ya que esa no es su tarea y no puede traducir de cada uno de los cientos de idiomas a cada uno de los otros cientos de idiomas.

Es por ese motivo que debes ser muy cuidadoso al elegir un conjunto de caracteres, ya que si eliges al equivocado luego será muy problemático cambiarlo. Hay programas utilitarios que te permiten realizar esa tarea pero en bases de datos muy grandes el tiempo que se demorarán se cuenta en muchas horas y quizás hasta en días. No es algo que les tomará un minuto o dos minutos terminar.

Sin embargo, en Firebird 3.0 si se tendrá la opción de cambiar el conjunto de caracteres por defecto. La sintaxis prevista es:

ALTER DATABASE
SET DEFAULT CHARACTER SET <nuevo_conjunto_de_caracteres>

Pero el autor de este blog aún no sabe en que condiciones se podrá realizar, ni cuanto tiempo se demorará la tarea de cambiar el conjunto de caracteres en bases de datos muy grandes.

Algo muy relacionado con el CHARACTER SET es lo que se llama COLLATE, que nos permite especificar como serán ordenados los caracteres. Para el lenguaje español es muy sencillo, siempre debemos elegir ES_ES.

Por lo tanto, al crear una Base de Datos que en sus columnas CHAR y VARCHAR podrá tener cualquiera de los caracteres del idioma español lo que debemos escribir es:

CREATE DATABASE 'MiBaseDatos.FDB'
DEFAULT CHARACTER SET ISO8859_1
COLLATION ES_ES;

Artículos relacionados:

Algo más sobre los conjuntos de caracteres

Funciones útiles con los conjuntos de caracteres

Entendiendo COLLATE

Consultando sin importar mayúsculas ni acentos

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Acelerando los SORT

1 comentario

Ya sabemos que en ocasiones el Firebird necesita ordenar las filas creando archivos temporales, a esa operación se la llama SORT (ordenar, en inglés) y puede ser lenta en tablas grandes. Entonces ¿qué podemos hacer para acelerar los SORT?

  1. Pedirle que los realice en un disco rápido
  2. Pedirle que los realice en un disco RAM
  3. Asegurándonos que el espacio libre en el disco temporario sea más grande que la Base de Datos
  4. Creando los archivos temporales en un disco distinto al de la Base de Datos

Los discos tienen distintas velocidades de lectura/escritura (y distintos precios también, claro). Los más rápidos suelen ser los SSD (Solid State Disk), aunque el problema es que son más caros que los discos duros magnéticos y también tienen una menor vida útil (aunque esto último está cambiando mucho, ya que cada vez duran más tiempo).

Como los SORT requieren crear archivos temporales, y muchos de esos archivos temporales pueden llegar a ser inmensos entonces debemos buscar por todos los medios acelerar el proceso.

Usar un disco RAM es una muy buena alternativa, aunque por supuesto eso requiere que la computadora tenga mucha memoria disponible. Para saber como crear un disco RAM y que programa usar para ello, puedes leer este artículo:

Usando un disco RAM para aumentar la velocidad

 ¿Y cuánto espacio libre debe tener el disco dónde se realizarán los SORT?

Evidentemente, cuanto más, mejor. Como no podemos saber de antemano cuanto será el espacio libre que necesitará el Firebird en general se toma como parámetro el tamaño de la Base de Datos. Es casi seguro que siempre necesitará menos que eso.

Entonces, si nuestra Base de Datos tiene un tamaño de 2 Gb, con tener 2 Gb en el disco donde se crearán los archivos temporales será suficiente. Pero no olvides que las bases de datos siempre crecen entonces hay que prever más, en ese caso un disco (duro o RAM) con 4 Gb sería muy bueno.

¿Y cómo se especifica dónde se crearán los archivos temporales?

En la carpeta donde instalaste el Firebird encontrarás un archivo cuyo nombre es FIREBIRD.CONF, dentro de ese archivo hay una entrada llamada “TempDirectories”. Debes borrar el símbolo de numeral que está a la izquierda y escribir los nombres de las carpetas donde se crearán los archivos temporales. Si usarás más de una carpeta deberás separarlas con punto y coma, por ejemplo:

TempDirectories=G:\TEMP
TempDirectories=G:\TEMP;F:\FIREBIRDTEMPS

¿Qué significan esas entradas?

La primera, que todos los archivos temporales se crearán en la carpeta G:\TEMP

La segunda, que los archivos temporales se crearán en G:\TEMP y que si alguna vez no hay espacio suficiente entonces se los continuará creando en F:\FIREBIRDTEMPS

Es decir, la segunda carpeta solamente se usa si la primera carpeta se quedó sin espacio.

¿Cómo se deben especificar los nombres de las carpetas en TempDirectories?

Como el Firebird primero usará la primera carpeta, y si se queda sin espacio allí usará la segunda carpeta, entonces en primer lugar deberías escribir el nombre de la carpeta que se encuentra en el disco más rápido. Si tienes un disco RAM entonces esa primera carpeta evidentemente será la del disco RAM.

En el ejemplo de arriba si el disco G: es un disco RAM y el disco F: es un disco SSD, entonces estará perfecto, porque primero especificamos el disco más rápido.

Desde luego que puedes tener más de dos carpetas, aunque en este ejemplo se mostraron 2 carpetas tú podrías tener 3, 4, 5, las que necesites. Solamente recuerda que siempre los discos más rápidos deben ser escritos primero.

¿Por qué los discos donde se encuentran las bases de datos y los archivos temporales debe ser distinto?

Si una sola persona está usando la Base de Datos, no hay problema, porque o está creando archivos temporales o no los está creando. En ambos casos estará trabajando a la máxima velocidad posible. Pero lo normal es que a una Base de Datos estén conectadas varias personas al mismo tiempo, cada una de ellas realizando alguna operación y ahí sí el tema se complica y mucho.

¿Por qué se complica?

Tenemos dos posibilidades:

  1. Están en un disco duro magnético
  2. Están en un disco no magnético (por ejemplo, un disco SSD)

Si están en un disco duro magnético, entonces hay un cabezal que debe moverse. Ese es un movimiento mecánico y por lo tanto siempre será lento. Si un usuario hace un SORT el cabezal debe moverse a un sector del disco duro. Otro usuario hace un INSERT, el cabezal debe moverse a otro sector del disco duro. Otro usuario hace un INSERT a otra tabla y nuevamente el cabezal debe moverse. Si los usuarios están ejecutando las operaciones de INSERT, UPDATE, DELETE, SELECT, FETCH, entonces los sectores estarán contiguos o muy cerca unos de otros, porque todos ocurren dentro de la misma Base de Datos. Pero si un usuario está haciendo un SORT esos sectores estarán muy alejados de la Base de Datos y ese constante ir y venir del cabezal hará que tanto las operaciones en la Base de Datos como el SORT sean mucho más lentos de lo que deberían. Si has leído algo sobre el diseño de Sistemas Operativos recordarás que usan semáforos: le dan unos milisegundos a un proceso para que haga lo que quiere hacer, luego otros milisegundos a un segundo proceso, luego otros milisegundos a un tercer proceso, y así sucesivamente hasta regresar al primer proceso. Para los usuarios esto es transparente, creen que cada uno de sus procesos se está ejecutando simultáneamente con los demás procesos pero eso no es así (bueno, salvo que la computadora tenga varios procesadores, claro). De todas maneras, sea la computadora multi-procesador o no, el cabezal del disco duro tendrá un constante ir y venir entre los sectores usados en el SORT y los sectores usados para las otras operaciones. Eso es lento y siempre será lento, podrá aumentarse la velocidad con discos duros más veloces, pero siempre será lento.

Si están en un disco no magnético (por ejemplo, un disco SSD), entonces no hay un cabezal que se mueve, en realidad nada se mueve, pero los discos tienen una vida útil limitada que aunque va aumentando porque la tecnología mejora, siempre tiene un límite. Entonces, si además de realizar las operaciones normales (INSERT, UPDATE, DELETE, SELECT, FETCH) también le pedimos que use a ese mismo disco SSD para los SORT, los backups y los restores, la vida útil de nuestro disco SSD será mucho más corta de lo que debería ser.

Por lo tanto la conclusión es muy sencilla: la Base de Datos debe encontrarse en un disco y los archivos temporales en un disco distinto.

Ese disco debe ser físicamente distinto, no sirve que esté particionado. Si está particionado se trata del mismo disco aunque para el Sistema Operativo sean dos discos diferentes, la realidad es que sigue siendo un mismo disco físico, y eso es justamente lo que no queremos.

Artículos relacionados:

Entendiendo el contenido de un PLAN

Usando un disco RAM para aumentar la velocidad

El índice del blog Firebird21

El foro del blog Firebird21

 

Entendiendo el contenido de un PLAN

6 comentarios

En Firebird todos los SELECTs que ejecutemos (manualmente o dentro de una vista) sí o sí usarán un PLAN. Ese PLAN puede ser elegido por el Firebird o por nosotros mismos. En general el Firebird hace un excelente trabajo en hallar el PLAN más adecuado pero en ocasiones no es así y allí nosotros podemos especificar uno mejor.

NOTA: Estos conocimientos no son necesarios para quienes son principiantes en Firebird, tales personas lo mejor que pueden hacer es dejarle al Firebird que especifique el PLAN a ser usado. Pero para quienes tienen un nivel más avanzado sí es importante, porque en algunos casos podrán mejorar drásticamente la velocidad de sus SELECTs.

Para entender mejor lo que es un PLAN puedes leer estos artículos:

Usando un PLAN

Algo más sobre PLAN

 En un PLAN lo que se especifica son los índices que serán usados. Las posibilidades son:

  • No hay un índice y no se usa un índice. Verás la palabra NATURAL.
  • No hay un índice y se necesita usar un índice. Verás la palabra SORT.
  • Hay un índice y no se usa ese índice. Verás la palabra NATURAL.
  • Hay un índice y se usa ese índice. Verás la palabra INDEX.

Si el SELECT tiene un JOIN (o más de uno, claro) entonces las posibilidades son:

  • No se necesita ordenar los conjuntos de resultados usados. Verás la palabra JOIN
  • Se necesita ordenar los conjuntos de resultados usados. Verás la palabra MERGE o más comúnmente las palabras SORT MERGE

Si se quiere mostrar las filas ordenadas:

  • Si hay un índice verás la palabra ORDER
  • Si no hay un índice verás la palabra SORT

El significado de NATURAL

Si ves esta palabra en el PLAN significa que no existe un índice o que sí existe pero no se lo utiliza. Si no se lo utiliza en general es por la pobre selectividad del mismo. Para saber más sobre la selectividad puedes leer estos artículos:

Selectividad de los índices

Recreando índices y calculando estadísticas

NATURAL significa que las filas son extraídas con el mismo orden en el cual fueron guardadas. Esto implica que todas las páginas deben ser leídas antes de que pueda ejecutarse el filtro (la condición que hayas puesto en la cláusula WHERE). Eso puede ser muy lento en tablas grandes.

Sin embargo, NATURAL no siempre es malo, si no te interesa mostrar a las filas con algún orden en particular entonces NATURAL es lo correcto porque es más rápido que usar un índice. Si se usa un índice primero se busca la clave en el índice y luego se extrae la fila correspondiente, si se usa NATURAL se ahorra el primer paso y por lo tanto es más rápido; la diferencia es muy notoria en tablas muy grandes.

El significado de SORT

La palabra SORT significa “ordenar” en inglés y eso es lo que hace el Firebird. Ordena el conjunto de resultados creando archivos temporales. Este proceso puede ser muy lento en tablas grandes porque crear esos archivos temporales puede demorar mucho tiempo. Si encuentras la palabra SORT eso es muy malo para el SELECT. Pero cuidado con esto, que sea malo para el SELECT no implica que necesariamente será malo para la Base de Datos. ¿Por qué? porque para evitar el SORT tendríamos que crear un índice, pero el mantenimiento de los índices es costoso, ya que cada vez que se hace un INSERT, un UPDATE, o un DELETE el índice debe ser modificado, eso toma tiempo y en tablas que constantemente están cambiando le provocarán un sobrecosto sustancial. Por lo tanto, la regla es crear un índice solamente si se lo usará frecuentemente. Por ejemplo, si tu SELECT se ejecutará una sola vez al año (o muy pocas veces en el año) y las filas involucradas se cuentan por millones entonces probablemente lo mejor sea no tener un índice y que el Firebird haga el SORT cuando lo necesite. Es cierto que el SELECT será lento, pero los INSERT, UPDATE, y DELETE serán más rápidos y como las filas se cuentan por millones entonces valdrá la pena no crear un índice.

El significado de INDEX

Esta palabra significa que existe un índice y que se lo utiliza.

El significado de JOIN

La palabra JOIN significa “juntar” en inglés. Si la ves en un PLAN significa que no se requiere ordenar ambos conjuntos de resultados porque ya se encuentran en el orden correcto.

El significado de MERGE o SORT MERGE

La palabra MERGE significa “combinar” o “mezclar” en inglés. MERGE y SORT MERGE hacen exactamente lo mismo, pero se suele escribir SORT MERGE para que quede más claro lo que hacen.

Lo que hacen es lo siguiente: un SORT al primer conjunto de resultados (como vimos más arriba), un SORT al segundo conjunto de resultados (como vimos más arriba) y luego combinar ambos conjuntos de resultados, ya ordenados.

Fíjate que hay dos SORT y una combinación de resultados. Cuidado con eso, algo así puede ser lentísimo en tablas grandes.

Un SORT MERGE es lo peor que puedes tener en un PLAN.

El significado de ORDER

La palabra ORDER significa “ordenar” en inglés, aunque en este caso la podríamos traducir “ordenar por el índice” y a continuación el nombre del índice que usa.

Analizando el PLAN

Para que todo esto quede más claro a continuación veremos varios ejemplos de SELECT con sus respectivos planes de ejecución; y explicaremos cada uno de ellos.

Ejemplo 1:

SELECT
   *
FROM
   BANCOS

PLAN (BANCOS NATURAL)

¿Qué significa este PLAN? que como no le dijimos en cual orden queremos ver a los Bancos entonces los mostró en el mismo orden conque los guardó en la tabla. Eso está muy bien, es lo correcto, porque es lo más rápido.

Ejemplo 2:

SELECT
   *
FROM
   BANCOS
ORDER BY
   BAN_CODSUC,
   BAN_IDENTI

PLAN (BANCOS ORDER PK_BANCOS)

¿Qué significa este PLAN? que como le dijimos que queremos ver a las filas ordenadas por Código de la Sucursal y por Identificador y tenemos un índice con esas columnas entonces usó a ese índice, cuyo nombre es PK_BANCOS.

Esto es más lento que el Ejemplo 1., pero si es lo que necesitamos mostrar entonces está ok.

Ejemplo 3:

SELECT
   *
FROM
   BANCOS
ORDER BY
   BAN_NOMBRE

PLAN SORT ((BANCOS NATURAL))

¿Qué significa este PLAN? que la tabla de BANCOS no estaba ordenada por la columna BAN_NOMBRE (por eso la palabra NATURAL) y que el Firebird tuvo que ordenarla (por eso la palabra SORT) antes de mostrar las filas. Ese ordenamiento crea archivos temporarios (en la memoria caché o en el disco duro) y puede ser muy lento en tablas muy grandes.

Ejemplo 4:

SELECT
   *
FROM
   BANCOS
WHERE
   BAN_CODSUC = 0 AND
   BAN_IDENTI > 10

PLAN (BANCOS INDEX (PK_BANCOS))

¿Qué significa este PLAN? que la tabla BANCOS tiene un índice según las columnas BAN_CODSUC y BAN_IDENTI (que son las usadas para filtrar con la cláusula WHERE) y que el Firebird usó ese índice.

En los WHERE hay siempre que tratar de usar índices porque eso aumenta muchísimo la velocidad.

Ejemplo 5:

SELECT
   *
FROM
   BANCOS
WHERE
   BAN_NOMBRE LIKE 'BAN%'

PLAN (BANCOS NATURAL)

¿Qué significa este PLAN? que la tabla BANCOS no tiene un índice según la columna BAN_NOMBRE y que como no le pedimos que muestre las filas con algún orden en particular las mostró como fueron guardadas.

Ejemplo 6:

SELECT
   *
FROM
   BANCOS
WHERE
   BAN_NOMBRE LIKE 'BAN%'
ORDER BY
   BAN_NOMBRE

PLAN SORT ((BANCOS NATURAL))

¿Qué significa este PLAN? que la tabla BANCOS no tiene un orden según la columna BAN_NOMBRE pero como se necesita mostrar a las filas ordenadas según esa columna entonces se hizo un SORT (es decir, se creó un archivo temporal para poder mostrar a las filas ordenadas).

No te olvides que hacer un SORT es más lento que usar un índice, a veces muchísimo más lento, porque las filas deben ser ordenadas y guardadas en un archivo temporal antes de ser mostradas y eso toma su tiempo.

Ejemplo 7:

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO

PLAN JOIN (B NATURAL, S INDEX (UQ_SUCURSALES))

¿Qué significa este PLAN? que la tabla S tiene un índice llamado UQ_SUCURSALES según su columna SUC_CODIGO, la tabla BANCOS no tiene un índice sobre su columna BAN_CODSUC y que se hará el JOIN sin ordenar las filas de la tabla B, así como están serán mostradas.

Ejemplo 8:

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO
ORDER BY
   B.BAN_NOMBRE

PLAN SORT (JOIN (B NATURAL, S INDEX (UQ_SUCURSALES)))

¿Qué significa este PLAN? que la tabla S tiene un índice llamado UQ_SUCURSALES según su columna SUC_CODIGO, la tabla BANCOS no tiene un índice sobre su columna BAN_CODSUC, que se hará el JOIN de esas filas (como vimos en el Ejemplo 7.) y posteriormente se hará un SORT para mostrar a las filas ordenadas según la columna BAN_NOMBRE.

Conclusión:

Entender el contenido del PLAN es muy útil por dos cosas: a) porque sabremos lo que está haciendo el Firebird para mostrar las filas del SELECT, y b) porque podremos analizarlo y quizás descubrir una mejor alternativa. Claro que esto último lleva su buen tiempo, no es algo que conseguiremos hacer enseguida pero vale la pena el esfuerzo porque un PLAN adecuado muestra a las filas muy rápidamente.

Si en el PLAN vemos la palabra SORT eso es algo malo y si vemos las palabras SORT MERGE eso es mucho más malo. Pero son malos para el SELECT, quizás no lo sean para la Base de Datos. Eso porque para evitar los SORT hay que crear índices, pero los índices deben ser mantenidos cada vez que se realiza un INSERT, un UPDATE o un DELETE, y ese mantenimiento no es instantáneo, toma su tiempo, que en tablas muy grandes y con muchas operaciones de inserción, actualización y borrado puede ser muy grande.

Por lo tanto debemos estudiar bien el caso y decidir si creamos el índice o si dejamos que se haga el SORT. En general, si el SELECT será ejecutado muchas veces, frecuentemente, deberíamos crear el índice. En cambio, si se lo ejecutará raramente lo mejor puede ser no crear el índice.

Otro aspecto muy importante a tener en cuenta es que las tablas son dinámicas, constantemente están cambiando, entonces un PLAN que hoy puede ser perfecto podría no serlo dentro de algunos meses o años. Si vamos a escribir el PLAN manualmente debemos tener bien presente que eso implicará más trabajo para nosotros porque de vez en cuando tendremos que verificar que siga funcionando muy bien, y si no es así entonces buscar y escribir un mejor PLAN. Pero encontrar un mejor PLAN no es algo que haremos en dos segundos, puede hacernos perder muchos minutos o horas inclusive.

Artículos relacionados:

Usando un PLAN

Algo más sobre PLAN

Selectividad de los índices

Recreando índices y calculando estadísticas

El índice del blog Firebird21

El foro del blog Firebird21

Entendiendo COLLATE

2 comentarios

Habrás notado que en las columnas de tipo carácter a veces aparece la palabra COLLATE.

¿Para qué sirve COLLATE?

Para decirle al Firebird cual ordenamiento debe utilizar en las columnas de tipo carácter. ¿Por qué eso? porque el ordenamiento depende del idioma. Por ejemplo en el idioma ruso hay muchos caracteres que no existen en el idioma castellano:

http://es.wikipedia.org/wiki/Alfabeto_ruso

entonces, como el Firebird se usa por personas que hablan en muchos idiomas y cada uno de ellos quiere ver sus informes correctamente ordenados según su propio idioma es que se le debe indicar como ordenar las letras. Un ordenamiento óptimo en castellano puede ser un desastre en holandés, polaco, ruso, etc. Y viceversa: en ruso puede estar perfecto pero en castellano quedar horrible.

¿Cuál es la solución a este problema?

Indicar el ordenamiento que se desea emplear (o sea, el COLLATE).

¿Cuándo se debe usar COLLATE?

Cuando la columna será ordenada (cláusula ORDER BY de un SELECT) o buscada (cláusula WHERE de un SELECT) y convertida a mayúsculas (función UPPER())

¿Qué significan las letras?

Las dos primeras indican el idioma. Las dos últimas el país o región. ¿Por qué? porque a veces hay diferencias en como se usa un idioma. Por ejemplo el francés hablado en Francia es distinto al francés hablado en Canadá. Y el inglés hablado en Inglaterra es distinto al inglés hablado en los Estados Unidos. Y el portugués de Portugal es distinto al portugués de Brasil.

FR_CA significa: idioma francés, tal como se lo habla en Canadá

FR_FR significa: idioma francés, tal como se lo habla en Francia

EN_UK significa: idioma inglés, tal como se lo habla en Inglaterra

EN_US significa: idioma inglés, tal como se lo habla en los Estados Unidos

PT_BR significa: idioma portugués, tal como se lo habla en Brasil

PT_PT significa: idioma portugués, tal como se lo habla en Portugal

¿Cuál COLLATE se debe usar con el idioma castellano?

El COLLATE adecuado es el ES_ES (español, tal como se lo habla en España)

Afortunadamente para nosotros el ordenamiento es igual en todos los países de habla castellana, así que no debemos preocuparnos por las dos últimas letras. Pero sí debemos recordar de usar COLLATE ES_ES en todas las columnas que ordenarán o se buscarán. Si no lo hacemos, entonces el orden podría ser incorrecto o podríamos no encontrar lo que estamos buscando. ¿Por qué eso? Porque el Firebird no tiene un COLLATE por defecto, por lo tanto siempre debemos indicarle uno si queremos asegurar que obtendremos los resultados correctos.

¿Qué significan las letras CI_AI?

A partir del Firebird 2.0 a algunos COLLATE (el ES_ES incluido) se les puede agregar CI_AI.

CI significa “case insensitive”. O en otras palabras, que no distinga entre mayúsculas y minúsculas.

AI significa “accent insensitive”. O en otras palabras, que no distinga entre vocales acentuadas y no acentuadas.

Por lo tanto, si elegimos el COLLATE ES_ES_CI_AI le estamos diciendo al Firebird: “ordena las columnas según el alfabeto español, tal como se lo usa en España, sin diferenciar entre mayúsculas y minúsculas, y sin diferenciar entre vocales acentuadas y no acentuadas”.

Para más información sobre el uso de COLLATE ES_ES_CI_AI puedes leer este artículo:

Consultando sin importar mayúsculas ni acentos

Ejemplo 1

CREATE TABLE ALUMNOS
   ALU_NOMBRE VARCHAR(25) COLLATE ES_ES,
   ALU_APELLD VARCHAR(25) COLLATE ES_ES,
   ...

Aquí le estamos diciendo al Firebird: “usa el ordenamiento del idioma castellano, tal como se lo usa en España”. Y como el ordenamiento es el mismo en España y en todos los demás países de habla castellana, ya está bien así.

Ejemplo 2

SELECT
   ALU_NOMBRE,
   ALU_APELLD
FROM
   ALUMNOS
ORDER BY
   ALU_APELLD COLLATE ES_ES,
   ALU_NOMBRE COLLATE ES_ES

Aquí le estamos diciendo al Firebird que nos muestre las columnas ALU_NOMBRE y ALU_APELLD, ordenadas según las reglas del idioma castellano.

Ejemplo 3

SELECT
   ALU_NOMBRE,
   ALU_APELLD
FROM
   ALUMNOS
WHERE
   ALU_APELLD COLLATE ES_ES = :tcApellido

Aquí, hacemos una búsqueda en la columna ALU_APELLD, usando las reglas del idioma castellano

Ejemplo 4

SELECT
   ALU_NOMBRE,
   ALU_APELLD
FROM
   ALUMNOS
WHERE
   UPPER(ALU_APELLD COLLATE ES_ES_CI_AI) = 'GARCIA'

Aquí, dentro de la función UPPER() se especificó el COLLATE que queremos usar. Eso es importante a recordar: para que la función UPPER() trabaje correctamente debemos especificar el COLLATE dentro suyo. Y como en este ejemplo usamos CI_AI, obtendremos ‘GARCIA’, ‘GARCÍA’, ‘Garcia’, ‘García’, ‘garcia’, etc.

Conclusión:

Los idiomas que hablamos los seres humanos tienen muchos alfabetos y muchas formas de ordenar esos alfabetos. Como el Firebird está pensado para ser usado por personas de todos los países del mundo entonces debe tener alguna forma de ordenar en forma correcta a las columnas de tipo carácter. Esa forma es mediante la cláusula COLLATE.

Al usar COLLATE nos aseguramos de que siempre las columnas de tipo carácter estarán ordenadas como queremos nosotros y que las búsquedas en esas columnas encontrarán lo que se está buscando.

Si no usamos COLLATE entonces …., no se garantiza que el resultado obtenido sea el correcto.

Artículos relacionados:

Consultando sin importar mayúsculas ni acentos

El índice del blog Firebird21

 

Obteniendo el último valor

4 comentarios

Muchas veces puedes necesitar obtener el último valor de los registrados, por ejemplo:

  • ¿cuál fue el último precio de compra de este producto?
  • ¿cuándo fue la última vez que le cobramos a este cliente?
  • ¿cuándo fue la última vez que cambiamos el precio de venta de este producto?
  • ¿cuándo fue la última vez que vendimos este producto?
  • ¿cuándo fue la última vez que este profesor se ausentó?
  • ¿cuándo fue la última vez que pagamos este impuesto?
  • ¿cuánto pagamos por este impuesto la última vez?

Para responder a esas preguntas debes ordenar la consulta en forma descendente y obtener solamente la primera fila. Como la consulta estará ordenada en forma descendente, en la primera fila se encontrarán los datos más nuevos.

Ejemplo:

Queremos saber cuando fue la última vez que le cobramos al cliente que tiene el identificador 2457.

SELECT
   COB_FECHAX
FROM
   COBRANZAS
WHERE
   COB_IDECLI = 2457
ORDER BY
   COB_FECHAX DESC
ROWS
   1

COB_FECHAX es la columna donde se guardan las fechas de las cobranzas.

COB_IDECLI es la columna donde se guardan los identificadores de los clientes.

COBRANZAS es la tabla donde se guardan las cobranzas realizadas.

Si el resultado es NULL eso significará que nunca se le ha cobrado a este cliente. Si el resultado es distinto de NULL entonces esa será la fecha de la última cobranza.

Para que esta consulta sea muy rápida aunque tengas millones de registros en tu tabla de COBRANZAS deberás crear un índice descendente sobre la columna COB_FECHAX. Si tienes un índice ascendente ese no te servirá porque el Firebird no lo utilizará.

Artículo relacionado:

El índice del blog Firebird21