Bucles y problemas de rendimiento (performance)

El lenguaje PL/SQL permite la contrucción de bucles, esta funcionalidad es, sin duda, extremadamente útil. No obstante, utilizar bucles puede causar problemas de rendimiento en nuestras rutinas, aunque ciertamente estos problemas son fáciles de determinar y corregir.

Imaginemos que una rutina PL/SQL contiene una sentencia select y los registros que devuelve son utilizados para actualizar otra tabla dentro de un bucle. Por ejemplo:

FOR bucle1 IN (
  SELECT articulo
  FROM articulos
  WHERE descripcion = '%zapato%'
) LOOP
  UPDATE precios
  SET precio = precio * 1.05
  WHERE articulo = bucle1.articulo;
END LOOP;

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.

Vistas materializadas (materialized views) en SQL y PL/SQL (1)

El SQL de las bases de datos Oracle permite crear vistas materializadas o materialized views. Estas vistas materializadas, a parte de almacenar la definición de la vista propiamente dicha, también almacenan los registros que resultan de la ejecución de la sentencia SELECT que define la vista. Como las vistas normales, la sentencia SELECT es la base de la vista, pero la sentencia SQL se ejecuta cuando se crea la vista y los resultados se almacenan físicamente constituyendo una tabla real que ocupa sitio en el disco duro. Esta tabla puede definirse utilizando los mismos parámetros de almacenamiento que se pueden utilizar para una tabla normal (tablespace, etcétera). Las vistas materializadas también admiten índices, esta funcionalidad resulta muy útil a la hora de mejorar el rendimiento de las sentencias PLSQL o SQL que utilicen vistas materializadas.

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.

Recorrer colecciones PL/SQL (métodos y funciones de las colecciones)

Una operación que se debe realizar muy comúnmente con las colecciones PLSQL es tener que recorrerse todos los elementos de la misma. La razones para tener que recorrerse totalmente los datos de una colección pueden se múltiples, desde mostrar la información que almacenan hasta buscar un elemento específico o ejecutar una sentencia DML (de manipulación de datos dentro de la base de datos Oracle) utilizando los datos almacenados en los diferentes elementos de la colección.

El tipo de código PL/SQL que se debe utilizar para operar y recorrer una colección viene determinado por el tipo de colección con la que estemos trabajando y la forma en que dicha colección fue inicializada con datos. Lo normal es elegir entre utilizar un bucle WHILE o utilizar un bucle numérico FOR.

Inicialización y asignación de valores a colecciones PL/SQL

Cuando se trabaja en PLSQL con tablas anidadas y varrays (ver artículo sobre los tipos de dato colección del PL/SQL), es necesario inicializar la variable colección antes de que pueda ser utilizada. Para conseguir esto se puede utilizar la denominada función constructora, una función que la base de datos Oracle crea de forma automática cuando se declara la variable colección.

Los que hace la función constructora es construir una instancia del tipo correspondientes al de la función. La función puede ser llamada sin utilizar argumento alguno, aunque también se le pueden pasar una o más expresiones del mismo tipo que sean elementos de la colección PL/SQL, elementos que se insertarán en la colección.

El tipo de dato colección del PL/SQL (PLSQL collection datatype)

En Oracle una “colección” PL/SQL (PLSQL collection) es un tipo de datos compuesto consistente en una matriz de una sola dimensión que está compuesta por uno o más elementos accesibles a través de un índice.

Los datos de tipo colección se utilizan en algunas de las más importantes funcionalidades del PL/SQL asociadas con la optimización del rendimiento de las bases de datos Oracle tales como:

• FORALL: dentro de los cuales se incluyen sentencias INSERT, UPDATE y DELETE que utilizan colecciones para cambiar múltiples registros de datos de una manera muy rápida.
• BULL COLLECT: sentencias SELECT que devuelven múltiples registros en una simple extracción, incrementando notablemente la velocidad de la consulta.
• Funciones de tipo tabla: funciones PLSQL que devuelven datos de tipo colección y que pueden utilizarse en la cláusula FROM de una sentencia SELECT.

Las colecciones también se pueden utilizar para trabajar en nuestros programas con listas de datos que no está almacenados en tablas de las base de datos Oracle.

Las funciones PL/SQL CONCAT, UPPER, LOWER, INITCAP, SUBSTR e INSTR (manejo de cadenas de caracteres I)

Una vez que hemos visto cuales son los tipos de datos para manejar cadenas de caracteres, podemos pasar a estudiar las diversas funciones PL/SQL que permiten analizar el contenido de una cadena, combinarlo con otras cadenas, o simplemente cambiarlo de alguna manera controlada. Para ayudarnos con este tipo de requerimientos, la base de datos Oracle proporciona un conjunto bastante amplio de funciones PLSQL que permiten manejar cadenas de caracteres de una manera sencilla y cómoda.

En este capítulo hablaremos en concreto de las funciones CONCAT, UPPER, LOWER, INITCAP, SUBSTR e INSTR.

Hints en PL/SQL para el modo de optimización

Los hints son pistas que se dan al optimizador SQL de Oracle para que elabore el plan de ejecución de una sentencia DML (sentencias de manipulación de datos como select, insert, update, delete, etc) según nosotros le aconsejemos. En este primer artículo sobre los hints voy a empezar hablando de aquellos que se utilizan para seleccionar el modo de trabajar del optimizador Oracle. Estos hints, hablando desde un punto de vista práctico, no son muy utilizados, aunque no por ello pueden dejar de ser útiles en determinadas circunstancias.

Los hints se incorporan a una sentencia DML en forma de comentario y deben ir justo detrás del comando principal. Por ejemplo, si se tratara de una sentencia SELECT el formato sería el siguiente:

     SELECT /*+ COMANDO-HINT */ ...

Gestión de errores en PL/SQL: generación de excepciones desde el código

En la mayoría de los casos, cuando una de nuestras aplicaciones PLSQL genera una excepción, es la base de datos Oracle la encargada de notificarlo automáticamente. Es decir, un programa PL/SQL reportará un error si durante la ejecución del mismo ocurre algún tipo de problema que la base de datos Oracle no puede manejar por si sola y nuestro código no tiene control sobre el mismo (ver artículo sobre el manejo de excepciones con PL/SQL).

Lo que muchos programadores desconocen es que PLSQL permite generar excepciones desde el propio código. ¿Por qué el código PL/SQL permite esto? Porque no todos los errores en una aplicación son debidos a fallos de procesamiento internos de la base de datos Oracle. También es posible que determinadas condiciones en los datos constituyan un error para nuestra aplicación, en cuyo caso necesitaremos parar nuestro programa y, con mucha probabilidad, notificar al usuario de que algo va mal.