SQL*Loader is one of the most widely used Oracle utilities. It provides the flexibility, robustness, and performance required by applications involving data to be loaded into database table(s) from flat files. SQL*Loader is the heart of the most ETL applications in production today.

SQL*Loader can be executed either in the conventional path (the default), which provides tremendous flexibility and power in manipulating data during load, or in the direct path, which at the cost of sacrificing a few of the flexibilities of the conventional path provides outstanding load performance. The focus of this article is to discuss the restrictions on direct path SQL*Loader in Oracle8i, and the enhancements in Oracle9i that helped eliminate some of the restrictions.

This article doesn’t go into the details of describing individual features or syntax of conventional and direct path SQL*Loader. For a detailed discussion on SQL*Loader, you can refer to Oracle Utilities Manual for Oracle8i and Oracle9i, or the book SQL*Loader: The Definitive Guide (published by O’Reilly & Associates).

Restrictions on Direct Path SQL*Loader in Oracle8i

In this article, we will focus on two major restrictions on Direct Path SQL*Loader in Oracle8i:

Homogeneous

Upto Oracle8i, direct path load requires that the connection between the client (the machine where you invoke the sqlldr command) and the server (the machine where the database is running) must be homogeneous with respect to the following:

• The client and server must be running the same operating system software. The version of the OS must the same on both the machines.
• The client and server Oracle installations must both be either 32-bit or 64-bit.

For example, if you try to run direct path SQL*Loader from a Solaris Client and connect to a database on a HP-UX server, you will get the following error:

Error checking path homogeneity
ORA-02352: Direct path connection must be homogeneous

Moreover, even if your client and server are on the same box (identical operating system), the Oracle client software and the Oracle server software must both be either 32-bit or 64-bit. If this doesn’t match, you will get the error mentioned above.

The second limitation is not much difficult to overcome. Since the client is already on the same box as the server, all you need to do is set the PATH of your sqlldr executable to the same as that of the database server. However, the first limitation may be very restrictive in some situations. To be able to get superior performance of direct path load, you may need to copy the file over to the database server (or another box with the same OS and Oracle version installed). This file transfer may be a painful process depending upon network security in your installation, and due to the time consumed in the transfer.

SQL Expressions

One of the most important features of the (conventional path) SQL*Loader is its ability to transform data while loading. You can use most of the supported SQL functions in the SQL*Loader to perform data transformation while loading. For example, you can use ROUND function to round the input data. However, with Oracle8i, you can’t use SQL functions with direct path load.

The following example shows a control file that uses the ROUND function to round the input data:

LOAD
INFILE SALES.DAT
TRUNCATE INTO TABLE SALES_HISTORY
FIELDS TERMINATED BY ','
(
PART_ID,
STORE_ID,
SALE_DATE,
QUANTITY "ROUND(:QUANTITY)"
)

If you try to use this control file for direct path load in Oracle8i, you will get the following error:

SQL*Loader-417: SQL string (on column QUANTITY) not allowed in direct path.

This error forces us to use conventional path load.

Restrictions Eliminated in Oracle9i

The limitations discussed above can be very restrictive to some applications. SQL*Loader users have been asking for these limitations to be eliminated from the Direct Path SQL*Loader for quite some time now. Oracle has enhanced SQL*Loader in Oracle9i, and removed these restrictions.

Homogeneous

In Oracle9i, you can use direct path SQL*Loader

• Across diverse platforms.
• Across any variations of 32-bit and 64-bit between the client and the server.

For example, you can now run direct path SQL*Loader from a Solaris Client and connect to a database on a HP-UX server. Moreover, you can run direct path load from a 32-bit Oracle client to a 64-bit Oracle server, and vice versa.

SQL Expressions

Oracle9i allows usage of SQL expressions in the control file while using direct path load. You can use the control file containing the ROUND SQL function (discussed in the previous section) with direct path loader in Oracle9i. This provides great advantage as you get the best of both worlds – the flexibility of using SQL expressions in the control file to transform data while loading, and the performance of direct path load.

A note of caution to the readers — I have observed that in some situations, using an SQL expression in conjunction with direct path load actually slows down the load even to the extent that the direct path load executes slower than the corresponding conventional path load. Therefore, it is always advisable that while using SQL expressions you should test and benchmark your load application with both the options (conventional and direct path) before putting it in production. (Author’s Note: On further research, it was found that “direct path load running slower than conventional load when using SQL expressions” is a known Oracle bug, and as per Oracle Support, it is fixed in 10.1.)

Conclusion

In this article, I discussed the two main limitations of direct path SQL*Loader in Oracle8i, namely, the need for the database connection to be homogeneous and the inability to use SQL expressions for transforming data while loading. These restrictions have been eliminated in Oracle9i. We will discuss other enhancements to direct path SQL*Loader (e.g., Resumable load, Multi-threading in direct path load, Date cache,and so on) in a continuation to this article.

--

Sanjay Mishra is a database administrator, application architect and software developer with more than twelve years of industry experience. He has worked extensively in the areas of database architecture, database management, backup / recovery, performance tuning, Oracle Parallel Server, parallel execution, partitioning. He has authored three books published by O'Reilly & Associates (Oracle Parallel Processing, Oracle SQL Loader: The Definitive Guide, Mastering Oracle SQL). Presently, he works as a database architect at Dallas-based i2 Technologies. Sanjay can be reached at smishra_tech@yahoo.com.