Databases Reference
In-Depth Information
External Tables
When working in data warehouse environments, the
Extraction
—
Transformation
—
Loading
(
ETL
) cycle frequently requires the user to load information from external
sources in plain file format, or perform data transfers among Oracle database in
a proprietary format. This requires the user to create control files to perform the
load. As the format of the source data regularly doesn't fit the one required by the
Data Warehouse, a common practice is to create stage tables that load data into the
database and create several queries that perform the transformation from this point
on, to take the data to its final destination.
A better approach, would be to perform this transformation 'on the fly' at load time.
That is what External Tables are for. They are basically external files, managed either
by means of the SQL*Loader or the data pump driver, which from the database
perspective can be seen as if they were regular read only tables.
This format allows the user to think about the data source as if the data was already
loaded into its stage table. This lets the user concentrate on the queries to perform
the transformation, thus saving precious time during the load phase.
External Tables can serve not only as improvements to the ETL process, but also as
a means to manage database environments, and a means of reducing the complexity
level of data management from the user's point of view.
Search WWH ::
Custom Search