Okay,
I cracked it ... if you are interested, read on ... it is not very optimal, but close to what I want. To me 8 streams is standard, so you'd see 8 as hardcoded. Also I found that
select sum(obj_last_analyze_time)/8 from statistics_info
/
was about 8425 (i.e. ~ 85 seconds).
So I wrote this not-so-dynamic sql
select group_id, sum(tm1), count(*)
from(
SELECT obj_owner, obj_name, tm1,
case when roll_sum <= 8400*1 then 1 else
case when roll_sum <= 8400*2 then 2 else
case when roll_sum <= 8400*3 then 3 else
case when roll_sum <= 8400*4 then 4 else
case when roll_sum <= 8400*5 then 5 else
case when roll_sum <= 8400*6 then 6 else
case when roll_sum <= 8400*7 then 7 else 8
end
end
end
end
end
end
end group_id
FROM (SELECT rnum, obj_owner, obj_name, tm1,
SUM (tm1) OVER
(ORDER BY rnum RANGE UNBOUNDED PRECEDING) roll_sum
FROM (SELECT ROWNUM rnum, obj_owner, obj_name, tm1
FROM (SELECT obj_owner ,obj_name,obj_last_analyze_time tm1
FROM statistics_info
ORDER BY obj_last_analyze_time)))
) group by group_id
/
The output is as follows ...
"GROUP_ID" "TOT_TIME" "TOT_TABLES"
---------- ------------ ------------
1 8397 1755
2 8387 667
3 8204 135
4 7984 20
5 8954 7
6 6928 3
7 7113 2
8 11438 1
I'll probably make it dynamic enough ... inside my package ...
Cheers
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: Jamadagni, Rajendra
Sent: Thursday, March 06, 2003 1:16 PM
To: '[EMAIL PROTECTED]'
Subject: RE: Tricky SQL Question
----- 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
*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1
