Other Optimizations - Oracle Database 11gR2 Performance Tuning - page 365

There were no errors while loading the file, and that there was a huge performance gain,
resulting in a total elapsed time of 1.13 seconds, about half the time elapsed in step 3.
In step 8 we drop MY_IMPORT_DATA table and in step 9 we create a directory object in the
database, which points to the /home/oracle/ file-system folder. We need a directory object
to create, in step 10, an external table. This object points to data outside the database,
that is data not stored inside Oracle database datafiles—providing the same interface to
manipulate the data, as if it was a regular database table.
When we create MY_IMPORT_DATA in step 10, we define the structure of the table—as it will
be seen from the database and depending on how the data is structured in the external file.
We specify the name of the data file to use and the directory object required to find the data
file in the file-system.
After creating the external table, in step 11, we query against it to test whether we can easily
access the data in the file as it was available in a regular database table.
We can see the result obtained from this operation in the following screenshot:
In step 11 we drop the MY_IMPORT_DATA table and in step 14 we drop the directory object
TEST_DATA_DIR .
In step 12 we use the command line utility Export Data Pump ( expdp ) to export the data and
metadata related to the CUSTOMERS and COUNTRIES tables in the test_exp.dmp file.