Vistas materializadas y la funcionalidad "Query Rewrite"

Ya he escrito anteriormente un par de artículos sobre vistas materializadas (materialized views): uno sobre los aspectos generales de las vistas materializadas en SQL y PLSQL y otro sobre el refresco de las vistas materializadas en SQL y PL/SQL. En este artículo voy a tratar una de las funcionalidades soportadas por las vistas materializadas, funcionalidad conocida como QUERY REWRITE.

Funcionalidad de reescritura de una consulta

Esta claro que acceder a una vista materializada puede ser significativamente más rápido que acceder a todas las tablas base utilizadas al crear dicha vista materializada. Es por esta causa por la que, si así lo hemos indicado al crear la vista materializada, el optimizador Oracle, si la consulta o query lo permite, puede reescribir el plan de ejecución de dicha consulta para acceder a la vista en lugar de a las tablas base. Obviamente, la reescritura de la consulta es transparente a las aplicaciones que la estén utilizando. Así pues, de alguna manera, el uso del QUERY REWRITE es similar al uso de un índice.

Los usuarios no necesitan tener privilegios especiales sobre la vista materializada para poder utilizar la reescritura de una consulta. Cualquier consulta ejecutada por un usuario que tenga permisos sobre las tablas base involucradas, podrá hacer uso de la reescritura de la consulta mediante el acceso a la vista materializada en lugar de acceder a las tablas base directamente. Esto sólo dependerá de lo que decida el optimizador.

Por otro lado, la funcionalidad de reescritura de una consulta puede ser habilitada o deshabilitada a la hora de crear o modificar una vista materializada:

CREATE MATERIALIZED VIEW nombre_vistam
.....
[{ENABLE|DISABLE} QUERY REWRITE]
.....
AS SELECT ... FROM ... WHERE ...

Cómo determinar si el optimizador usa el QUERY REWRITE

La mejor forma para saber si el optimizador está utilizando la funcionalidad de reescritura de una consulta es utilizar el comando EXPLAIN PLAN o la funcionalidad AUTOTRACE. Otro aspecto a considerar es que, si el optimizador utiliza una vista materializada, entonces se debe observar una mejora en el tiempo de respuesta de la consulta.

Control de la funcionalidad QUERY REWRITE

La funcionalidad de reescritura de una consulta sólo puede utilizarse cuando se utiliza el optimizador Oracle basado en costes.

Por otro lado, el parámetro dinámico QUERY_REWRITE_ENABLED se utiliza para configurar una sesión o instancia de una base de datos Oracle, indicando si permitimos al optimizador el uso o no de la funcionalidad de reescritura de una consulta. Si ponemos este parámetro a FALSE, entonces habremos desactivado la funcionalidad de QUERY REWRITE para dicha sesión o instancia.

QUERY_REWRITE_INTEGRITY es otro parámetro dinámico que nos permite configurar la funcionalidad de reescritura de una consulta para una sesión o instancia. Dicho parámetro acepta los siguientes valores:

ENFORCED: Se trata del valor por defecto. Si el parámetro toma este valor, entonces el optimizador reescribirá las consultas sólo si puede garantizar la consistencia de los datos entregados. Es decir, sólo las vistas materializadas que han sido actualizadas pueden ser utilizadas por la funcionalidad.

TRUSTED: El optimizador reescribirá las consultas basándose en relaciones declaradas previamente sin necesidad de que estas hayan sido impuestas (ENFORCED). Es decir, todas las vistas materializadas que estén debidamente actualizadas serán utilizadas por la funcionalidad de reescritura de consultas.

STALE_TOLERATED: Siempre que sea posible el optimizador utilizará las vistas materializadas aunque éstas no estén actualizadas. Obviamente esto puede causar que la consulta devuelva valores incorrectos.

También existen hints que permiten influir al optimizador en la decisión de utilizar o no la funcionalidad de QUERY REWRITE al elaborar el plan de ejcución de una consulta, estos hints son REWRITE y NOREWRITE.

Nueva wiki oficial de Oracle

