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.
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
To learn more about these techniques,
see the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it directly from the
publisher and get instant access to the code
depot of utilities scripts.