Tablas Oracle: Claves naturales o claves sustitutivas

En algunas ocasiones me he encontrado con bases de datos en las que se aplica la norma de, a la hora de diseñar una tabla nueva, utilizar siempre una clave sustitutiva (surrogate key) , incluso existiendo una clave natural perfectamente aplicable. Cuando he preguntado por el motivo de crear tales claves sustitutivas, la razón ha sido casi siempre la de aumentar la eficiencia de la base de datos eliminando la posibilidad de tener que enlazar dos tablas utilizando más de una columna.

Primero aclararé, para aquellos que no lo tengan claro, el concepto de clave sustitutiva o surrogate key. Una clave sustitutiva no es más que una clave interna, un identificador único, que no tiene significado para el negocio, y que identifica de forma única un registro de una tabla de la base de datos.

Cambios de rendimiento en una sentencia SQL al activar el trazado

Hace unos días un lector del blog me enviaba un email contándome un "extraño" problema de rendimiento que tenía con una sentencia SQL o PL/SQL. Dicha sentencia SQL tardaba mucho tiempo en devolver resultados y, tras activar la utilidad de trazado SQL (SQL_TRACE=TRUE), el problema desaparecía y la respuesta de la sentencia SQL era inmediata.

La verdad es que el fenómeno no es tan extraño una vez que se conoce la causa. Cuando se activa el trazado haciendo SQL_TRACE=TRUE, lo que ocurre es que la sesión Oracle utiliza una nueva área de SQL compartido. Esto supone que el parsing (ver artículo sobre las fases durante el procesamiento de una sentencia SQL) de cualquier sentencia SQL que se ejecute después de activar el trazado vuelva a tener lugar o que, de existir una versión de dicha sentencia SQL ya parseada en la nueva área de SQL compartido, dicha versión no coincida con la versión existente cuando el trazado no está activo. Esto causa que, cuando la sentencia SQL utiliza variables (bind variables), puesto que los valores reales de dichas variables son tomados en el momento del parsing, muy probablemente, los planes de ejecución de la misma sentencia SQL sean diferentes antes y después de activar el trazado al haberse generado utilizando valores de variable distintos.

Funcionalidad errorlogging del SQL*Plus

Hasta la versión de la base de datos Oracle 10g Release 2 no era posible capturar en SQL*Plus los errores que se generan cuando escribíamos incorrectamente una sentencia SQL. Es decir, los errores conocidos como SP2 no podían ser gestionados por las funcionalidades estándar del SQL*Plus OSERROR o SQLERROR. Esto era algo normal ya que los errores SP2 no son errores del tipo OS, como el típico error "unable to open spool file", ni tampoco son errores de tipo SQL o PLSQL, ya que la sentencia que hemos escrito incorrectamente en realidad no se trata de ningún comando SQL y nunca llegó a alcanzar la capa SQL o PL/SQL de la base de datos Oracle.

Para los que todavía estén un poco confundidos y aún no hayan identificado cuáles son los errores SQL de tipo SP2 os dejo el siguiente ejemplo:

Cláusula DEFAULT para definición de columnas - Base de datos Oracle 12c

Las mejoras fundamentales que aporta la cláusula DEFAULT de la versión 12c de las bases de datos Oracle para definición de las columnas: una significativa mejora del rendimiento y una mayor facilidad para inicializar los datos de los registros de una tabla, lo que al final implica menos líneas de código.

Supongamos que para una tabla concreta necesitamos que, cuando se inserta un nuevo registro, un campo determinado tome el valor de una secuencia. La forma en que implementaríamos este requerimiento en versiones anteriores a la 12c sería mediante un trigger PLSQL.

Identificación de tablas y columnas en una base de datos Oracle mediante sentencias SQL

Una vez que una tabla Oracle es creada utilizando el comando CREATE TABLE, la estructura de dicha tabla se puede visualizar utilizando las siguientes vistas (views) del sistema de la base de datos Oracle:

DBA_TABLES: Muestra la información de la tabla a nivel de cabecera.

DBA_TAB_COLUMNS: Muestra la información de la tabla a nivel de columna.

DBA_TAB_PRIVS: Muestra los privilegios de acceso a las tablas de los usuarios de la base de datos.

DBA_COL_PRIVS: Muestra los privilegios de acceso a nivel de columna. Es bastante raro tener la necesidad de definir permisos a nivel de columna, de hecho, yo nunca me he encontrado con una bases de datos Oracle que utilice esta funcionalidad.

Cómo analizar la interdepencia entre los objetos de una base de datos Oracle

Para analizar y conocer las dependencias existentes entre funciones, procedimientos, paquetes y triggers a los que tiene acceso un usuario de la base de datos Oracle se debe utilizar la vista USER_DEPENDENCIES.

Esta vista, sin ir más lejos, se puede utilizar para realizar un análisis de nuestro código PL/SQL, permitiendo por ejemplo identificar que programas necesitan ser revisados y actualizados si realizamos algún tipo de cambio sobre una tabla determinada de la base de datos.

Tablas temporales en las bases de datos Oracle

Además de las tablas de la base de datos permanentes, Oracle permite la creación de tablas temporales para mantener datos propios y exclusivos a una sesión Oracle determinada. Estos datos permanecerán en el sistema sólo durante el tiempo que dure la transacción o sesión involucrada. No obstante, al igual que para las tablas permanentes, la definición de las tablas temporales se almacena en las tablas del sistema.

La sentencia CREATE GLOBAL TEMPORARY TABLE crea una tabla temporal Oracle cuya temporalidad puede ser definida a nivel de transacción (los datos existen mientras se realiza la transacción) o a nivel de sesión (los datos existen mientras dura la sesión). Los datos en una tabla temporal son propios y privativos de la sesión Oracle que la está utilizando. Una sesión Oracle determinada puede ver y modificar los datos que durante dicha sesión se insertaron en la tabla temporal, pero estos datos no son accesibles desde otra sesión diferente. Como es lógico, la sentencia LOCK no tiene efecto sobre las tablas temporales ya que cada sesión hace uso de sus propios datos.

Fases durante el procesamiento de una sentencia SQL

Durante el procesamiento de una sentencia SQL, ya sea mediante un script o un programa PL/SQL, se distinguen cuatro fases: análisis de la sintaxis (parsing), análisis de las variables (binding), ejecución (executing) y recuperación de datos (fetching).

Fase de parsing

Durante esta fase el servidor de la base de datos Oracle realiza las siguientes acciones:

• Busca la sentencia SQL en la memoria compartida (shared pool).
• Chequea la sintaxis de la sentencia siguiendo las especificaciones y la gramática del lenguaje SQL.
• Chequea la semántica, asegurando que los objetos Oracle referenciados en la sentencia SQL son válidos (existen en la base de datos) y satisfacen las restricciones de seguridad (es decir, el usuario que ejecuta la sentencia tienen los permisos adecuados sobre dichos objetos).
• Determina si el proceso que lanza la sentencia SQL tiene los permisos apropiados para ejecutarlo.
• Si la sentencia SQL incluye una vista (view) o una subquery (una subquery no es más que una sentencia SELECT que está dentro de otra sentencia SQL), transforma dicha sentencia en una sentencia SQL equivalente e intenta simplificar la sentencia resultante.
• Determina y almacena el plan de ejecución o, si es posible, utiliza un plan de ejecución existente.

Acceso remoto mediante DBLINK a una base de datos Oracle

La manera más sencilla de acceder desde una base de datos Oracle a tablas y vistas (views) de otra base de datos Oracle es mediante el uso de un DBLink (enlace a base de datos). No obstante, en muchos casos puede no ser recomendable la utilización de DBLinks, ya que el acceso a tablas y vistas remotas puede provocar importantes problemas de rendimiento en ambas bases de datos, tanto la remota como la local. En la mayoría de los casos estos problemas de rendimiento se deben a la imposibilidad de utilizar los índices de las tablas remotas.

