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

 

Anuncios

Limitando los derechos de ejecución de los usuarios

7 comentarios

Una tarea muy importante que tenemos cuando administramos una Base de Datos es limitar los derechos que los distintos usuarios tienen sobre ella. De esa manera impediremos que vean o hagan algo que no deberían ver ni hacer.

Ejemplo:

Tenemos un stored procedure llamado MiStoredProcedure el cual hace un SELECT a una tabla llamada MiTabla. El usuario ELIZABETH debe tener el derecho de ejecutar ese stored procedure. ¿Cómo le otorgamos ese derecho?

Intento 1:

   GRANT EXECUTE ON PROCEDURE MiStoredProcedure TO ELIZABETH;

   COMMIT;

Cuando ELIZABETH quiera ejecutar a MiStoredProcedure obtendrá un mensaje de error: “no permission for read/select access to TABLE MiTabla”

¿Por qué el error?

Porque MiStoredProcedure necesita permiso para realizar el SELECT a la tabla MiTabla y no tiene ese permiso.

Intento 2:

   GRANT SELECT ON MiTabla TO ELIZABETH;

   GRANT EXECUTE ON PROCEDURE MiStoredProcedure TO ELIZABETH;

   COMMIT;

Ahora sí ELIZABETH podrá ejecutar a MiStoredProcedure, no tendrá problemas para hacerlo, pero … hay algo que está mal aquí. ¿Lo has descubierto?, ¿qué es lo que está mal?

Lo que está mal es que ELIZABETH podrá escribir por su cuenta algo como:

SELECT
   *
FROM
   MiTabla;

O sea, podrá conocer todo el contenido de MiTabla y eso puede ser muy inseguro. Por ejemplo, si MiStoredProcedure recibe como parámetro de entrada el Identificador de un Cliente y devuelve el saldo de ese cliente, solamente ese dato queremos que ELIZABETH conozca. Pero si tiene derecho de SELECT sobre la tabla CLIENTES podrá conocer todos los datos de todos los clientes (nombres, direcciones, teléfonos, e-mails, etc.) y no queremos que algo así pueda suceder. Puede ser muy peligroso.

Intento 3:

   GRANT SELECT ON MiTabla TO PROCEDURE MiStoredProcedure;

   GRANT EXECUTE ON PROCEDURE MiStoredProcedure TO ELIZABETH;

   COMMIT;

La diferencia con el Intento 2 es que aquí el derecho de SELECT no se le otorgó a ELIZABETH sino que se le otorgó a MiStoredProcedure.

Como ELIZABETH tiene derecho de ejecución sobre MiStoredProcedure entonces podrá ejecutarlo cuando quiera, pero sin embargo si escribe:

SELECT
   *
FROM
   MiTabla;

Recibirá el mensaje de error: “no permission for read/select access to TABLE MiTabla”. ¿Por qué? Porque ella no tiene permiso de SELECT sobre MiTabla. Ese permiso lo tiene MiStoredProcedure, pero no lo tiene ELIZABETH.

Conclusión:

Mantener a los datos seguros y confidenciales puede ser muy importante en muchos casos, si nosotros somos buenos profesionales debemos tomar las medidas de seguridad apropiadas para que ningún usuario tenga la posibilidad de hacer aquello que no debería hacer.

En general, otorgarle a un usuario o a un rol derechos (permisos) sobre una tabla es un gran error.

Los derechos (permisos) deben otorgarse a las vistas y a los stored procedures, nunca a los usuarios ni a los roles.

De esta manera tendremos bien limitado lo que cada usuario puede hacer, y no nos encontraremos con sorpresas desagradables.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

 

Hallando la última palabra

2 comentarios

Ya vimos como hallar la primera palabra de un texto, ¿pero y si necesitamos hallar la última palabra, cómo lo conseguimos?

LEFT1

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

Listado 1.

SELECT
   RIGHT(ALU_NOMBRE, POSITION(' ' IN REVERSE (ALU_NOMBRE) || ' '))
FROM
   ALUMNOS

RIGHT1

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

Explicación:

La función POSITION() devuelve la posición en la cual se encuentra un string dentro de otro string. La función REVERSE() lo muestra de atrás para adelante. Si hay al menos un espacio en blanco entonces la función RIGHT() en el Listado 1. nos devolverá la última palabra. ¿Pero y si no hay ese espacio en blanco? Entonces devolverá un string vacío. Para evitarlo, se concatena un espacio en blanco y así se soluciona.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Hallando la primera palabra

Deja un comentario

En ocasiones necesitamos hallar la primera palabra de un texto, ¿cómo lo conseguimos?

LEFT1

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

Listado 1.

SELECT
   LEFT(ALU_NOMBRE, POSITION(' ', ALU_NOMBRE || ' '))
FROM
   ALUMNOS

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

Explicación:

La función POSITION() devuelve la posición en la cual se encuentra un string dentro de otro string. Si hay al menos un espacio en blanco entonces la función LEFT() en el Listado 1. nos devolverá la primera palabra. ¿Pero y si no hay ese espacio en blanco? Entonces devolverá un string vacío. Para evitarlo, se concatena un espacio en blanco y así se soluciona.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Error 802 en una consulta

1 comentario

En el foro preguntaron:

¿Por qué esta consulta:

Listado 1.

SELECT
   192.76110000000000 / 2.2200
FROM
   RDB$DATABASE

muestra el error: -802 Arithmetic overflow or division by zero has occurred.?

El problema, tal y como se explicó en el artículo Usando NUMERIC y DECIMAL es debido a la forma en que el Firebird define a los números escalados (también llamados de coma fija o de punto fijo; son sinónimos).

En Firebird los números escalados tienen el formato:

p.s

donde p indica la precisión y s indica la escala (s=scale, en inglés).

La precisión máxima en Firebird, hasta la versión 2.5.4 es de 18. La escala siempre debe ser menor o igual a la precisión, nunca puede ser mayor.

¿Cuál es la precisión del resultado cuándo se hace una división?

Siempre es 18.

¿Cuál es la escala del resultado cuándo se hace una división?

Siempre es la suma de la escala del numerador más la escala del denominador.

¿Cuál fue el problema en la división que dio origen a este artículo?

Que el numerador (192.76110000000000) tiene una escala de 14 y el denominador (2.2200) tiene una escala de 4. El resultado de la división por lo tanto tendrá una escala de 18 (es decir, 14 + 4). Ya está en el límite máximo permitido.

La división anterior sí funcionará si la parte entera del numerador es pequeña, por ejemplo:

Listado 2.

SELECT
   19.76110000000000 / 2.2200
FROM
   RDB$DATABASE

La parte entera del numerador es 19 y allí sí funciona.

¿Por qué funciona cuándo el numerador es pequeño?

Si la precisión es 18 entonces el número se guarda como un BIGINT. Los números de tipo BIGINT pueden encontrarse entre -2 * 10 ^ 63 y 2 * 10 ^ 63 – 1. O sea, entre -9.223.372.036.854.775.808 y +9.223.372.036.854.775.807

Si la escala es 18 entonces el Firebird divide a ese número por 1.000.000.000.000.000.000 o sea un 1 seguido por 18 ceros.

Al dividir 9.223.372.036.854.775.807 por 1.000.000.000.000.000.000 obtenemos: 9,223372036854775807

Por lo tanto, si la precisión es 18 y la escala es 18 (como en el Listado 1.) el resultado de la división debe ser 9,22 ó menor para no obtener un error de overflow.

Pero en el Listado 1. el resultado de la división es 86,829321…., ese número es mayor que 9,22 y entonces lo que se obtiene es un … error de overflow.

¿Y si necesitamos hacer sí o sí la división del Listado 1., cómo lo solucionamos?

Tenemos varias alternativas:

Listado 3.

SELECT
   192.7611 / 2.2200
FROM
   RDB$DATABASE

Le quitamos los ceros al numerador y funciona sin problemas porque ahora la escala es de 8.

Listado 4.

SELECT
   192.76110000000000 / 2.22
FROM
   RDB$DATABASE

Le quitamos los ceros al denominador y funciona sin problemas porque ahora la escala es de 16.

Listado 5.

SELECT
   CAST(192.76110000000000 AS NUMERIC(18, 13)) / 2.2200
FROM
   RDB$DATABASE

Empleamos la función CAST() para disminuir la escala del numerador y funcionó sin problemas.

Listado 6.

SELECT
   CAST(192.761100000000000 AS FLOAT) / 2.2200
FROM
   RDB$DATABASE

Empleamos la función CAST() para cambiar el tipo de datos del numerador a FLOAT y funcionó sin problemas.

Conclusión:

Tal y como se dijo en el artículo antes citado: “La precisión del resultado siempre es 18. La escala del resultado es la suma de las escalas del dividendo y del divisor. Esto implica que la escala del cociente siempre será mayor que las del dividendo y del divisor. Debes tener cuidado con esto si los números que empleas tienen muchos decimales o podrías sobrepasar la escala máxima que es 18 o necesitar una precisión mayor que 18 y en ambos casos eso te ocasionaría un error de overflow.”

