Tips

It is a common requirement to upload daily comma separated value files (csv) files from external system into an RDBMS such as Oracle.   Challenges are faced when importing data from external files like excel and comma separated value files.  Problems can vary from trapping errors and working with complex structures of Oracle loader. In this context, Oracle has introduced external table features that help us to import data from excel or any other file format in easy or quickly manner.    A solution to these and other challenges is to use external tables.

BENEFITS OF EXTERNAL TABLES

a) Queries of external tables complete very quickly even though a full table scan id required with each access
b)  Queries can be generated from inside the database using SQL. So validation checks can be run as SELECT statements without the need for a holding table.
b) External tables can be joined with each other or to standard tables
c) Respective data will not use any additional space on the data base storage server

LIMITATIONS ON EXTERNAL TABLES

External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. By providing Oracle with metadata describing an external table, Oracle is able to expose the data in the external table as if it were data residing in a regular database table. The external data can be queried directly and in parallel using SQL.

The following is a listing of some of the limitations to external tables:

• The metadata of external tables is created using the SQL CREATE TABLE ... ORGANIZATION EXTERNAL statement.
• The actual data resides outside the database in OS files, hence the clause ORGANIZATION EXTERNAL organization.
• The OS files are identified inside the database through a logical directory defining the OS physical directory where they are located.
• The data is read only.
• You cannot perform any DML operations (INSERT, UPDATE, or DELETE), nor create indexes on external tables.
• You can, although, create views and synonyms for external tables.
• The external table can be queried and joined directly, in parallel using the SQL statement SELECT.
• The ANALYZE statement is not supported for gathering statistics for external tables. The DBMS_STATS package, although, can be used for gathering statistics for external tables.