Sentencia CASE en PL/SQL de Oracle

Las versiones de base de datos Oracle 9i y posteriores incluyen la posibilidad de utilizar la sentencia CASE dentro de una sentencia SQL (SELECT, UPDATE, etcétera). La sentencia CASE permite realizar las mismas operaciones que las sentencias de control PL/SQL IF-THEN-ELSIF-ELSE pero con la particularidad de que pueden utilizarse dentro de una sentencia SQL. La sintaxis de la sentencia CASE es como sigue:

CASE  [ expresion ]
  WHEN condicion_1 THEN resultado_1
  WHEN condicion_2 THEN resultado_2
  ...
  WHEN condicion_n THEN resultado_n
  ELSE resultado
END

La opción expresion es opcional. Se trata del valor que estaríamos comparando con la lista de condiciones (condicion_1, condicion_2, ..., condicion_n).

Las condiciones, condicion_1 a condicion_n, son analizadas en el mismo orden en que aparecen listadas y, en el momento en que una de estas condiciones se cumple como verdadera, la sentencia CASE devuelve el resultado correspondiente y deja de analizar el resto de condiciones.

Los resultados, resultado_1 a resultado_n, deben ser todos del mismo tipo de dato PLSQL. Son los valores que devuelve la sentencia CASE en el momento en que una condición se cumple como verdadera.

Si ninguna de la condiciones se cumple como verdadera, entonces la sentencia CASE devuelve el valor de la cláusula ELSE. Si la cláusula ELSE se omite y ninguna condición se cumple como verdadera, entonces la sentencia CASE devuelve el valor NULL.

En una sentencia CASE puede haber hasta 255 cláusulas WHEN-THEN-ELSE, por lo tanto podremos construir hasta 128 comparaciones.

A continuación podéis ver un par de ejemplos de sentencia SELECT que incluyen una sentencia CASE:

SELECT apellido, 
  CASE nombre
    WHEN 'Pepe' THEN 'se llama Pepe'
    WHEN 'Juan' THEN 'se llama Juan'
    ELSE 'no se llama ni Pepe, ni Juan'
  END
FROM empleados;

SELECT apellido, 
  CASE
    WHEN nombre = 'Pepe' THEN 'se llama Pepe'
    WHEN nombre = 'Juan' THEN 'se llama Juan'
    ELSE 'no se llama ni Pepe, ni Juan'
  END
FROM empleados;

Ambas sentencias SQL devolverán los mismo resultados aunque la segunda sentencia se ha escrito si utilizar la opción expresion.

La sentencia CASE admite múltiples combinaciones, por ejemplo se puede utilizar más de un campo y las condiciones que se pueden contruir pueden ser todo lo complejas que se nos ocurra. Veamos otro ejemplo:

SELECT apellido, nombre,
  CASE
    WHEN salario > pago_especie
     AND apellido < 'P' THEN 'es rico'
    WHEN salario < pago_especie
     AND apellido >= 'Q' THEN 'es pobre'
    ELSE 'no es ni pobre ni rico'
  END
FROM empleados;

En nuestro ejemplo resultaría que los empleados cuyo salarío fuera mayor que lo que reciben en especie y cuyo nombre empezase por una letra de la A a la O, serían considerados ricos, los empleados cuyo salarío fuera menor que lo que reciben en especie y cuyo nombre empezase por una letra de la Q a la Z, serían considerados pobres, y el resto de los empleados no serían considerados ni ricos ni pobres.

Artículos relacionados:
La sentencia MERGE en PL/SQL.
La cláusula PIVOT en PLSQL.
La cláusula WITH en PL/SQL.

Haz clic aquí para leer todo el artículo.

Cómo ejecutar sentencias DDL dentro de un trigger PL/SQL

Supongo que muchos conoceréis el hecho de que no es posible incluir sentencias DDL (es decir, sentencias de definición de objetos como CREATE, REVOKE, GRANT, ALTER, etcétera) dentro de un trigger PL/SQL. Esto es un hecho que tiene una explicación muy sencilla, ¿qué ocurriría si dentro de un trigger ejecutamos una sentencia no transaccional o DDL y necesitamos deshacer la transacción (rollback)? Sencillamente no podríamos deshacer la ejecución de dicha sentencia. En esta situación nuestra transacción dentro del trigger PLSQL no habría tenido lugar, pero la sentencia DDL se habría ejecutado dejando nuestra base de datos Oracle en una situación claramente no deseable.

La ejecución de sentencias no transaccionales en un trigger sólo puede derivar en problemas, así que, en el caso improbable de que necesitemos ejecutar una sentencia DDL dentro de un trigger, mi recomendación es utilizar el paquete PLSQL DBMS_JOB para programar la ejecución de dicha sentencia DDL (CREATE, REPLACE, DROP, etc.). Veamos un ejemplo:

CREATE TABLE ejecutar_DDL (
  njob NUMBER PRIMARY KEY,
  sentencia VARCHAR2(2000) );

CREATE OR REPLACE
PROCEDURE ejecutar_DDL_en_trigger
(  p_njob IN NUMBER )
IS
  l_ejecutar_DDL ejecutar_DDL%ROWTYPE;
BEGIN
  SELECT * INTO l_ejecutar_DDL
  FROM ejecutar_DDL
  WHERE njob = p_njob;
  EXECUTE IMMEDIATE l_ejecutar_DDL.sentencia;
END;

Ahora lo único que queda es, una vez que nuestra transacción en el trigger PL/SQL se ha completado, invocar nuestra sentencia DDL utilizando un bloque PLSQL similar a este:

DECLARE
  l_njob NUMBER;
