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.

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.

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).

El parsing puede ser duro (hard) o blando (soft). El parsing duro se realiza cuando se genera el plan de ejecución, incluyendo la optimización del mismo, y dicho plan de ejecución se almacena en la librería cache. El parsing blando hace referencia a que cuando la sentencia SQL o PLSQL es enviada al kernel para generar el plan de ejecución, el kernel encuentra dicho plan en la librería cache, por lo que no es necesario generar el plan de ejecución, y sólo se necesitan verificar los permisos de acceso a los objetos Oracle utilizados en la sentencia.

Fase de ejecución (execute): Este paso se corresponde con la ejecución propiamente dicha de la sentencia SQL o PLSQL por parte del servidor de la base de datos Oracle. Para las sentencias INSERT, UPDATE y DELETE, este paso implica la modificación de los datos y, a veces, su ordenación. Para la sentencia SELECT, esta fase supone la identificación de los registros seleccionados.

Fase de fetching (fetch): Durante este paso el servidor Oracle devuelve los registros resultantes de la ejecución de una sentencia SELECT y, si es necesario, los ordena. Esta fase sólo afecta a las sentencias SELECT.

Columnas del informe

Las estadísticas entregadas por el comando TKPROF se organizan en siete categorías o columnas:

Count: Número de veces que una sentencia SQL o PL/SQL fue analizada según fases. A la hora de interpretar los resultados que aparecen en las otras columnas del informe generado por el comando TKPROF, es importante comprobar si esta columna toma valores mayores que uno, ya que si hemos utilizado la opción AGGREGATE=YES, el comando TKPROF agrupará la ejecución de todas las sentencias SQL o PLSQL idénticas en una sola tabla de resultados.

CPU: Tiempo total en segundos de uso de CPU según fases.

Elapsed: Lapso de tiempo total en segundos según fases.

Disk: Número de bloques de datos físicamente leídos desde disco según fases.

Query: Número de bloques de memoria intermedia (buffers) leídos en modo consistente. Las lecturas consistentes se producen generalmente para las sentencias SELECT. Una lectura consistente indica que los registros leídos que han sido modificados y que no han sido confirmados mediante el comando COMMIT, tienen que reconstruirse a partir de los segmentos de rollback.

Current: Número de bloques de memoria intermedia (buffers) leídos en modo normal (current). Las lecturas en modo normal se producen generalmente para las sentencias DML (sentencias de manipulación de datos).

Rows: Número de registros procesados por la sentencia SQL o PL/SQL. Este valor no incluye los registros procesados por las subqueries (sentencias SELECT incluídas en otras sentencias SQL o PLSQL). Para las sentencias SELECT, el número de registros devueltos se mostrará en la fase de fetching. Para las sentencias INSERT, UPDATE y DELETE, el número de registros procesados se mostrará en la fase de ejecución.

Ejemplo de salida resultado de la ejecución del comando TKPROF:

select categoria, trabajo, nombre, salario
from empleados, salarios
where salario between infsal and supsal
order by categoria, trabajo

call    count   cpu elapsed disk query current rows
------- -----  ---- ------- ---- ----- ------- ----
Parse       1  0.06    0.08    2    18       1    0
Execute     1  0.00    0.00    0     0       0    0
Fetch       1  0.01    0.01    1    11      12   14
------- -----  ---- ------- ---- ----- ------- ----
total       3  0.07    0.09    3    29      13   14

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 8 (SCOTT)

Rows     Execution Plan
-------  -------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     14   SORT (ORDER BY)
     14    NESTED LOOPS
      5     TABLE ACCESS (FULL) OF 'SALARIOS'
     70     TABLE ACCESS (FULL) OF 'EMPLEADOS'

Obviamente, si el fichero de trazado que estamos analizando con el comando TKPROF, contiene información sobre la ejecución de varias sentencias SQL o PL/SQL, la salida mostrará la información arriba indicada para cada una de las sentencias SQL que han sido analizadas.

Uso del comando TKPROF para formatear los ficheros de trazado PL/SQL

Ya hablamos en un articulo anterior acerca de como activar y desactivar la funcionalidad de trazado SQL o PLSQL. En este artículo voy a escribir acerca de como utilizar el comando del sistema operativo TKPROF, cuyo cometido es formatear los ficheros binarios generados mientras la traza SQL esta activa, de manera que éstos sean legibles. La sintaxis del comando es como sigue:

UNIX> tkprof fichero_trazado fichero_salida [opciones]

Cuando el comando TKPROF es ejecutado sin utilizar ningún argumento, la salida del comando muestra un mensaje de ayuda junto con la descripción de todas las opciones del comando disponibles.

Opciones del comando TKPROF

Sort=opción - Ordena las sentencias SQL de la traza en base a distintos criterios. Los criterios de ordenación más útiles a mí entender son:

    - prscpu: Ordenado según el tiempo de CPU.
    - prsela: Ordenado según el tiempo de ejecución.

Print=n - El informe mostrará únicamente las n primeras sentencias SQL o PL/SQL. Esta opción es especialmente útil si se utiliza conjuntamente con las opción sort.

Explain=usuario/password - Se conecta a la base de datos utilizando dicho usuario y genera los planes de ejecución (EXPLAIN PLAN) de las sentencias SQL o PL/SQL en el esquema de la base de datos especificado.

Insert=nombre_fichero - Crea un script SQL para cargar los resultados del comando TKPROF dentro de una tabla de la base de datos Oracle.

Sys=no - El informe no muestra las sentencias SQL ejecutadas por el usuario SYS. Esta opción es bastante útil ya que elimina todas sentencias SQL internas. Estas sentencias, al ejecutarlas internamente el sistema de la base de datos Oracle, no pueden ser optimizadas y no es necesario, por lo general, tenerlas en consideración.

Aggregate=no - No acumula los resultados de las sentencias SQL idénticas en un solo registro.

Record=nombre_fichero - Crea un script SQL con todas las sentencias SQL no recursivas encontradas en el fichero de trazado. Este script se puede utilizar posteriormente para repetir la sesión de optimización o para modificar y optimizar las sentencias SQL sobre dicho script y comprobar que el rendimiento mejora al ejecutarlo tras la modificación.

Table=esquema.nombre_tabla - Especifica el esquema y el nombre de la tabla donde se almacenará temporalmente el plan de ejecución antes de que se genere el fichero de salida. Este parámetro es ignorado si no se utiliza la opción explain. Puede ser útil cuando simultáneamente varios usuarios utilizan el comando TKPROF para optimizar el mismo esquema de la base de datos.

Ya he hablado acerca de como ejecutar el comando TKPROF, ahora queda la ardua tarea de escribir acerca de como interpretar la salida de dicho comando, pero esto será objeto de otro artículo en esta bitácora.

Como avance mencionaré algunas circunstancias que nos pueden llevar a malinterpretar la salida del comando TKPROF:

Problemas con el número de lecturas consistentes: Si en el momento de la ejecución de una sentencia SQL o PLSQL hay transacciones no confirmadas (uncommitted transactions) sobre la tabla utilizada, entonces el número de bloques leídos aumentará ya que la base de datos Oracle necesitará construir y leer bloques adicionales para mantener la integridad.

Interpretación del tiempo de ejecución: Si una sentencia DML (sentencias de manipulación de datos) muestra un tiempo de ejecución elevado, puede ocurrir que esto sea debido a que otra transacción esté bloqueando la tabla involucrada. Por esta razón, el tiempo de CPU es un mejor indicador que el tiempo de ejecución.

Diferencias en el tiempo: Si el plan de ejecución de la sentencia SQL analizada indica que la tabla en cuestión es accedida a través de un índice, pero las estadísticas del comando TKPROF muestran un número de bloques leídos muy elevado y, además, los valores para la columna current del informe no son cero, entonces probablemente lo que haya ocurrido es que la tabla fue realmente accedida realizando un escaneado completo (full scan). Este tipo de situaciones se pueden dar cuando la generación del fichero de trazado haya sido anterior a la creación del índice o, si estamos utilizando el optimizador basado en costes, a la regeneración de las estadísticas asociadas con la tabla involucrada.

La existencia de triggers: Los valores mostrados por el comando TKPROF a la hora de analizar una sentencia SQL incluyen todas las sub-sentencias SQL ejecutadas desde la sentencia principal. Es decir, si una sentencia SQL dispara un trigger, los recursos utilizados por dicho trigger serán incluidos en el informe final acerca de la sentencia principal.

Cómo obtener el plan de ejecución de una sentencia SQL o PL/SQL

Una de las formas más usuales de mejorar el rendimiento de una sentencia SQL o PL/SQL es analizar el plan de ejecución que devuelve el optimizador Oracle. En SQL*Plus se puede obtener dicho plan de ejecución, además de algunas estadísticas referentes al resultado de la ejecución de la sentencia SQL o PLSQL, utilizando el comando AUTOTRACE. Para obtener el plan de ejecución no hay necesidad de ejecutar dicho comando pero, ciertamente, si no lo utilizamos, la poca amigabilidad del comando que debemos ejecutar (EXPLAIN PLAN), el formato de dicho comando y lo complejo que resulta analizar el contenido de la tabla V$SQL_PLAN, hacen que, por mi parte, recomiende encarecidamente el uso del comando SQL*Plus AUTOTRACE.

En mi opinión, AUTOTRACE es una buenísima herramienta de diagnóstico y una excelente ayuda para optimizar sentencias SQL y PL/SQL. El comando AUTOTRACE es puramente declarativo, por lo que es mucho más fácil de utilizar que el comando EXPLAIN PLAN. La sintaxis del comando AUTOTRACE es como sigue:

SET AUTOTRACE OFF - Deshabilita el análisis (traceado) de las sentencias SQL.

SET AUTOTRACE ON - Habilita el análisis (traceado) de las sentencias SQL.

SET AUTOTRACE TRACEONLY - Habilita el análisis (traceado) de las sentencias SQL pero no devuelve la salida de dicha sentencia. Su uso es recomendable si sólo estamos analizando el rendimiento de la sentencia y no nos interesa conocer los registros que pueda devolver.

SET AUTOTRACE ON/TRACEONLY EXPLAIN - Muestra el plan de ejecución de la sentencia pero no muestra las estadísticas.

SET AUTOTRACE ON STATISTICS - Muestra las estadísticas pero no muestra el plan de ejecución de la sentencia.

Nota: Si se omiten las opciones EXPLAIN y STATISTICS, entonces al ejecutar una sentencia SQL se mostrarán tanto el plan de ejecución como las estadísticas.

Para poder utilizar la opción EXPLAIN del comando AUTOTRACE, es necesario crear la tabla PLAN_TABLE en el esquema del usuario, es por eso que este comando sólo puede ser ejecutado por determinados usuarios, aquellos para los que la mencionada tabla ya ha sido creada. Es importante pues, conocer los usuarios Oracle que han sido configurados para poder ejecutar el comando AUTOTRACE.

Por otro lado, para acceder a las estadísticas, hay que tener acceso a varias tablas del sistema en las que se almacenan los datos del rendimiento de las sentencias SQL. Los DBA pueden dar este acceso utilizando el script plustrce.sql. El nombre de este script puede variar dependiendo del sistema operativo. El DBA tiene que ejecutar dicho script como usuario SYS y, asignar al usuario en cuestión, el papel (role) correspondiente.

Una vez que se ha configurado convenientemente un usuario para que pueda acceder al plan de ejecución y a las estadísticas, basta habilitar el AUTOTRACE para que, al ejecutar una sentencia SQL, nos aparezca el plan de ejecución así como los correspondientes valores estadísticos.

Los valores estadísticos más importantes mostrados por la base de datos Oracle, una vez activado el comando AUTOTRACE, son los siguientes:

