Entendiendo a GSTAT (4)

Deja un comentario

Entender como funciona el programa GSTAT es bastante largo. Ya hemos visto algo sobre él en estos artículos:

Entendiendo a GSTAT (1)

Entendiendo a GSTAT (2)

Entendiendo a GSTAT (3)

ahora, continuamos.

Opción -index

Esta es la opción que debemos elegir cuando solamente nos interesan los índices de nuestras tablas.

gstat01

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

Como es lo usual, la contraseña la extraemos de un archivo de texto y la salida del programa GSTAT la enviamos a otro archivo de texto. Esto último es para facilitarnos la tarea.

gstat02

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

En la Captura 2. vemos una parte del contenido del archivo de texto que nos muestra información sobre nuestros índices. Los nombres de los índices siempre aparecen ordenados alfabéticamente.

¿Cuál es el significado de lo que estamos viendo?

ASIENTOSCAB es el nombre de la tabla

131 es el identificador que la tabla ASIENTOSCAB tiene dentro de la tabla del sistema RDB$RELATIONS. Es en RDB$RELATIONS donde se  guardan los nombres de todas las tablas de la Base de Datos.

ASC01, ASC02, ASC03, y PK_ASIENTOSCAB son los nombres de los índices

0, 1, 2, 3, los números entre paréntesis que vemos después de los nombres de los índices son los identificadores de los índices menos 1 y nos indican el orden en el cual fueron creados, siendo 0 el primer índice que creamos, 1 el segundo índice que creamos, 2 el tercer índice que creamos y así sucesivamente. Si se creó un índice y luego se lo eliminó, el número que tenía no aparecerá en la lista.

Listado 1.

SELECT
   RDB$INDEX_ID,
   RDB$RELATION_NAME,
   RDB$INDEX_NAME
FROM
   RDB$INDICES
WHERE
   RDB$RELATION_NAME = 'ASIENTOSCAB'
ORDER BY
   RDB$INDEX_ID

gstat03

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

¿Por qué en la Captura 2. no muestra los verdaderos identificadores sino los identificadores menos 1? Es un verdadero misterio, quizás haya alguna razón valedera para ello pero es dudoso que exista. Probablemente sólo sea por costumbre.

Depth es la cantidad de indirecciones o desviaciones que hay en el árbol B-tree del índice. Lo ideal es que ese número sea como máximo 3. Si es mayor que 3 entonces el índice no será tan eficiente como podría ser ¿cómo lo solucionamos? aumentando el tamaño de la página de nuestra Base de Datos. Si por ejemplo el tamaño de la página es 4096 lo aumentamos a 8192 y volvemos a ejecutar a GSTAT con la opción -index para comprobar si Depth ahora es 1, 2, ó 3 (esos son los mejores valores que puede tener Depth). Si sigue siendo mayor que 3 entonces volvemos a aumentar el tamaño de la página de nuestra Base de Datos y ahora lo ponemos en 16384.

Leaf buckets es la cantidad de páginas que están en el nivel más bajo del árbol B-tree. Es en estas páginas donde se guardan los punteros a las filas de la tabla. En las demás páginas de índice se guardan los enlaces a otras páginas de índice.

Nodes es la cantidad total de filas en la tabla que han sido indexadas. Sin embargo, este número puede ser erróneo porque podrían aparecer filas que han sido borradas con DELETE y aún su basura no ha sido recolectada o también porque las columnas del índice cambiaron de valor. Por eso, es conveniente ejecutar a GSTAT con la opción -index solamente después de un sweep o de un ciclo backup/restore.

Average data length es el tamaño en bytes promedio de los datos de la columna (o columnas) que se indexaron. Como Firebird comprime esos datos antes de grabarlos en una página de índices, el average data length será siempre menor a la suma del tamaño de las columnas de la tabla.

Total dup es la cantidad total de duplicados que tiene un índice. Los índices que se utilizan en las restricciones Primary Key y Unique Key no admiten duplicados, pero los otros índices sí los admiten. Cuantos más duplicados haya, peor es el índice.

 Listado 2.

SELECT
   ASC_ANOEJE,
   ASC_CODSUC,
   ASC_NUMERO,
   COUNT(*)
FROM
   ASIENTOSCAB
GROUP BY
   ASC_ANOEJE,
   ASC_CODSUC,
   ASC_NUMERO

gstat05

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

gstat04

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

En la Captura 4. vemos que al ejecutar el Listado 2. se usó el índice ASC01, y en la Captura 5. vemos la cantidad de veces que los valores de ese índice aparecieron. En este caso no hay duplicados, ya que COUNT siempre nos muestra el número 1 pero en otros índices sí podría haber duplicados. El Listado 2. nos ayudará a conocer cuantos duplicados tiene nuestro índice. Recuerda que Total dup puede mostrarte una cantidad incorrecta de duplicados si no has hecho previamente un ciclo backup/restore.

Max dup es la cantidad máxima de valores duplicados que tiene un índice.

Fill distribution es una tabla de frecuencias y seguramente te recordarás de ellas si alguna vez estudiaste Estadísticas. Hay 5 filas, yendo de 20% en 20%, cada fila indicando la cantidad de páginas de índices que están completadas hasta ese porcentaje.

gstat02

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

En la Captura 6. vemos que hay 8 páginas de índices que están llenas entre un 20% y un 39%. Y hay 3 páginas de índices que están llenas entre un 40% y un 59%. La suma de 8 + 3 es 11, y siempre debe coincidir con leaf buckets.

Esta distribución es mala, estamos usando más páginas que las necesarias. ¿Cómo sabemos eso? porque no hay páginas rellenas entre un 80% y un 99%. En una buena distribución el número mayor debería estar siempre en la última fila, (es decir, en 80% a 99%). ¿Cómo conseguimos eso? Con un ciclo backup/restore y luego usando el backup restaurado.

Artículos relacionados:

Entendiendo a GSTAT (1)

Entendiendo a GSTAT (2)

Entendiendo a GSTAT (3)

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