Arbeiten mit External Tables
von Ulrike Schwinn, ORACLE Deutschland GmbH

External Tables sind seit Oracle 9i das geeignete Mittel, Datenmengen, die in Flat Files ausserhalb der Datenbank gespeichert sind, im READ-ONLY Zugriff zu haben. In die Datenbank selbst werden keine Daten geladen, sondern nur die Metadaten der External Tables gespeichert. Voraussetzung ist dabei die Beschreibbarkeit der Flat Datei im "Oracle Access" Treiber-Format wie SQL*Loader und Datapump.
So können grosse Datenmengen aus Flat Dateien über External Tables einfach selektiert, über einen Join ausgewertet oder sortiert werden wie mit normalen Tabellen. CREATE VIEW oder CREATE SYNONYM-Operationen sind ebenso möglich. Zu beachten ist allerdings, dass keine DML-Operationen oder Indizierung erlaubt sind.

Darüberhinaus ist es seit Oracle 10g möglich, das Resultat eines beliebigen SELECT-Statements mit der External Tables Technik zu entladen. Seit 11g Release 2 können die Flat Dateien für die External Tables Nutzung vorab mit anderen Programmen aufbereitet werden (pre-processing). So können auch Daten, die nicht im Oracle Treiber Format vorliegen, als Eingabe verwendet werden. Die Nutzung von External Tables bietet somit die Möglichkeit, Daten performant zu laden und zu entladen.

Der folgende Tipp zeigt an verschiedenen Beispielen die gesamte Bandbreite - bis einschliesslich 11g Release 2 Features - der Funktionen der External Tables-Technik auf.

Als Einstieg steht die folgende Datei mit Namen prodDelta im logischen Directory data_dir auf dem Server zur Verfügung. Ein Auschnitt der Datei sieht dabei folgendermassen aus: 

50013|5MP Telephoto Digital Camera|5MP Telephoto Digital Camera|Cameras|2044|Cam
eras|Photo|204|Photo|1|U|P|1|obsolete|899.99|899.99|TOTAL|1||01-JAN-98||A|
50014|17" LCD w/built-in HDTV Tuner|17" LCD w/built-in HDTV Tuner|Monitors|2035|
Monitors|Peripherals and Accessories|203|Peripherals and Accessories|1|U|P|1|ord
ered|999.99|999.99|TOTAL|1||01-JAN-98||A|
...
Wie leicht zu erkennen ist, ist das Format dieser Datei prodDelta im SQL*Loader einfach zu beschreiben, so dass wir die Daten als External Table zur Verfügung stellen können.

Folgendes Listing erzeugt eine External Table. Zuerst werden dabei die relationalen Spalten angegeben, danach mit dem Parameter TYPE der Access Treiber (hier oracle_loader), der das Beschreibungsformat festlegt. Der Ort und Name der Datei wird durch den Parameter DIRECTORY und LOCATION festgelegt. Der Abschnitt ACCESS PARAMETERS legt die SQL*Loader-Beschreibung fest. 
CREATE TABLE products_delta
(PROD_ID NUMBER(6),PROD_NAME VARCHAR2(50),PROD_DESC VARCHAR2(4000),PROD_SUBCATEGORY VARCHAR2(50),
PROD_SUBCATEGORY_ID NUMBER,PROD_SUBCATEGORY_DESC VARCHAR2(2000),PROD_CATEGORY VARCHAR2(50),
PROD_CATEGORY_ID NUMBER,PROD_CATEGORY_DESC VARCHAR2(2000),PROD_WEIGHT_CLASS NUMBER(2),
PROD_UNIT_OF_MEASURE VARCHAR2(20),PROD_PACK_SIZE VARCHAR2(30),SUPPLIER_ID NUMBER(6),
PROD_STATUS VARCHAR2(20),PROD_LIST_PRICE NUMBER(8,2),PROD_MIN_PRICE NUMBER(8,2),PROD_TOTAL VARCHAR2(13),
PROD_TOTAL_ID NUMBER,PROD_SRC_ID NUMBER,PROD_EFF_FROM DATE,PROD_EFF_TO DATE,PROD_VALID CHAR(1))
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY "|")
LOCATION ('prodDelta')
)
REJECT LIMIT UNLIMITED NOPARALLEL;

Das neuerzeugte Objekt products_delta lässt sich nun in verschiedenen Statements verwenden z.B. als Grundlage eines MERGE-Statements wie das folgende Beispiel zeigt. 
MERGE INTO products t USING products_delta s ON (t.prod_id = s.prod_id)
WHEN MATCHED THEN 
UPDATE SET t.prod_list_price=s.prod_list_price,
t.prod_min_price=s.prod_min_price
WHEN NOT MATCHED THEN INSERT ("PROD_ID", "PROD_NAME", "PROD_DESC", "PROD_SUBCATEGORY", 
	"PROD_SUBCATEGORY_ID", "PROD_SUBCATEGORY_DESC", 
	"PROD_CATEGORY", "PROD_CATEGORY_ID", 
	"PROD_CATEGORY_DESC", "PROD_WEIGHT_CLASS", 
	"PROD_UNIT_OF_MEASURE", "PROD_PACK_SIZE", 
	"SUPPLIER_ID", "PROD_STATUS", 
	"PROD_LIST_PRICE", "PROD_MIN_PRICE", "PROD_TOTAL", 
	"PROD_TOTAL_ID", "PROD_SRC_ID", "PROD_EFF_FROM", "PROD_EFF_TO", 
	"PROD_VALID")
VALUES (s.PROD_ID, s.PROD_NAME, s.PROD_DESC,s.PROD_SUBCATEGORY, 
	s."PROD_SUBCATEGORY_ID", s."PROD_SUBCATEGORY_DESC", 
	s."PROD_CATEGORY", s."PROD_CATEGORY_ID", 
	s."PROD_CATEGORY_DESC", s."PROD_WEIGHT_CLASS", 
	s."PROD_UNIT_OF_MEASURE", s."PROD_PACK_SIZE", 
	s."SUPPLIER_ID", s."PROD_STATUS", 
	s."PROD_LIST_PRICE", s."PROD_MIN_PRICE", 
	s."PROD_TOTAL", s."PROD_TOTAL_ID", 
	s."PROD_SRC_ID", s."PROD_EFF_FROM", 
	s."PROD_EFF_TO", s."PROD_VALID")
WHERE s.prod_status <> 'obsolete';
Allerdings sind einige Einschränkungen zu berücksichtigen. So sind folgende Konstrukte nicht möglich:
  • DML-Operationen
  • Indizes
  • Virtuelle Spalten
  • Constraints
  • Temporäre Tabellen
  • Spezielle Datentypen wie Objekttypen, XMLTYPE oder LONG

    • Da die Anzahl der Zeilen unbekannt ist, kann auch keine Schätzung mit ESTIMATE_PERCENT bei der Statistikberechnung erfolgen. So erhält man Statistiken auf External Tables nur über die Nutzung der COMPUTE-Option, wie das folgende Beispiel zeigt: 
    execute dbms_stats.gather_table_stats('US','PRODUCTS_DELTA',estimate_percent=>null);
    Mit Oracle Version 10g sind External Tables um das Schreiben/Entladen von Daten erweitert worden. Dabei werden die Daten aus der Datenbank in eine binäre Datei geschrieben und können dann einfach und schnell auf einem anderen System zur Verfügung gestellt werden. Wie bei der ursprünglichen Verwendung von External Tables ist auch hier der Zugriff auf ein logisches Datenbank-Directory notwendig. Das Entladen der Daten aus der Datenbank erfolgt dann mit der erweiterten External Table-Syntax wie das folgende Listing zeigt. Im Unterschied zur 9i-Syntax wird bei der Angabe der TYPE-Komponente der Wert oracle_datapump statt oracle_loader angegeben. Das SELECT-Statement bestimmt dann den Inhalt der Daten. 
    CREATE TABLE scott.ext_emp_dept 
ORGANIZATION EXTERNAL 
(
TYPE oracle_datapump 
DEFAULT DIRECTORY data_dir 
LOCATION ('emp_dept.exp')
) 
REJECT LIMIT UNLIMITED 
AS SELECT e.ename, d.dname FROM scott.dept d JOIN scott.emp e USING (deptno);
    Auf diese Weise ist eine binäre Datei mit Namen emp_dept.exp im logischen Directory data_dir erzeugt worden, die auf das Zielsystem kopiert werden kann. Um nicht nur die Daten, sondern auch das passende CREATE TABLE-Statement auf dem Zielsystem verfügbar zu haben, eignet sich die Nutzung des Package DBMS_METADATA. 
    CONNECT scott/tiger
SET HEADING OFF LONG 10000 PAGESIZE 1000
SELECT dbms_metadata.get_ddl('TABLE', 'EXT_EMP_DEPT') FROM dual;
    Das Ergebnis der Abfrage sieht dann folgendermassen aus: 
      CREATE TABLE "SCOTT"."EXT_EMP_DEPT"
   (    "ENAME" VARCHAR2(10),
        "DNAME" VARCHAR2(14))
   ORGANIZATION EXTERNAL
    ( 
      TYPE oracle_datapump
      DEFAULT DIRECTORY data_dir
      LOCATION ('emp_dept.exp')
    )
   REJECT LIMIT UNLIMITED
    Nach Ausführung des generierten CREATE TABLE-Statements und dem zur Verfügungstellen der binären Datei im entsprechenden logischen Directory auf dem Zieldatenbanksystem, stehen die Daten sofort für Abfragen zur Verfügung.

    Neu in 11g Release 2 ist die Möglichkeit, Dateien die nicht im SQL*Loader Format vorliegen, zuerst einen Preprozessor-Vorgang durchlaufen zu lassen. Dies ist möglich mit der neuen zusätzlich Option PREPROCESSOR. Nehmen wir in unserem Beispiel an, dass die Datei prodDelta nur gezippt als prodDelta.gz verfügbar ist. Der Preprocessor Vorgang wird also den gzip Befehl mit entsprechender Option ausführen. Unser Beispiel von oben sieht dann folgendermassen aus. 
    CREATE TABLE products_delta_gz
(PROD_ID NUMBER(6),PROD_NAME VARCHAR2(50),PROD_DESC VARCHAR2(4000),PROD_SUBCATEGORY VARCHAR2(50),
PROD_SUBCATEGORY_ID NUMBER,PROD_SUBCATEGORY_DESC VARCHAR2(2000),PROD_CATEGORY VARCHAR2(50),
PROD_CATEGORY_ID NUMBER,PROD_CATEGORY_DESC VARCHAR2(2000),PROD_WEIGHT_CLASS NUMBER(2),
PROD_UNIT_OF_MEASURE VARCHAR2(20),PROD_PACK_SIZE VARCHAR2(30),SUPPLIER_ID NUMBER(6),
PROD_STATUS VARCHAR2(20),PROD_LIST_PRICE NUMBER(8,2),PROD_MIN_PRICE NUMBER(8,2),PROD_TOTAL VARCHAR2(13),
PROD_TOTAL_ID NUMBER,PROD_SRC_ID NUMBER,PROD_EFF_FROM DATE,PROD_EFF_TO DATE,PROD_VALID CHAR(1))
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS 
(
  RECORDS DELIMITED BY NEWLINE 
  PREPROCESSOR exec_dir:'gzip1.sh'
  FIELDS TERMINATED BY "|"
)
LOCATION ('prodDelta.gz')
)
REJECT LIMIT UNLIMITED NOPARALLEL;

    Zu beachten sind folgende Informationen:
  • Alle Programmaufrufe mit Argumentspezifikationen wie zum Beispiel 'gzip -c' müssen als Shell-Programm verfügbar sein.
  • Der vollständige Pfad des ausführenden Programms ist notwendig.
  • Es wird auf STDOUT geschrieben werden, damit die Verarbeitung stattfinden kann.
  • Das Privileg EXECUTE auf dem logischen Directory exec_dir, in dem die Preprozessor-Skripte liegen, ist erforderlich.

    • Zusätzlich sind natürlich einige Sicherheitsaspekte zu berücksichtigen. Aus Sicherheitsgründen sollten zum Beispiel separate Verzeichnisse für die Datendateien, die Preprozessor-Skripte, und Log- bzw. Bad-Dateien erzeugt werden. WRITE- und EXECUTE- Privilegien sollten nicht gleichzeitig auf einem Directory existieren. Das CREATE ANY DIRECTORY- bzw. DROP ANY DIRECTORY- Privileg sollte nur sparsam verwendet werden. Mehr Informationen dazu finden Sie im Oracle Database Security Guide im Kapitel 10.
    Um unser Beispiel kurz zu halten, sind nur die folgenden beiden logischen Directories und Zugriffsrechte erteilt worden. 
    CREATE DIRECTORY data_dir AS '/home/oracle/data';

GRANT read, write ON DIRECTORY data_dir TO sh;

CREATE DIRECTORY exec_dir AS 'home/oracle/skripte';

GRANT execute ON DIRECTORY exec_dir TO sh;
    Die Log-und Bad-Dateien werden dabei in das logische Directory data_dir geschrieben. Dies sollte man aus Sicherheitgründen natürlich in separate logische Directories legen. Das Shell-Programm gzip1.sh liegt im logischen Directory exec_dir und sieht folgendermassen aus: 
    /bin/gzip -d -c $1
    $1 bezieht sich dabei auf die entspechende Datendatei - in unserem Fall prodDelta.gz.

    Die Data Dictionary View USER_EXTERNAL_TABLES gibt generell Auskunft über die verwendeten External Tables. Das folgende Beispiel zeigt einen Überblick über die verwendeten Beispiele: 
    SELECT table_name, type_name, default_directory_name, reject_limit, access_parameters
FROM dba_external_tables;
    Das Ergebnis sieht dann folgendermassen aus: 
    TABLE_NAME                     TYPE_NAME                                
------------------------------ ------------------------------            
DEFAULT_DIRECTORY_NAME         REJECT_LIMIT                                     
------------------------------ ------------------------------         
ACCESS_PARAMETERS                                                               
-------------------------------------------------------------
PRODUCTS_DELTA_GZ              ORACLE_LOADER                            
DATA_DIR                          UNLIMITED                                        
RECORDS DELIMITED BY NEWLINE                                                    
PREPROCESSOR exec_dir: 'gzip1.sh'                                                  
FIELDS TERMINATED BY                                                            
                                                                                
PRODUCTS_DELTA                 ORACLE_LOADER
DATA_DIR                       UNLIMITED
RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY "|"

EXT_EMP_DEPT                   ORACLE_DATAPUMP
DATA_DIR                       UNLIMITED

    Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...

    Zurück zur Community-Seite