BEGIN
  DBMS_JOB.SUBMIT (
    l_njob,
    'ejecutar_DDL_en_trigger(JOB);' );
  INSERT INTO ejecutar_DDL
  VALUES (
    l_njob,
    '...sentencia DDL (sin ; al final)...' );
  COMMIT;
END;

De esta forma dispondremos de un procedimiento PL/SQL, ejecutar_DDL_en_trigger, que nos permitirá ejecutar nuestra sentencia DDL recogiendo, de ser necesario, los errores producidos y que, además, se ejecutará poco tiempo después de que la transacción realizada dentro del trigger se haya completado (commit). Y lo mejor de todo es que si nuestra transacción se deshace (rollback), el INSERT en la tabla ejecutar_DDL también se habrá deshecho y la sentencia DDL no se ejecutará.

Conclusión: dentro de un trigger PL/SQL, siempre deberemos crear soluciones semejantes a ésta cuando estemos pensando en ejecutar algo del tipo no transaccional (ver artículo problemas con los triggers PLSQL).

Haz clic aquí para leer todo el artículo.

Transacciones parciales en PLSQL para lidiar con tablas muy grandes o voluminosas

Hace unas semanas alguien mencionó en un comentario que en su base de datos Oracle tenía una tabla con millones de registros y que, utilizando un bucle PLSQL, pensaba ejecutar un UPDATE y un COMMIT por cada, digamos, 500 registros procesados en dicha tabla, evitando así posibles problemas con los segmentos de rollback. El caso es que dicho lector me preguntaba si yo tenía alguna sugerencia al respecto. Este tipo de problema es, ciertamente, algo más complejo de lo que a simple vista parece, y sobre el que conviene escribir con cierta calma.

Si dividimos una transacción muy larga en muchas otras más pequeñas, existen bastantes probabilidades de que a mitad de la transacción global se produzca un error ORA-01555 (snapshop too old), problema generalmente causado por la alta frecuencia en la ejecución de sentencias COMMIT, o que simplemente se produzca un fallo del sistema. Entonces nos encontraremos con una transacción realizada parcialmente. Por lo tanto, antes de implementar este tipo de solución, deberemos asegurarnos de la que la transacción global puede re-ejecutarse. La cuestión es que en muchos casos esto no será posible y, por tanto, tendremos que escribir bastante código PL/SQL para hacer posible esta re-ejecución.

Supongamos que tenemos el siguiente bloque de código PLSQL:

DECLARE
  CURSOR cur IS SELECT * FROM tmillon;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT INTO cur_rec LIMIT 500;
    ...
    FORALL i IN 1 .. cur_rec.COUNT
      UPDATE tmillon SET ... ;
      -- cur_rec se usa en el UPDATE
    ...
    COMMIT;
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  CLOSE cur;
END;

En el caso concreto de este bloque PL/SQL os puedo asegurar de que la probabilidad de que se produzca un error ORA-01555 es bastante elevada, la razón principal es que estamos leyendo y modificando la misma tabla y que el SELECT del cursor debe entregar, en todo instante, los valores del momento en que se inició la consulta. El caso es que la base de datos necesitará espacio para el SELECT y para poder hacer un ROLLBACK de los UPDATE ejecutados, siendo muy probable que se produzca el mencionado error.

Entonces, puesto que se puede producir este error, deberemos pensar en alguna forma de poder re-ejecutar el bloque PL/SQL, necesitaremos bien una columna en la tabla que nos indique que dicho registro ha sido actualizado, u otra tabla en la que se inserten las claves primarias de los registros que han sido modificados. Si empleamos esta última solución, nuestro cursor quedaría de la siguiente manera:

CURSOR cur IS SELECT * FROM tmillon t
WHERE NOT EXISTS (
  SELECT 'Existe'
  FROM claves_ok c
  WHERE c.prclave = t.prclave);

Estás dos soluciones no son únicas, pero lo que yo quiero hacer patente es que, en cualquier caso, siempre tendremos que escribir algo de código adicional.

Para lidiar con este tipo de situaciones yo recomiendo:

• Actualizar la tabla utilizando una sola sentencia PLSQL. Esta es, sin duda, la manera más eficiente en términos de utilización de los recursos de la base de datos. Además, no nos debemos preocupar demasiado por un posible problema con los segmentos de rollback, ya que estos se necesitan para que la transacción se complete correctamente.


• Utilizar el paquete PL/SQL DBMS_REDEFINITION. Con este paquete crearemos una copia de la tabla base de forma online, es decir, que no será necesario bloquear las modificaciones de la tabla, y, después, utilizaremos dicha tabla en el cursor principal. Estaremos utilizando una estructura de datos más compacta e incluso, si nos interesa, en la versión 10g de la base de datos Oracle, podemos añadir un comando ORDER BY a la redefinición de la tabla para reordenar los registros de la misma en el disco. De este paquete es muy probable que hable en una entrada posterior.


• Utilizar el comando CREATE TABLE AS SELECT. Esto sería similar a utilizar el paquete PL/SQL DBMS_REDEFINITION pero, en este caso, no debemos permitir que mientras se realice esta operación se esté modificando la tabla fuente original. Además, el paquete DBMS_REDEFINITION se encarga de la creación automática de todo los objetos necesarios para crear una copia de la tabla (índices, permisos, etcétera), mientras que si utilizamos CREATE TABLE AS SELECT, de ser necesario, tendremos que crear todos estos objetos manualmente.

Haz clic aquí para leer todo el artículo.

Cómo utilizar un cursor PL/SQL como parámetro de salida en un procedimiento