- DB block gets: Número de operaciones de entrada/salida realizadas sobre la memoria caché.
- Consistent gets: Número de operaciones de entrada/salida realizadas sobre los segmentos de rollback debido a cambios en la memoria caché.
- Physical reads: Número de bloques leídos desde el disco.
- Sorts (memory): Número de operaciones realizadas en memoria para ordenar los datos.
- Sorts (disk): Número de operaciones realizadas en disco para ordenar los datos.

A la hora de mejorar el rendimiento de una sentencia SQL o PL/SQL, debemos conseguir que el número de db block gets, consistent gets y physical reads sea bajo comparado con el número de registros devueltos por dicha sentencia. Por otro lado, la ordenación de los datos debe realizarse, siempre que sea posible, en memoria.

En cuanto a lo que se refiere al plan de ejecución, desde este enlace podéis acceder a un ejemplo de sentencia SQL con su correspondiente plan de ejecución y una breve interpretación de dicho plan: Ejemplo de plan de ejecución.

Como un primer consejo a la hora de analizar un plan de ejecución, me gustaría indicar que lo primero que hay que evitar son los FULL SCAN (recorrido de todos los registros de una tabla). No obstante, hay determinadas circunstancias bajo las que un FULL SCAN puede ser recomendable; así, cuando una tabla tiene pocos registros, puede ser conveniente realizar un FULL SCAN, en vez de acceder a la misma a través de un índice.

Ya he escrito algunos artículos que pueden ayudar a interpretar un plan de ejecución desarrollado por el optimizador Oracle:

- Puesta a punto de sentencias SQL.
- Bucles y problemas de rendimiento.
- Hints en PL/SQL para el modo de optimización.
- Hints en PL/SQL para determinar el método de acceso.

No os preocupéis que próximamente escribiré alguno más.

Tablas externas en PL/SQL

Las tablas externas permiten hacer consultas desde una base de datos Oracle sobre datos almacenados en un fichero de texto como si dicho fichero fuera una tabla de la base de datos. En Oracle 9i, sólo se pueden realizar operaciones de lectura con las tablas externas; en cambio, en Oracle 10g, se puede también escribir datos en una tabla externa que será creada en ese momento, es decir, no se puede utilizar una tabla externa que ya existe para realizar esta operación.

Aunque se pueden hacer consultas sobre las tablas externas, éstas no permiten todas las funcionalidades que permite Oracle sobre tablas normales. Por ejemplo, no es posible realizar algunas operaciones DDL (sentencias de definición de objetos como revoke, grant, etc.) sobre tablas externas aparte de la creación y actualización de la definición de la misma; por lo tanto no es posible crear índices sobre una tabla externa.

Oracle utiliza el SQL*Loader a través del driver ORACLE_LOADER para cargar datos desde un fichero de texto en la base de datos; y, por otro lado, el driver Data Pump (Bombeo de Datos) permite pasar datos desde la base de datos a un fichero de texto utilizando un formato propietario de Oracle, y, obviamente, dicho fichero de texto se puede cargar de nuevo en la misma u otra base de datos. Existen diferentes restricciones y maneras de proceder, pero se puede pensar que las tablas externas son otra opción alternativa al SQL*Loader y al Data Pump.

Por ejemplo, suponiendo que recibimos un informe .csv diariamente. En vez de escribir un script en SQL*Loader para importar los datos todos los días, se puede simplemente crear una tabla externa y escribir una sentencia SQL "insert ... select" para insertar los datos directamente en las tablas de la base de datos. Así que, diariamente podríamos colocar el fichero CSV en el directorio correspondiente, ejecutar la sentencia insert y tendríamos los datos cargados en nuestra base de datos.

Creación de una tabla externa

Puesto que los datos de las tablas externas están en ficheros de texto, estos ficheros deben estar en un lugar al que Oracle tenga acceso. Así pues, el primer paso es crear un directorio y dar acceso de lectura y escritura al usuario del sistema operativo que se encarga de ejecutar los procesos de la base de datos Oracle. Dicho directorio no puede ser un link simbólico, debe ser un directorio real.

