RE: Tricky SQL Question

Raj,

I may not be offering information useful in solving your specific stats problem. If that's the case, Undskyld. However, this information is certainly useful.

This link to the asktom website contains a method for dividing up large tables into ranges of rowids so that multiple sessions can efficiently process different pieces of the same object.

Last month, I had the opportunity to see Mr. Kyte demonstrate this during one of his presentations.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:763874375 0722

-Steve

-----Original Message-----
Sent: Thursday, March 06, 2003 11:44 AM
To: Multiple recipients of list ORACLE-L

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
> -------------------------------------------------------------
> 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 !!
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Steven_Galli_at_Dell.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).