Hints en PL/SQL para determinar el método de acceso

Ya hemos hablado de los hints para el modo optimización. En este segundo artículo continuaré hablando de los hints pero, en concreto, de aquellos que permiten indicar al optimizador Oracle el modo en que se debe acceder a los datos de las tablas. Este tipo de hints resultan extremadamente eficaces a la hora de optimizar una sentencia SQL.

En su día ya indiqué cual es la sintaxis de los hints pero creo que no está de más que la muestre de nuevo:

{ DELETE | INSERT | SELECT | UPDATE } /*+ HINT (parámetros) */

o

{ DELETE | INSERT | SELECT | UPDATE } --+ HINT (parámetros)

Los hints básicos que sirven para determinar el metodo de acceso a los datos de una tabla Oracle son los siguientes:

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.

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.