$ cd $ORACLE_HOME
$ mkdir texternal
$ mkdir data
$ ls -l $ORACLE_HOME/texternal
total 30
drwxr-x---   2 oracle   dba         5120 Jul 17  2006 data

Después pondremos el fichero de texto en dicho directorio. En el presente ejemplo utilizaremos un fichero CSV (proyectos.csv):

3002508,ESP Pinto,Calidad,RZAPA001
3002509,ESP Pinto,Supervisión,CGAMI001
3002510,ESP Humanes,Calidad,RZAPA001
3002511,ESP Humanes,Supervisión,GDIAZ001
3002512,ESP Humanes,Instalación,HPERE001

El siguiente paso es crear el directorio en Oracle y dar permiso de lectura/escritura sobre dicho directorio al usuario de Oracle que creará la tabla externa. A la hora de crear el directorio hay que estar seguro de que se usa el path completo y que no se utiliza ningún link simbólico. En nuestro ejemplo supondremos que la variable $ORACLE_HOME tiene el valor /u01/app/oracle/ y por lo tanto el nombre del directorio con el path completo sería /u01/app/oracle/texternal/data.

SQL> connect sys as sysdba
Enter password:
Connected.

SQL> create or replace directory texternal_data
2     as '/u01/app/oracle/texternal/data';

Directory created.

SQL> grant read,write on directory texternal_data to userexte;

Grant succeeded.

El último paso es crear la tabla externa. El comando es exactamente el mismo que se utiliza para crear tablas normales, CREATE TABLE, pero incluye un bloque con sentencias específicas que informan a Oracle de como debe interpretar los datos almacenados en el fichero de texto.

SQL> connect userexte
Enter password:
Connected.

SQL> create table text_proyectos
2     (
3     orden number,
4     proyecto varchar2(30),
5     actividad varchar2(15),
6     supervisor varchar(8)
7     )
8     organization external
9     (
10    default directory texternal_data
11    access parameters
12      (
13      records delimited by newline
14      fields terminated by ','
15      )
16    location ('proyectos.csv')
17    );

Table created.

El comando CREATE TABLE no realiza ninguna validación de los datos que hay cargados en el fichero de texto, de hecho el comando va a funcionar incluso si el fichero de texto con los datos no está en el directorio. Con el comando CREATE TABLE sólo hemos creado los metadatos de la tabla externa en el diccionario de datos y le hemos indicado a Oracle como utilizar el driver ORACLE_LOADER para analizar los datos del fichero de texto.

Una vez que colocamos el fichero de texto en el directorio correcto, ya podemos acceder a los datos mediante la ejecución de un comando select:

SQL> select * from text_proyectos;

Orden   Proyecto    Actividad   Supervisor
------- ----------- ----------- ----------
3002508 ESP Pinto   Calidad     RZAPA001
3002509 ESP Pinto   Supervisión CGAMI001
3002510 ESP Humanes Calidad     RZAPA001
3002511 ESP Humanes Supervisión GDIAZ001
3002512 ESP Humanes Instalación HPERE001

5 rows selected.

Oracle utiliza el driver ORACLE_LOADER para procesar el fichero y, de igual forma que la utilidad SQL*Loader, crea un fichero de log en el que se almacena lo que ha ocurrido al procesar el fichero. El fichero de log se habrá creado en el directorio por defecto especificado en la sentencia CREATE TABLE que utilizamos para definir la tabla externa, el nombre del fichero vendrá determinado por el nombre de la tabla seguido por el ID del proceso del sistema operativo que procesó los datos de la tabla externa:

$ ls -l
total 45
-rw-r--r--  1 oracle  dba  1031 Jul 17  2006 TEXT_PROYECTOS_12345.log
-rw-------  1 oracle  dba   298 Jul 17  2006 proyectos.csv

Si Oracle detecta algún problema a la hora de procesar el fichero de texto, Oracle generará un error que será mostrado en la línea de comando así como en el fichero de log, además se crearán también un par de ficheros más, el badfile y/o el discardfile. Uno de los errores más típicos es dejar una línea en blanco al final del fichero de texto, Oracle intentará procesar esta última línea y, al no tener ningún dato, se producirá un error.