Desde 1990, el sitio web Oracle Technology Network (OTN), ha proporcionado a la comunidad de usuarios de las bases de datos Oracle un medio informal de comunicarse e intercambiar conocimientos entre ellos mismos, así como con empleados de Oracle. En 2005, esta fórmula fue mejorada al surgir una gran colección de blogs oficiales de Oracle. No obstante, esto recursos contienen fundamentalmente información proporcionada por Oracle.

Ahora, por primera vez, clientes y asociados pueden complementar este contenido oficial con el que ha generado la comunidad OTN, todo gracias a la nueva wiki de Oracle. Para los que no sepan que es una wiki, diré que se trata de un software que permite a los usuarios editar, crear y enlazar páginas web de una manera muy sencilla. En esencia se trata de un sistema de gestión de contenidos muy fácil de usar y que facilita el cruce de enlaces y la publicación de artículos en colaboración con otros usuarios.

Con la nueva wiki de Oracle, puedes crear tu propio contenido web, incluyendo trucos y guías de instalación, documentación de proyectos, notas técnicas o cualquier otra cosa que capture tu imaginación. También se pueden publicar comentarios en las páginas web creadas por otros miembros de la comunidad Oracle. Sin duda, este mundo paralelo de Oracle formado por clientes y asociados, se trata del complemento perfecto al sitio web OTN y a los foros de discusión y blogs de Oracle.

Otro hecho que quiero mencionar es la existencia del llamado Oracle ACE Program, los Oracle ACEs son profesionales conocidos por sus profundos conocimientos en aplicaciones Oracle y por sus credenciales como entusiastas defensores de la comunidad Oracle. Los candidatos a ser Oracle ACEs pueden ser nominados por cualquier miembro de comunidad de aplicaciones y tecnología Oracle. Para poder acceder al programa Oracle ACE, los candidatos deben ser expertos que pongan su conocimiento de las aplicaciones Oracle al alcance de todos, a través de blogs, podcasts, conferencias, artículos o foros.

A continuación os pongo algunos enlaces a sitios de la Oracle Technology Network que no he mencionado anteriormente en el contenido de este artículo:

Notas de prensa
Descargas de software gratis
Documentación
Centros de tecnología
Podcasts
Artículos técnicos
Foros de discusión
Calendario de eventos para desarrolladores

Cambios de rendimiento en una sentencia SQL al activar el trazado

Hace unos días un lector del blog me enviaba un email contándome un "extraño" problema de rendimiento que tenía con una sentencia SQL. Dicha sentencia SQL tardaba mucho tiempo en devolver resultados y, tras activar la utilidad de trazado SQL (SQL_TRACE=TRUE), el problema desaparecía y la respuesta de la sentencia SQL era inmediata.

La verdad es que el fenómeno no es tan extraño una vez que se conoce la causa. Cuando se activa el trazado haciendo SQL_TRACE=TRUE, lo que ocurre es que la sesión Oracle utiliza una nueva área de SQL compartido. Esto supone que el parsing (ver artículo sobre las fases durante el procesamiento de una sentencia SQL) de cualquier sentencia SQL que se ejecute después de activar el trazado vuelva a tener lugar o que, de existir una versión de dicha sentencia SQL ya parseada en la nueva área de SQL compartido, dicha versión no coincida con la versión existente cuando el trazado no está activo. Esto causa que, cuando la sentencia SQL utiliza variables (bind variables), puesto que los valores reales de dichas variables son tomados en el momento del parsing, muy probablemente, los planes de ejecución de la misma sentencia SQL sean diferentes antes y después de activar el trazado al haberse generado utilizando valores de variable distintos.

Veamos un ejemplo:

SQL> CREATE TABLE pepe AS
 2   SELECT CASE WHEN rownum = 1 THEN 1
 3   ELSE 0 END pepe_id, all_tab_columns.*
 4   FROM all_tab_columns
 5   /
Table created.

SQL> CREATE INDEX pepe_idx ON pepe(pepe_id);
Index created.

SQL> BEGIN
 2   DBMS_STATS.gather_table_stats (
 3   user, 'T', method_opt=>
 4   'for all'||
 5   'indexed columns'||
 6   'size 254' );
 7   END;
 8   /
PL/SQL procedure successfully completed.

