Un Database Link (DBLink) en Oracle es un tipo de objeto que permite realizar una conexión desde una base de datos a otra. Su principal objetivo es ocultar el detalle de los parámetros de conexión necesarios, facilitándonos un sencillo acceso a los recursos disponibles en otras bases de datos, independientemente de que estas se encuentren instaladas en el mismo servidor o no.

Aunque es un concepto sencillo de entender, suele generar bastante confusión cuando uno se tropieza con él por primera vez, sobre todo porque hay varios elementos involucrados en su creación y suelen plantearse dudas acerca de en qué base de datos concreta, de entre las dos que se quieren conectar, tienen que crearse dichos elementos.

Supongamos que tenemos dos bases de datos: productos y usuarios. La base de datos de productos almacena el inventario de una empresa, con los detalles de los artículos que oferta y entre los que se incluye el tipo de público al que se dirige cada artículo en particular. Por su parte la base de datos de usuarios contiene un ficha detallada de cada uno de los usuarios registrados en una web en la que se incluye la edad, sexo y lugar de residencia. En un determinado momento, la web decide realizar un estudio para comprobar que producto dentro de los ofertados por la empresa serían del agrado de sus usuarios en función de sus datos personales. Para realizar tal estudio decide contrastar la información de las dos bases de datos, y para ello deciden utilizar un DBLink que permita consultar datos de los productos desde la base de datos de usuarios. ¿Cuales serían los pasos a seguir?

En primer lugar, en la base de datos de productos, debería crearse un usuario nuevo, que será el que se utilice para acceder a los datos de productos. Dicho usuario será un usuario normal de Oracle, y debería tener como mínimo permiso para conectar e iniciar sesión en la base de datos de productos, y por supuesto para acceder a los objetos (tablas, vistas, …) que contengan los datos que se quieren poder consultar desde la base de datos de usuarios. En un caso bastante típico de este tipo de accesos lo que se hará será crear un role para aglutinar en él los permisos, en vez de asignárselos directamente al usuario, y crear vistas específicas para los datos que deben poder consultarse, en vez de permitir que pueda accederse directamente a toda la base de datos.

CREATE ROLE rol;
GRANT CONNECT TO rol;
GRANT SELECT ON vista1 TO rol;
GRANT SELECT ON vista2 TO rol;
CREATE USER usuario IDENTIFIED BY clave;
GRANT rol TO usuario;

Y en segundo lugar, en la base de datos de usuarios, deberá crearse el database link para acceder a la base de datos de productos utilizando el usuario y clave recién creados en la otra base de datos.

CREATE PUBLIC DATABASE LINK db_productos
CONNECT TO usuario
IDENTIFIED BY clave
USING 'connect_string_productos';

Una vez creado el DBLink puede empezar a usarse sin más en la base de datos de usuarios con la siguiente sintaxis:

SELECT * FROM vista1@db_productos;

Aunque para simplificar las sentencias se puede crear un sinónimo en la base de datos de usuarios:

CREATE PUBLIC SYNONYM vista1_productos FOR vista1@db_productos;

De forma que pueda escribirse:

SELECT * FROM vista1_productos;

Continuando con la disertación de ayer acerca del modelado de una jerarquía, hoy voy a hablar de la implementación de la relación de agregación.

En el sistema de archivos que ponía ayer como ejemplo, la jerarquía en forma de árbol es bastante sencilla de implementar, ya que todos los elementos tienen un único padre y no se permiten ciclos. Para modelar tal tipo de relación basta con que cada elemento guarde una referencia a su padre. La cuestión a considerar es si resulta más conveniente añadir un nueva columna a las tablas de entidades o crear tablas aparte para guardar esas relaciones.

Si se decidiera modelar la herencia con dos tablas, entonces cada una de ellas debería tener una columna que hiciera referencia a la tabla de directorios para indicar cual es su padre; la tabla de ficheros haría referencia a la tabla de directorios, y la tabla de ficheros haría una auto-referencia a sí misma. Por el contrario, si se decidiera modelar la herencia con una sola tabla, entonces sólo existiría una auto-referencia a la propia tabla. En ambos casos la única excepción sería el nodo raíz de la jerarquía, que no tiene padre, y que por lo tanto obligaría a dejar vacía la columna y permitir que fuera NULL.

Las soluciones del párrafo anterior presentan una serie de inconvenientes. La primera, y que debería llamar enseguida nuestra atención, es el hecho de tener que dejar que la columna pueda tomar valor NULL. Hay algunos diseñadores que opinan que por lo general no es recomendable diseñar tablas con columnas que permitan valores nulos. Sobre todo si, como en este caso, lo que se quiere indicar con el valor nulo es que no existe una determinada relación entre entidades. La forma más correcta de modelar relaciones es a través de tablas intermedias que reflejen tal relación. Si no existe una determinada relación, entonces simplemente no existirá el registro correspondiente en la tabla de relación.

