La forma correcta de realizar el SWEEP

Deja un comentario

Una de las tareas de mantenimiento que se deben realizar en las bases de datos, especialmente en las que tienen tablas con muchos millones de filas, es la del sweep.

Pero ¿cuál es la manera correcta de realizar esa tarea?

Primero, debemos recordar que el sweep se encarga de eliminar toda la basura que se haya acumulado dentro de la Base de Datos a causa de la ejecución de los comandos UPDATE y DELETE. Siempre que se ejecuta el comando UPDATE o el comando DELETE se deja basura dentro de la Base de Datos, sin importar que la transacción haya finalizado con un COMMIT o con un ROLLBACK; por lo tanto, en algún momento deberemos eliminar esa basura.

Segundo, un sweep automático es realizado por el Firebird cada vez que creamos un backup usando el programa GBAK. Por lo tanto, no siempre es necesario realizar un sweep manual, aunque debemos recordar que el sweep automático también puede fallar y por lo tanto también deberemos verificarlo.

Tercero, tanto si realizamos un sweep manual como un sweep automático debemos comprobar que fue completado exitosamente. ¿Por qué? Porque si no fue completado exitosamente entonces dentro de nuestra Base de Datos habrá quedado mucha basura y eso hará que las tareas que se realicen en ella sean mucho más lentas de lo que deberían ser.

Pasos a seguir:

  1. Verificar si es necesario realizar el sweep
  2. Realizar el sweep
  3. Comprobar si el sweep fue completado exitosamente
  4. Si descubrimos que hay problemas, buscar y corregir esos problemas

Paso 1. Verificar si es necesario realizar el sweep

Desde luego que este paso solamente lo hacemos antes de realizar un sweep manual. El programa GSTAT con la opción -h nos da la información que estamos necesitando.

sweep01

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

En la Captura 1. vemos que la diferencia entre la Oldest Transaction y la Next Transaction es muy grande. Eso puede ocurrir normalmente y no está mal que ocurra cuando hay muchos usuarios conectados y trabajando con la Base de Datos. Pero si no hay otros usuarios conectados o hay muy pocos usuarios conectados es un indicador de que hay mucha basura acumulada dentro de la Base de Datos. Una regla rápida que podemos usar es la siguiente: “si hay hasta 10 transacciones faltantes por cada usuario conectado, es aceptable”. ¿Por qué? porque en general los usuarios ejecutan muchos comandos INSERT y muchos comandos SELECT, y muy pocos comandos UPDATE o DELETE. Estos dos últimos comandos son los que colocan basura dentro de la Base de Datos. Desde luego que esta regla rápida no se aplica a todos los casos, ya que cada caso es un caso, y habrá aplicaciones que realizan legítimamente muchos UPDATE y muchos DELETE y así en lugar de 10 transacciones faltantes por cada usuario la cantidad podría ser de 100, de 1000, o incluso más.

En el caso de la Captura 1. vemos que la Oldest Transaction es 60325 y que la Next Transaction es 71820, siendo la diferencia entre ellas de 11495, un número muy grande de transacciones faltantes. Como la aplicación que usa esa Base de Datos realiza pocos UPDATE y pocos DELETE entonces es un indicador de que un sweep es requerido.

Paso 2. Realizar el sweep

Un sweep manual se realiza mediante el programa GFIX con la opción -sweep

sweep02

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

Como puedes ver, el programa GFIX no te da información sobre la tarea que realizó. Lo positivo es que si no muestra un mensaje significa que finalizó ok.

Paso 3. Comprobar si el sweep fue completado exitosamente

Después de haber ejecutado el programa GFIX con la opción –sweep debemos conectarnos a nuestra Base de Datos, iniciar una transacción, y finalizar esa transacción con el comando COMMIT. Esto es necesario para que se muevan los identificadores de las transacciones.

 sweep04

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

Desde luego que no es necesario usar el programa ISQL para iniciar una transacción y finalizarla con un COMMIT. Cualquier programa que pueda conectarse a la Base de Datos servirá muy bien. Lo importante es iniciar una transacción y finalizarla con un COMMIT, para así mover los identificadores de las transacciones. Cual programa usar es lo de menos.

sweep05

 

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

En la Captura 4. hemos vuelto a ejecutar el programa GSTAT con la opción -h y así podemos observar que los identificadores de las transacciones están todos ok. Los identificadores están todos ok cuando la diferencia entre ellos es de 1 ó máximo de 2.

Paso 4.  Si descubrimos que hay problemas, buscar y corregir esos problemas