De esta manera habremos generado la tabla pepe que contendrá un registro con la columna pepe_id=1, mientras que para el resto de registros (digamos que alrededor de 75.000), dicha columna tomará el valor de 0.

Resulta pues evidente que, si utilizamos el optimizador basado en costes, al haber generado los correspondientes histogramas con el procedimiento PL/SQL gather_table_stats, los planes de ejecución serán muy diferentes si ejecutamos SELECT * FROM pepe WHERE pepe_id=1, sentencia SQL para la cual el optimizador elegirá utilizar el índice pepe_idx, o si, por contra, ejecutamos SELECT * FROM pepe WHERE pepe_id=0, sentencia para la cual el optimizador elegirá realizar un full scan de la tabla.

Pero que ocurriría si utilizamos una variable a la hora de ejecutar la sentencia SQL, es decir, si ejecutamos SELECT * FROM pepe WHERE pepe_id=:id. En este caso la respuesta varía dependiendo de cual es la versión de la base de datos Oracle que estemos utilizando:

Base de datos Oracle 8i Release 3 y anteriores

En este caso el optimizador dispone de la siguiente información, la columna pepe_id puede tomar dos valores (1 y 0) y, además, la tabla tiene unos 75.000 registros. Debido al hecho de que la columna puede tomar sólo dos valores, el viejo optimizador de la versión 8i supondrá que para cualquier valor de la variable :id, la consulta SELECT asociada devolverá aproximadamente la mitad de los registros de la tabla. Así que, la decisión más probable del optimizador será realizar un escaneado completo (full scan) de la tabla.

Bases de datos Oracle 9i y 10g

Si disponemos de una versión de la base de datos Oracle incluida entre las versiones 9i y 10g, entonces la base de datos Oracle esperará a que se suministre el valor de la variable, antes de que el optimizador decida cual es el mejor plan de ejecución. Esto es lo que se conoce como bind variable peeking (que traducido vendría a ser algo como echar un vistazo a la variable). Así pues, en lo que se refiere a nuestra consulta SELECT, el optimizador elegirá bien realizar un full scan (:id=0), o bien utilizar el índice pepe_idx (:id=1), dependiendo del valor de la variable :id.

Y aquí es donde nos podemos encontrar con un problema de rendimiento ya que, el plan de ejecución que el optimizador utilice por primera vez, será el plan de ejecución que se almacene en el área de SQL compartido, y dicho plan será el que se utilice para ejecutar nuestra sentencia SQL SELECT * FROM pepe WHERE pepe_id=:id, independientemente del valor que demos a la variable :id con posterioridad.

Entonces ya sabemos que es lo que le pasó al lector que inspiró este artículo:

a) Alguien ejecuto la consulta en cuestión utilizando un determinado valor para la variable :id.

b) El plan de ejecución correspondiente a dicho valor de la variable se almacenó en el área de SQL compartido.

c) Nuestro lector ejecuto la misma consulta pero utilizando otro valor para la variable :id. El optimizador reutilizó el plan de ejecución almacenado en el área de SQL compartido. Dicho plan no era el más eficiente para el nuevo valor de la variable, de hecho se trataba de un plan de ejecución bastante pobre. Como resultado la consulta tardó en responder más tiempo del esperado.

d) Posteriormente nuestro lector activó el trazado (SQL_TRACE=TRUE) y volvió a ejecutar la consulta pero, en este caso, al estar el trazado activo, el plan de ejecución no se sacó del área de SQL compartido, sino que se elaboró un nuevo plan de ejecución mucho más eficiente, dando como resultado que la sentencia SQL se ejecutase de forma rápida.

Posibles soluciones

Existen algunas soluciones que nos permitirán evitar este tipo de situaciones provocadas por el bind variable peeking:

a) No utilizar variables (bind variables). En el ejemplo que he puesto es bastante evidente que no es necesario utilizar variables, simplemente podemos utilizar constantes (valores literales) en nuestra cláusula WHERE. Esta es, sin duda, la mejor solución.