Lo importante a recordar aquí por lo tanto es que si divides números que tienen muchos decimales puedes sobrepasar la escala máxima de 18 o necesitar una precisión mayor que 18 y en ambos casos tendrás un error de overflow.

Hay varios métodos para realizar la división de todos modos, algunos de los cuales vimos en los últimos listados de arriba (hay varios más, pero ya tienes la idea).

Artículos relacionados:

Usando NUMERIC y DECIMAL

El índice del blog Firebird21

El foro del blog Firebird21

Respuestas a más preguntas sobre transacciones (4)

1 comentario

¿Estás respondiendo las preguntas sobre transacciones?

Si lo estás haciendo, felicitaciones, si no lo estás haciendo, …. deberías, para aprender más.

Veamos cuales son las respuestas correctas del artículo: Más preguntas sobre transacciones (4)

Pregunta 1. Si una transacción solamente hará SELECTs ¿cuál debe ser su acceso?

[READ ONLY]     [READ WRITE]     [Es indiferente]

La respuesta correcta: Es indiferente. ¿Por qué? porque en ambos casos podrá hacer los SELECTs, sin embargo es preferible que sea READ ONLY porque las transacciones READ ONLY terminan más rápido que las transacciones READ WRITE debido a que el Firebird realiza menos tareas, pero no es obligatorio que sea READ ONLY.

Pregunta 2. Si una transacción solamente hará INSERTs ¿cuál debe ser su acceso?

[READ ONLY]     [READ WRITE]     [Es indiferente]

En este caso la transacción debe ser READ WRITE porque no se puede realizar un INSERT (ni un UPDATE, ni un DELETE) en las transacciones READ ONLY.

Pregunta 3. Si una transacción hará SELECTs e INSERTs, ¿cuál debe ser su acceso?

[READ ONLY]     [READ WRITE]     [Es indiferente]

Debe ser READ WRITE sí o sí, porque no se puede realizar un INSERT (ni un UPDATE, ni un DELETE) en una transacción READ ONLY.

Pregunta 4. Si una transacción quiere conocer lo que otras transacciones que finalizaron con COMMIT hicieron. ¿Cuál debe ser su aislamiento?

[READ COMMITTED]     [SNAPSHOT]     [Es imposible, nunca puede conocer lo que otras transacciones hicieron]

El aislamiento READ COMMITTED permite conocer lo que otras transacciones que ya finalizaron con un COMMIT hicieron. El aislamiento SNAPSHOT no permite tal cosa.

Pregunta 5. Si una transacción T1 ha bloqueado a una fila y una transacción T2 quiere actualizar a esa misma fila. ¿Cuál debe ser el modo de bloqueo de la transacción T2 para que pueda hacerlo?

[WAIT]     [NO WAIT]     [Cualquiera, es indiferente]     [Nunca la transacción T2 podrá actualizar esa fila]

Debe ser WAIT y entonces la transacción T2 esperará hasta que la transacción T1 finalice. Si el modo de bloqueo de la transacción T2 es NO WAIT entonces inmediatamente obtendrá una excepción y nunca podrá actualizar a esa fila.

Pregunta 6. Si una transacción T1 ha bloqueado a una fila, y quieres que la transacción T2 pueda ver la última versión confirmada de esa fila. ¿Cuál versión de registro debe tener la transacción T2?

[RECORD_VERSION]     [NO RECORD_VERSION]     [Es indiferente]

Debes usar RECORD_VERSION, porque en este caso la transacción T2 podrá ver la última versión confirmada de la fila. Si usas NO RECORD_VERSION entonces la fila que actualizó la transacción T1 será inaccesible para la transacción T2, inclusive para lectura.

Pregunta 7. Si una transacción T1 ha bloqueado una fila y no quieres que la transacción T2 pueda ni siquiera ver la última versión confirmada de esa fila ¿Cuál versión de registro debe tener la transacción T2?

[RECORD_VERSION]     [NO RECORD_VERSION]     [Es indiferente]

Debe ser NO RECORD_VERSION porque en este caso si una fila está bloqueada por una transacción las demás transacciones no pueden acceder a esa fila, ni siquiera para lectura.

Pregunta 8. Si una transacción quiere tener acceso exclusivo a todas las tablas que utiliza ¿puede hacerlo?

[Sí]     [No]

La respuesta es . En ese caso su aislamiento debe ser SNAPSHOT TABLE STABILITY. Si la transacción pudo iniciarse (no siempre podrá, otras transacciones podrían impedírselo) entonces tendrá acceso exclusivo.