En este artículo o voy a proponer un ejercicio práctico bastante sencillo que puede resultar de bastante útilidad práctica. Supongamos que queremos crear un procedimiento PLSQL que utilice un par de parámetros de entrada, que podrán tomar el valor NULL, y con un parámetro de salida que será el cursor PL/SQL correspondiente a la siguiente sentencia SELECT:

SELECT * FROM empleados
WHERE nombre = parametro_1
  AND apellidos = parametro_2;

Lo primero que tendremos que hacer es crear un paquete PL/SQL que contenga la definición del cursor:

CREATE OR REPLACE PACKAGE empleados_pkg
IS
  /* Definición del REF CURSOR type */
  TYPE empleados_type IS REF CURSOR
  RETURN empleados%ROWTYPE;
END empleados_pkg;

Después sólo tendremos que crear el procedimiento PLSQL que devolverá como salida el cursor definido en el paso anterior, y que utilizará los dos parámetros de entrada indicados en el enunciado del problema que estamos intentando resolver.

CREATE OR REPLACE PROCEDURE datos_empleados
  (
  par1 IN VARCHAR2,
  par2 IN VARCHAR2,
  cursor1 OUT empleados_pkg.empleados_type
  )
AS
BEGIN
  OPEN cursor1 FOR
  SELECT * FROM empleados
  WHERE nombre = NVL(par1,nombre)
    AND apellidos = NVL(par2,apellidos);
END datos_empleados;

No obstante, la utilización de la función NVL (si par1 es NULL toma el valor nombre, si par2 es NULL toma el valor apellidos), podría no ser muy conveniente y derivar en problemas de rendimiento, por lo que es recomendable definir el procedimiento anterior como sigue:

CREATE OR REPLACE PROCEDURE datos_empleados
  (
  par1 IN VARCHAR2,
  par2 IN VARCHAR2,
  cursor1 OUT empleados_pkg.empleados_type
  )
AS
BEGIN

  /* Todos los parámetros tienen valor */
  IF par1 IS NOT NULL AND par2 IS NOT NULL
  THEN
    OPEN cursor1 FOR
    SELECT * FROM empleados
    WHERE nombre = par1
      AND apellidos = par2;

  /* Sólo tiene valor par1 */
  ELSIF par1 IS NOT NULL
  THEN
    OPEN cursor1 FOR
    SELECT * FROM empleados
    WHERE nombre = par1;

  /* Sólo tienen valor par2 */
  ELSIF par2 IS NOT NULL
  THEN
    OPEN cursor1 FOR
    SELECT * FROM empleados
    WHERE apellidos = par2;

  /* Ningún parámetro tiene valor */
  ELSE
  THEN
    OPEN cursor1 FOR
    SELECT * FROM empleados;

  END IF;

END datos_empleados;

Artículos relacionados: Cursores en PLSQL.

Haz clic aquí para leer todo el artículo.

El paquete PL/SQL DBMS_SCHEDULER para programación de trabajos

DBMS_SCHEDULER es el paquete PLSQL que reemplazó en la versión de la base de datos Oracle 10g al paquete DBMS_JOB. Aunque el paquete DBMS_JOB sigue existiendo por razones de compatibilidad, no debe utilizarse ya que es muy probable que deje de existir en futuras versiones de la base de datos Oracle. El paquete DBMS_SCHEDULER permite programar la ejecución, en los instantes que deseemos, de bloques PLSQL, así como de procedimientos y funciones PL/SQL. Por otro lado, también permite programar la ejecución de binarios y shell-scripts.

Permisos necesarios

Con permisos de DBA se tiene acceso a todas las funciones del paquete DBMS_SCHEDULER. Para administrar la programación de procesos se necesita tener la rol (role) de SCHEDULER_ADMIN. Y finalmente, para crear y ejecutar procesos bajo tu propia identidad, se necesita tener el privilegio CREATE JOB. Por otro lado, aprovecho para mencionar que el paquete DBMS_JOB requería la inicialización de un parámetro del sistema, tras lo cual se lanzaba un proceso en background encargado de coordinar la ejecución de los distintos procesos programados, pero esto ya no es necesario si utilizamos Oracle 10g y el paquete DBMS_SCHEDULER.

Programación rápida de ejecución de un bloque PL/SQL

Para programar la ejecución de un bloque PLSQL podemos utilizar directamente el siguiente código. Sin duda es la forma más rápida y sencilla de programar la ejecución de un trabajo.

BEGIN
  DBMS_SCHEDULER.create_job(
     job_name        => 'DEMO_BLOQUE_PROGRAMADO'
    ,job_type        => 'PLSQL_BLOCK'
    ,job_action      => 
      'begin 
       nom_paq.nom_proced(''valor_param'');
       end;'
    ,start_date      => '06/11/2009 02:00 AM'
    ,repeat_interval => 'FREQ=DAILY'
    ,enabled         => TRUE
    ,comments        => 'Bloque PLSQL programado');
  COMMIT;
END;
/

En el código de arriba lo que hemos hecho es programar un bloque PL/SQL para que se ejecute diariamente a las dos de la mañana a partir del día 11 de junio de 2009.

Ejemplo completo de programación de un trabajo

Lo primero que debemos hacer el definir el programa que se va a ejecutar.

BEGIN
  DBMS_SCHEDULER.create_program (
     program_name        => 'DEMO_PROC_PROGRAMADO'
    ,program_type        => 'STORED_PROCEDURE'
    ,program_action      => 'nom_paq.nom_proced'
    ,number_of_arguments => 1
    ,enabled             => FALSE
    ,comments            => 'Proc. PLSQL programado');
  DBMS_SCHEDULER.define_program_argument (
     program_name      => 'DEMO_PROC_PROGRAMADO'
    ,argument_position => 1
    ,argument_name     => 'valor1'
    ,argument_type     => 'VARCHAR2'
    ,default_value     => 'valor por defecto');
  DBMS_SCHEDULER.enable (
     name => 'DEMO_PROC_PROGRAMADO');
  COMMIT;