Si los identificadores de las transacciones no están todos ok (o sea, la diferencia entre ellos es más que 2 y no había otros usuarios conectados a la Base de Datos cuando se realizó el sweep), eso significa que el sweep no eliminó a toda la basura. ¿Y por qué no eliminó a toda la basura? La razón más frecuente es que hay una transacción (o más de una transacción) cuyo acceso es READ WRITE (o sea, que la transacción puede escribir en la Base de Datos) y esa transacción hace mucho tiempo que se inició y aún no ha finalizado con un COMMIT ni con un ROLLBACK. Otra razón, mucho más dolorosa, es que la Base de Datos está corrupta.

En estas circunstancias (es decir, cuando los identificadores no están todos ok) debemos ejecutar el programa GFIX con las opciones -validate y -full

sweep06

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

Si queremos conocer cuales son las transacciones que hace mucho tiempo se iniciaron y aún no han finalizado ni con un COMMIT, ni con un ROLLBACK, podemos escribir el siguiente comando:

Listado 1.

SELECT
   MON$TRANSACTION_ID,
   MON$TIMESTAMP
FROM
   MON$TRANSACTIONS
ORDER BY
   MON$TRANSACTION_I

Y para eliminar a la transacción problemática, podemos escribir:

Listado 2.

DELETE FROM
   MON$TRANSACTIONS
WHERE
   MON$TRANSACTION_ID = NúmeroTransacciónProblemática

Si las transacciones problemáticas son varias, entonces podríamos necesitar repetir estos pasos varias veces.

Conclusión:

Tanto si se realiza un sweep manual como un sweep automático, debemos verificar que al finalizar los identificadores de las transacciones estén todos ok. Los identificadores de las transacciones están todos ok cuando la diferencia entre ellos es de 1 ó de 2, cuando no hay otros usuarios conectados a la Base de Datos. Si los identificadores no están ok, entonces debemos validar la Base de Datos, buscar las transacciones que se están demorando mucho en finalizar y eliminar a esas transacciones.

Artículos relacionados:

Entendiendo los identificadores de las transacciones

Entendiendo sweep y garbage collection

El índice del blog Firebird21

El foro del blog Firebird21

Viendo las estadísticas de un backup y de un restore

Deja un comentario

Como sabes, con el programa GBAK puedes realizar el backup de una Base de Datos y también lo puedes restaurar más tarde.

Una interesante opción de ese programa es pedirle que muestre algunas estadísticas de la tarea que esté realizando. Para eso utilizaremos la opción STATISTICS.

La opción STATISTICS puede recibir los siguientes parámetros:

T   para mostrar el tiempo transcurrido desde que se inició el backup o el restore

D   para mostrar el tiempo que demoró finalizar la tarea que se realizó

R   para mostrar la cantidad de páginas de la Base de Datos que fueron leídas

W   para mostrar la cantidad de páginas de la Base de Datos que fueron escritas

Ejemplos:

gbak01

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

En la Captura 1. hacemos un backup de una Base de Datos y pedimos que se nos muestren las estadísticas. El resultado de ejecutar al programa GBAK lo vemos a continuación.

gbak02

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

A continuación de la palabra “gbak:” vemos 4 números, correspondientes a los parámetros T, D, R, W de la opción STATISTICS.

En (1) vemos que escribir en el backup los roles finalizó a los 27 segundos y 324 milésimas de segundo, y que esa tarea demoró 27 milisegundos en finalizar. También vemos que se leyeron 7 páginas y que 0 páginas fueron escritas.

En (2) vemos que realizar el backup completo se demoró 27 segundos y 407 milisegundos; que mostrar las estadísticas demoró 2 milisegundos; que en total se leyeron 2696 páginas; y que en total se escribieron 18 páginas.

gbak03

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

Al restaurar también usamos la opción STATISTICS, tal como podemos ver en la Captura 3.

 gbak04Captura 4. Si haces clic en la imagen la verás más grande

Y en la Captura 4. podemos ver que realizar el restore demoró en total 58 segundos y 248 milisegundos, que mostrar las estadísticas demoró 1 milisegundo, que en total se leyeron 7645 páginas y que se escribieron 5616 páginas.

Conclusión:

Poder ver las estadísticas puede ser muy útil para nosotros, así sabremos cuales tareas son las que demoran más tiempo en completarse y si esa demora es exagerada, buscar alguna solución al problema.

