Tablas externas en Oracle9i

por Dave Moore

Aquí está un ejemplo paso a paso de como crear una tabla externa y ejecutar query's de esta fuente de datos dentro de Oracle, junto con una discusión de los usos prácticos para las tablas externas y su funcionamiento.

ORACLE9i tiene muchas nuevas características, y uno de mis favoritas es la capacidad de crear las tablas externas. Una tabla externa es una tabla donde la estructura se define dentro de la base de datos, aunque sus datos residen externamente en unos o más archivos en el sistema operativo (véase el cuadro 1). Las tablas externas son muy similares a las tablas regulares en Oracle, a menos que los datos no se almacenen en datafiles de Oracle y no sean manejados por la base de datos.

Cuadro 1: Estructura externa de la tabla en Oracle

Ejemplo

Este ejemplo comienza con la información de producto enumerada en una hoja de balance de Microsoft Excel (véase el cuadro 2). Los datos estan en formato (CSV) , D:productsproducts.csv. La hoja de balance contiene tres columnas: Número, descripción, y precio del producto. Este archivo contiene los datos que preguntaremos de Oracle.

Cuadro 2: Los datos de producto en Excel.

Después de que se guarde el archivo de excel, la tarea siguiente es crear un objeto "DIRECTORIO" , que es donde esta fisicamente el archivo. Este DIRECTORIO se requiere para crear la tabla externa.

SQL> CREATE DIRECTORY PRODUCT_DIR AS 'd:products';
Directory created.

Ahora la tabla externa es creada usando el comando de la CREATE TABLE :

create table products (
product_no number,
description varchar2(100),
price varchar2(20)
)
organization EXTERNAL (
type oracle_loader
default directory PRODUCT_DIR
access parameters
( records delimited by newline
badfile 'products.bad'
logfile 'products.log'
fields terminated by ','
)
location ('products.csv')
)
reject limit unlimited
/

La primera parte de la declaración de Create Table no tiene nada nuevo. Sin embargo, la parte siguiente de la declaración especifica ORGANIZATION EXTERNAL, que indica que esta tabla es una tabla externa. Esta parte de la declaración también especifica un tipo de Oracle_loader, el cual es solamente soportado por Oracle. Oracle_loader es realmente un tipo de objeto TYPE de Oracle definido en la base de datos para manejar los procesos.

También note que el directorio es parte de la declaración default directory PRODUCT_DIR; en esta linea se le indica a Oracle dónde encontrar los archivos.

La parte siguiente de la declaración especifica los parámetros del acceso, que deben parecer familiares a cualquier persona que ha utilizado SQL*Loader:

records delimited, especifica los caracteres que será utilizado para separar las filas.
badfile, especifica el archivo que el Oracle utilizará almacenar las filas rechazadas.
logfile, especifica el archivo que el Oracle utilizará almacenar la información del registro. La documentación de cualquier error será proporcionada en este archivo.
fields, especifica el separador distinguirá una columna de otra durante la carga.

Finalmente, se especifican la localización y el límite del rechazo:

location, la localización proporciona el nombre del archivo real al acceso. Si Oracle necesita tener acceso a archivos múltiples, pueden ser especificados como sigue:
location ('file1.dat', 'file2.dat')

reject limit, especifica el número de las filas que pueden ser rechazadas antes de que el comando devuelva un error. Si se alcanza este umbral, el error siguiente aparece al intentar tener acceso a la tabla:
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

El DDL para crear la declaración funcionará incluso si el archivo no existe en el sistema. Por un lado, usted no sabrá si la tabla fue creada con éxito hasta que una sentecnia se ejecute contra la tabla. Inversamente, el archivo no tiene que existir cuando se crea la tabla. De hecho, el archivo puede venir e ir según lo necesitado, este esquema es absolutamente acostumbrado en ambientes de OLAP.

Una vez creada la tabla, si otro usuario intenta tener acceso a la tabla a este punto, este usuario recibirá el error:

SQL> select count(*) from dave.products;
select count(*) from dave.products
*
ERROR at line 1:
ORA-06564: object PRODUCT_DIR does not exist

Para prevenir este error, usted debe dar acceso de lectura y escritura en el directorio para cualquier usuario que desee consultar datos de la tabla. El conceder acceso de SELECT en la tabla permitirá que el objeto sea considerado, pero usted debe también conceder el acceso al objeto subyacente del directorio.

grant read, write on directory products_dir to alex;

Listing 1: Querying the table.

