External Table

Oracle External Table allows Oracle Database to query data that are stored outside of  the database in flat files as if they were inside a database. External tables are used to load data into a table, during Extract Transform and Load (ETL)  process or to compare between two files. SQL Loader will be your great choice if you are loading data in a table that will require index.

External table will not allow any DDL instead you can drop and recreate them. You can’t perform any DML operation on external tables other than SELECT. You will need to put a little thought on how you are going to query it before designing an external table.

Oracle_loader and Oracle_Datapum are two driver that are used for reading/writing data from Operating System (OS).  By default external table uses Oracle Loader driver. External table is like any other object in Oracle therefore, you can have views and synonyms on it.

My recent experience using External tables. We have a Java application that compares two files and email the difference to the client. The code was written in java where the code is reading the file line-by-line  in order to get the difference. This process is freaky fast for a file with 1000 records.

This process was working great until the file size increased to 7 millions records, this is a lot to read line by line, isn’t it? This application become slow that it impacted customer Service Level Agreement (SLA) and the depending jobs/processes. The client is not happy and has asked developer to make this lightning fast like before.

This is where DBA can shine and use their magic:  I worked with the developer, created two External tables that reads data from the compare files and ran the MINUS query on those tables. The use of External table reduced the processing time to under 5 minutes for 7 millions records. This is how the DBA can make a developer, his boss and his clients happy just by utilizing Oracle  silly external table.

CREATE 
OR 
replace directory ext_tab_data AS '/data';

The schema user should have read and write access to the folder
GRANT CREATE ANY directory TO test_user1; -- make dir access 
GRANT READ ON ext_tab_data TO test_user1; -- read access  
GRANT WRITE ON ext_tab_date TO test_user1; --write access  

Check the directory if they exist already

SELECT * 
FROM   dba_directories; 

--Sample Countries1.txt
 ENG,England,English
 SCO,Scotland,English
 IRE,Ireland,English
 WAL,Wales,Welsh

 ---Sample Countries2.txt
FRA,France,French
GER,Germany,German
USA,Unites States of America,English

 
--Create External Table - Syntax
CREATE TABLE countries_ext
  (
    country_code     VARCHAR2(5),
    country_name     VARCHAR2(50),
    country_language VARCHAR2(50)
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_data ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ( country_code CHAR(5), country_name CHAR(50), country_language CHAR(50) ) ) LOCATION ('Countries1.txt’') ---- File to read from
  )
  PARALLEL 5 reject limit unlimited;

---Test Your  External table to see if you can query from it

select  * from countries_ext;

11gR2 Updates: Oracle 11g Release 2 introduced the PREPROCESSOR clause to identify a directory object and script used to process the files before they are read by the external table. This feature was backported to 11gR1 (11.1.0.7). The PREPROCESSOR clause is especially useful for reading compressed files, since they are unzipped and piped straight into the external table process without ever having to be unzipped on the file system.

CREATE OR REPLACE DIRECTORY exec_dir AS '/bin';
CREATE TABLE countries_ext
  (
   country_code     VARCHAR2(5),
   country_name     VARCHAR2(50),
   country_language VARCHAR2(50)
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_data ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE PREPROCESSOR exec_dir:'zcat' FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ( country_code CHAR(5), country_name CHAR(50), country_language CHAR(50) ) ) LOCATION ('Countries1.txt.gz','Countries2.txt.gz')

  )
  PARALLEL 5 REJECT LIMIT UNLIMITED;

This is it!
I encourage you all to share your experience using external table use and how you become the Hero at the job? If you need to know more on this topic, I encourage you to visit Oracle Doc on External Table.