Los DBLinks se crean en la base de datos local utilizando el comando PL/SQL o SQL CREATE DATABASE LINK. Obviamente, el usuario que ejecute dicho comando debe tener los permisos necesarios para poder hacerlo. La sintaxis del comando SQL mencionado es como sigue:

Nuevos tipos de datos PL/SQL soportados desde la versión 12c al asociar datos vía SQL (SQL binding)

Anteriormente a la versión 12c de la base de datos Oracle, siempre que era necesario asociar una variable a una expresión PL/SQL mediante el uso de la sentencia EXECUTE IMMEDIATE o del paquete DBMS_SQL, el tipo de datos PLSQL de dicha expresión debía ser un tipo de dato SQL permitido. Concretamente, no era posible asociar datos de tipo BOOLEAN, ni tampoco tipos de datos definidos por el usuarios que hubiesen sido declarados en la especificación de un paquete PL/SQL, incluyendo registros y colecciones.

En la versión 12c se han eliminado prácticamente todas las restricciones de este tipo. Ahora, por ejemplo, se pueden asociar datos de tipo BOOLEAN para ejecutar un bloque de PLSQL dinámico con el comando EXECUTE INMEDIATE. Además, también se pueden asociar matrices asociativas y utilizarlas dentro de una llamada al operador TABLE. Ambas cosas no estaban soportadas por versiones anteriores a la 12c.

Cómo obtener información sobre los objetos almacenados en las bases de datos Oracle