También nos permite responder al cliente que nos dice: “el backup tarda demasiado tiempo en finalizar”. Mirando las estadísticas de la Captura 2., en (2) podemos ver cuanto es “demasiado tiempo” para él.

El parámetro T puede ser muy útil para los usuarios y para nosotros. El parámetro D puede ser muy útil para nosotros. Los parámetros R y D sirven de muy poco, en raras ocasiones podrían sernos de utilidad.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Buscando texto eficientemente dentro de un string grande

1 comentario

Cuando necesitamos buscar un texto dentro de un string podemos usar LIKE ‘%TextoBuscado%’ o también podemos usar CONTAINING. Ambos funcionarán muy bien, pero tienen un problema: si la cantidad de filas es muy grande o la columna donde puede encontrarse el texto que buscamos tiene muchos caracteres, puede ser muy lento, a veces inclusive extremadamente lento.

La idea para este artículo la obtuve de aquí:

https://blog.cincura.net/233577-poor-mans-full-text-using-psql-only-on-firebird/

Listado 1.

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna LIKE '%Asunción%'

Listado 2.

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna CONTAINING 'Asunción'

Tanto el SELECT del Listado 1. como el SELECT del Listado 2. harán bien su trabajo, pero como ya dijimos antes, si la cantidad de filas de MiTabla es muy grande o la cantidad de caracteres en MiColumna es muy grande, pueden ser muy lentos. ¿Por qué? Porque el Firebird nunca usará un índice en esos casos y por lo tanto deberá buscar secuencialmente en cada columna de cada fila la palabra que deseamos encontrar.

Si necesitamos gran velocidad en la búsqueda entonces debemos emplear otro aprovechamiento: usar un diccionario de palabras.

La idea es la siguiente: cada palabra que aparece en la columna MiColumna la guardamos en otra tabla, junto con su correspondiente Identificador. A esa columna le definiremos un índice y de esa manera las búsquedas serán rapidísimas.

Desde luego que hacer eso solamente se justificará si las búsquedas realizadas mediante el Listado 1. o el Listado 2. son lentas, en otro caso no vale la pena tomarse la molestia de hacerlo.

Para que nuestra técnica sea más inteligente también debemos tener en cuenta que a veces los usuarios se equivocan al escribir las palabras que están buscando. Por ejemplo, quieren buscar ‘Asunción’ pero escribieron ‘Asuncion’; o sea, sin el acento sobre la letra ‘o’. O escribieron ‘ASUNCION’, o sea todo en mayúsculas; o escribieron ‘Asumción’, o sea que en lugar de la letra ‘n’ pusieron la letra ‘m’.

Entonces lo que haremos será lo siguiente:

  1. Crear un dominio que acepte tanto mayúsculas como mínusculas (case insensitive) y que acepte tanto palabras acentuadas como no acentuadas (accent insensitive)
  2. Crear una tabla donde se guardarán las palabras y en la cual usaremos el dominio creado
  3. Crear un índice normal que usaremos para buscar palabras normales
  4. Crear un índice inverso que usaremos para buscar palabras invertidas
  5. Crear un stored procedure seleccionable que servirá para extraer todas las palabras de un texto
  6. Crear un trigger que ejecutará al stored procedure seleccionable y luego guardará cada palabra distinta en la tabla, junto con el Identificador de la fila donde se encuentra

Paso 1. Crear un dominio

Listado 3.

CREATE DOMAIN D_PALABRAS_CI_AI 
AS VARCHAR(40) 
CHARACTER SET ISO8859_1
COLLATE ES_ES_CI_AI;

Definimos este dominio como VARCHAR(40) porque suponemos que ninguna de las palabras tendrá más de 40 caracteres, si eso pudiera ocurrir entonces tendrías que aumentar el tamaño.

Paso 2. Crear la tabla donde se guardarán las palabras

palabras01

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

Nuestra tabla (bastante apropiadamente) se llama DICCIONARIO porque contendrá una lista de palabras.

En la columna DIC_TABLAX se guardará el nombre de la tabla que contiene las palabras que podríamos buscar. Eso porque podríamos tener varias tablas en las cuales sería útil realizar esta búsqueda.

En la columna DIC_COLUMN se guardará el nombre de la columna que contiene las palabras que podríamos buscar. Eso porque dentro de una misma tabla podríamos tener varias columnas que nos interesan.

En la columna DIC_IDECAB se guardará el Identificador de la fila que corresponde a la tabla DIC_TABLAX. De esa manera podremos saber en cual fila de la tabla DIC_TABLAX se encuentra la palabra buscada.

