Cláusula BULK COLLECT para mejorar el rendimiento al realizar procesamiento masivo

Yo siempre he dicho que cuando para hacer algo se pueden utilizar sentencias SQL sencillas, no resulta conveniente emplear complicados procedimientos PL/SQL que implementen la misma solución. Sin embargo, hay situaciones en que para mejorar el rendimiento de determinados bucles FOR en los que se realizan actualizaciones masivas sobre una determinada tabla de la base de datos Oracle, resulta conveniente utilizar técnicas PLSQL de procesamiento masivo (lo que en inglés se denomina BULK COLLECT).

Para entender mejor en qué consiste esta técnica, primero hay que comprender los motivos por los que un simple bucle FOR puede generar importantes problemas de rendimiento. Veamos el siguiente código PL/SQL:

La cláusula PIPELINED en las funciones PL/SQL y la excepción NO_DATA_NEEDED

En esta entrada explicaré para que sirve la cláusula PIPELINED en las funciones PL/SQL, y como se debe utilizar la excepción NO_DATA_NEEDED, que nada tiene que ver con la excepción NO_DATA_FOUND, para controlar las funciones PLSQL que incluyen dicha cláusula y que en inglés se denominan pipelined functions. Primero quiero remarcar que la funcionalidad pipelined fue introducida por primera vez en la versión 9i de las bases de datos Oracle. Básicamente, el uso de la cláusula PIPELINED resulta de gran utilidad y es prácticamente imprescindible cuando necesitamos que en lugar de una tabla sea una rutina PL/SQL la que nos sirva como fuente de datos.

La mejor forma de explicar el funcionamiento de esta cláusula es con un ejemplo.

¿Qué es mejor para el rendimiento, utilizar consultas PLSQL con subqueries o con joins?

Alguna vez me han llegado cuestiones en la que se me preguntaba qué es mejor, en términos de rendimiento de las bases de datos Oracle, si utilizar en las consultas PLSQL subqueries (subconsultas) o utilizar joins (es decir, listar todas las tablas en la clausula FROM y unirlas en el WHERE). Lo primero que hay que tener claro es que escribir una consulta PL/SQL utilizando subqueries o utilizando joins es semánticamente diferente; además, utilizar una u otra forma puede derivar en que ambas consultas devuelvan resultados diferentes y que no sean directamente intercambiables.

Lo que yo recomiendo para elegir entre un tipo de consulta u otro es, en general, hacer lo siguiente:

• Utilizar una subquery o subconsulta cuando no se necesita ninguna columna de la tabla que es referenciada en la subquery.
• Utilizar un join en caso de necesitar alguna de las columnas.

SQL y PL/SQL - La sentencia MERGE

La sentencia MERGE, a la que muchos denominan UPSERT debido a su funcionalidad, está disponible desde la aparición de la versión 9i de la base de datos Oracle. Se trata de una de las funcionalidades del kernel de Oracle más utiles a la hora de permitir el uso de la tecnología ETL (Extract, Transform and Load - Extraer, Transformar y Cargar) con las bases de datos Oracle. Este tipo de tecnología puede utilizarse y está especialmente enfocada para ser usada en aplicaciones de data warehousing (almacen de datos). Básicamente, lo que permite la sentencia SQL MERGE es, dependiendo de una condición lógica, actualizar registros (UPDATE) cuando la condición se cumple, o insertar registros (INSERT) cuando dicha condición no se cumple, de ahí surge la denominación de UPSERT.

Anteriormente a la versión 9i de Oracle, la alternativa en SQL era ejecutar dos sentencias DML, un UPDATE y un INSERT, cada una utilizando condiciones lógicas opuestas. En cuanto a las alternativas en PL/SQL eran: bien intentar insertar un registro y si la sentencia INSERT fallaba debido a una excepción PLSQL del tipo DUP_VAL_ON_INDEX, entonces realizar un UPDATE del registro en cuestión; bien intentar actualizar un registro y si la sentencia UPDATE devolvía SQL%NOTFOUND, entonces ejecutar la correspondiente sentencia INSERT.

Hints avanzados en PL/SQL para forzar la forma de acceder a una tabla

Ya he hablado anteriormente acerca de los hints PLSQL más comunes (FULL, ROWID, INDEX, NO_INDEX) para forzar el método de accesso a una tabla Oracle. En este artículo voy a presentar algunos hints más de este tipo, que se utilizan menos frecuentemente, pero no por ello menos útiles.

CLUSTER (nombre_de_tabla): Fuerza el accesso a la tabla indicada utilizando un índice de tipo cluster. Los índices de este tipo se utilizan para localizar registros que comparten valores comunes de una forma rápida. La clave del índice cluster puede estar constituida por una o más columnas de la tabla. Los registros de la tabla son agrupados según la clave y almacenados físicamente juntos en el disco duro.

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.

La claúsula WITH en SQL y PL/SQL

La versión 9i de las bases de datos Oracle permite el uso de la claúsula WITH en SQL y PLSQL. Este comando permite reusar una consulta SELECT cuando esta hay que utilizarla más de una vez en una sentencia o consulta SQL compleja. Los resultados de la consulta definida en la claúsula WITH son almacenados en una tabla temporal pudiendo de esta forma mejorar el rendimiento de la sentencia principal.

Aunque no siempre conseguiremos mejorar el rendimiento utilizando la claúsula WITH, lo que sin duda facilitaremos es la lectura y el mantenimiento del código PL/SQL o SQL. Dentro de la claúsula WITH daremos un nombre a las consultas SELECT a reutilizar (WITH admite la definición de múltiples consultas con sólo separarlas por comas), dicho nombre será visible para todas las consultas definidas posteriormente dentro del mismo WITH. Obviamente, también será visible para la sentencia o consulta principal.

Operaciones aritméticas con fechas y la función TRUNC del PL/SQL

Siguiendo con el tema de algunos de los artículos de este blog, hoy pretendemos dejar ya zanjado el tutorial sobre como podemos trabajar en PL/SQL con los campos tipo DATE y TIMESTAMP de las bases de datos Oracle. En esta ocasión empezaremos hablando de las operaciones aritméticas que podemos realizar con estos campos y terminaremos escribiendo sobre la función estándar del PLSQL TRUNC.

Las bases de datos Oracle permiten realizar una gran variedad de operaciones aritméticas con los tipos de datos DATE y TIMESTAMP, pudiendo realizar dichas operaciones de diversas maneras.

Funciones de grupo en SQL (SUM, AVG, COUNT, MAX y MIN)

Las llamadas group functions o funciones de grupo son funciones que operan sobre múltiples registros de una sentencia SELECT. En este artículo hablaré sobre las funciones de grupo más comunes del SQL (en un artículo posterior también hablaré sobre las cláusulas GROUP BY y HAVING muy relacionadas con este tipo de funciones).

Lo primero que hay que saber de las funciones de grupo es que hacen cálculos sobre un grupo de registros devolviendo un resultado único, por lo que permiten, por ejemplo, obtener totales.