Re: Tricky SQL Question

> "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:jonathan_at_jlcomp.demon.co.uk]
> 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" <ORACLE-L_at_fatcity.com>
> 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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).