En la columna DIC_PALABR se guardará la palabra que puede ser buscada.

Paso 3. Crear un índice normal

Listado 4.

CREATE INDEX IDX_DICCIONARIO ON DICCIONARIO COMPUTED BY (DIC_TABLAX || DIC_COLUMN || UPPER(DIC_PALABR)
);

Paso 4. Crear un índice inverso

Listado 5.

CREATE INDEX IDX_DICCIONARIO1 ON DICCIONARIO COMPUTED BY (DIC_TABLAX || DIC_COLUMN || REVERSE(UPPER(DIC_PALABR))
);

Paso 5. Crear un stored procedure seleccionable

Listado 6.

CREATE PROCEDURE SPG_HALLAR_PALABRAS(
   ftcTexto VARCHAR(32765))
RETURNS(
   ftcPalabra TYPE OF D_NOMBRE40)
AS
   DECLARE VARIABLE lnI        SMALLINT;
   DECLARE VARIABLE lnInicio   SMALLINT;
   DECLARE VARIABLE lnLongitud SMALLINT;
BEGIN

   lnI        = 1;
   lnInicio   = 1;
   ftcTexto   = ftcTexto || ' ';
   lnLongitud = CHARACTER_LENGTH(ftcTexto);

   WHILE (lnI <= lnLongitud) DO BEGIN
      IF(CAST(SUBSTRING(ftcTexto FROM lnI FOR 1) AS D_PALABRAS_CI_AI) NOT SIMILAR TO '[[:ALNUM:]]' AND POSITION(SUBSTRING(ftcTexto FROM lnI FOR 1) IN 'áéíóúñÁÉÍÓÚÑ') = 0) THEN BEGIN
         IF(lnI > lnInicio) THEN BEGIN
            ftcPalabra = SUBSTRING(ftcTexto FROM lnInicio FOR lnI - lnInicio);
            SUSPEND;
         END
         lnInicio = lnI + 1;
      END
      lnI = lnI + 1;
   END
END;

El stored procedure SPG_HALLAR_PALABRAS hallará cada una de las palabras contenidas en el texto que se le envíe como parámetro de entrada. Veamos algunos ejemplos:

Listado 7.

SELECT
   *
FROM
   SPG_HALLAR_PALABRAS('Hoy es un día lluvioso')

Listado 8.

SELECT
   *
FROM
   SPG_HALLAR_PALABRAS('        Hoy es un día lluvioso')

Listado 9.

SELECT
   *
FROM
   SPG_HALLAR_PALABRAS('       Hoy         es         un día lluvioso')

Listado 10.

SELECT
   *
FROM
   SPG_HALLAR_PALABRAS('   Hoy es un        día lluvioso            ')

Tanto si ejecutamos el Listado 7., como el Listado 8., como el Listado 9., como el Listado 10., siempre obtendremos el mismo resultado, aún cuando a la frase original se le hayan agregado espacios en blanco al principio, en el medio, y al final del texto:

palabras02

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

Paso 6. Crear un trigger 

Listado 11.

CREATE TRIGGER PRODUCTOS_BIUD FOR PRODUCTOS
   ACTIVE BEFORE INSERT OR UPDATE OR DELETE
   POSITION 1
AS
BEGIN

   -- Se borran las filas que se habían insertado correspondientes a este Producto

   IF (UPDATING OR DELETING) THEN
      DELETE FROM
         DICCIONARIO
      WHERE
         DIC_TABLAX = 'PRODUCTOS' AND
         DIC_COLUMN = 'PRD_NOMBRE' AND
         DIC_IDECAB = OLD.PRD_IDENTI;

   -- Se insertan las nuevas filas, una fila por cada palabra de la columna PRD_NOMBRE

   IF (INSERTING OR UPDATING) THEN BEGIN
      INSERT INTO DICCIONARIO (DIC_TABLAX, DIC_COLUMN, DIC_IDECAB, DIC_PALABR)
         SELECT 'PRODUCTOS', 'PRD_NOMBRE', NEW.PRD_IDENTI, ftcPALABRA FROM SPG_HALLAR_PALABRAS(NEW.PRD_NOMBRE);

   END

END;

Para cada tabla que nos interese deberemos crear un trigger similar al mostrado en el Listado 11., de esa manera cada vez que se realice un INSERT, un UPDATE, o un DELETE a alguna fila de esa tabla que nos interesa, se actualizará también la tabla DICCIONARIO.