Otro problema que se deriva de las soluciones anteriores, si se modela la herencia con dos tablas, es que la jerarquía se distribuye en dos tablas en vez de una. Si con el tiempo aparecen más elementos, para los que se crearían nuevas tablas, entonces la jerarquía se iría expandiendo también por esas nuevas tablas. Hacer consultas o comprobar dependencias entre elementos sería cada vez más complicado.

Pero independientemente de que se modele la herencia con una o más tablas, el principal problema de añadir un campo en la tabla para implementar la agregación es que estamos incurriendo en el defecto de mezclar los datos con la forma en que se organizan. Si en futuro cambiara la organización, ¡y siempre cambia!, entonces el modelo dejaría de ser válido. Las entidades se deberían separar, en la medida de lo posible, de cómo se organizan. La forma en la que se organiza la información depende de muy diversos factores, la mayoría de ellos muy subjetivos. Varios departamentos dentro una misma empresa suelen tener visiones muy distintas de una misma información. La manera en la que se presenta la información no debe mandar sobre la forma en la que se almacena.

Después de todo lo anterior, debería haber quedado claro que para el ejemplo del sistema de archivos personalmente me inclino más por utilizar una única tabla para las entidades, y otra para las relaciones.

Uno de los principales desafíos que sin duda plantea este modelo es el recorrido de la tabla de relaciones. SQL no fue diseñado originalmente para realizar consultas sobre jerarquías recursivas; no permite que las filas recuperadas durante una consulta hagan referencia a otras filas concretas de esa misma consulta. No se pueden hacer recuperaciones del tipo «dame el hijo de X, y el hijo del hijo de X, y el hijo del hijo del hijo de X, ….». La solución a este inconveniente puede resolverse de dos formas. La primera es recuperando un solo nivel del árbol cada vez, y la segunda utilizando las extensiones que ofrecen algunos gestores de base de datos. Oracle por ejemplo permite realizar consultas sobre estructuras recursivas mediante el uso de cláusulas START WITH y CONNECT BY.

Por último, me gustaría comentar que a pesar de la simpleza del modelo, aún quedarían bastantes cosas por definir. La primera sería la correcta elección de tipos para las columnas, aunque a este respecto lo normal es que el proyecto (o la compañía desarrolladora) tenga criterios preestablecidos para dar uniformidad a todos los desarrollos. La segunda sería decidir la clave primaria en la tabla de relaciones; si no se permite que una entidad tenga más de un padre entonces bastaría con definir la clave primaria sobre el ID de la entidad hija, aunque es bastante probable que se tenga que definir un índice sobre el ID de padre para agilizar las búsquedas. La tercera sería tener en cuenta futuras modificaciones o ampliaciones. Un cambio bastante probable sería la necesidad de organizar la información de diversas formas, en árboles con una fisonomía distinta. Para ello bastaría con añadir una nueva columna a la tabla de relaciones que permitiese distinguir un árbol de otro dentro de la misma tabla.

Un tipo de organización que suele utilizarse con bastante frecuencia en el mundo real es la jerarquía. Por ejemplo, la mayoría tenemos un jefe del que dependemos, y este a su vez tiene otro jefe. Desde nuestro punto de vista, nuestro jefe es nuestro responsable y nosotros sus empleados. Pero desde el punto de vista de nuestro jefe, él es a su vez un empleado de su propio jefe. La característica común que compartimos dentro de la jerarquía, y que nos hace pertenecer a ella, es que en realidad todos somos empleados. Nuestro puesto concreto de trabajo simplemente determina nuestra posición dentro de la jerarquía.

Con las familias ocurre algo parecido. Los padres tienen hijos que acaban convirtiéndose a su vez en padres, al tiempo que todos también son a un mismo tiempo hermanos, primos, tíos, cuñados, suegros, abuelos, … Familiares, en resumidas cuentas.

El ejemplo paradigmático de este tipo de estructuras en informática son los sistemas de archivos, como los que utilizamos normalmente para organizar el contenido de nuestros discos duros, en donde un archivo puede ser, o bien un fichero, o bien un directorio. El quid del asunto está en que un directorio a su vez puede contener otros ficheros o directorios, lo que lo convierte por si mismo en un nuevo sistema de archivos dentro del sistema de archivos que lo contiene. La parte se convierte en un todo, de ahí el nombre de este tipo de estructuras.

Existe un patrón de diseño llamado «Composite» que aprehende completamente y de una forma muy elegante este tipo de jerarquías.