b) No generar estadísticas que puedan hacer variar los planes de ejecución. Obviamente, si no hubiéramos generado los histogramas de la tabla pepe, el optimizador siempre generaría el mismo plan de ejecución con independencia del valor de la variable. A veces esto puede ser recomendable, pero si no generamos estadísticas, entonces perderemos algunas de las ventajas derivadas de utilizar el optimizador basado en costes.

c) Cambiar el valor del parámetro CURSOR_SHARING para que en vez de FORCE sea SIMILAR. Si utilizamos el valor SIMILAR evitaremos que el bind variable peeking entre en juego.

d) Actualizar nuestra base de datos Oracle a la versión 11g, ya que esta versión está dotada de una nueva funcionalidad conocida como intelligent cursor sharing (compartición de cursores inteligente). Así pues, para nuestra consulta ejemplo, el optimizador de la versión 11g es capaz de identificar que un plan de ejecución no es suficiente para todos los valores posibles de la variable :id, permitiendo que se generen varios planes de ejecución distintos para una misma sentencia SQL.

Optimización SQL y PL/SQL - Código compartido

Cuando alguien solicita la ejecución de algún código SQL o PL/SQL, Oracle busca dicho código o sentencia en el área de SQL compartido. Si la sentencia y su correspondiente análisis sintáctico (parsing) existen en la librería caché de la base de datos, entonces Oracle reutiliza el ejecutable correspondiente. Dentro de este área de SQL compartido, cada sentencia SQL ocupa un espacio determinado conocido como área contextual o, en terminología inglesa, cursor (ojo, esto no tiene nada que ver con los cursores PL/SQL). Cada cursor localizado en el área de SQL compartido contiene la siguiente información:

- El análisis sintáctico de la sentencia SQL (ver artículo sobre las fases del procesamiento de una sentencia SQL).

- El plan de ejecución.

- La lista de objetos de la base de datos que son referenciados por la sentencia.

Si dos usuarios ejecutan la misma sentencia SQL o PL/SQL, entonces ambos utilizaran el mismo cursor. Los beneficios de los cursores compartidos son evidentes:

- Reducción del tiempo de parsing o análisis sintáctico de la sentencia SQL o PLSQL.

- Las necesidades de memoria se ajustan dinámicamente y el uso de la misma puede mejorar de forma importante.

Los cursores sólo pueden ser compartidos por sentencias SQL que tienen las siguientes características:

- Se trata de sentencias SQL idénticas.

- El texto de las sentencias SQL debe ser exactamente el mismo, incluyendo mayúsculas, espacios, tabuladores, retornos de carro y comentarios.

- Los objetos de la base de datos Oracle a los que hace referencia la sentencia SQL deben ser también idénticos. Por ejemplo, dos tablas pueden recibir el mismo nombre pero pertenecer a diferentes usuarios o esquemas y, por tanto, tratarse a nivel de base de datos de tablas diferentes.

- Los tipos de las variables usadas en la sentencia SQL deben ser iguales (ver tipos de datos en PL/SQL). No es necesario que los nombres de las variables sean idénticos, es decir, sentencias SQL como SELECT * FROM clientes WHERE cliente_id = :c y SELECT * FROM clientes WHERE cliente_id = :d pueden ser sentencias idénticas ya que las variables c: y d: son renombradas internamente (la primera variable de una sentencia SQL es siempre renombrada como :b1, la segunda como :b2 y así sucesivamente).

Con respecto a este punto debo mencionar que la mayoría de las herramientas Oracle, tales como PL/SQL, precompiladores y Oracle Developer, realizan un preprocesado de las sentencias SQL para hacerlas tan idénticas como sea posible mediante la eliminación de comentarios y de espacios innecesarios, así como convirtiendo mayúsculas y minúsculas cuando esto es posible. SQL*Plus es la excepción y envía las sentencias SQL tal y como son escritas por el usuario.

Por esta razón es muy importante que, cuando escribamos código SQL y PLSQL, creemos código lo más genérico posible mediante:

- La utilización de procedimientos, funciones y paquetes PL/SQL almacenados.

- El uso de triggers PL/SQL.

- Hacer llamadas a triggers y procedimientos almacenados en la base de datos cuando utilicemos Oracle Developer.

- Escribir librerías de rutinas y procedimientos.

