Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: has anyone used threads to parallelize bulk loads?

Re: has anyone used threads to parallelize bulk loads?

From: Tim Gorman <>
Date: Sat, 30 Aug 2003 13:39:26 -0800
Message-ID: <>

Or forget about 3GL programming altogether and just query 9i external tables in parallel and pass the results into an INSERT /*+ APPEND PARALLEL */? Combine it all with PL/SQL pipelined table functions and you have parallel ETL -- ³extraction² (using 9i external tables), ³transformation² (using PL/SQL pipelined table functions), and ³loading² (using INSERT APPEND) -- in a single SQL statement. Also, don¹t forget about 9i multi-table INSERT statements, conditional INSERT statements, and of course the MERGE (a.k.a ³up-sert²) statements. There is a tremendous amount of flexibility and new features around ETL built into Oracle these days...

I¹ve been programming ³C² for 20 years and there are very few reasons to write another line of ³C² code these days. I still find reasons, but only rarely. And never related to ETL...

on 8/30/03 1:54 PM, Ryan at wrote:

> I find this useful when I have large loads with alot of small to medium sized
> DML statements. Instead of running them serially I use DBMS_JOB and break them
> up into pieces.
> DBMS_JOB is limiting in that it can only do 36 at once per instance. If I want
> to do more, Id have to do it outside the database. I have read about unix
> 'pthreads'. These appear to have the same wait,notify,synchronize interface
> that java has. Im also on Solaris.
> Has anyone done anything like this? Any features on Solaris that I can use? I
> know solaris has some built-in low level C functions to handle locking(such as
> a mutex script). Any idea of some sources I can use to look this up?
> I dont need this for work right now, but I may need it in the future, so I
> want to play around with it. Or do I have to take this to a unix sys admin
> forum? If so anyone know any good ones?
> Threading interfaces seem to be very similiar across platforms. In oracle you
> achieve the same thing with dbms_job,dbms_lock,and dbms_alert.
> anyone got any scripts or experience with this? This is not essential. Im just
> playing around.

Please see the official ORACLE-L FAQ:
Author: Tim Gorman

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Sat Aug 30 2003 - 16:39:26 CDT

Original text of this message