RE: Tricky SQL Question -- Solved

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Thu, 06 Mar 2003 11:38:48 -0800
Message-ID: <F001.00562429.20030306113848@fatcity.com>

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-----
Sent: Thursday, March 06, 2003 1:16 PM
To: 'ORACLE-L_at_fatcity.com'
----- 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

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



text/plain attachment: ESPN_Disclaimer.txt

Received on Thu Mar 06 2003 - 13:38:48 CST