También es crucial que sigamos unos estándares a la hora de escribir código SQL:

- Seguir unos estándares para todo tipo de sentencias, incluyendo las sentencias en código PLSQL.

- Desarrollar normas en cuanto al uso de mayúsculas y minúsculas.

- Desarrollar normas en cuanto a la utilización de espacios, tabuladores y retornos de carro.

- Seguir unos estándares para los comentarios. Preferiblemente los comentarios deben mantenerse fuera de las sentencias SQL o PL/SQL.

- Utilizar los mismos nombres a la hora de referirse a objetos de la base de datos idénticos. Por ejemplo, aunque incomodo puede resultar interesante preceder a los nombres de las tablas con el nombre del esquema (o usuario) al que pertenecen.

El refresco de las vistas materializadas en SQL y PL/SQL

Ya he hablado en otro artículo acerca del funcionamiento básico de las vistas materializadas (materialized views), en éste voy a exponer los distintos tipos de refresco que se pueden utilizar para actualizar una vista materializada con los cambios provocados por las actualizaciones en las tablas base utilizadas en la misma. El tipo de refresco que debemos elegir dependerá de la frecuencia de actualización de las tablas base y de las necesidades que tengamos de disponer de datos exactos.

Tipos de refresco

COMPLETE: Este tipo de refresco implica el borrado de los datos existentes y la reinserción de todos los datos mediante la reejecución de la consulta SELECT que define la vista materializada.

FAST: El refresco aplica sólo a los cambios realizados sobre las tablas base desde el último refresco. Puede ser de dos tipos:

- Utilizando los logs de la vista materializada: En este caso todos los cambios sobre las tablas base se almacenan en dichos logs, aplicándose en el momento del refresco a la vista materializada. Estos logs deben ser creados sobre todas las tablas base de la vista utilizando el comando CREATE MATERIALIZED VIEW LOG, es decir, necesitaremos crear un log por cada tabla base que se utilice en la vista materializada.

- Utilizando rangos ROWID: Este tipo de refresco necesita de otro tipo de logs, los llamados logs de carga directa (direct loader logs).

Conviene puntualizar que no todas las vistas materializadas pueden soportar el refresco FAST. Por ejemplo, el uso de funciones SQL como SUM, AVG, MAX, MIN o COUNT no son admitidas por este tipo de refresco.

FORCE: Es la opción por defecto si no se selecciona ningún tipo de refresco a la hora de crear la vista materializada. Este tipo de refresco funciona de la siguiente manera, si es posible la vista se refrescará utilizando el mecanismo FAST, en caso contrario se empleará la opción COMPLETE.

NEVER: Esta opción suprime todos los refrescos de la vista materializada.

Formas de refresco

Refresco manual: Los refrescos manuales de las vistas materializadas se realizan utilizando el paquete PL/SQL estándar DBMS_MVIEW. Este paquete incluye un buen número de funciones y procedimientos PLSQL que permiten gestionar las vistas materializadas. Entre ellos cabe destacar:

DBMS_MVIEW.REFRESH ('nombre_vista_materializada') - Refresca una vista materializada específica.

DBMS_MVIEW.REFRESH_DEPENDENT ('nom_tab1, nom_tab2, ...') - Refresca todas las vistas materializadas que utilicen como tabla base alguna de las tablas o vistas materializadas indicadas en la lista (los nombres de las tablas o vistas materializadas deben separarse mediante comas).

DBMS_MVIEW.REFRESH_ALL_MVIEWS (n) - Refresca todas las vistas materializadas del sistema devolviendo un entero (n) que indica el número de registros que se han refrescado.

Todos estos procedimientos y funciones admiten parámetros adicionales entre los que cabe mencionar: el tipo de refresco (? - force, f - fast, c - complete), el segmento de rollback que se debe usar durante el refresco, si se continúa (true) o no (false) realizando el refresco tras detectar un error, si el refresco de todas las vistas materializadas se realiza en una sola transacción de manera que si falla el refresco de una vista falla el refresco de todas (true) o de si cada vista materializada se refresca en transacciones separadas (false).

