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.

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.

Enlos ejemplos que veréis a continuación entederéis rápidamente la gran utilidad de dicha sentencia. La sintaxis de la sentencia CASE es como sigue:

Los tipos de dato CHAR, VARCHAR2 y CLOB (Cadenas de caracteres en PL/SQL)

Después de haber hablado de los tipos numérico y de fecha del PL/SQL, llega el turno de empezar con las cadenas de caracteres. Lo primero que hay que saber al respecto es que una cadena de caracteres es una secuencia de símbolos pertenecientes a un conjunto particular de caracteres. En otras palabras, una cadena de caracteres puede estar compuesta por las letras del abecedario español, pero también podría estar compuesta por un conjunto de caracteres chinos o japoneses.

En PL/SQL existe tres tipos de cadenas de caracteres:

• De longitud fija: de manera que la cadena de caracteres se rellena con espacios en blanco si su longitud es menor que la declarada.
• De longitud variable: al definir la variable se declara la longitud máxima que puede alcanzar pero no se rellena con espacios si la longitud de la cadena es menor que la declarada. La máxima longitud de una variable de este tipo es de 32,767 caracteres.
• Objetos CLOB (Character Large Objects): se trata de un tipo especial del PLSQL que permite almacenar cadenas de caracteres de longitud variable de hasta 128 terabytes.

Análisis de la salida del comando TKPROF en PL/SQL y SQL

Ya hemos hablado en otro artículo acerca de las opciones y parámetros del comando TKPROF y de su uso para analizar los fichero de trazado PLSQL. Ahora es el momento de escribir acerca de cómo interpretar la salida de dicho comando. La salida del comando TKPROF muestra las estadíticas resultantes de la ejecución de una sentencia SQL o PLSQL agrupadas por fases de procesamiento. En el artículo "Pasos seguidos durante la ejecución de una sentencia SQL", ya hice una exposición detallada de lo que acontece en cada una de estas fases, no obstante, a continuación, voy a realizar un pequeño resumen.

Fases de procesamiento analizadas por el comando TKPROF

Fase de parsing (parse): Durante este paso se genera el plan de ejecución de la sentencia SQL o PL/SQL, se comprueban los permisos de ejecución y se verifica la existencia de los objetos referenciados en dicha sentencia (tablas, vistas, columnas, etcétera).

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.