En el Listado 11. la tabla que nos interesa se llama PRODUCTOS, y dentro de esa tabla la columna que nos interesa se llama PRD_NOMBRE.

Eso significa que cada una de las palabras que coloquemos en la columna PRD_NOMBRE se insertará en la tabla DICCIONARIO para que podamos buscarla muy rápidamente.

Agregando filas a la tabla DICCIONARIO

Para verificar que todo funciona bien le agregaremos algunas filas a la tabla PRODUCTOS y al hacerlo también le estaremos agregando filas a la tabla DICCIONARIO.

Listado 12.

INSERT INTO PRODUCTOS (PRD_CODIGO, PRD_NOMBRE) VALUES('CC267', 'COCA COLA DE 1 LITRO RETORNABLE');
INSERT INTO PRODUCTOS (PRD_CODIGO, PRD_NOMBRE) VALUES('CP389', 'CERVEZA PILSEN DE 750 C.C.');
INSERT INTO PRODUCTOS (PRD_CODIGO, PRD_NOMBRE) VALUES('LT224', 'Leche Trébol de 1 litro descremada');
INSERT INTO PRODUCTOS (PRD_CODIGO, PRD_NOMBRE) VALUES('CB357', 'Cerveza BUDWEISER 66 DE 1 ÑITRO');

Al insertar esas filas a la tabla PRODUCTOS nuestra tabla DICCIONARIO quedó así:

palabras03

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

Verificando que funciona

Ahora que tenemos todo listo lo único que nos falta comprobar es que funcione bien.

Mirando la Captura 3. podemos ver que la palabra “LITRO” está escrita en mayúsculas, en minúsculas, y también mal escrita (en la fila 24 dice “ÑITRO” en lugar de “LITRO”).

Entonces, ¿cómo podemos obtener rápidamente las 3 filas donde se encuentra la palabra ‘LITRO’?

Listado 13.

SELECT
   *
FROM
   DICCIONARIO
WHERE
   DIC_TABLAX || DIC_COLUMN || UPPER(DIC_PALABR) STARTING WITH 'PRODUCTOS' || 'PRD_NOMBRE' || 'LITRO' OR
   DIC_TABLAX || DIC_COLUMN || REVERSE(UPPER(DIC_PALABR)) STARTING WITH 'PRODUCTOS' || 'PRD_NOMBRE' || REVERSE('ITRO') OR
   DIC_TABLAX || DIC_COLUMN || REVERSE(UPPER(DIC_PALABR)) STARTING WITH 'PRODUCTOS' || 'PRD_NOMBRE' || REVERSE('TRO') OR
   DIC_TABLAX || DIC_COLUMN || REVERSE(UPPER(DIC_PALABR)) STARTING WITH 'PRODUCTOS' || 'PRD_NOMBRE' || REVERSE('RO')

Si ejecutamos el Listado 13., obtendremos:

palabras04

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

O sea, que tenemos todas las apariciones de la palabra ‘LITRO’ aún aquella que está mal escrita. Justamente para eso sirven las 3 últimas condiciones puestas en la cláusula WHERE, aquellas que usan la función REVERSE(). En el primer caso escribimos ‘ITRO’, eso significa que la primera letra puede estar mal escrita. En el segundo caso escribimos ‘TRO’, eso significa que las primeras dos letras pueden estar mal escritas. En el tercer caso escribimos ‘RO’, eso significa que las primeras tres letras pueden estar mal escritas.

¿Y qué tan eficiente es nuestro SELECT?

Veamos el PLAN que ejecutó el Firebird para saberlo.

palabras05

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

Como se puede ver en la Captura 5. para la primera línea de la cláusula WHERE usará el índice IDX_DICCIONARIO, y para la segunda, la tercera, y la cuarta líneas, usará el índice IDX_DICCIONARIO1.

O sea, exactamente lo que queríamos conseguir.

Veamos ahora la eficiencia del Listado 13. en forma gráfica.

palabras06

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

Está perfecto. Hay 3 filas que cumplen con la condición impuesta en la cláusula WHERE y hay 3 filas extraídas usando los índices.

Conclusión:

Normalmente podemos usar LIKE ‘%MiTextoBuscado%’ o CONTAINING ‘MiTextoBuscado’ cuando queremos obtener las filas que tienen a ‘MiTextoBuscado’ en ellas. Sin embargo, hay ocasiones en que usar dichas sub-cláusulas puede ser extremadamente lento: funciona bien, pero son muy lentas.