Los refrescos manuales requieren que los parámetros del sistema JOB_QUEUE_PROCESSES y JOB_QUEUE_INTERVAL estén configurados para permitir la ejecución de trabajos encolados.

Refresco automático: Esta forma de refresco puede realizarse de dos formas:

ON COMMIT: La vista materializada se refresca cada vez que se ejecuta un COMMIT sobre alguna de las tablas base de la vista. Esto significa que la ejecución del COMMIT tomará más tiempo lo que puede afectar seriamente al rendimiento. Yo personalmente no recomiendo este tipo de refresco.

Refresco programado: El refresco se programa para que ocurra a una hora o tiempo determinado. Por ejemplo, una vista se podría programar para que se refresque todos los días a una determinada hora mediante el uso de las cláusulas START WITH (seguido de la hora en formato datetime del primer refresco automático) y NEXT (seguido de una expresión en formato datetime que se utilizará para calcular el intervalo entre refrescos automáticos). En este caso también es necesario configurar correctamente el parámetro del sistema JOB_QUEUE_PROCESSES.

Ejemplo:

CREATE MATERIALIZED VIEW nombre_vm
...
REFRESH START WITH ROUND(SYSDATE + 1) + 9/24
NEXT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') + 16/24
AS SELECT ...;

En caso del ejemplo, la base de datos Oracle refrescará automáticamente la vista materializada mañana a la 9:00 AM y posteriormente todos los martes a la 4:00 PM.

SQL y PL/SQL - La nueva 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.

Las ventajas de la sentencia SQL MERGE son claras:

- Permite con una misma sentencia realizar un UPDATE si el registro existe, o un INSERT si se trata de un nuevo registro.

- La sentencia puede paralelizarse de forma transparente.

- Se evita la necesidad de realizar actualizaciones múltiples.

- Es especialmente útil para realizar operaciones en masa y, como ya he mencionado, en aplicaciones de data warehousing.

- El rendimiento de la base de datos mejora ya que, al necesitarse menos sentencias SQL para realizar las mismas operaciones, también se necesitan menos accesos a las tablas fuente.

Ejemplo de sentencia MERGE

En el presente ejemplo voy a partir del hecho de que tenemos dos tablas en un data warehouse, una de ellas con muchos registros denominada clientes y otra más pequeña denominada datos_cli cuyos registros deben insertarse en la tabla clientes. En pocas palabras, estoy suponiendo que la tabla datos_cli contiene los cambios a realizar sobre la tabla clientes. La sentencia SQL MERGE que debemos utilizar para actualizar la tabla cliente podría ser la siguiente:

MERGE INTO clientes cli USING datos_cli dac
  ON (cli.cliente_id = dac.cliente_id)
  WHEN MATCHED THEN
    UPDATE SET 
      cli.nombre = dac.nombre,
      cli.direccion = dac.direccion
  WHEN NOT MATCHED THEN
    INSERT (cliente_id, nombre, direccion)
    VALUES (dac.cliente_id, dac.nombre, dac.direccion);

Así pues, la sentencia MERGE del ejemplo realizará las siguientes operaciones:

- Si existe un registro en datos_cli con el mismo ID de otro registro en la tabla clientes, entoces actualizará el valor de los campos nombre y dirección de la tabla clientes con los valores correspondientes de la tabla datos_cli.

- Si no existe un registro en clientes con el ID del registro de datos_cli que está siendo procesado, entonces se insertará dicho registro en la tabla clientes.

En resumen, la sintaxis de la sentencia MERGE debe incorporar:

Una cláusula INTO, que especifica la tabla destino donde los registros serán actualizados o insertados.

Una cláusula USING, especificando el origen de los datos que van a ser insertados o que van a servir para actualizar la tabla destino. El origen de los datos puede tratarse de una tabla, una vista, o del resultado de la ejecución de una consulta SELECT.

Una cláusula ON, que especifica la condición bajo la cual se realizará, bien la operación UPDATE (si la condición se cumple), bien la operación INSERT (si la condición no se cumple).

Las cláusulas WHEN MATCHED | NOT MATCHED, que son las que indican a la base de datos Oracle que acción realizar si se cumple o no se cumple la condición del ON. Estas dos cláusulas se pueden poner en cualquier orden.