Se pueden configurar directorios separados para los distintos ficheros (LOG, BAD y DISCARD) así como para el fichero de texto con los datos. En mi opinión, es una buena idea utilizar un directorio para los datos y otro para los ficheros de log. Así pues, igual que tuvimos que crear un directorio para guardar los ficheros de texto con los datos, ahora tenemos que crear un nuevo directorio para almacenar los "logs", tanto en el sistema operativo (por ejemplo, /u01/app/oracle/texternal/log), como en Oracle (en este caso lo llamaremos texternal_log). Ahora utilizando el comando ALTER TABLE podemos cambiar la definición de la tabla externa:

SQL> alter table text_proyectos
1     access parameters
2     (
3     records delimited by newline
4     badfile texternal_log:'text_proyectos.bad'
5     logfile texternal_log:'text_proyectos.log'
6     discardfile texternal_log:'text_proyectos.dsc'
7     fields terminated by ','
8     );

También existe la opción de definir la tabla externa de forma que no se genere ningún fichero de log, bad o discard. En este caso la sentencia select va a fallar cuando se exceda el número máximo de registros rechazados por existir algún tipo de problema con los mismos, es exactamente lo mismo que ocurre con la utilidad SQL*Loader. Este límite se puede cambiar también con el comando ALTER TABLE:

SQL> alter table text_proyectos set reject_limit 250;

Carga de datos en una tabla de la base de datos

Ahora viene la parte interesante de este artículo y es donde voy a mostrar como podemos cargar los datos almacenados en una tabla externa en una tabla real de la base de datos. Además, es importante reseñar que podemos utilizar funciones PL/SQL para transformar los datos de la tabla externa antes de que sean cargados en las tablas de la base de datos.

Por ejemplo, suponiendo que en nuestra base de datos existe una tabla llamada "proyectos" con las columnas proyecto, actividad, nombre_supervisor y de que disponemos de una función PL/SQL denominada get_nombre_supervisor() que permite obtener el nombre de un supervisor en base al campo supervisor de nuestra tabla externa, entonces podemos construir la siguiente sentencia SQL para cargar los datos de nuestra tabla externa en la tabla proyectos de la base de datos:

SQL> insert into proyectos
1     (
2     select proyecto
3     ,  actividad
4     , get_nombre_supervisor(supervisor) nombre_supervisor
5     from text_proyectos
6     );

5 rows inserted.

Descarga de datos a una tabla externa

Oracle 10g permite crear una tabla externa utilizando datos existentes en la base de datos, estos datos se descargan a un fichero de texto utilizando el driver ORACLE_DATAPUMP. Este fichero de texto es almacenado con un formato propietario de Oracle que puede ser leído por el driver Data Pump. En este caso, es importante indicar en el comando CREATE TABLE, el tipo de driver a utilizar, ORACLE_DATAPUMP, ya que el driver por defecto es ORACLE_LOADER. La sintaxis del comando sería:

SQL> create table text_export_proy
2     organization external
3     (
4     type oracle_datapump
5     default directory texternal_data
6     location ('export_proy.dmp')
7     ) as select * from proyectos;

Evidentemente ahora podemos coger el fichero que acabamos de crear, export_proy.dmp, llevarlo a otro sistema y crear una tabla externa para leer los datos.

SQL> connect userexte@otradb
Enter password:
Connected.

SQL> create table text_proyectos
2     (
4     proyecto varchar2(30),
5     actividad varchar2(15),
6     nombre_supervisor varchar(30)
7     )
8     organization external
9     (
10    type oracle_datapump
10    default directory texternal_data
16    location ('export_proy.dmp')
17    );

Table created.

Conclusión

En este artículo hemos visto como podemos cargar y descargar datos en y hacia una base de datos utilizando tablas externas. Las tablas externas en 9i y 10g permiten mover datos integrando las funcionalidades de Oracle, SQL*Loader y Data Pump, con la mejora añadida de que se pueden utilizar sentencias SQL en el proceso.