Indexando una columna computada

Deja un comentario

Como sabes, las columnas computadas son muy útiles para ver su contenido en los SELECTs y también cuando las usamos en la cláusula WHERE (de un UPDATE, de un DELETE, o de un SELECT) o en la cláusula ORDER BY.

Ya las habíamos visto en varios artículos anteriormente, ahora veremos una de sus características más interesantes: pueden ser indexadas.

Las primeras versiones del Firebird no lo permitían, pero a partir de la versión 2.0 ya tenemos esa posibilidad.

Por ejemplo, la tabla de SUCURSALES tiene estos datos:

INDICE1

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

Como podemos ver, algunos nombres están en mayúsculas y algunos nombres están en minúsculas. Podríamos crear el siguiente índice:


CREATE INDEX IDX_SUCURSALES1 ON SUCURSALES COMPUTED BY (UPPER(SUC_NOMBRE));

COMMIT;

Y luego usarlo en comandos como los siguientes:


SELECT * FROM SUCURSALES ORDER BY UPPER(SUC_NOMBRE)

INDICE2

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

Aquí, los nombres de las sucursales aparecen ordenados alfabéticamente y al mirar el PLAN vemos que efectivamente se usó el índice recientemente creado.


SELECT * FROM SUCURSALES WHERE UPPER(SUC_NOMBRE) STARTING WITH 'BOG'

INDICE3

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

Aquí le pedimos que nos muestre los nombres de todas las sucursales que empiezan con las letras ‘BOG’ y vemos que también usó el índice computado que habíamos creado anteriormente.


DELETE FROM SUCURSALES WHERE UPPER(SUC_NOMBRE) = 'MIAMI - ESTADOS UNIDOS'

INDICE4

También en este caso se usó el índice de la columna computada, como podemos comprobarlo mirando el PLAN.

Otros ejemplos:


CREATE INDEX IDX_MOVIMCAB1 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX))

CREATE INDEX IDX_MOVIMCAB2 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX) || MVC_NRODOC)

CREATE DESCENDING INDEX IDX_MOVIMCAB3 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX) || MVC_NRODOC)

El índice IDX_MOVIMCAB1 de la tabla MOVIMCAB (cabecera de movimientos) la indexa según el año del movimiento.

El índice IDX_MOVIMCAB2 de la tabla MOVIMCAB (cabecera de movimientos) la indexa según el año del movimiento y el número del documento.

El índice IDX_MOVIMCAB3 de la tabla MOVIMCAB (cabecera de movimientos) es muy parecido al índice IDX_MOVIMCAB2 pero ahora el índice es descendente (es decir, de mayor a menor).

Conclusión:

Tener columnas computadas es algo muy bueno porque nos facilita la vida. Poder indexar a esas columnas computadas es algo más bueno aún.

Como hemos visto en los ejemplos anteriores, el Firebird usará a los índices de las columnas computadas cada vez que los necesita.

Esos índices pueden involucrar a una sola columna o a varias columnas (como en los dos últimos ejemplos donde IDX_MOVIMCAB2 y IDX_MOVIMCAB3 involucran a las columnas MVC_FECHAX y MVC_NRODOC).

Artículos relacionados:

Columnas computadas

Un truco para encontrar valores que pueden estar en varias columnas

Usando un SELECT en una columna computada

Utilizando columnas computadas

Algunos ejemplos de uso de las columnas computadas

Usando un PLAN

Algo más sobre PLAN

Entendiendo el contenido de un PLAN

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Funciones útiles con los conjuntos de caracteres

4 comentarios

El Firebird dispone de 3 funciones que podemos usar cuando estamos tratando con conjuntos de caracteres, ellas son:

BIT_LENGTH() que nos devuelve la longitud de un string en bits

CHAR_LENGTH() que nos devuelve la longitud de un string en caracteres

OCTECT_LENGTH() que nos devuelve la longitud de un string en bytes

La diferencia entre ellas es notoria cuando se usan distintos conjuntos de caracteres, como en los ejemplos que vemos a continuación:

Ejemplo 1. Creando una Base de Datos que por defecto usa ISO8859_1

CHARSET1

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

Ahora le agregamos una tabla llamada TEST:

CREATE TABLE TEST (
   NOMBRE VARCHAR(40));

Y luego le insertamos algunas filas a esta tabla:

INSERT INTO TEST (NOMBRE) VALUES ('aeiou');
INSERT INTO TEST (NOMBRE) VALUES ('áéíóú');
INSERT INTO TEST (NOMBRE) VALUES ('HOLA');
INSERT INTO TEST (NOMBRE) VALUES ('HOLAaeiou');
INSERT INTO TEST (NOMBRE) VALUES ('HOLAáéíóú');

A continuación usamos las 3 funciones anteriores para ver los resultados que obtenemos:

SELECT
   NOMBRE,
   BIT_LENGTH(NOMBRE),
   CHAR_LENGTH(NOMBRE),
   OCTET_LENGTH(NOMBRE)
FROM
   TEST

CHARSET3

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

Lo que podemos notar es que usando el conjunto de caracteres ISO8859_1 siempre CHAR_LENGTH es igual a OCTET_LENGTH y que al multiplicar a cualquiera de ellos por 8 obtenemos BIT_LENGTH.

Ejemplo 2. Creando una Base de Datos que por defecto usa UTF8

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

Ahora le agregamos una tabla llamada TEST:

CREATE TABLE TEST (
   NOMBRE VARCHAR(40));

Y luego le insertamos algunas filas a esta tabla:

INSERT INTO TEST (NOMBRE) VALUES ('aeiou');
INSERT INTO TEST (NOMBRE) VALUES ('áéíóú');
INSERT INTO TEST (NOMBRE) VALUES ('HOLA');
INSERT INTO TEST (NOMBRE) VALUES ('HOLAaeiou');
INSERT INTO TEST (NOMBRE) VALUES ('HOLAáéíóú');

A continuación usamos las 3 funciones anteriores para ver los resultados que obtenemos:

SELECT
   NOMBRE,
   BIT_LENGTH(NOMBRE),
   CHAR_LENGTH(NOMBRE),
   OCTET_LENGTH(NOMBRE)
FROM
   TEST

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

Y ahora lo que notamos es que CHAR_LENGTH es igual a OCTET_LENGTH solamente cuando se usan los caracteres ASCII, es decir cuando no hay vocales acentuadas. En cambio cuando hay vocales acentuadas cada una de ellas usa 2 octetos (ó 2 bytes, son sinónimos). En la última fila podemos ver que hay 9 caracteres pero que se necesitan 14 bytes para almacenarlos ¿por qué? porque cada letra no acentuada ocupa 1 byte y cada letra acentuada ocupa 2 bytes. Entonces, la palabra “HOLA” se guarda en 4 bytes y las vocales “áéíóú” se guardan en 10 bytes. La suma da 14 bytes.

En todos los casos BIT_LENGTH es igual a OCTET_LENGTH multiplicado por 8. Eso es lógico, ya que cada octeto (o byte, son sinónimos) tiene 8 bits.

Conclusión:

Como se puede ver en los ejemplos anteriores, si todo el texto que se introducirá en las columnas de tipo CHAR o VARCHAR estará en alguno de los idiomas europeos occidentales (español, portugués, francés, inglés, alemán, etc.) entonces debemos crear a nuestra Base de Datos con el conjunto de caracteres ISO8859_1 porque si usamos UTF8 cada vocal acentuada y cada letra eñe ocupará 2 bytes, en lugar de solamente 1 byte. Y por lo tanto, con ISO8859_1 se ahorra espacio y con UTF8 se desperdicia espacio.

¿Cuándo usaríamos UTF8? Cuando el texto puede estar escrito también con otros lenguajes europeos, como el checo, el polaco, el ruso, el ucraniano, etc. Si ese no es el caso, lo mejor es usar ISO8859_1 porque se ahorrará espacio en el disco duro.

Artículos relacionados:

Entendiendo a los conjuntos de caracteres

Algo más sobre los conjuntos de caracteres

Entendiendo COLLATE

Consultando sin importar mayúsculas ni acentos

El índice del blog Firebird21

El foro del blog Firebird21

SET AUTODDL ON

3 comentarios

Las siglas DDL significan: Data Definition Language o en castellano: Lenguaje para la Definición de Datos.

Entonces, al escribir el comando SET AUTODDL ON le estamos indicando al Firebird que queremos que realice un COMMIT automático a continuación de cada instrucción DDL.

¿Cuáles son las instrucciones DDL?

Todas las que empiezan con las palabras: CREATE, ALTER, DROP, DECLARE, RECREATE, SET

Sirven para crear tablas, índices, stored procedure, triggers, etc. También para realizar modificaciones, borrados, declaraciones.

Ejemplos: CREATE TABLE, CREATE INDEX, ALTER TABLE, DROP INDEX

¿Cuál es la ventaja de escribir SET AUTODDL ON?

Que no necesitaremos escribir un COMMIT a continuación de las instrucciones DDL

¿Dónde se usa SET DDL ON?

En el programa ISQL.EXE, en los scripts, y en algunos otros programas que implementan esta funcionalidad.

Ejemplo:

Queremos agregarle la tabla BANCOS a nuestra Base de Datos mediante un script:

/* Al poner AUTODDL en ON no necesitamos escribir los COMMIT */

SET AUTODDL ON;

/* Primero, creamos la tabla BANCOS, sin un COMMIT al final del CREATE */

CREATE TABLE BANCOS (
   BAN_IDENTI D_IDENTIFICADOR DEFAULT 0 NOT NULL,
   BAN_NOMBRE D_NOMBRE40 NOT NULL);

/* Después, creamos las restricciones Primary Key y Unique Key, sin COMMIT */

ALTER TABLE BANCOS ADD CONSTRAINT PK_BANCOS PRIMARY KEY (BAN_IDENTI);

ALTER TABLE BANCOS ADD CONSTRAINT UQ_BANCOS UNIQUE (BAN_NOMBRE);

/* A continuación creamos un trigger para tener un identificador que sea autoincremental */

SET TERM ^ ;

CREATE TRIGGER BI_BANCOS_BAN_IDENTI FOR BANCOS
   ACTIVE BEFORE INSERT
   POSITION 0
AS
BEGIN
   IF (NEW.BAN_IDENTI IS NULL OR NEW.BAN_IDENTI = 0) THEN
      NEW.BAN_IDENTI = GEN_ID(BANCOS_BAN_IDENTI_GEN, 1);
END^

SET TERM ; ^

Como puedes ver, ningún COMMIT ha sido escrito sin embargo la tabla BANCOS ha sido creada exitosamente, como también las dos restricciones y el trigger.

¿Por qué? Porque al estar AUTODDL en ON es el propio Firebird quien se encarga de poner el COMMIT luego de cada instrucción DDL.

Artículo relacionado:

El índice del blog Firebird21