DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=from:to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:in-reply-to:x-mimeole:thread-index;
        b=WLieUynZk+mcpqcCCM4TFP8+JRf6izw8mZl70HziOwKfGfWkcvlcIGUUnqmBYGfbO+ACTWeimc8irPWbTOdf5Fm+J2UKZhPtyxAvcviDkqkpEJyBBRynBaGLOgCxL5eAk4RIKEUmju0AlAeUhQow9Bak3qjvtF8P0fV8+feES/A=
From: "Connor McDonald" <mcdonald.connor@gmail.com>
To: <oracle-l@freelists.org>
Subject: RE: Paralellizing Pl/sql inserts
Date: Wed, 23 Jan 2008 22:06:51 +0900
Message-ID: <004e01c85dc0$d4353560$0b00a8c0@dell600m>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
In-Reply-To: <901CE9A27473416CABACB807651DE527@KenPC>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: mcdonald.connor@gmail.com


-----Original Message-----
From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]
On Behalf Of Ken Naim
Sent: Wednesday, 23 January 2008 9:04 AM
To: oracle-l@freelists.org
Subject: Paralellizing Pl/sql inserts


I have a process that is currently running at a rate of 4 million inserts
per hour. In selects from a cursor that returns 65 million records bulk
collects 500 at a time and forall inserts them into another table with 1.5
billion rows. I cannot do an insert as select as the record may already
exists. I use the save exceptions clause to allow the code to continue
processing but I don't care about which record turn the unique constraint
violation. The 1.2 billion row table is partitioned but the data can go into
any partition. The 65 million row table is not partitioned

<snip>

I am open to any suggestions but my question is how can I parallelize the
process into multiple (24-48) threads.

Thanks,
Ken 

--
http://www.freelists.org/webpage/oracle-l


Some things to possibly consider:

- Use SQL and LOG ERRORS clause to catch your exceptions
- Why "insert-select-where-not-exists" to avoid your duplicates
- Are your indexes global or local ?  If local, you might get some nice
parallelism by selecting from your 65mill table rows based on the partition
they will end up in, and then hammer against multiple partitions in parallel

hth
connor

--
http://www.freelists.org/webpage/oracle-l