Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Parallel Application Processing

Parallel Application Processing

From: Billy <vslabs_at_onwe.co.za>
Date: 8 Aug 2005 03:09:28 -0700
Message-ID: <1123495768.957381.281320@f14g2000cwb.googlegroups.com>


Suggestions appreciated on how to tackle parallel application processing on a single large Oracle table.

The PL/SQL application processes needs to grab n rows for update, process, update and commit them. Several of these application processes need to be run in parallel due to the data volumes.

The problem is ensuring that each parallel application process grabs a different set of rows for processing so that there are no contention - with one app process either waiting for another or raising an exception as its attempt to grab rows hit already locked rows.

I've tried the following basic SQL construct: select * from table sample(m) where rownum < n for update nowait skip locked

The idea is to grab any random sample of data, and from that attempt to grab at most n rows that are not locked, and lock them for updating.

If the sample is not used, then each process will/could hit the same rows which means the 1st one may get n rows to lock and the 2nd process will find nothing as it will simply skip n rows. A method is thus needed to randomly identify a m set of rows and grab n rows for updating from it.

Does this method make sense? Are there better methods to consider?

Thanks.

--
Billy
Received on Mon Aug 08 2005 - 05:09:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US