Pregunta 9. Si una transacción quiere tener acceso exclusivo solamente a algunas de las tablas que utiliza ¿puede hacerlo?

[Sí]     [No]

La respuesta es . En ese caso debe escribir RESERVING y la lista de las tablas a las que quiere acceder de forma exclusiva.

Artículos relacionados:

Entendiendo a las transacciones

3 preguntas sobre transacciones

Respuestas a las 3 preguntas sobre transacciones

Más preguntas sobre transacciones (2)

Respuestas a más preguntas sobre transacciones (2)

Más preguntas sobre transacciones (3)

Respuestas a más preguntas sobre transacciones (3)

Más preguntas sobre transacciones (4)

El índice del blog Firebird21

El foro del blog Firebird21

Creando una subconsulta que devuelva muchas columnas

6 comentarios

A veces necesitamos tener en un SELECT varias columnas que provienen de la misma subconsulta. Hay dos formas de conseguir algo así: la forma mala y la forma buena. He visto que muchas personas usan la “forma mala”, así que ahora explicaré la “forma buena”.

Veamos el caso:

Listado 1. La “forma mala”

SELECT
   MiColumna1,
   MiColumna2,
   (SELECT MiSubColumna1 FROM MiTabla2 JOIN MiEnlace WHERE MiCondición) AS MiColumna3,
   (SELECT MiSubColumna2 FROM MiTabla2 JOIN MiEnlace WHERE MiCondición) AS MiColumna4,
   (SELECT MiSubColumna3 FROM MiTabla2 JOIN MiEnlace WHERE MiCondición) AS MiColumna5
FROM
   MiTabla1

Si te fijas en las subconsultas del Listado 1. verás que son muy, muy, similares, la única diferencia es la columna que devuelven a la consulta principal, el resto es idéntico.

¿Cuál es el defecto de hacerlo así?

Que la subconsulta se ejecuta muchas veces (3 veces en el Listado 1., pero en otros casos podría ser más) y eso puede llegar a ser muy lento en tablas grandes.

Además, si la consulta es muy larga, deberás escribir mucho y te complicará la lectura.

Así que veamos una forma alternativa (y mejor) de obtener el mismo resultado:

Listado 2. La “forma buena”

WITH MiSubConsulta AS (
   SELECT
      MiSubColumna1,
      MiSubColumna2,
      MiSubColumna3
   FROM
      MiTabla2
   JOIN
      MiEnlace
   WHERE
      MiCondición
)

SELECT
   T1.MiColumna1,
   T1.MiColumna2,
   T2.MiSubColumna1 AS MiColumna3,
   T2.MiSubColumna2 AS MiColumna4,
   T2.MiSubColumna3 AS MiColumna5
FROM
   MiTabla1      T1
LEFT JOIN
   MiSubConsulta T2
      ON T1.MiColumna1 = T2.MiSubColumna1

¿Cuáles son las ventajas de hacerlo así?

  1. La tabla virtual CTE (es virtual porque solamente existe en la memoria de la computadora) es creada una sola vez y sus columnas pueden ser usadas muchísimas veces. En este ejemplo sus columnas (llamadas MiSubColumna1, MiSubColumna2, y MiSubColumna3) fueron usadas 3 veces pero en otros casos podrían usarse muchas más veces. Como se la crea una sola vez eso es mucho más rápido que crearla 3 veces que era el caso en el Listado 1.
  2. Es muy fácil de entender. Si miramos a la subconsulta muy fácilmente entenderemos lo que hace.

¿Qué fue lo que hicimos?

  1. Creamos una tabla virtual llamada “MiSubConsulta” (el nombre puede ser cualquiera, “MiSubConsulta” es solamente un ejemplo)
  2. En la tabla virtual colocamos todas las columnas que nos interesan
  3. En el SELECT principal hicimos un LEFT JOIN a la tabla virtual y por eso ya pudimos acceder a todas sus columnas. Si la condición de enlace no se cumple entonces tendremos NULL en las columnas que provienen de la tabla virtual.

Observación: La condición de enlace del Listado 2. (T1.MiColumna1 = T2.MiSubColumna1) es solamente un ejemplo, allí tú pondrás la condición de enlace adecuada a tu caso.

Conclusión:

Si necesitamos tener varias subconsultas en la lista de columnas de un SELECT y esas subconsultas son muy similares lo mejor es crear una tabla CTE (o sea, una tabla virtual) y así conseguiremos una mejor velocidad de respuesta y también que nuestro código sea más fácil de entender.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Older Entries