> "Jamadagni, Rajendra" wrote: > > Thanks Jonathan, > > I'd like to assign the tables to a group, but need to do that > periodically. Also what I do is load all tables that belong to a group > in a pl/sql table (bulk updates/bulk collects). That's why I don't > want to do read-from-table ... do-action > > BTW this doesn't have to be optimal ... I am just trying to split the > load ... > > Raj > ------------------------------------------------------------- > Rajendra dot Jamadagni at espn dot com > Any views expressed here are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art !! > > -----Original Message----- > From: Jonathan Lewis [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 06, 2003 12:44 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Tricky SQL Question > > Thinking back to university days, I think this > was called the knapsack problem, and at the > time there was no algorithm guaranteed to > give an optimal solution. > > If there is no simple non-procedural algorithm - > how about a strategy that simply allows each > slave to take the longest task that has not yet > been run until there are no jobs left to run ? > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Coming soon one-day tutorials: > Cost Based Optimisation > Trouble-shooting and Tuning > Indexing Strategies > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > ____UK_______March 19th > ____UK_______April 8th > ____UK_______April 22nd > > ____USA_(FL)_May 2nd > > Next Seminar dates: > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > ____USA_(CA, TX)_August > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: 06 March 2003 16:49 > > > Hi all, > > > > I have a tricky situation ... I have a table > > > > columns are > > owner varchar2(), > > name varchar2(), > > ana_tm number > > > > ana_tm represents how much time it took to perform statistics > collection for > > owner.name value. the number ranges from 0 to about 12000 right now, > > and is > > subject to change. and say sum(ana_tm) over the table is say X. > > > > What I'd like to have is split this data into say N groups (Let's > say 8), > > so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in > this > > example). > > > > What I need is a way in SQL to splice the table list in eight groups > > so that > > when I run a parallel 8 stream analyze, they all roughly take same > amount of > > time. I tried width_bucket() and it doesn't give me things that I > need. It > > assumes a linear distribution, which I do not have. > > > > Is this possible to do in SQL only? > > > > Thanks in advance, yes, you can go crazy with syntax, it is 9202. > > Raj
Raj, I have taken good note that 'elegant' is not one of your requirements :-). select decode(sign(8 - mod(rownum - 1, 14)), 1, mod(rownum - 1, 14), 7 - mod(rownum - 1, 7)) "GROUP", x.owner, x.name from (select owner, name from your_table order by ana_tm desc) x; This should more or less work, even on 7.2. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).