Skip to content

sql

Creación y Uso de DBLinks en Oracle

OracleUn 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.

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.

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

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

De forma que pueda escribirse:

Modelado de Jerarquías Todo-Parte con Bases de Datos (Agregación)

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.

Modelo

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.

Modelado de Jerarquías Todo-Parte con Bases de Datos (Herencia)

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.

Composite

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.

Cómo elegir la clave primaria de una tabla

SQLA veces tengo la impresión de que elegir las columnas que constituirán la clave primaria de una tabla es una decisión que suele tomarse muy a la ligera, cuando la realidad es que la correcta elección de las claves primarias es un factor decisivo para poder construir un modelo relacional de base de datos bien formado, coherente, mantenible y fácilmente ampliable. Lo más irónico del asunto es que en realidad resulta una tarea muy sencilla si se comprende que ningún valor tomado del dominio que estamos intentando modelar sirve para identificar unívocamente a los registros en base de datos. Dicho de otra forma, no puede utilizarse como clave primaria una columna que contenga información que tenga significado para los usuarios; hay que crear una columna expresamente para la clave primaria. O dicho de una tercera forma, si tiene que crear una tabla de personas no utilice DNI como clave primaria.

¿Pero por qué no es una buena idea elegir DNI como clave primaria?, ¿acaso ese número no nos identifica de forma unívoca?. Pues sí, pero rotundamente NO. Piense por ejemplo que los menores de edad o los ciudadanos de otros paises no tienen normalmente un carnet de identidad, y todo ello sin mencionar además el hecho de que los números del DNI no son únicos, históricamente la misma numeración se ha reutilizado en distintas personas.

Puede que en este momento esté pensando en alternativas muy inteligentes para tratar de resolver los problemas que se plantean en el párrafo anterior, posiblemente mediante la incorporación de nuevas columnas a la clave primaria, pero créame, déjelo, el esfuerzo será inútil, la realidad es que los problemas no desaparecerán, se incrementarán. Por ejemplo, podemos pensar que podremos almacenar menores de edad en nuestra tabla si añadimos una nueva columna a la clave primaria que nos indique si en realidad el DNI es suyo o de su representante legal. Pero ocurre que una persona puede tener varios hijos, con lo cual necesitariamos otra nueva columna para distinguirlos. También podemos pensar que podremos almacenar personas de distintos paises si añadimos una nueva columna que nos indique la nacionalidad de cada persona, permitiendo así además que un mismo número se repita para personas de distintos paises. Pero ocurre que una persona puede tener varias nacionalidades. Y así, vuelta a empezar. Lo normal es que siguiendo esta línea de razonamiento se acabe necesitando facilmente 7 u 8 columnas más.

En lineas generales no se deben utilizar claves primarias compuestas en las tablas maestras, es decir, claves formadas por varias columnas en las tablas que almacenan las entidades básicas que gestiona un sistema. Complica los modelos haciéndolos difíciles de mantener y ampliar. Piense en el ejemplo anterior, en si persitieramos en nuestra idea original de utilizar DNI como clave primaria, de forma que el día de mañana nos encontráramos un problema de los antes citados, y que además lo solucionáramos añadiendo un nuevo campo a la clave primaria. Pues ocurriría que tendriamos que añadir ese nuevo campo a todas las tablas y procesos que hicieran referencia a la tabla de personas. El cambio no sería único y localizado, si no que se expandería como una enfermedad contagiosa por todas las entidades y procesos del sistema que tuvieran algún tipo de relación con la tabla de personas.

Hay que tener cuidado con las claves compuestas, sobre todo porque a veces se presentan camufladas bajo la apariencia de lo que normalmente se denominan «códigos inteligentes». Un código inteligente es la unión de varias claves en una sola columna, o sea, una clave primaria compuesta con piel de cordero. Un ejemplo típico de este tipo de código son los localizadores, como el de una entrada de cine en la que figura «S07-F12-B08», lo que vendría a significar «Sala 7» (S07) «Fila 12» (F12) «Butaca 8» (B08). Este tipo de composición se utiliza a veces para identificar las entidades por su ubicación, suponiendo el hecho de que sólo puede haber una entidad en un mismo sitio a un mismo tiempo. El principal problema que plantea este tipo de clave primaria es que a veces, en el mundo real, las entidades se extravían y acaban en localizaciones distintas a las que indican sus códigos, siendo necesario cambiar las claves primarias en la tabla principal y en todas las tablas en las que aparezcan referenciadas, lo que puede ser bastante laborioso, por no decir engorroso y proclive a errores.

El párrafo anterior nos da las dos últimas pistas definitivas para entender el por qué no hay que utilizar DNI como clave primaria de la tabla de personas. La primera pista es que una clave primaria nunca puede depender de información introducida manualmente. Si se hace depender el valor de una clave primaria de lo que escriba un usuario se está creando una dependencia brutal de éste con el funcionamiento interno del sistema. El acoplamiento entre capas de una aplicación siempre debe ser débil, de forma que se pueda modificar cada componente de forma individual sin afectar al resto, y en consecuencia, las reglas que rigen el mundo real no tienen porque influir en las que se establecen en el interior de una aplicación. Si la clave primaria de la tabla persona fuera DNI, y el día de mañana se decidiera utilizar el NIF, entonces se tendría que cambiar todo el modelo y los procesos para gestionar la clave como un campo alfanumérico en vez de como sólo nunérico. Si DNI no fuera clave entonces bastaría con cambiar ese campo concreto de forma aislada.