Para esos casos lo conveniente es tener un diccionario de palabras, que servirá para indicarnos en cuales filas está el texto que buscamos.

En este artículo hemos visto un método que podemos utilizar para conseguir nuestro objetivo: búsquedas muy rápidas del texto, aunque la tabla tenga muchísimas filas o aunque tenga columnas que contienen muchísimas palabras.

Artículos relacionados:

https://blog.cincura.net/233577-poor-mans-full-text-using-psql-only-on-firebird/

La función REVERSE()

El índice del blog Firebird21

El foro del blog Firebird21

 

 

Validando números escritos en distintos formatos

Deja un comentario

Tenemos el siguiente problema: en una tabla hay una columna donde se  guardan números de teléfono, los cuales pueden estar repetidos y queremos que haya una sola fila por cada número de teléfono. Pero el problema es que esos números de teléfono pueden estar escritos de varias maneras, por ejemplo:

  • 11-555-9090
  • (11)555-9090
  • 115559090

 

validar01

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

En la Captura 1. vemos el Identificador y el Número de Teléfono que existen en siete filas de una tabla. Solamente el Número de Teléfono de la fila que tiene Identificador igual a 4 no está repetido. En todas las demás filas hay simplemente variaciones de los mismos números, escritos con diferentes formatos.

Entonces, ¿cómo podemos saber cuales filas tienen números de teléfono repetidos?

Primero, buscaremos cuales son los números repetidos, estén escritos en el formato que sea.

Para ellos usaremos la función REPLACE() la cual nos permite reemplazar una carácter (o varios caracteres) por otro carácter (o por varios caracteres). En nuestro caso reemplazaremos por un carácter vacío.

Listado 1.

SELECT
   REPLACE(ALU_TELEFO, '(', '')
FROM
   ALUMNOS

Al ejecutar el Listado 1. veremos algo como:

validar02

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

Si observas la Captura 2. notarás que han desaparecido los paréntesis izquierdos. Seguimos refinando nuestro SELECT.

Listado 2.

SELECT
   REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', '')
FROM
   ALUMNOS

validar03

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

Si observas la Captura 3. notarás que han desaparecido los paréntesis izquierdos y también los paréntesis derechos. Seguimos refinando nuestro SELECT.

Listado 3.

