Oracle External Tables

External Tables in 9i

In version 9, Oracle introduced the concept of external tables.  These objects provide a way to access data stored outside of the database with a SQL statement.  These also provide an alternative to SQL*Loader, given their simplicity and comparable performance.  Data loads can be performed by simple statements like:

SQL> create table load_table as select * from external_table;

When executed, the data would be loaded from an operating system data file into LOAD_TABLE.  

Summary

The data copy utilities are some of the most useful utilities within Oracle.  This chapter gave a high level overview of each utility, with an emphasis on optimizing them in specific environments.  Each utility supports a number of options, many more than could be detailed in this chapter. 

Performance is always an issue with the data copy utilities.  The best way to optimize performance of any of these utilities is to eliminate any steps that are not required.  In addition, it is clear from the performance benchmarks that the direct option should be utilized for any utility that supports it.   However, it is important to note that direct path exports are different than direct path SQL*Loader executions.  A direct path export simply skips the SQL evaluation buffer, whereas a direct path load writes directly to the Oracle datafiles. 

The best export time achieved for the benchmark was 41 seconds.  The best import time was 67 seconds, for a total export/import elapsed time of 108 seconds.  The copy command copied the same table data to a new table in the same database in 85 seconds using SQL*Net!  The copy command is indeed a viable option for copying data from one database to another. 

SQL*Loader is hard to beat for fast data loads, especially when using direct path loads and the unrecoverable option.  If only SQL*Loader worked with an export file, it would greatly enhance the entire unload/load process.