SQL> select product_no, substr(description,1,40) "Desc", Price from products;

PRODUCT_NO Desc PRICE
---------- -------------------------------------- ---------------
12300 Robin Yount Autographed Baseball $29.99
12301 George Brett Autographed Baseball $19.99
12302 Dale Murphy Autographed Baseball $19.99
12303 Paul Molitor Autographed Baseball $19.99
12304 Nolan Ryan Autographed Baseball $19.99
12305 Craig Biggio Autographed Baseball $19.99
12306 Jeff Bagwell Autographed Baseball $19.99
12307 Barry Bonds Autographed Baseball $19.99
12308 Mark McGuire Autographed Baseball $19.99
12309 Sammy Sosa Autographed Baseball $19.99
12310 Jeff Kent Autographed Baseball $19.99
12311 Roger Clemens Autographed Baseball $19.99
12312 Goose Gossage Autographed Baseball $19.99
12313 Derek Jeter Autographed Baseball $19.99

14 rows selected.

El acceso de lectura/grabación significa que en Oracle estará permitido escribir a ese directorio cuando necesita poner al día el logfile o badfile. Como usuario del Sistema Operativo, usted no tiene acceso a esos archivos en el sistema operativo a menos que su identificación tenga privilegios apropiados; consecuentemente, la seguridad no se compromete.

Después de crear la tabla externa y de conceder privilegios, la tabla se puede utilizar como cualquier otra tabla (véase el listado 1).

La tabla externa se puede utilizar como substituto de SQL*Loader y una tabla regular se puede utilizar para llevar a cabo sus datos:

INSERT INTO PROD.PRODUCTS AS SELECT * from DAVE.PRODUCTS;

Los datos que estaban en excel se cargan en Oracle, lo que permite que sea sostenido y que se realice mejor que una tabla externa.

Limitaciones

Las tablas externas en Oracle9i tienen las siguientes limitaciones:

Son inalterables, así que ninguna operacion de manipulación de datos (DML) (tales como insert, update o delete) se pueden realizar contra ellos. También, no se pueden definir índices en la tabla. Oracle tiene planeado apoyar la escritura en estas tablas en un lanzamiento futuro.

No soporta archivos más grandes de 2GB. Si usted procura tener acceso a un archivo más grande que 2GB, Oracle falla con el siguiente error :

KUP - 04039: unexpected error while trying to find file
in director

Ciertos comandos contra la tabla, por ejemplo ANALYZE, fallarán.

SQL> analyze table products compute statistics;
analyze table products compute statistics

*
ERROR at line 1:
ORA-30657: operation not supported on external organized
Table

Esta limitación es importante porque la mayoría del DBAs tiene escrituras que restauren regularmente la estadística del objeto basada en un esquema. Si usted intenta generar estadística en una tabla externa, el comando fallará.

Los datos en tablas externas no se sostienen como parte de rutinas de Backup regular de Oracle, porque están fuera del alcance de la base de datos.

Funcionamiento

Uno espera que el núcleo de Oracle incurra en más gastos indirectos al procesar las tablas externas. Oracle TYPE and TYPE BODY named SYS.ORACLE_LOADER existen en la base de datos y procesan todas las declaraciones que tienen acceso a las tablas externas. Este proceso aumenta los gastos indirectos para tener acceso a los datos, y cuando está comparado a una tabla regular es muchas veces más lento. Oracle debe traer y realizar las tareas que no realiza (por ejemplo las conversiones, manejando rechazamientos, y registrándolos) y es normalmente por lo tanto, perceptiblemente más lento.

Experimenté con el funcionamiento de tablas externas creando una tabla interna con los datos exactos como el externo:

SQL> create table products_internal as select * from
products;

Table created.

La tabla contuvo 5.292 filas, con los mismos datos que en la hoja de balance. La tabla interna no tenía índices o llaves primarias definidas. De acuerdo con la escritura demostrada en el listado 2, la tabla interna era constantemente 8-10 veces más rápida en tener acceso que la externa.

Recomendación, las tablas externas se deben utilizar como medios de carga de datos en las tablas internas y no se deben utilizar como fuente de datos externa.

Listado 2: El acceso a la tabla interna es perceptiblemente más rápido que a la tabla externa.