Ya hemos hablado en un artículo anterior sobre el diccionario de datos de las bases de datos Oracle, la vista USER_OBJECTS de dicho diccionario contiene un registro por cada objeto de la base de datos del que somos propietarios (o mejor dicho, del que es propietario el esquema con el que estamos conectados a dicha base de datos.

USER_OBJECTS

Las columnas que se utilizan de forma más común dentro de esta vista son:

• OBJECT_NAME: nombre del objeto.
• OBJECT_TYPE: tipo de objeto (toma valores como PACKAGE (paquete PLSQL), PROCEDURE (procedimiento), FUNCTION (función) o TRIGGER.
• STATUS: muestra el estado del objeto que puede ser VALID (válido) o INVALID (inválido).
• LAST_DDL_TIME: indica la última vez que el objeto fue cambiado.

Asignación de permisos (ROLES) a los programas PLSQL (mejoras versión 12c)

Con anterioridad a la versión 12c de la base de datos Oracle, un procedimiento PLSQL o unidad de programa con privilegios definidos a través de la cláusula AUTHID DEFINER siempre se ejecutaba con los privilegios del propietario del programa. Por otro lado, cuando la misma unidad de programa se definía utilizando la cláusula AUTHID CURRENT_USER, dicho programa siempre se ejecutaba con los privilegios del usuario que lo ejecutaba.

Disponer de solo estas dos formas de utilización de la cláusula AUTHID limitaba bastante la funcionalidad de las bases de datos Oracle en cuestión de seguridad, ya que cuando un usuario necesitaba ejecutar un programa PL/SQL determinado, dicho usuario tenía que tener los mismos privilegios que el propietario del programa. El problema de seguridad se agravaba mucho más cuando eran todos los usuarios los que necesitan tener acceso a dicho programa.

Creación diferida de segmentos (Deferred Segment Creation), funcionalidad de la release 2 de Oracle 11g

En las versiones anteriores a la release 2 de la base de datos Oracle 11g, cuando se creaba cualquier objeto en la base de datos utilizando una sentencia SQL o PL/SQL, ya fuera un tabla, un índice o cualquier otro objeto que requiriese ser almacenado, el gestor de la base de datos creaba los segmentos necesarios y asignaba un tamaño inicial a los mismos, un tamaño que podía ser pequeño, de unos 64 Kbytes mínimo, pero dicho espacio ya no podía ser utilizado para otras necesidades. En los tiempos actuales, un tamaño de 64 Kbytes no es nada, pero si algo tan pequeño se tienen que repetir muchas veces, entonces el consumo de recursos de almacenamiento puede llegar a ser bastante grande.

Por ejemplo, podemos pensar en alguna aplicación que necesitase crear cientos o miles de tablas, tablas que nunca van a ser utilizadas pero cuyos segmentos de almacenamiento tienen que ser creados de todas formas. Algunos pueden preguntarse por qué un aplicación puede necesitar crear tablas que nunca van a ser utilizadas, este hecho no es tan extraño ya que existen muchas aplicaciones, como por ejemplo Oracle Financials, que ofrecen funcionalidades que requieren el uso de determinadas tablas y dichas tablas sólo contienen datos cuando se hace uso de dichas funcionalidades. Para evitar el consumo de almacenamiento en este tipo de situaciones, la release 2 de la base de datos Oracle 11g incorpora una nueva funcionalidad conocida como creación diferida de segmentos (Deferred Segment Creation), funcionalidad que a continuación explicaremos y analizaremos.

Uso de ediciones para actualizar la base de datos

Desde mi punto de vista, la funcionalidad Edition-Based Redefinition, cuya traducción directa sería "redefinición basada en ediciones", se trata de la característica más interesante de la release 2 de la base de datos Oracle 11g. En pocas palabras se trata de la posibilidad de actualizar nuestra aplicación PL/SQL sin tener que poner en modo restringido la base de datos, es decir, a partir de esta versión de la base de datos Oracle es posible realizar online la actualización de nuestra aplicación. Explicado de esta manera tan sencilla la mencionada funcionalidad suena como si se ratase de un juego de niños, pero en realidad es algo cuya verdadera dimensión resulta difícil de medir por sus enormes implicaciones.

Cláusula WITH FUNCTION (mejoras en la versión 12c de la base de datos Oracle)

Arquitectura de la version 12c de bases de datos Oracle

Uso de funciones PLSQL dentro de una sentencia SELECT

Ya hace bastante tiempo que los programadores de bases de datos Oracle podemos llamar a nuestras propias funciones PL/SQL desde una sentencia SQL. Por ejemplo, supongamos que definimos la función PORCENTAJE que realiza una serie de cálculos para devolver un porcentaje. La función puede ser algo tan sencillo como:

FUNCTION porcentaje (
  val1 IN NUMBER,
  val2 IN NUMBER
)
RETURN NUMBER
IS
BEGIN
  RETURN (val1*100/(val1+val2));
END;

Memoria PGA frente a espacio de almacenamiento temporal en las bases de datos Oracle

Recientemente he recibido en este blogs sobre programación PL/SQL algunas preguntas referentes a las diferencias entre el tipo de operaciones que la base de datos Oracle realiza en la memoria del área global de programa (PGA - Program Global Area) frente a las que realiza en el espacio del almacenamiento temporal (TEMP space). Primero os comentaré que las versiones antiguas de las bases de datos Oracle solían utilizar los parámetros SORT_AREA_SIZE y HASH_AREA_SIZE para controlar cuanta memoria PGA era posible utilizar antes de empezar a tener que usar el espacio de disco temporal (TEMP).

No obstante en las nuevas versiones de las bases de datos Oracle, la gestión de la memoria PGA se realiza de forma automática y mucho más dinámica.

Objetos bloqueados en una base de datos (2)

Esta nota es continuación del anterior post Objetos bloqueados en una base de datos (1). Así pretendo dejar zanjado el tema referente a la información almacenada en la tabla V$LOCK.

Tipos de locks y las columnas ID1 e ID2

En nuestro ejemplo de la nota anterior, ya sabemos que nuestro lock es un lock producido por una sentencia DML (sentencias de manipulación de datos como select, insert, update, delete, etc), pero esto es porque fuimos nosotros los que ejecutamos la sentencia bloqueadora. Evidentemente este no va a ser siempre el caso, ya que una base de datos es normalmente compartida por multitud de usuarios. Afortunadamente, podemos encontrar la información que necesitamos en la tabla V$LOCK.

El diccionario de datos PL/SQL en las bases de datos Oracle

Si habéis llegado hasta este artículo es muy probable que tengáis que escribir en algunas ocasiones código PLSQL. Esto quiere decir que también, al menos ocasionalmente, necesitaréis analizar dicho código contestando cuestiones como:

• ¿De qué objetos de la base de datos depende mi programa?
• ¿Cuáles de mis paquetes PL/SQL contienen llamadas a subprogramas en otros paquetes o referencias a variables globales?
• ¿Contiene alguno de mis subrutinas PLSQL parámetros con tipos de datos que no se deberían seguir utilizando?
• ¿Están todo mi código compilado con el suficiente nivel de optimización?

Obviamente, siempre podréis utilizar las funcionalidades de búsqueda de vuestro editor PL/SQL o de vuestro sistema integrado de desarrollo para navegar a través de los múltiples objetos y ficheros de la base de datos Oracle tratando de encontrar específicos pedazos de texto. Pero esto no sería suficiente para poder contestar todas las preguntas anteriores y algunas más que os pudieran surgir. Con este artículo y algunos más que escribiré más adelante podréis conocer las respuestas a dichas preguntas, y para ello hay que conocer en que consiste el diccionario de datos PLSQL de las bases de datos Oracle.

Cómo obtener información sobre los procedimientos, funciones y triggers PLSQL

Obtener información sobre Procedimientos y Funciones PL/SQL

La vista USER_PROCEDURES proporciona información sobre todas la funciones y procedimientos dentro de nuestro esquema, tanto a nivel de esquema como aquellas que se encuentran definidas dentro de los paquetes PL/SQL.

Las columnas más significativas dentro de esta vista son:

• AUTHID: Identifica si el procedimiento o función se ejecutará con los permisos del usuario que lo llama (CURRENT_USER) o con los permisos del propietario del programa (DEFINER).
• DETERMINISTIC: Esta columna toma el valor de YES si la función se ha definido como deterministas (deterministic), lo que teóricamente significa que el valor devuelto por la función PLSQL queda unívocamente determinado dependiendo de los valores que tomen los argumentos de la función.
• PIPELINED: Cuando toma el valor de YES quiere decir que la función ha sido definida como pipelined, lo cual quiere decir que puede ejecutarse el paralelo como parte de una consulta con ejecución paralela.
• OVERLOAD: Este campo tendrá un valor numérico positivo si el programa correspondiente esta overloaded, o lo que es lo mismo, cuando dentro del mismo paquete PLSQL existen al menos dos subprogramas con el mismo nombre (como podéis ver, la palabra overloaded casi nada tiene que ver con su traducción directa sobrecargado).

Uso de Rollback Segments por sentencias SELECT

En alguna ocasión algún lector me ha preguntado, con cierta sorpresa, acerca del por qué una sentencia SELECT le fallaba con el mensaje de error "No es posible ampliar el segmento de rollback" ("Unable to extend rollback segment"). La sorpresa proviene del hecho de que son muchos los desarrolladores PL/SQL los que piensan que los segmentos de rollback sólo se utilizan cuando se emplean sentencias PLSQL en las que se modifican o actualizan datos dentro de la base de datos Oracle. Bajo este tipo de pensamiento es normal que, cuando se produce el error mencionado anteriormente al ejecutar una sentencia SELECT, uno se pregunte: ¿utiliza la base de datos Oracle segmentos de rollback al ejecutar sentencias SELECT?

Bueno, en mi opinión, lo primero que hay que hacer es reformular la pregunta y cambiarla por la siguiente: ¿una sentencia SELECT necesita crear o leer segmentos de rollback? La pregunta formulada de esta manera seguro que nos ayudará a comprender mejor este artículo, ya que el verbo "utilizar" usado en la primera pregunta no es lo suficientemente específico.