Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!f14g2000cwb.googlegroups.com!not-for-mail From: "Billy" Newsgroups: comp.databases.oracle.server Subject: Parallel Application Processing Date: 8 Aug 2005 03:09:28 -0700 Organization: http://groups.google.com Lines: 32 Message-ID: <1123495768.957381.281320@f14g2000cwb.googlegroups.com> NNTP-Posting-Host: 198.54.206.91 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1123495774 22964 127.0.0.1 (8 Aug 2005 10:09:34 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 8 Aug 2005 10:09:34 +0000 (UTC) User-Agent: G2/0.2 Complaints-To: groups-abuse@google.com Injection-Info: f14g2000cwb.googlegroups.com; posting-host=198.54.206.91; posting-account=wp5kwAwAAAAK-35tPJYPGFtR9KGfcge6 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:249074 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