En el diagrama UML se observan tres clases. La clase «Archivo» sería la representación idealizada de todos los elementos que componen nuestro sistema, con los atributos y métodos que comparten en común, como el hecho de tener un nombre, permisos, o una series de fechas (creación, modificación, último acceso, …). La clase «Fichero» sería un elemento concreto e individual del que no pueden depender otros, como un fichero de texto o una imagen por ejemplo. Y por último, la clase «Directorio» sería una agregación de objetos heredados de la clase «Archivo», que como acabamos de ver, pueden ser ficheros individuales u otros directorios a su vez. Es esta última agregación la que proporciona la recursividad necesaria para modelar correctamente el sistema.

El modelo físico de una base de datos que implemente este patrón puede realizarse de muchas formas, pero fundamentalmente me gustaría destacar dos puntos concretos. Por una parte el modelado de la relación de herencia, y por otra parte el modelado de la relación de agregación.

La herencia se implementa tradicionalmente de dos formas. La primera de ellas consiste en crear una tabla distinta para cada entidad, lo que resultaría, siguiendo con nuestro ejemplo, en una tabla para «Fichero» y otra para «Directorio». La segunda forma consiste en crear una única tabla para todas las entidades, con una columna que permite distinguir el tipo concreto al que pertenece cada registro insertado en la misma. En nuestro ejemplo, con esta última forma de implementación, resultaría en una única tabla para «Archivo» con una columna «tipo» que indicaría si un registro corresponde a un directorio o un fichero.

Particularmente no creo que exista un criterio válido universal a través del que decidir cual de las dos soluciones anteriores es la más adecuada. Dado el dominio del problema, yo me inclinaría por la segunda alternativa, utilizando una única tabla para todos los elementos, sobre todo porque es bastante probable que en un futuro se quieran introducir más elementos de distinto tipo en la jerarquía, aparte de ficheros o directorios, como por ejemplo «accesos directos». Haciéndolo con una sola tabla se consigue un tratamiento más homogéneo de la información capturando la esencia de la relación «es un» implícita en el modelo.

Lo que debe quedar claro en cualquier caso, es que la forma en la que se implemente la herencia decidirá la forma en la que se implemente la agregación, algo de lo que hablaré en un próximo post.

El gestor de base de datos Oracle permite cambiar atributos de las columnas de las tablas sin tener que volver a recrearlas completamente. Sin embargo algunos cambios no están permitidos si las columnas a modificar ya contienen datos. Esta limitación motiva que por lo general cambios de esta naturaleza requieran una planificación previa, sobre todo si la tabla contiene un volumen muy elevado de registros. Un ejemplo típico de esto podría ser el querer cambiar la precisión de una columna de tipo NUMBER(38,2) por NUMBER(38,3) en un tabla que ya almacena varias decenas de millones de registros en un entorno de producción.

Una primera solución podría consistir en hacer un export de la tabla original, recrearla con la columna afectada ya modificada, y restaurar sus registros mediante un import. Naturalmente el principal inconveniente de este método es que fuerza una indisponibilidad del sistema durante el tiempo que se está ejecutando el proceso de exportación y exportación para garantizar la coherencia en el acceso a la información.

Una segunda solución podría consistir en crear una tabla temporal de trabajo con la misma estructura que la original pero con la precisión de la columna afectada ya modificada, y mediante un bloque de código en PL/SQL ir moviendo registros de la tabla original a la temporal. Una vez volcados todos los registros borrar la original y renombrar la temporal como si fuera la original. El principal problema de este método sería que puede llegar a requerir un tiempo largo de ejecución y un gran tamaño en los tablespaces para asegurar el éxito de la ejecución del bloque PL/SQL porque durante un instante se están duplicando efectivamente todos los registros de la tabla original, al menos que se vayan borrando de la original a medida que se mueven en la temporal provocando entonces una indisponibilidad del sistema.

Una tercera solución similar a la anterior sería utilizar un sentencia de creación que incluya una SELECT sobre la tabla original con un CAST sobre la columna a modificar, para a continuación borrar la tabla original y renombrar la temporal con su nombre definitivo:


CREATE TABLE t_temp AS
SELECT c1, CAST(c2 as NUMBER(38,3)) c2, c3 FROM t;
DROP TABLE t;
RENAME TABLE t_temp TO t;

Nuevamente los inconvenientes de esta última solución son el tiempo de ejecución y el tamaño requerido en los tablespaces.

Resumiendo, personalmente no creo que haya una solución mejor o peor, todo depende de la disponibilidad requerida para el sistema, del volumen de datos, del tiempo disponible, y de lo cómodo que nos sintamos con una solución u otra.

Actualización 20/02/2007: Corregida la síntaxis de la sentencia SQL. Gracias a Sebastian Esparllagas por hacerme notar el error.