15 The Fastest Way to Load Data from Flat Files

Scenario

The weekly sales data of a company is stored in a flat file called weeklysales.txt. This data needs to be loaded into a table in the Warehouse Builder workspace.

An example data set of the source file is defined as:

SALESREP, MONTH,PRODUCT_ID,W1_QTY,W2_QTY,W3_QTY.W4_QTY
100,JAN02,3247,4,36,21,42
101,JUL02,3248,24,26,4,13

Each record in the file contains details of the quantity sold by each sales representative in each week of a month. This data needs to be loaded into the Warehouse Builder workspace.

Solution

Warehouse Builder provides two methods of loading data stored in flat files. The methods are:

Solution 1: Using SQL*Loader
Solution 2: Using External Tables

Table 15-1 lists the differences between using SQL*Loader and external tables in the procedure used to load data from flat files.

Table 15-1 Differences Between SQL*Loader and External Tables

SQL*Loader	External tables
Requires multiple steps to enable data transformation. You must load the data into a staging area and then transform the data in a separate step.	The transformation and loading of data is combined into a single SQL DML statement. There is no need to stage the data temporarily before inserting it into the target table.

SQL*Loader

External tables

Requires multiple steps to enable data transformation.

You must load the data into a staging area and then transform the data in a separate step.

The transformation and loading of data is combined into a single SQL DML statement.

There is no need to stage the data temporarily before inserting it into the target table.

SQL *Loader

SQL*Loader is an Oracle tool that enables you to load data from flat files into tables in an Oracle Database. In Warehouse Builder, use the Flat File operator to load data using SQL*Loader.

SQL*Loader is the only method you can use to load data from a flat file into a database whose version is Oracle 8i Release 3 (8.1.7) or earlier.

When To Use SQL*Loader

Use SQL*Loader to load data from a flat file if:

The database version is Oracle 8i Release 3 (8.1.7) or earlier.
No complex transformations are required on the input data.

External Tables

An external table is a database object that enables you to access data stored in external sources. External tables allow flat files to have the same properties as database tables (read-only) and extend the power of SQL to reading flat files. You can also query, join, transform, and constrain the flat file data before loading it into the database.

Note:

External tables are supported only from Oracle9i onwards.

In Warehouse Builder, use the external table object and the Mapping External Table operator to load data from a flat file into the workspace. The design benefit of using external tables is that it extends additional database features to a flat file. By using external tables instead of flat files, you can apply complex transformations to the data in flat files that were previously only used for relational files.

Benefits of Using External Tables

Provides faster access to flat files because the external data can be accessed in parallel during a load.
Can perform heterogeneous joins with database tables or other external tables.

When To Use External Tables

To transform flat file data before loading into the database
To perform complex transformations, such as joins and aggregations, on the flat file data before loading it into the Warehouse Builder workspace

External tables can be faster when the following conditions are met:

The hardware has multiple processors.
The flat file is large (has many records).

When these conditions are met, the benefits of parallel processing will outperform SQL*Loader processing.

Solution 1: Using SQL*Loader

Use SQL*Loader to load data from the flat file into the target table. Warehouse Builder provides the Flat File operator that enables you to load data into a target table using SQL*Loader.However, the transformations that you can perform on data loaded using a flat file operator are limited to SQL*Loader transformations only. You can use only the following mapping operators when you use a Flat File operator as a source:

Filter operator
Constant operator
Data Generator operator
Mapping Sequence operator
Expression operator
Transformation operator

To load data using SQL*Loader, create a mapping that uses the mapping flat file operator to represent the source data. Map the output of this operator directly to the target table.

Solution 2: Using External Tables

Use external tables to load data from the flat file weeklysales.txt into the workspace table SALES_DATA. Create a mapping that contains the External Table operator as the source. This External Table operator must be bound to the external table object that you create referring to the flat file. Map the output of the external table operator directly to the target table.

Mapping to Load Data Using External Tables

In the mapping that loads the data from the flat file, use the External Table operator to represent the source data. Map the output of the External Table operator to the target table SALES_DATA.