SQL Loader

6/15/99, A. Chang

 

The document consists discussions on two different tools. The first section of the document highlights the functions of SQL*Loader and its potential implementations in our DSS environment. The second portion of the document introduces the COPY command in SQL*Plus and its possible usage in the DSS environment.

 

Introduction

In the current DSS environment, BCP is one of the methods for loading external data into and out of Sybase database tables. In Oracle, the function of loading external data into an Oracle database tables is provided by SQL*Loader. SQL*Loader provides powerful mechanism for loading data from external files into tables of an Oracle database, however, it does not support "loading out" data from Oracle to external files. Researches are undertaken to find a suitable tool for loading data out of Oracle database tables. This document, however, deals strictly with the analysis of functions, applications, and implementations of SQL*Loader in the future Oracle Decision Support environment.

This document is a summary based on the following materials:

  1. Oracle in 21 Days (basic introductory book available at the DBA team).
  2. Oracle 8 – A Beginners’ Guide (also available at the DBA team).
  3. Oracle Technet (http://technet.oracle.com/doc/server.815/a67792/):

Description of Tool/Technique

SQL*Loader loads non-Oracle data into Oracle database. Non-Oracle datafiles to be loaded can be in fixed record format, variable record format, or stream record format. SQL*Loader is character set aware, supports two loading "paths" – Conventional and Direct, and provides error reporting. Specifically, SQL*Loader:

As shown in the following diagram, SQL*Loader takes the "Loader Control File" which specifies and controls the behavior of SQL*Loader and one or more datafiles for processing, then output the datafile in Oracle database, a log file, a bad file, and potentially a discard file.

 


Possible Applications:

SQL*Loader’s function in our future Decision Support environment is to load non-Oracle data, such as mainframe or Sybase files, into Oracle.

Implementation:

For DSSers who are familiar with the concepts of BCP or Fast BCP, the two methods of SQL*Loader – Conventional and Direct Path Loads – are easy to grasp.

 

Conventional Loading Methods

Overview:

  1. Executes SQL INSERT statement(s) to populate table(s).

Direct Path Load**

 

 

  1. Eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files.
  2. Does not compete with users for database resources so it can usually load data at near disk speed.
  3. Faster than Conventional Path Load; however, there are restrictions on Direct Path Load.

When to use it:

  1. When accessing an indexed table concurrently with the load, or when applying inserts or updates to a non-indexed table concurrently with the load.
  2. When Loading data with SQL*Net across heterogeneous platforms. (Cannot load data using a direct path load over Net8 unless both systems belong to the same family of computers, and both are using the same character set; performance is an issue due to network overhead.)
  3. When loading data into a clustered table. (Direct Path Load does not support loading of clustered tables.)
  4. When loading a relatively small number of rows into a large index table. (More efficient than Direct Path Load if the index is very large and the number of new keys is very small.)
  5. When loading a relatively small number of rows into a large table with referential and column-check integrity constraints. (More efficient than Direct Path Load.)
  6. When SQL functions are applied to data fields.

 

 

  1. When SQL*Loader has exclusive write access to the table and exclusive read-write access to any indexes and faster speed of loading is desired.
  2. When loading large amount of data.
  3. When loading data in PARALLEL for maximum performance.
  4. When loading data in character set that cannot be supported in the current session or when the conventional conversion to the dataset character set would cause errors.

**Restrictions:

  1. When using Direct Path Load, tables cannot be clustered, and tables should have no active transaction pending.
  2. Cannot have SQL strings in the control file.
  3. Cannot load object columns, LOBs, VARRAYs, or nested tables.
  4. Cannot specify OIDs for object tables with system-generated OIDs.
  5. Cannot specify SIDs.
  6. Cannot load REF or BFILE columns.
  7. Cannot load physical records larger than 64k.

Evaluations:

SQL*Loader is definitely a tool to be used in loading non-Oracle datafiles into an Oracle database. The differences in restrictions, advantages, and disadvantages between Conventional and Direct Path Loads shown above provides programmers with basic guidelines on using SQL*Loader. As the pros and cons of each method are provided in the numerous documentations from Oracle, the next step is to try out the SQL*Loader for some of the Sybase and Mainframe tables or files currently used in our DSS environment to test the syntax. Foreseeable problems with Oracle SQL*Loader for use in our DSS environment are not apparent at this point, however, through testing it is hoped that problems will be discovered and addressed.

In addition, further research attention needs to be given to loading data OUT of Oracle database.

 

 

COPY Command in SQL*Plus

The COPY command allows users to use SQL*Plus as a conduit for transferring data between two Oracle databases and between tables on the same database. With the COPY command, users can copy data between databases in the following ways: