External Table Enhancements

Along with the introduction of Oracle 10g Data Pump, the external table feature, which was introduced with Oracle 9i, also offers new improvements for data movement within the Oracle server. Prior to Oracle 10g, external table support was limited to only read-only access. This limitation meant you could load data only from a properly formatted external table.

With Oracle 10g, external tables can now be also written to through the use of the new ORACLE_DATAPUMP access driver. The major benefit of this new functionality is that it allows you to unload and transform large volumes of data into platform-independent flat files and use them for either data propagation or storage. Although neither DML (data manipulation language) operations nor index creation are allowed on an external table, you can use the CREATE TABLE AS SELECT... command to populate the external table. When populating an external table using the Data Pump access driver, the data is converted from its Oracle internal representation into an equivalent proprietary format using the Direct Path API (DPAPI). Because of this new DPAPI proprietary format, you cannot use the new external table population operation with any external table created using the ORACLE_LOADER access driver. Although the Data Pump export and import utilities support the new external table access driver, using this technology for complex ETL (extract, transform, and load) purposes should be handled manually with external table commands.

When running large external operations, you can use a parallel-population operation to unload your data. You can initiate parallelism on your external table operation by using the PARALLEL parameter. Unlike with a parallel query from an external table, the DOP (degree of parallelism) of a parallel-population operation is constrained by the number of concurrent files that can be written to by the access driver. Therefore, there is no longer the one parallel-execution server writing to one file any one point during your parallel-population operation.

Following is an example of using the new external table–population operation. In this example, an external table (TS_DOC_EXT) is created using the new external table–population operation (in parallel) from a join between the DOCUMENTS and DOCUMENT_COMPONENTS tables.

create table TS_DOC_EXT (doc_id, doc_title, doc_slug, doc_date)
ORGANIZATION EXTERNAL (
                         type ORACLE_DATAPUMP
                         default directory ts_dir
                         location ('ts01.exp','ts02.exp', 'ts03.exp', 'ts04.exp')
                      )
PARALLEL
as
select d.document_id, d.document_title, c.document_slug, d.create_date
from documents d, document_components c
where d.document_id = c.doc_document_id
and d.create_date > sysdate – 14;