set term off
col a new_value start
select dbms_utility.get_time() a from dual;
select count(*) from products_internal where product_no = 12313;
col b new_value stop
select dbms_utility.get_time() b from dual;
col c new_value answer
select (&stop - &start) c from dual;
col d new_value start_ext
select dbms_utility.get_time() d from dual;
select count(*) from products where product_no = 12313;
col e new_value stop_ext
select dbms_utility.get_time() e from dual;
col f new_value answer_ext
select (&stop_ext - &start_ext) f from dual;
col ans form 999
col ans_ext form 999
set term on
prompt
prompt
select 'Internal Table Execution Time in ms ', &answer ans
from dual;
select 'External Table Execution Time in ms ', &answer_ext ans_ext
from dual;

Tomando las siguientes acciones, usted puede reducir al mínimo los gastos indirectos usados al procesar una tabla externa:

Utilice la cláusula PARALLEL cuando cree la tabla. Este valor indica el número de procesadores que serán utilizados para procesar los datafiles y dividirá los archivos en porciones para que puedan ser procesadas por separado.
Utilice datatypes en Oracle, ya que emparejará las cualidades físicas de los datos, eliminando la conversión de datos costosa.
Utilice valores fijos cuando sea posible, incluyendo:
Fixed-width character sets
Fixed-length fields
Fixed-length records

La cláusula RECORDS FIXED se enumera bajo parámetros del acceso y requiere la definición de campos. En el siguiente ejemplo , la línea de datos tiene 40 bytes de largo, más un byte para la nueva línea. Los nombres de campo deben ser iguales que los nombres de la columna a los cuales corresponden.

RECORDS FIXED 41
FIELDS
(
emp_first_name char(20)
emp_last_name char(20)
)

Utilice delimitadores de un solo caracter, y utilice los mismos juegos de caracteres que utiliza la base de datos.
Reduzca al mínimo los rechazos puesto que Oracle realiza más I/O para cada.

Usos Prácticos

Las tablas externas tienen muchos usos prácticos, que pondré en dos categorías: proceso de negocio y administración de la base de datos.

Desde el punto de vista de negocio-proceso, las tablas externas responden a una necesidad vital en un ambiente de dato-almacenamiento, en el cual la extracción, transformación, y los procesos de la carga son comunes. Las tablas externas hacen innecesario que los usuarios creen las tablas temporales durante estos procesos, reduciendo el espacio requerido y el riesgo de trabajos fallados. Las tablas externas se pueden utilizar en vez de las tablas temporales y de utilidades como SQL*Loader. También proporcionan una manera fácil para que las compañías puedan cargar diversas fuentes de información en Oracle desde Excel, ACT!, o Access.

En cuanto a la administración de la base de datos . Si deseo supervisar esos archivos que reviso con mas frecuencia como alert.log e init.ora desde un prompt SQL>. Entonces puedo utilizar comandos del SQL para preguntar datos del archivo y de especificar criterios para un proceso más sofisticado. Un ejemplo de crear una tabla externa para revisar al registro alerta (alert.log ) :

create directory BDUMP AS 'd:oracle9iadminPRODdump';
create table alert_log (text varchar2(200))
organization EXTERNAL (
type oracle_loader
default directory BDUMP
access parameters
( records delimited by newline
badfile 'dave.bad'
logfile 'dave.log'
fields terminated by ' '
)
location ('PRODALRT.LOG')
)
reject limit unlimited;

Listado 3: La opinión de DBA_EXTERNAL_LOCATIONS.

SQL> desc dba_external_locations;
Name Null? Type
----------------------------------------- -------- ----------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
LOCATION VARCHAR2(4000)
DIRECTORY_OWNER CHAR(3)
DIRECTORY_NAME VARCHAR2(30)

Administración De la Base de datos

Es importante saber qué vistas en Oracle contienen la información que pertenece a las tablas externas. La vista DBA_TABLES demuestra las tablas externas y tiene un valor de 0 para PCT_FREE, PCT_USED, INI_TRANS, y MAX_TRANS. El resto de las columnas del almacenaje en la vista son nulas. Los scripts que utiliza esta vista para determinar problemas debe ser actualizado para tener acceso a DBA_EXTERNAL_TABLES. Esta vista contiene todos los parámetros que usted especificó cuando usted creó la tabla externa.

Otra vista útil es DBA_EXTERNAL_ LOCATIONS, que proporciona una manera rápida de ver qué archivos están registrados en la base de datos (véase el listado 3).

---
Dave Moore es arquitecto del producto en BMC Austin, TX. He's also a Java and PL/SQL developer, Oracle DBA and author of Oracle Utilities by Rampant Tech Press