La segunda pista es que una clave primaria nunca ha de poder modificarse. La clave primaria de un registro identifica de forma unívoca a un objeto dentro del dominio que se está modelando. Si se cambia la clave primaria de un registro se produce una «mutación» por la cual el registro pasa a representar a otro objeto. Este aspecto quizás sea un poco sutil de entender, sobre todo por que conlleva cierto nivel de abstracción. Una persona se podría decir que es la suma de sus características individuales, así como del lugar que ocupa en cualquiera de las dimensiones en las que se proyecta (y posiblemente de su ausencia en las dimensiones en las que no se proyecta). Es una entidad, un ser único y distinguible. Su clave primaria ha de identificarlo de forma unívoca, si se cambia representaría otra entidad, no al individuo original. Piense en términos de variables, como las que se utilizan normalmente en cualquier lenguaje de programación. Primero se definen, y luego se les puede cambiar su contenido, de igual forma que una persona puede cambiarse de ropa o de peinado. Las variables siempren hacen referencia a una misma información concreta, independientemente del valor que contengan en un momento dado. Con las personas ocurre lo mismo, da igual el peinado o la ropa que llevemos, e incluso donde nos encontremos, por encima de todo seguimos siendo nosotros mismos, no otros. Si a una persona se le cambia su DNI porque se introdujo un número equivocado en el alta, debe seguir siendo la misma entidad que se insertó originalmente en el sistema, no otra distinta que implique una actualización masiva de las relaciones.

En definitiva, la forma correcta de identificar las entidades en una tabla es utilizar un código o identificador único que carezca de significado en el mundo real. Un simple valor numérico generado de forma secuencial es suficiente. La idea es crear una tabla con una primera columna de tipo numérico que sirva como clave primaria. En el ejemplo inicial, la tabla de personas, podría ser algo como: ID_PERSON NUMBER(10) NOT NULL. De forma que cuando se inserte una primera persona en la tabla se le asigne el valor 1 como clave primaria (ó 1527, es indistinto). Cuando se inserte una nueva persona se le asigne el 2 (ó 1528). Y así sucesivamente. De esta forma no hay posibilidad de conflictos, o lo que es lo mismo, de encontrarse con errores por claves primarias duplicadas. Cada persona queda emparejada con un identificador que le distingue unívocamente del resto. Y lo dicho para persona sirve para cualquier tipo de entidad. Un motor no se identifica por su número de bastidor, sino por su clave primaria. Un televisor no se identifica por su número de serie, sino por su clave primaria. Una factura no se identifica por su número, sino por su clave primaria. Un usuario no indentifica por su nombre, sino por clave primaria. ¿Captan la idea?

Esta forma de definir las claves suele crear bastante confusión en los desarrolladores no acostumbrados a esta forma de modelar. Las primeras cuestiones que se suelen plantear son la tocantes al rendimiento, sobre todo porque las búsquedas por columnas como DNI son bastantes habituales. Al dejar de ser parte de la clave primaria hay que definir índices adicionales sobre las tablas para estas columnas. Algunos diseñadores reniegan espontáneamente cuando se sugiere añadir varios índices sobre una misma tabla alegando que corresponde a un mal diseño. Pero entonces, ¿para qué diablos queremos los índices?. Otra cuestión habitual que se suele plantear es cómo generar las claves primarias. La respuesta creo que depende en gran medida de las herramientas que se utilicen para desarrollar. Oracle por ejemplo proporciona los objetos de tipo SEQUENCE, que generan valores secuenciales de forma atómica. Algunos gestores permiten indicar que las columnas sean autoincrementales, de forma que cada vez que se inserta un registro se genera automáticamente un nuevo valor. Los «gurús» suelen divagar acerca de aspectos tales como si es necesario utilizar una única secuencia para todas las entidades del sistema o una secuencia distinta para cada tabla. Lo que está claro es que nunca se debe ejecutar COUNT o MAX sobre las tablas para obtener el siguiente secuencial, son atentados directos contra la integridad y el rendimiento de la aplicación. Otra cuestión que se suele plantear es el tema del tamaño requerido por la columna de la clave primaria. Un tamaño de 10 representa una cantidad de diez billones de valores posibles, suficiente para la mayoría de aplicaciones de propósito general. Lo que hay que hacer sobre todo es que todas las tablas tengan una columna de clave primaria con el mismo tamaño, no cada una con un tamaño distinto ajustado en función de su ocupación estimada. Referente a esta última cuestión, hay que matizar que añadir una columna de tamaño diez no implica añadir automáticamente 10 bytes por registro, el espacio adicional requerido dependerá enteramente del gestor de base de datos que se esté utilizando.

Trabajar con tablas definidas de esta forma por primera vez puede resultar un tanto inquietante al principio. Puede parecer que mantener la coherencia de tantos identificadores es algo complicado, pero no lo es, de hecho, hoy en día es la forma más natural de diseñar y trabajar. Simplifica el diseño de las capas de persistencia con las que se consiguen mapear los objetos en memoria, al tiempo que ayuda a separar la información que resulta significativa para los usuarios de la forma en que se gestiona internamente. Si sus herramientas no soportan esta forma de trabajar vaya pensando seriamente en sustituirlas por otras nuevas.

Y por último, no quería dejar de comentar el hecho de que seguramente todos estos razonamientos parezcan complicaciones innecesarias para sistemas pequeños en ambientes muy controlados. Sin embargo, pensar así es olvidar una máxima informática que hay que tener siempre muy presente: ¡los requerimientos siempren cambian!

Cambiar la precisión de una columna en Oracle

SQLEl 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:

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.