Artículos relacionados:
La sentencia INSERT multitabla de la versión 9i de Oracle.
La cláusula WITH de la versión 9i de la base de datos Oracle.

SQL y PL/SQL - La sentencia INSERT multitabla de Oracle 9i

La versión 9i de las bases de datos Oracle ha introducido la posibilidad de utilizar sentencias INSERT multitabla. Así pues, la sentencia SQL o PLSQL INSERT... SELECT ha cambiado ligeramente su sintaxis, de manera que ahora permite la inserción de datos en más de una tabla de la base de datos de forma paralela. Existen dos formas de utilizar el comando INSERT multitabla: no condicional y condicional. En la forma no condicional, una cláusula compuesta INTO se ejecuta cada vez que la consulta SELECT devuelve un registro. En la forma condicional, las cláusulas compuestas INTO figuran dentro de cláusulas WHEN a partir de las que se determina si la correspondiente cláusula compuesta INTO se ejecuta o no.

Una claúsula compuesta INTO consiste de una o más cláusulas INTO. Una cláusula INTO debe especificar la tabla de la base de datos sobre la que se van a insertar los datos. Esta cláusula no admite alias. La cláusula INTO tambien proporciona el valor del los campos a ser insertados mediante la cláusula VALUES. La expresiones usadas en la cláusula VALUE pueden tratarse de cualquier expresión permitida, pero siempre debe hacer referencia a columnas devueltas por la consulta SELECT incluida en el INSERT.

Veamos un ejemplo de la forma no condicional:

INSERT ALL
 INTO productos
 VALUES (producto_id, producto, cantidad)
 INTO ventas
 VALUES (cliente_id, producto_id, cantidad, total)
SELECT cliente_id,
      producto_id,
      producto,
      SUM(cantidad) cantidad,
      SUM(precio) total
FROM   pedidos, lineas_pedidos
WHERE  pedidos.pedido_id = lineas_pedidos.pedido_id
GROUP BY cliente_id, producto_id, producto;

En el ejemplo vemos como la sentencia INSERT se utiliza para insertar los valores producto_id, producto y cantidad en la tabla productos y los valores cliente_id, producto_id, cantidad y total en la tabla ventas, todo ello utilizando una sola sentencia SQL o PL/SQL. Resulta obvio comentar que la sentencia que he utilizado como ejemplo, podría escribirse de forma alternativa mediante el empleo de dos sentencias INSERT.

La forma condicional de las sentencias SQL o PLSQL INSERT multitabla dispone, a su vez, de dos opciones:

1) Forma condicional FIRST, sólo se insertan los valores especificados en la primera condicion verdadera.

2) Forma condicional ALL, se insertan los valores especificados en todas las condiciones verdaderas.

La sintaxis sería:

INSERT [ALL/FIRST]
WHEN  THEN
 INTO  (col, col, ...) VALUES (val, val, ...)
 .....
 INTO  (col, col, ...) VALUES (val, val, ...)
WHEN  THEN
 INTO  (col, col, ...) VALUES (val, val, ...)
 .....
 INTO  (col, col, ...) VALUES (val, val, ...)
ELSE
 INTO  (col, col, ...) VALUES (val, val, ...)
 .....
 INTO  (col, col, ...) VALUES (val, val, ...)
SELECT ...;

Ventajas de las sentencias INSERT multitabla en SQL y PL/SQL

1) Eliminan la necesidad de utilizar múltiples sentencias INSERT ... SELECT para añadir registros en varias tablas de la base de datos.

2) Elimina la necesidad de utilizar un procedimiento PL/SQL para realizar diferentes inserciones de registros dependiendo de diferentes condiciones lógicas. Esto ya se puede realizar con una única sentencia SQL del tipo INSERT ... SELECT, mediante el uso de la cláusula WHEN.

3) Mejora el rendimiento significativamente ya que la consulta SELECT correspondiente, al existir un sólo comando INSERT, sólo tienen que ejecutarse una vez, en lugar de tener que repetirse su ejecución en cada INSERT.

Artículos relacionados: La nueva cláusula WITH de la versión 9i de la base de datos Oracle.