END;
/

El parámetro program_type puede tomar los siguiente valores: PLSQL_BLOCK, STORED_PROCEDURE o EXECUTABLE.

A continuación debemos definir cuando y con que frecuencia queremos ejecutar nuestro programa, es decir, debemos definir la schedule propiamente dicha.

BEGIN
  DBMS_SCHEDULER.create_schedule(  
     schedule_name   => 'DEMO_SCHEDULE'
    ,start_date      =>  '01/01/2009 01:00:00'
    ,repeat_interval => 'FREQ=WEEKLY'
    ,comments        => 'Semanalmente a las 01:00');
  COMMIT;
END;
/

Si queremos deshabilitar la schedule anteriormente definida utilizaremos el siguiente código:

BEGIN
  DBMS_SCHEDULER.drop_schedule(
     schedule_name => 'DEMO_SCHEDULE'
    ,force         => TRUE );
  COMMIT;
END;
/

Para establecer la frecuencia (repeat_interval) podemos utilizar las siguientes expresiones: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY y SECONDELY.

A continuación debemos definir el proceso o trabajo que queremos programar. Esto se realiza mediante la asignación de un programa a una o más schedules.

BEGIN
  DBMS_SCHEDULER.create_job(
     job_name      => 'DEMO_TRABAJO_PLSQL'
    ,program_name  =>'DEMO_PROC_PROGRAMADO'
    ,schedule_name =>'DEMO_SCHEDULE'
    ,enabled       => FALSE
    ,comments      => 'Semanal a las 01:00');
  DBMS_SCHEDULER.set_job_argument_value(
     job_name          => 'DEMO_TRABAJO_PLSQL'
    ,argument_position => 1
    ,argument_value    => 'valorparametro1');
  DBMS_SCHEDULER.enable('DEMO_TRABAJO_PLSQL');
  COMMIT;
END;
/

Por otro lado y según ya he comentado, con el paquete DBMS_SCHEDULER también podemos hacer correr directamente un ejecutable o una shell script:

BEGIN
   DBMS_SCHEDULER.create_job
   (
      job_name      => 'RUN_SHELL_SCRIPT_DEMO',
      schedule_name => 'DEMO_SCHEDULE',
      job_type      => 'EXECUTABLE',
      job_action    => '/home/shell/demo_script.sh',
      enabled       => TRUE,
      comments      => 'Ejecución de un shell script'
   );
  COMMIT;
END;
/

Otros paquetes estándares del PLSQL: DBMS_LOCK, DBMS_SQL y DBMS_OUTPUT.

Haz clic aquí para leer todo el artículo.

Cómo evitar el uso de constantes fijas (hard-coded) en PL/SQL

Esta claro que la mayoría de los programas y aplicaciones PLSQL tienen su propio conjunto de constantes que determinan las características de dicha aplicación. Por lo general, estos valores constantes tienen que ser utilizados en distintos lugares del código. En muchas ocasiones estos valores permanecerán invariables durante todo el ciclo de vida de la aplicación pero, en muchos otros casos, cambiarán de forma periódica (por ejemplo, una vez al año).

Pongamos un ejemplo, supongamos que en un programa PL/SQL establecemos que el salario anual de un empleado no puede superar los 50.000 euros. Podemos codificar esta norma utilizando la siguiente subrutina:

