Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Use SQL*Loader with Named Pipe

Posted by Dylan Wan on June 24, 2008

Typically using SQL*Loader assumes that a flat file will be used as the input. The file will need to be created and generated before the SQL*Loader can take the data from the file and load the data into Oracle. The performance can be improved and the disk space can be saved if you use named pipe with SQL*Loader.

What is Named Pipe?

Named Pipe is a way to pass the information between two processes. It was originated from Unix and now also supported in XP. [1][2]

A pipe can be used like a file, but it does not take any disk space.

How to create a Named Pipe?

To create a named pipe, you can use this command under unix shell.

% mkfifo mypipe.dat

The command mkfifo means “make a first in first out buffer”.

The mypipe.dat appears like a file, but it is actually just a memory buffer allocated by UNIX.

There seems no equivalent command in XP. You can only create a named pipe programatically. Also, pipe name in Window always like “\\.\pipe\mypipe.dat” [3]

How to use a named pipe?

To write into the the pipe, you can simply use this command:

% cat file.txt > mypipe.dat &

This command will put the data from the file.txt to the pipe.

At this moment, the mypipe.dat memory space has not been written. the process is actually blocked and waiting for another process to consume and read the data.

You can use a program to write into a pipe. It is also possible to use the ftp client to save the data into the pipe. By writing the remote file directly into a pipe, the data from the remote machine immediately loaded into the database.

Use Named Pipe in SQL*Loader

You can then use SQL*Loader to read the data from the pipe. You use the mypipe.dat just like a file. In a separate process of shell, you can run this command:

% SQLLDR …. data=mypipe.dat &

If you issue this command before you write to the pipe. The SQLLDR command will be hanging and waiting for the data arrived from the other process.

The beauty of this approach is that the process of writing the data into mypipe.dat, like the cat command above, does not write to a file, but directly write into memory and thus to the SQL*Loader. Since no physical file is acutally created, the data aren’t duplicated on disk which conserves disk space, and since no I/O is taking place, this results in a very fast process.

References

[1] Introduction to Interprocess Communication Using Named Pipes

[2] Wikipedia: Named pipe

[3] Named Pipe in MSDN

Advertisements

5 Responses to “Use SQL*Loader with Named Pipe”

  1. veeolina said

    thanks..
    it helps me a lot

  2. Thank you for puting the time in to publish this info. I found it very useful. If you are ever interested in link building directory list then please contact me….

    […]Use SQL*Loader with Named Pipe « Dylan’s BI Study Notes[…]…

  3. performance tuning…

    […]Use SQL*Loader with Named Pipe « Dylan’s BI Study Notes[…]…

    • shenlnda said

      We use pipe/sqlldr, but the performance is poor. Do you have any idea to improve the performance? Thanks.

  4. great dividend stocks…

    […]Use SQL*Loader with Named Pipe « Dylan’s BI Study Notes[…]…

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s