SELECT
   REPLACE(REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', ''), '-', '')
FROM
   ALUMNOS

validar04

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

Como ya habrás visto, en la Captura 4. no hay ni paréntesis ni guiones. Así que buscar duplicados ahora ya es más fácil.

Listado 4.

SELECT
   REPLACE(REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', ''), '-', '')
FROM
   ALUMNOS
GROUP BY
   REPLACE(REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', ''), '-', '')
HAVING
   COUNT(*) >= 2

El SELECT del Listado 4. también podríamos haberlo escrito de forma más simplificada así:

Listado 5.

SELECT
   REPLACE(REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', ''), '-', '')
FROM
   ALUMNOS
GROUP BY
   1
HAVING
   COUNT(*) >= 2

En ambos casos obtendremos el mismo resultado:

validar05

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

Donde podemos ver los números que se repiten. Ahora bien, ya sabemos cuales son los números que se repiten, pero ¿en cuáles filas están? Esa información la obtendremos mediante el siguiente SELECT

Listado 6.

SELECT
   ALU_IDENTI,
   ALU_TELEFO
FROM
   ALUMNOS
WHERE
   REPLACE(REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', ''), '-', '') IN
      (SELECT
         REPLACE(REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', ''), '-', '')
       FROM
          ALUMNOS
       GROUP BY
          1
       HAVING
          COUNT(*) >= 2)

validar06

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

Donde la única fila que no aparece es la que tiene Identificador igual a 4, porque esa fila no tiene un Número de Teléfono que esté repetido.

Como seguramente te habrás dado cuenta mirando los SELECTs anteriores, usamos una función REPLACE() por cada carácter que deseamos extraer. Un carácter, una función REPLACE(); dos caracteres, dos funciones REPLACE(); tres caracteres, tres funciones REPLACE().

Supongamos ahora que queremos dejar en nuestra tabla solamente la primera fila de cada número. O sea que quisiéramos obtener esto:

validar07

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

¿Cómo lo podemos conseguir?

Listado 7.

DELETE FROM
   ALUMNOS T1
WHERE
   NOT EXISTS (SELECT
                  T2.ALU_IDENTI
               FROM
                  ALUMNOS T2
               WHERE
                  REPLACE(REPLACE(REPLACE(T1.ALU_TELEFO, '(', ''), ')', ''), '-', '') = REPLACE(REPLACE(REPLACE(T2.ALU_TELEFO, '(', ''), ')', ''), '-', '') AND
                  T1.ALU_IDENTI > T2.ALU_IDENTI)

Conclusión:

Hay varias técnicas que podemos usar para conocer si hay números duplicados aunque estén escritos con diferentes formatos, y para borrarlos en caso de necesidad. En este artículo se mostró una de esas técnicas, la cual al autor le parece mucho más sencilla que, por ejemplo, escribir un stored procedure que realice la misma tarea.

Artículos relacionados:

La función REPLACE()

Agrupando por una columna … y viendo todas las demás columnas

Obteniendo la primera fila de cada grupo

El índice del blog Firebird21

El foro del blog Firebird21

 

Listando las funciones externas

Deja un comentario

Como recordarás, Firebird nos permite usar funciones externas en nuestras bases de datos. Una función externa no está incluida en la instalación del Firebird sino que la agregamos después y a cada Base de Datos que la necesite utilizar. Eso significa que cada una de nuestras bases de datos puede tener cero, una, o varias funciones externas.

Si queremos saber cuales son las funciones externas que hemos registrado en una Base de Datos, podemos usar nuestro administrador gráfico para ello:

externa01

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

o podemos escribir un SELECT que nos de esa información:

Listado 1.

SELECT
   *
FROM
   RDB$FUNCTIONS
WHERE
   RDB$SYSTEM_FLAG = 0

Donde obtendremos un resultado similar al siguiente:

externa02

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

  • RDB$FUNCTION_NAME. Es el nombre con el cual se conoce a esta función dentro de nuestra Base de Datos
  • RDB$FUNCTION_TYPE. Tipo de la función, no está siendo usado y por eso siempre es Null
  • RDB$QUERY_NAME. Nombre de la consulta, no está siendo usada y por eso siempre es Null
  • RDB$DESCRIPTION. Comentarios que podemos escribir para describir lo que hace la función
  • RDB$MODULE_NAME. El nombre que tiene el archivo .DLL o el objeto compartido, en el disco duro u otro dispositivo de almacenamiento
  • RDB$ENTRYPOINT. El nombre que tiene la función externa en el archivo .DLL o en el objeto compartido. No siempre es igual a RDB$FUNCTION_NAME
  • RDB$RETURN_ARGUMENT. El número de posición del argumento que se devuelve, en la lista de argumentos de entrada
  • RDB$SYSTEM_FLAG. Una bandera que indica si la función fue definida internamente o externamente. 0=definida externamente, 1=definida internamente

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

 

 

 

Entendiendo los índices de expresiones

2 comentarios

Los índices sirven para dos cosas:

  1. Mostrar el resultado de los SELECTs ordenados por el criterio que nos interesa
  2. Realizar búsquedas o filtros, para que solamente sean afectadas las filas que cumplan con la condición que establecimos

Lo más común es que los índices estén compuestos por una o más columnas en forma directa. Veamos un ejemplo:

indices01

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

indices02

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

Tenemos una tabla ALUMNOS y para ordenar a los alumnos por APELLIDOS y por NOMBRES podríamos crear un índice como el siguiente:

Listado 1.

   CREATE INDEX IDX_ALUMNOS ON ALUMNOS(ALU_APELLD, ALU_NOMBRE);

Y está muy bien, funcionará perfectamente.

Podríamos escribir entonces un SELECT como el siguiente:

Listado 2.

SELECT
   *
FROM
   ALUMNOS
ORDER BY
   ALU_APELLD,
   ALU_NOMBRE

Y así obtendríamos un resultado como este:

indices03

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

Donde como puedes observar, los resultados aparecen ordenados por ALU_APELLD. Pero si queremos saber la diferencia entre ALU_TOTANO y ALU_TOTCOB no es posible usar un índice normal.

¿Y entonces?

Entonces la solución es crear un índice de expresión.

¿Qué es un índice de expresión?

Un índice en el cual se utiliza una expresión aritmética o una expresión alfanumérica o funciones internas.

Ejemplos de índices de expresión:

Listado 3.

   CREATE INDEX IDX_ALUMNOS2 ON ALUMNOS COMPUTED BY (LEFT(ALU_APELLD, 1) || LEFT(ALU_NOMBRE, 1));

   CREATE INDEX IDX_ALUMNOS3 ON ALUMNOS COMPUTED BY (ALU_TOTANO - ALU_TOTCOB);

Como puedes ver, la diferencia entre el índice creado en el Listado 1. y los índices creados en el Listado 3., es que en estos últimos se escribieron las palabras COMPUTED BY y también se usó la función LEFT() en IDX_ALUMNOS2 y una operación aritmética de resta en IDX_ALUMNOS3.

En todos los índices de expresión se deben escribir las palabras COMPUTED BY, tal como vimos en el Listado 3.

Usando índices de expresión

Algo muy importante a recordar es que cuando usamos índices de expresión debemos usarlos exactamente igual a como los definimos.

Listado 4.

SELECT
   *
FROM
   ALUMNOS
WHERE
   LEFT(ALU_APELLD, 1) || LEFT(ALU_NOMBRE, 1) = 'KM'

En este caso el Firebird usará el índice IDX_ALUMNOS2 porque la expresión escrita en la cláusula WHERE es la misma expresión usada en la definición del índice IDX_ALUMNOS2.

Listado 5.

SELECT
   *
FROM
   ALUMNOS
WHERE
   LEFT(ALU_NOMBRE, 1) || LEFT(ALU_APELLD, 1) = 'MK'

En el SELECT del Listado 5. el Firebird no usará el índice IDX_ALUMNOS2 porque la condición escrita en la cláusula WHERE no es igual a la expresión usada en la definición del índice IDX_ALUMNOS2.

Listado 6.

SELECT
   *
FROM
   ALUMNOS
WHERE
   ALU_TOTANO - ALU_TOTCOB > 1000

En el SELECT del Listado 6. el Firebird usará el índice IDX_ALUMNOS3 porque la condición escrita en la cláusula WHERE es la misma expresión escrita en la definición del índice IDX_ALUMNOS3.

Listado 7.

SELECT
   *
FROM
   ALUMNOS
ORDER BY
   ALU_TOTANO - ALU_TOTCOB

En el SELECT del Listado 7. también se usará el índice IDX_ALUMNOS3 porque la expresión escrita en la cláusula ORDER BY es la misma expresión que se usó en la definición del índice IDX_ALUMNOS3.

Conclusión:

Los índices de expresión pueden ser muy útiles en algunos casos, es bueno saber que contamos con esta herramienta para poder usarla cuando nos haga falta.

Artículos relacionados:

Optimizando un SELECT que compara columnas de la misma tabla (2)

El índice del blog Firebird21

El foro del blog Firebird21

 

Optimizando un SELECT que compara columnas de la misma tabla (2)

6 comentarios

En este artículo:

Optimizando un SELECT que compara columnas de la misma tabla

vimos una técnica para optimizar los SELECT que comparan columnas de la misma tabla. La ventaja de esa técnica es que funcionará con cualquier motor SQL que utilicemos. Pero con Firebird tenemos además otra posibilidad, que es mejor que la anterior: usar índices de expresiones.

Nuestra tabla PRODUCTOS tiene la siguiente estructura:

optimizando1

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

Y podríamos crear un índice de expresión como el siguiente:

Listado 1.

CREATE INDEX IDX_PRODUCTOS ON PRODUCTOS COMPUTED BY (PRD_PREVTA - PRD_PRECTO);

Y nuestro SELECT tendría que ser así:

Listado 2.

SELECT
   *
FROM
   PRODUCTOS
WHERE
   PRD_PREVTA - PRD_PRECTO < 0

Donde la condición puesta en el WHERE tiene que ser igual que la expresión entre paréntesis en el Listado 1. Si no son iguales, el índice no será usado.

Si ahora miramos el rendimiento obtenido:

optimizando2

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

Veremos que efectivamente se ha usado el índice de expresión que creamos.

Las ventajas de usar un índice de expresión son:

  1. No necesitas crear una columna adicional
  2. No necesitas ejecutar un UPDATE para actualizar el contenido de la columna adicional
  3. No necesitas escribir un trigger que se dedique a actualizar el contenido de la columna adicional

Conclusión:

Usar un índice de expresión nos facilita el trabajo cuando necesitamos poner en la cláusula WHERE una condición que compara columnas, pero como todo índice hacemos trabajar más al motor cada vez que se realiza un INSERT, un UPDATE, o un DELETE en la tabla, así que debemos sopesar las ventajas y las desventajas de utilizarlo.

Artículos relacionados:

Optimizando un SELECT que compara columnas de la misma tabla

El índice del blog Firebird21

El foro del blog Firebird21

 

 

 

Older Entries