IF l_salario_anual > 50000
THEN
RAISE_APPLICATION_ERROR (
  -20001, 
  'El salario anual no puede ser superior
  a 50000 euros');
END IF;

La mayoría de los programadores sabemos que referenciar un valor constante de esta manera no es muy recomendable. En inglés se dice que la constante está "hard-coded" directamente en la rutina. Resulta obvio señalar que este tipo de prácticas suele derivar en problemas, ya que el valor de 50.000 euros cambiará muy probablemente de año en año y, cuando esto ocurra, deberemos revisar el código PLSQL y cambiar el antiguo valor por el nuevo en cada uno de los sitios donde sea utilizado.

En PL/SQL disponemos de tres formas bastante evidentes de evitar tener que utilizar constantes directamente en el código:

• Crear un paquete PLSQL que asigne el valor a esa constante
• Crear un función PLSQL que devuelva dicho valor
• Almacenar y gestionar el valor de dicha constante en una tabla de la base de datos Oracle

A continuación pondré un ejemplo de cada una de las opciones mencionadas.

Paquete PL/SQL de constantes

CREATE PACKAGE c_constantes IS
 c_salario_maximo CONSTANT NUMBER := 50000;
END c_constantes;

Una vez definido el paquete ya sólo tendríamos que referirnos al mismo de la siguiente manera:

IF l_salario_anual > 
   c_constantes.c_salario_maximo
THEN
  RAISE_APPLICATION_ERROR (
  -20001, 
  'El salario anual no puede ser superior a '
  || c_constantes.c_salario_maximo
  || ' euros');
END IF;

Función PL/SQL que devuelve el valor de la constante

CREATE PACKAGE f_constantes IS
FUNCTION f_salario_maximo RETURN NUMBER;
END f_constantes;

CREATE PACKAGE BODY f_constantes IS
FUNCTION f_salario_maximo RETURN NUMBER IS
BEGIN
  RETURN 50000;
END;
END f_constantes;

Y ahora para referirnos a la constante utilizaríamos el siguiente código:

IF l_salario_anual > 
   f_constantes.f_salario_maximo()
THEN
  RAISE_APPLICATION_ERROR (
  -20001, 
  'El salario anual no puede ser superior a '
  || f_constantes.f_salario_maximo()
  || ' euros');
END IF;

Almacenar la constante en una tabla de la base de datos

Si en nuestra aplicación debemos gestionar un buen número de constantes, la opción más recomendable sería crear una tabla con los campos, por ejemplo, "nombre", "descripción" y "valor", y sacar de ella los valores constantes correspondientes. En nuestro caso, y suponiendo que el campo "valor" es un VARCHAR2, podríamos escribir la siguiente función PLSQL:

CREATE PACKAGE t_constantes IS
FUNCTION t_numero 
  (t_nombre IN tabla_constantes.valor%TYPE) 
  RETURN NUMBER;
END t_constantes;

CREATE PACKAGE BODY t_constantes IS
FUNCTION t_numero 
  (p_nombre IN tabla_constantes.valor%TYPE) 
  RETURN NUMBER IS
BEGIN
  v_valor tabla_constantes.valor%TYPE;
  SELECT valor
  INTO v_valor
  FROM tabla_constantes
  WHERE nombre = p_nombre;
  RETURN TO_NUMBER(v_valor);
END;
END t_constantes;

Y para referirnos a la constante utilizaríamos algo semejante a:

IF l_salario_anual >
   t_constantes.t_numero('salario maximo')
THEN
  RAISE_APPLICATION_ERROR (
  -20001, 
  'El salario anual no puede ser superior a '
  || t_constantes.t_numero('salario maximo')
  || ' euros');
END IF;

En este ejemplo he definido una función para devolver valores de tipo numérico (NUMBER), pero igualmente podríamos definir funciones semejantes que devuelvan valores de tipo VARCHAR2, DATE, etcétera.

Artículos relacionados:
Tipos de datos en PLSQL.
Procedimientos y funciones en PLSQL.

Haz clic aquí para leer todo el artículo.

La funcionalidad de muestreo dinámico o Dynamic Sampling

La funcionalidad de muestreo dinámico (Dynamic Sampling) estuvo por primera vez disponible para la release 2 de la bases de datos Oracle 9i. Esta funcionalidad posibilita que el optimizador SQL y PL/SQL basado en costes (CBO) muestree las tablas que utiliza una consulta (query) durante la fase de parsing duro, para mejorar los valores estadísticos que utiliza el optimizador al incluir en dichas estadísticas los segmentos no analizados con anterioridad. Como ya he indicado, este muestro sólo ocurre durante la fase de parsing duro y se utiliza para mejorar las estadísticas utilizadas por el optimizador PLSQL, de ahí el nombre de muestro dinámico.

El optimizador de las bases de datos Oracle usa una gran variedad de datos de entradas para generar los planes de ejecución. Utiliza todos los índices y restricciones (constraints) definidos en las tablas, las estadísticas del sistema (velocidad de entrada/salida de los servidores, velocidad de CPU), y las estadísticas recogidas de los segmentos involucrados en la ejecución de la consulta. El optimizador utiliza estas estadísticas para estimar el número de registros involucrados en cada uno de los pasos de los que consta un plan de ejecución específico, siendo este volumen de registros la variable principal a la hora de calcular el coste de ejecución de una consulta. Cuando el optimizador calcula incorrectamente el volumen de registros, probablemente escogerá un plan de ejecución ineficiente causando problemas de rendimiento en la base de datos. De hecho, la principal razón por la que el optimizador Oracle puede decidirse por un plan de ejecución incorrecto, es precisamente por esta causa, es decir, una estimación incorrecta del volumen de registros a procesar en cada paso del plan de ejecución.

En conclusión, el principal motivo que existe detrás de utilizar la funcionalidad del muestreo dinámico, es ayudar al optimizador a estimar correctamente el volumen de registros a procesar, alimentándole con mayor cantidad de datos y una información más exacta relativa a la ejecución de la consulta que se necesita ejecutar en ese mismo momento.

Formas de trabajar del muestreo dinámico o Dynamic Sampling

El tipo de muestreo dinámico puede establecerse de dos formas diferentes:

• Con el parámetro OPTIMIZER_DYNAMIC_SAMPLING puede establecerse el tipo de muestreo dinámico, bien a nivel de instancia de la base de datos, bien a nivel de sesión mediante la utilización del comando ALTER SESSION.
  
• También se puede utilizar el hint de SQL o PL/SQL denominado DYNAMIC_SAMPLING para establecer es tipo de muestreo a nivel de consulta

A continuación os mostraré un ejemplo en el que podréis ver algunas diferencias entre un plan de ejecución generado por el optimizador Oracle sin utilizar muestreo dinámico, y otro plan en el que si que se utiliza dicha funcionalidad. En el ejemplo debemos considerar que la tabla mitabla consta de 60.000 registros:

SQL> set autotrace traceonly explain
SQL> select /*+ dynamic_sampling(mit 0) */ * 
SQL> from mitabla mit;

Execution Plan
------------------------------------------------------
Plan hash value: 1601036213
------------------------------------------------------
|Id|Operation        |Name|Rows |Cost (%CPU)|Time    |
------------------------------------------------------
| 0|SELECT STATEMENT |    |15034| 52     (0)|00:00:01|
| 1|TABLE ACCESS FULL|T   |15034| 52     (0)|00:00:01|
------------------------------------------------------

SQL> select * from mitabla mit;

Execution Plan
------------------------------------------------------
Plan hash value: 1601036213
------------------------------------------------------
|Id|Operation        |Name|Rows |Cost (%CPU)|Time    |
------------------------------------------------------
| 0|SELECT STATEMENT |    |64222| 54     (2)|00:00:01|
| 1|TABLE ACCESS FULL|T   |64222| 54     (2)|00:00:01|
------------------------------------------------------

En el ejemplo vemos un primer caso de plan de ejecución en el que hemos deshabilitado el muestreo dinámico utilizando el hint DYNAMIC_SAMPLING, mientras que el segundo plan de ejecución, puesto que por defecto dicha funcionalidad está habilitada y no hemos utilizado ningún hint en la consulta, se ha generado utilizando el muestro dinámico. Como podemos ver, los planes de ejecución arrojan datos bastante dispares. Al deshabilitar el muestreo dinámico, el optimizador utiliza los valores estadísticos por defecto que típicamente no son muy fiables, ya que el número de registros se estima en base a número de bloques de datos en la tabla y al tamaño medio de los registros de dicha tabla. Así, al deshabilitar el muestreo dinámico, el optimizador deduce que la tabla cuenta con tan sólo 15.034 registros frente a los 60.000 que tiene en realidad. Por otro lado, al activar el muestreo dinámico, el optimizador da un valor mucho más cercano a la realidad, 64.222 registros.

La inexactitud se hace todavía más patente si borramos totalmente el contenido de la tabla mitabla. En este caso, el plan de ejecución si deshabilitamos el muestreo dinámico seguirá devolviéndonos el valor de 15.034 registros, mientras que el plan de ejecución con el dynamic sampling habilitado no dirá que el número de registros a procesar es tan sólo 1.

El muestreo dinámico es más eficaz cuando en una consulta estamos utilizando una tabla que ha sido creada y poblada con datos, pero que todavía no ha sido analizada mediante el paquete estándar PLSQL DBMS_STATS, paquete que se utiliza para generar los datos estadísticos de las tablas. Hay que considerar que desde la primera release de la base de datos Oracle 10g, ya sólo esta soportado el optimizador basado en costes, y que dicho optimizador necesita datos estadísticos exactos para funcionar correctamente. Por lo tanto, si una tabla existe y sus estadísticas no han sido recogidas todavía, el muestreo dinámico ayuda sobremanera a que el optimizador genere planes de ejecución adecuados.

Por último terminaré indicando que la funcionalidad de muestreo dinámico ofrece once niveles de dynamic sampling, de 0 (deshabilitado) a 10. En la release 2 de Oracle 9i el valor por defecto es 1, mientras que en la release 2 de Oracle 10g y superiores, el valor por defecto es 2. Si queréis informaros acerca del significado e implicaciones que supone utilizar un nivel de muestreo dinámico u otro, os emplazo a que visitéis este enlace: Dynamic Sampling Levels.

Artículos relacionados:
Hints PLSQL para el modo de optimización.
Planes de ejecución Oracle.

Haz clic aquí para leer todo el artículo.

El paquete estándar DBMS_LOCK para sincronizar procesos

Hace unas semanas alguien me preguntó como se podía determinar si un procedimiento PL/SQL (procedure PLSQL) estaba siendo ejecutado para evitar tener dos instancias del mismo proceso corriendo simultáneamente. Para manejar este tipo de situaciones, así como situaciones en las que queramos ejecutar procedimientos y funciones de forma secuencial y sincronizar diferentes procesos, Oracle dispone de el paquete de funciones y procedimientos DBMS_LOCK.

Con el paquete DBMS_LOCK podemos establecer bloqueos de usuario (PL/SQL User Locks (UL)) con los que podremos parar la ejecución de un procedure si al ejecutarlo nos encontramos con que un determinado bloqueo o lock está en proceso.

A continuación os describo los tres procedimientos y funciones del paquete DBMS_LOCK más importantes:

DBMS_LOCK.allocate_unique (nombre_lock, id_lock, expira_seg): Este procedimiento lo que hace es asignar un identificador único (id_lock) para el nombre de bloqueo (nombre_lock) que hemos utilizado como parámetro de entrada. Por lo tanto, id_lock se trata de un parámetro de salida, y los valores que puede tomar están comprendidos en el rango de 1073741824 a 1999999999. Por otro lado, el parámetro expira_seg es opcional e indica el número de segundos que se mantendrá el bloqueo una vez que haya sido solicitado, si no se especifica, el valor por defecto es de 864000 segundos (10 días).

DBMS_LOCK.request (id_lock, tipo_lock, timeout, unlock_on): Con esta función lo que hacemos es solicitar el establecimiento de un bloqueo de usuario.

Veamos el uso de los distintos parámetros:

• id_lock: Valor del identificador único generado anteriormente mediante la ejecución del procedimiento DBMS_LOCK.allocate_unique.


• tipo_lock: Permite definir el tipo de bloqueo que queremos establecer. Puede tomar los siguientes valores: 2 - Row Share (SS), 3 - Row Exclusive (SX), 4 - Share (S), 5 - Share Row Exclusive (SSX) y 6 - Exclusive (X). En la mayoría de los casos utilizaremos el valor de 6 para establecer un bloqueo exclusivo.


• timeout: Número de segundos que queremos permanecer esperando a que se libere el bloqueo si nos encontramos con que otro idéntico ha sido solicitado anteriormente y está todavía en marcha. Si no especificamos ningún valor, la función permanecerá en espera hasta que el bloqueo se libere (el valor por defecto que toma la variable timeout es muy alto).


• unlock_on: Indica si el bloqueo se debe liberar al ejecutar los comandos COMMIT o ROLLBACK (valor TRUE) o si debe mantenerse aunque ejecutemos alguno de los comandos mencionados (valor FALSE). El valor por defecto es FALSE.

Los valores que puede devolver esta función son los siguientes:

• 0: Bloqueo establecido satisfactoriamente.


• 1: El bloqueo no se pudo establecer por superarse el tiempo de espera (timeout).


• 2: Bloqueo multiple (deadlock). Aparece cuando se ha excedido el límite de peticiones de bloqueo permitidas sobre el mismo identificador.


• 3: Parámetro erróneo o inválido.


• 4: Aparece cuando en una misma sesión se solicita el mismo bloqueo por segunda vez sin haber liberado la primera petición.


• 5: Identificador de lock ilegal.

DBMS_LOCK.release (id_lock): Esta función se utiliza para liberar un bloqueo previamente establecido con la función DBMS_LOCK.request. La función puede devolver los valores 0, 3, 4 ó 5. El significado de dichos valores es exactamente el mismo que en la función DBMS_LOCK.request salvo el valor 4, que en este caso indica que no somos los dueños del lock y no podemos liberarlo.

Ejemplo práctico del uso del paquete DBMS_LOCK

En una primera sesión ejecutamos:

DECLARE
  v_lockhandle VARCHAR2(200);
BEGIN
  DBMS_LOCK.allocate_unique
    ('Mi_Procedure', v_lockhandle);
  DBMS_OUTPUT.put_line
    ('id_lock = '||v_lockhandle);
  DBMS_OUTPUT.put_line
    ('request status = ' ||
     DBMS_LOCK.request(v_lockhandle, 6));
END;

La salida por pantalla tras la ejecución del processo sería:

id_lock = 1074484814107448481486
request status = 0

A continuación en la tabla V$LOCK podemos identificar el bloqueo en curso de la siguiente manera (ojo, sólo hay que utilizar los diez primeros caracteres del id_lock):

SELECT sid, type, id1, lmode, 
       request, ctime, block
FROM   v$lock 
WHERE  id1 = '1074484814'

SID TYPE ID1        LMODE REQUEST CTIME BLOCK
117 UL   1074484814     6       0   201     0

Si ejecutamos el mismo proceso sobre una segunda sesión, observaremos que dicho proceso se queda esperando a que el bloqueo establecido en la primera sesión se libere. En la tabla V$LOCK veremos que aparece una nueva línea en espera de establecer un bloqueo exclusivo (lmode = 0, request = 6):

SID TYPE ID1        LMODE REQUEST CTIME BLOCK
117 UL   1074484814     6       0   790     1
 97 UL   1074484814     0       6     9     0

Ahora si abrimos una tercera sesión y ejecutamos:

DECLARE
  v_lockhandle VARCHAR2(200);
BEGIN
  DBMS_LOCK.allocate_unique
    ('Mi_Procedure', v_lockhandle);
  DBMS_OUTPUT.put_line
    ('id_lock = '||v_lockhandle);
  DBMS_OUTPUT.put_line
    ('request status = ' ||
    DBMS_LOCK.request(v_lockhandle, 6, 20));
END;

El proceso se quedará en espera durante 20 segundos (observad que he utilizado el parámetro de timeout con el valor de 20). Transcurrido este tiempo el proceso terminará y obtendremos la siguiente salida:

id_lock = 1074484814107448481486
request status = 1

Es decir, el proceso DBMS_LOCK.request devuelve el valor "1" indicando que el establecimiento del bloqueo falló por timeout. Por otro lado, durante esos 20 segundos de espera, aparecerá una tercera línea en la tabla V$LOCK (lmode = 0, request = 6), que no considero necesario mostrar.

Ahora, si en la primera sesión liberamos el bloqueo ejecutando:

DECLARE
  v_lockhandle VARCHAR2(200) := '1074484814';
BEGIN
  DBMS_LOCK.release(v_lockhandle);
END;

Entonces observaremos que, al liberarse el bloqueo, la ejecución del proceso que estaba en espera en la segunda sesión termina. En la tabla V$LOCK nos encontraremos con lo siguiente:

SID TYPE ID1        LMODE REQUEST CTIME BLOCK
 97 UL   1074484814     6       0     3     0

Es decir, ahora el bloqueo exclusivo (lmode = 6) está establecido por la segunda sesión.

Artículos relacionados:
El paquete estándar PLSQL DBMS_SQL.
El paquete estándar PLSQL DBMS_OUTPUT.

Haz clic aquí para leer todo el artículo.

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.

En mi opinión, si al definir una tabla disponemos de una clave natural, que obviamente debe ser única e inmutable, debemos utilizarla en detrimento de la clave sustitutiva. El argumento de que la utilización de claves sustitutivas va a mejorar el rendimiento de la base de datos es erróneo, y las claves naturales hacen más sencilla la escritura de nuestras consultas SQL o del código PL/SQL.

Por ejemplo, si disponemos de un par de tablas, la tabla PRODUCTOS y la tabla VERSION_PRODUCTOS, lo lógico sería utilizar para la primera tabla un campo producto_id como clave primaria (primary key) , campo que podría tratarse de una clave sustitutiva poblada por una secuencia, y usar como clave primaria de la segunda tabla una combinación de producto_id y de numero_version. Las tablas podrían definirse como sigue:

CREATE TABLE productos
( producto_id NUMBER PRIMARY KEY,
  otros_datos... );

CREATE TABLE version_productos
( producto_id REFERENCES productos,
  numero_version NUMBER,
  otros_datos...
  CONSTRAINT ver_pro_pk PRIMARY KEY 
  (producto_id, numero_version));

En nuestro ejemplo, el uso de una clave sustitutiva añadiría un trabajo extra a cualquier operación de INSERT que debería generar la clave sustitutiva, además de hacer lo propio para la clave natural. Mi opinión es que si la clave natural es inmutable y razonable, entonces debemos utilizarla. En este caso concreto, con razonable quiero significar que la clave no necesite utilizar quince campos para ser generada, sino que sólo hagan falta de dos a cinco columnas.

Haz clic aquí para leer todo el artículo.

Problemas con los triggers SQL

Mucha gente piensa que los triggers PL/SQL son una de las más potentes herramientas de las bases de datos Oracle. De hecho lo son, pero existen dos razones fundamentales por las que, personalmente, trato de evitar la utilización de triggers a la hora de implementar mis proyectos en PL/SQL.

Problemas de mantenimiento

A largo plazo, la utilización de triggers suele causar grandes dolores de cabeza a la hora de pensar en el mantenimiento. Al ser piezas del código que sólo ocurren como consecuencia de que se ha realizado otra operación, es muy frecuente que la gente se olvide de que los triggers están allí, y revisar el código pensando en todas las piezas de la base de datos que pueden afectarle, se hace poco menos que imposible.

Este problema es muy sencillo de comprender. Una persona experta en PL/SQL que no conoce una aplicación realizada en código PL/SQL y SQL, puede comprender fácilmente lo que realiza un procedimiento almacenado, pero si nuestro sistema utiliza triggers de manera rutinaria, entonces ese procedimiento puede desencadenar centenares de otro tipo de procesos y cambios en la base de datos que no son detectables a simple vista.

En conclusión, los triggers hacen que la comprensión de un sistema sea complicada, causan que su mantenimiento sea más difícil de lo normal y crean confusión al permanecer ocultos en el esquema la base de datos. Mucha gente considera que los triggers son como sentencias DDL y que, al igual que no hay necesidad de revisar una sentencia CREATE TABLE, tampoco hay necesidad de revisar el código de un trigger PL/SQL. En realidad, los triggers son subrutinas que son llamadas una y otra vez, cuyo código debe revisarse de igual manera que se revisan paquetes y procedimientos almacenados.

Uso incorrecto de los triggers

En muchos casos de los que yo he analizado, los triggers PL/SQL se han implementado de forma incorrecta. El código de los triggers suele incluir enormes errores que el programador no fue capaz de prever o anticipar. Veamos algunos ejemplos de este tipo de implementaciones incorrectas.

En alguna ocasión me he encontrado con triggers que realizan operaciones como esta:

:new.nombre_completo := 
:new.nombre||' '||:new.apellidos;

Lo primero que se me viene a la cabeza al ver una línea de código como la de arriba, es que la columna nombre_completo debería ser una columna de una vista (view) o, en Oracle 11g, una columna virtual. La columna nombre_completo es el resultado de una función trivial, con casi ningún coste de procesamiento y que, de ser necesario, podría hasta ser indexada. No existe ninguna razón para almacenar el resultado de la función en la tabla física.

Además, imaginemos que por alguna razón queremos cambiar el valor de la columna nombre_completo para algunos registros, simplemente el trigger no nos dejará hacerlo y, si no conocemos la existencia del mismo, nos preguntaremos una y mil veces por qué el campo no se actualiza al ejecutar el comando UPDATE. El trigger creará, cuando menos, confusión, y, con suerte, nos daremos cuenta de su existencia y de que debemos deshabilitarlo para poder realizar la deseada actualización del campo.

Otro de los usos incorrectos de los triggers es utilizarlos para enviar emails a los usuarios cuando se inserta o se actualiza un registro de una tabla. Veamos el siguiente trigger:

SQL> CREATE TRIGGER enviar_correo
 2   AFTER INSERT ON pedidos
 3   FOR EACH ROW
 4   BEGIN
 5   UTL_MAIL.send
 6   (sender=>'app@miempresa.com',
 7   recipients=>'pedidos@miempresa.com',
 8   subject=>'Nuevo pedido '||:new.num_pedido,
 9   message=>'Nuevo pedido recibido');
 10  END;
 11  /

Trigger created.

El trigger es sintácticamente correcto, compilará sin problemas y, si se inserta un nuevo pedido en la tabla "pedidos", nuestros usuarios recibirán un email indicándoselo. Sin embargo, el trigger tiene un grave problema. Alguien se ha preguntado qué ocurrirá si insertamos cien registros en la mencionada tabla y, por cualquier razón, decidimos deshacer las inserciones ejecutando el comando ROLLBACK. Pues simplemente lo que ocurrirá es que habremos enviado cien emails a los usuarios indicándoles que existen cien nuevos pedidos que en realidad no existen.

En este sentido conviene señalar que, para que este tipo de situaciones ocurran, no es necesario que uno mismo invoque el ROLLBACK, sino que continuamente la base de datos Oracle deshace operaciones INSERT, UPDATE, MERGE o DELETE, sin necesidad de que uno se lo indique personalmente.

Podemos decir que prácticamente cualquier trigger PL/SQL que llame a funciones UTL_ (como UTL_FILE, UTL_HTTP, UTL_MAIL, UTL_SMTP, UTL_TCP, etcétera) estará incorrectamente implementado. En conclusión, con los triggers no se debe realizar ninguna operación que no pueda ser deshecha con posterioridad.

Los triggers también pueden causar problemas cuando los programadores no entienden debidamente una de las características más interesantes de las bases de datos Oracle, el hecho de que las lecturas no bloquean las escrituras, y de que las escrituras no bloquean las lecturas. Esto se torna especialmente crítico cuando los triggers se utilizan para forzar que se cumpla alguna regla determinada. Todo esto será tema de otro artículo posterior.

Haz clic aquí para leer todo el artículo.